1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
|
<div align=center>
<picture align=center>
<source media="(prefers-color-scheme: dark)" srcset="https://assets.timescale.com/docs/images/tigerdata-gradient-white.svg">
<source media="(prefers-color-scheme: light)" srcset="https://assets.timescale.com/docs/images/tigerdata-gradient-black.svg">
<img alt="TigerData logo" >
</picture>
</div>
<div align=center>
<h3>TimescaleDB is a PostgreSQL extension for high-performance real-time analytics on time-series and event data</h3>
[](https://docs.tigerdata.com/)
[](https://timescaledb.slack.com/archives/C4GT3N90X)
[](https://console.cloud.timescale.com/signup)
</div>
## Install TimescaleDB
Install from a Docker container:
1. Run the TimescaleDB container:
```bash
docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb-ha:pg17
```
1. Connect to a database:
```bash
docker exec -it timescaledb psql -d "postgres://postgres:password@localhost/postgres"
```
See [other installation options](https://docs.tigerdata.com/self-hosted/latest/install/) or try [Tiger Cloud](https://docs.tigerdata.com/getting-started/latest/) for free.
## Create a hypertable
TimescaleDB's hypercore is a hybrid row-columnar store that boosts analytical query performance on your time-series and event data, while reducing data size by more than 90%. This keeps your analytics operating at lightning speed and ensures low storage costs as you scale. Data is inserted in row format in the rowstore and converted to columnar format in the columnstore based on your configuration.
```sql
-- Create a hypertable, with the columnstore from the hypercore engine
-- "time" as partitioning column and a segment by on location
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL,
humidity DOUBLE PRECISION NULL
)
WITH (
timescaledb.hypertable,
timescaledb.partition_column='time',
timescaledb.segmentby='location'
);
```
See more:
- [About hypertables](https://docs.tigerdata.com/use-timescale/latest/hypertables/)
- [API reference](https://docs.tigerdata.com/api/latest/hypertable/)
- [About columnstore](https://docs.tigerdata.com/use-timescale/latest/compression/about-compression/)
- [Enable columnstore manually](https://docs.tigerdata.com/use-timescale/latest/compression/manual-compression/)
- [API reference](https://docs.tigerdata.com/api/latest/compression/)
## Insert and query data
Insert and query data in a hypertable via regular SQL commands. For example:
- Insert data into a hypertable named `conditions`:
```sql
INSERT INTO conditions
VALUES
(NOW(), 'office', 70.0, 50.0),
(NOW(), 'basement', 66.5, 60.0),
(NOW(), 'garage', 77.0, 65.2);
```
- Return the number of entries written to the table conditions in the last 12 hours:
```sql
SELECT
COUNT(*)
FROM
conditions
WHERE
time > NOW() - INTERVAL '12 hours';
```
See more:
- [Query data](https://docs.tigerdata.com/use-timescale/latest/query-data/)
- [Write data](https://docs.tigerdata.com/use-timescale/latest/write-data/)
## Create time buckets
Time buckets enable you to aggregate data in hypertables by time interval and calculate summary values.
For example, calculate the average daily temperature in a table named `conditions`. The table has a `time` and `temperature` columns:
```sql
SELECT
time_bucket('1 day', time) AS bucket,
AVG(temperature) AS avg_temp
FROM
conditions
GROUP BY
bucket
ORDER BY
bucket ASC;
```
See more:
- [About time buckets](https://docs.tigerdata.com/use-timescale/latest/time-buckets/about-time-buckets/)
- [API reference](https://docs.tigerdata.com/api/latest/hyperfunctions/time_bucket/)
- [All TimescaleDB features](https://docs.tigerdata.com/use-timescale/latest/)
- [Tutorials](https://docs.tigerdata.com/tutorials/latest/)
## Create continuous aggregates
Continuous aggregates make real-time analytics run faster on very large datasets. They continuously and incrementally refresh a query in the background, so that when you run such query, only the data that has changed needs to be computed, not the entire dataset. This is what makes them different from regular PostgreSQL [materialized views](https://www.postgresql.org/docs/current/rules-materializedviews.html), which cannot be incrementally materialized and have to be rebuilt from scratch every time you want to refresh it.
For example, create a continuous aggregate view for daily weather data in two simple steps:
1. Create a materialized view:
```sql
CREATE MATERIALIZED VIEW conditions_summary_daily
WITH (timescaledb.continuous) AS
SELECT
location,
time_bucket(INTERVAL '1 day', time) AS bucket,
AVG(temperature),
MAX(temperature),
MIN(temperature)
FROM
conditions
GROUP BY
location,
bucket;
```
1. Create a policy to refresh the view every hour:
```sql
SELECT
add_continuous_aggregate_policy(
'conditions_summary_daily',
start_offset => INTERVAL '1 month',
end_offset => INTERVAL '1 day',
schedule_interval => INTERVAL '1 hour'
);
```
See more:
- [About continuous aggregates](https://docs.tigerdata.com/use-timescale/latest/continuous-aggregates/)
- [API reference](https://docs.tigerdata.com/api/latest/continuous-aggregates/create_materialized_view/)
## Want TimescaleDB hosted and managed for you? Try Tiger Cloud
[Tiger Cloud](https://docs.tigerdata.com/getting-started/latest/) is the modern PostgreSQL data platform for all your applications. It enhances PostgreSQL to handle time series, events, real-time analytics, and vector search—all in a single database alongside transactional workloads. You get one system that handles live data ingestion, late and out-of-order updates, and low latency queries, with the performance, reliability, and scalability your app needs. Ideal for IoT, crypto, finance, SaaS, and a myriad other domains, Tiger Cloud allows you to build data-heavy, mission-critical apps while retaining the familiarity and reliability of PostgreSQL. See [our whitepaper](https://docs.tigerdata.com/about/latest/whitepaper/) for a deep dive into Tiger Cloud's architecture and how it meets the needs of even the most demanding applications.
A Tiger Cloud service is a single optimized 100% PostgreSQL database instance that you use as is, or extend with capabilities specific to your business needs. The available capabilities are:
- **Time-series and analytics**: PostgreSQL with TimescaleDB. The PostgreSQL you know and love, supercharged with functionality for storing and querying time-series data at scale for real-time analytics and other use cases. Get faster time-based queries with hypertables, continuous aggregates, and columnar storage. Save on storage with native compression, data retention policies, and bottomless data tiering to Amazon S3.
- **AI and vector**: PostgreSQL with vector extensions. Use PostgreSQL as a vector database with purpose built extensions for building AI applications from start to scale. Get fast and accurate similarity search with the pgvector and pgvectorscale extensions. Create vector embeddings and perform LLM reasoning on your data with the pgai extension.
- **PostgreSQL**: the trusted industry-standard RDBMS. Ideal for applications requiring strong data consistency, complex relationships, and advanced querying capabilities. Get ACID compliance, extensive SQL support, JSON handling, and extensibility through custom functions, data types, and extensions.
All services include all the cloud tooling you'd expect for production use: [automatic backups](https://docs.tigerdata.com/use-timescale/latest/backup-restore/backup-restore-cloud/), [high availability](https://docs.tigerdata.com/use-timescale/latest/ha-replicas/), [read replicas](https://docs.tigerdata.com/use-timescale/latest/ha-replicas/read-scaling/), [data forking](https://docs.tigerdata.com/use-timescale/latest/services/service-management/#fork-a-service), [connection pooling](https://docs.tigerdata.com/use-timescale/latest/services/connection-pooling/), [tiered storage](https://docs.tigerdata.com/use-timescale/latest/data-tiering/), [usage-based storage](https://docs.tigerdata.com/about/latest/pricing-and-account-management/), and much more.
## Check build status
|Linux/macOS|Linux i386|Windows|Coverity|Code Coverage|OpenSSF|
|:---:|:---:|:---:|:---:|:---:|:---:|
|[](https://github.com/timescale/timescaledb/actions/workflows/linux-build-and-test.yaml?query=workflow%3ARegression+branch%3Amain+event%3Aschedule)|[](https://github.com/timescale/timescaledb/actions/workflows/linux-32bit-build-and-test.yaml?query=workflow%3ARegression+branch%3Amain+event%3Aschedule)|[](https://github.com/timescale/timescaledb/actions/workflows/windows-build-and-test.yaml?query=workflow%3ARegression+branch%3Amain+event%3Aschedule)|[](https://scan.coverity.com/projects/timescale-timescaledb)|[](https://codecov.io/gh/timescale/timescaledb)|[](https://www.bestpractices.dev/projects/8012)|
## Get involved
We welcome contributions to TimescaleDB! See [Contributing](https://github.com/timescale/timescaledb/blob/main/CONTRIBUTING.md) and [Code style guide](https://github.com/timescale/timescaledb/blob/main/docs/StyleGuide.md) for details.
## Learn about TigerData
TigerData is the fastest PostgreSQL for transactional, analytical and agentic workloads. To learn more about the company and its products, visit [tigerdata.com](https://www.tigerdata.com).
|
