Introduction

TimescaleDB adds support for time-series data storage and manipulation to PostgreSQL.

Key points:

  • 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.

    1
2

    # 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

    1

    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

    1
2

    INSERT INTO conditions(time, location, temperature, humidity)
VALUES (NOW(), 'room ceiling', 70.0, 50.0);

  • As with normal queries

    1

    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;

  • The result: Time-bucket function example