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

-- Enable the "timescaledb" extension
create extension timescaledb with schema extensions;

-- Disable the "timescaledb" extension
drop extension if exists timescaledb;

Even though the SQL code is create extension, this is the equivalent of "enabling the extension". To disable an extension you can call drop extension.

It's good practice to create the extension within a separate schema (like extensions) to keep your public schema clean.

Usage

To demonstrate how timescaledb works, consider an example where we have a table that stores temperature data from different sensors. 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.

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

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.

select
    time_bucket('1 hour', time) AS hour,
    avg(temperature) AS average_temperature
from
    temperatures
where
    sensor_id = 1
    and time > NOW() - interval '1 hour'
group by
    hour;

Resources

• Official timescaledb documentation