Time-series data in Postgres with TimescaleDB

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.

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

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

  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: