timescaledb: Time-Series data

timescaledb is a Postgres extension designed for improved handling of time-series data. It provides a scalable, high-performance solution for storing and querying time-series data on top of a standard Postgres database.

timescaledb uses a time-series-aware storage model and indexing techniques to improve performance of Postgres in working with time-series data. The extension divides data into chunks based on time intervals, allowing it to scale efficiently, especially for large data sets. The data is then compressed, optimized for write-heavy workloads, and partitioned for parallel processing. timescaledb also includes a set of functions, operators, and indexes that work with time-series data to reduce query times, and make data easier to work with.

Enable the extension#

Usage#

To demonstrate how timescaledb works, let's consider a simple example where we have a table that stores temperature data from different sensors. We will create a table named "temperatures" and store data for two sensors.

First we create a hypertable, which is a virtual table that is partitioned into chunks based on time intervals. The hypertable acts as a proxy for the actual table and makes it easy to query and manage time-series data.

1
2
3
4
5
6
7
create table temperatures (  time timestamptz not null,  sensor_id int not null,  temperature double precision not null);select create_hypertable('temperatures', 'time');

Next, we can populate some values

1
2
3
4
5
6
7
8
9
10
11
12
insert into temperatures (time, sensor_id, temperature)values    ('2023-02-14 09:00:00', 1, 23.5),    ('2023-02-14 09:00:00', 2, 21.2),    ('2023-02-14 09:05:00', 1, 24.5),    ('2023-02-14 09:05:00', 2, 22.3),    ('2023-02-14 09:10:00', 1, 25.1),    ('2023-02-14 09:10:00', 2, 23.9),    ('2023-02-14 09:15:00', 1, 24.9),    ('2023-02-14 09:15:00', 2, 22.7),    ('2023-02-14 09:20:00', 1, 24.7),    ('2023-02-14 09:20:00', 2, 23.5);

And finally we can query the table using timescaledb's time_bucket function to divide the time-series into intervals of the specified size (in this case, 1 hour) averaging the temperature reading within each group.

1
2
3
4
5
6
7
8
9
10
select    time_bucket('1 hour', time) AS hour,    avg(temperature) AS average_temperaturefrom    temperatureswhere    sensor_id = 1    and time > NOW() - interval '1 hour'group by    hour;

Resources#

• Official timescaledb documentation