TimescaleDB adds support for time-series data storage and manipulation to PostgreSQL.
- It’s open-source;
- engineered up from PostgreSQL and packaged as a PostgreSQL extension;
- full SQL support;
- designed to make SQL scalable for time-series data;
- offers a paid managed on-cloud version.
How to install
Download and install TimescaleDB
Load the extension into the postgres conf file.
# in my case it's located in /opt/homebrew/var/postgres/postgresql.conf shared_preload_libraries = 'timescaledb'
Load the extension if it’s not present
CREATE EXTENSION timescaledb;
TimescaleDB has also a tuning tool to make the TimescaleDB database perform its best based on the host’s resources such as memory and number of CPUs.
How to use
Create a time-series table by creating a normal table and then converting it to an hypertable(an abstraction created by TimescaleDB to handle everything time-series)
1 2 3 4 5 6 7 8 9 10
-- We start by creating a regular SQL table CREATE TABLE conditions ( time TIMESTAMPTZ NOT NULL, location TEXT NOT NULL, temperature DOUBLE PRECISION NULL, humidity DOUBLE PRECISION NULL ); -- Then we convert it into a hypertable that is partitioned by time SELECT create_hypertable('conditions', 'time');
Data mutations can be done normally like they would on postgreSQL
INSERT INTO conditions(time, location, temperature, humidity) VALUES (NOW(), 'room ceiling', 70.0, 50.0);
As with normal queries
SELECT * FROM conditions ORDER BY time DESC LIMIT 100;
TimescaleDB adds additional functions for time-series analysis, like time-bucket
1 2 3 4 5 6 7 8
SELECT time_bucket('2 minutes', time) AS two_min, location, COUNT(*), MAX(temperature) AS max_temp, MAX(humidity) AS max_hum FROM conditions WHERE time > NOW() - interval '30 minutes' GROUP BY two_min, location ORDER BY two_min DESC, max_temp DESC;