TimescaleDB: Hypertables & Chunks

TimescaleDB is an open-source time-series database built as an extension on top of PostgreSQL. It adds automatic partitioning and query optimizations for time-stamped data while remaining fully SQL-compatible -- existing PostgreSQL tooling, ORMs, and drivers work unmodified.

Creating a Hypertable

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE sensor_data (
  time        TIMESTAMPTZ NOT NULL,
  device_id   TEXT NOT NULL,
  temperature DOUBLE PRECISION,
  humidity    DOUBLE PRECISION
);

-- Converts the regular table into a hypertable, automatically
-- partitioned into chunks by the `time` column
SELECT create_hypertable('sensor_data', 'time');

-- Queries use plain SQL -- TimescaleDB transparently routes them
-- to the relevant underlying chunks
SELECT * FROM sensor_data WHERE time > now() - INTERVAL '1 day';

Why Chunking Matters

A query filtered to a recent time range only needs to scan the handful of chunks covering that window, not the entire historical dataset. New writes concentrate in the current, relatively small chunk -- keeping insert performance stable as the table grows, unlike one enormous unpartitioned table whose ever-growing indexes get progressively more expensive to maintain.

Multi-Dimensional Partitioning

-- Partition on both time and device_id, useful when queries
-- commonly filter by both a time range and a specific device
SELECT create_hypertable(
  'sensor_data',
  'time',
  partitioning_column => 'device_id',
  number_partitions => 4
);

time_bucket() for Custom Interval Grouping

-- Groups into arbitrary custom intervals -- date_trunc() alone
-- only supports fixed units like hour/day/month
SELECT
  time_bucket('5 minutes', time) AS bucket,
  avg(temperature) AS avg_temp
FROM sensor_data
WHERE time > now() - INTERVAL '1 day'
GROUP BY bucket
ORDER BY bucket;