TimescaleDB
Postgres extended for time series: hypertables, automatic chunking, columnar compression.
TimescaleDB is a Postgres extension that turns ordinary tables into hypertables — automatically partitioned into time-based chunks, each with columnar-compressed segments. Full Postgres SQL applies (joins, CTEs, extensions), while chunk interval, compression, and retention policies manage time-series lifecycle automatically.
Hypertables and chunks
A hypertable is a virtual table partitioned into chunks by time (and optional space dimension). Each chunk is a real Postgres table with its own indexes — queries prune to the chunks their time range touches.
Native columnar compression
Recent chunks stay row-based for fast inserts; a policy compresses older chunks into columnar form — typically 10-20× smaller, with segment-by reordering for query speed.
Policies, not cron jobs
Compression, retention, downsampling, and aggregation refresh run as scheduled policies inside the database — lifecycle management becomes data definition.
Continuous aggregates
Materialized views over time buckets that refresh incrementally as new data lands — raw data and rollups coexist with different retention.
Why not just Postgres?
Plain Postgres handles time series until indexes bloat and inserts scatter across the heap. TimescaleDB chunks by time so each insert hits the current chunk, indexes stay small, and old chunks compress columnar — the partitioning a DBA would hand-build, automated.
Because chunks are ordinary tables, everything Postgres works: joins against relational reference data, PostGIS, full-text search, extensions. Time-series specialization without leaving the ecosystem.
SELECT time_bucket('5 minutes', time) AS bucket,
avg(temperature) AS avg_temp
FROM conditions
WHERE time > NOW() - INTERVAL '1 day'
GROUP BY bucket
ORDER BY bucket;
The lifecycle is data definition
Compression at 7 days, retention at 90 days, continuous aggregates refreshed hourly: three policies replace a rack of cron scripts. Each runs inside the engine with transactional guarantees.
Diagrams
Inserts land in the newest chunk under standard Postgres WAL; compression and retention policies run transactionally against whole chunks.
[caption] Chunks are real Postgres tables — pruning is plan-time chunk exclusion.
Inserts land in the newest row-based chunk; scheduled policies compress older chunks columnar and drop chunks past retention.
Raw sensor points and the continuous-aggregate rollup served from compressed chunks.
[caption] Illustrative shapes for the continuous-aggregate pattern — not measured data.
Raw sensor points and the continuous-aggregate rollup served from compressed chunks.