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.
Supabase projects come with TimescaleDB Apache 2 Edition. Functionality only available under the Community Edition is not available.
Enable the extension
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- 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.
_10create table temperatures (_10 time timestamptz not null,_10 sensor_id int not null,_10 temperature double precision not null_10);_10_10select create_hypertable('temperatures', 'time');
Next, we can populate some values
_12insert into temperatures (time, sensor_id, temperature)_12values_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.
_10select_10 time_bucket('1 hour', time) AS hour,_10 avg(temperature) AS average_temperature_10from_10 temperatures_10where_10 sensor_id = 1_10 and time > NOW() - interval '1 hour'_10group by_10 hour;
Resources
- Official
timescaledb
documentation