TimescaleDB Data Model

TimescaleDB is a PostgreSQL extension. Tables, types, indexes, joins, transactions and the whole PostgreSQL toolchain work unchanged; it adds automatic time partitioning, columnar compression and incremental materialised aggregates.

Hypertables

A hypertable is a regular table that TimescaleDB partitions into chunks by time, transparently.

CREATE TABLE readings (
    ts         timestamptz NOT NULL,
    device_id  bigint      NOT NULL,
    metric     text        NOT NULL,
    value      double precision NOT NULL
);
 
SELECT create_hypertable('readings', by_range('ts', INTERVAL '1 day'));
 
CREATE INDEX ON readings (device_id, ts DESC);

Chunk interval is the main sizing decision: a chunk plus its indexes should fit comfortably in memory, because recent chunks are the working set. Too large and inserts touch cold memory; too small and the planner deals with excessive chunk counts.

SELECT show_chunks('readings', older_than => INTERVAL '7 days');
SELECT chunk_name, range_start, range_end,
       pg_size_pretty(total_bytes) AS size
FROM chunks_detailed_size('readings') ORDER BY range_start DESC LIMIT 5;

Compression

Compression converts a chunk from row storage to a columnar layout, typically reducing size by an order of magnitude.

ALTER TABLE readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id, metric',
    timescaledb.compress_orderby   = 'ts DESC'
);
 
SELECT add_compression_policy('readings', compress_after => INTERVAL '7 days');

compress_segmentby should list the columns you filter on — they remain directly queryable. compress_orderby determines ordering within a compressed batch, which drives the compression ratio.

Continuous aggregates

An incrementally maintained materialised view over a hypertable — the mechanism for downsampling.

CREATE MATERIALIZED VIEW readings_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket,
       device_id,
       metric,
       avg(value)  AS avg_value,
       max(value)  AS max_value,
       count(*)    AS samples
FROM readings
GROUP BY bucket, device_id, metric;
 
SELECT add_continuous_aggregate_policy('readings_hourly',
    start_offset => INTERVAL '3 days',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

end_offset keeps the aggregate away from the most recent, still-changing data. Real-time aggregation can combine the materialised part with live raw data at query time, so recent points are not missing from results.

Aggregates can be stacked — hourly from raw, daily from hourly — which is how you keep years of history at low resolution and cost.

Retention

SELECT add_retention_policy('readings', drop_after => INTERVAL '90 days');

Retention drops whole chunks, so it is a metadata operation rather than a mass delete. Keep the continuous aggregates longer than the raw data — the point of downsampling is that history survives the raw retention window.