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