Database

timescaledb: Time-Series data

timescaledb is a PostgreSQL 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 PostgreSQL database.

timescaledb uses a time-series-aware storage model and indexing techniques to improve performance of PostgreSQL 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

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. Search for "timescaledb" and 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.


_10
create table temperatures (
_10
time timestamptz not null,
_10
sensor_id int not null,
_10
temperature double precision not null
_10
);
_10
_10
select create_hypertable('temperatures', 'time');

Next, we can populate some values


_12
insert into temperatures (time, sensor_id, temperature)
_12
values
_12
('2023-02-14 09:00:00', 1, 23.5),
_12
('2023-02-14 09:00:00', 2, 21.2),
_12
('2023-02-14 09:05:00', 1, 24.5),
_12
('2023-02-14 09:05:00', 2, 22.3),
_12
('2023-02-14 09:10:00', 1, 25.1),
_12
('2023-02-14 09:10:00', 2, 23.9),
_12
('2023-02-14 09:15:00', 1, 24.9),
_12
('2023-02-14 09:15:00', 2, 22.7),
_12
('2023-02-14 09:20:00', 1, 24.7),
_12
('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.


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

Resources