Home

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 timestamp 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#