TimescaleDB Hypertables, Continuous Aggregates & Compression (2026 Production Guide)

TimescaleDB is a PostgreSQL extension that automatically manages time-series data with hypertables, chunk-based storage, and continuous aggregates — without leaving the PostgreSQL ecosystem.

Installation

# Add TimescaleDB to shared_preload_libraries
# postgresql.conf:
# shared_preload_libraries = 'timescaledb'

# Install extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

Create a Hypertable

-- Regular PostgreSQL table
CREATE TABLE metrics (
  time        TIMESTAMPTZ NOT NULL,
  device_id   TEXT NOT NULL,
  cpu_pct     DOUBLE PRECISION,
  mem_pct     DOUBLE PRECISION
);

-- Convert to hypertable (chunks by 1 day)
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');

Query Performance

-- Time-series queries are automatically chunk-pruned
SELECT time_bucket('5 minutes', time) AS bucket,
       device_id,
       avg(cpu_pct) AS avg_cpu
FROM metrics
WHERE time > now() - INTERVAL '1 hour'
  AND device_id = 'server-01'
GROUP BY bucket, device_id
ORDER BY bucket DESC;

Continuous Aggregates

-- Pre-compute hourly averages
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       device_id,
       avg(cpu_pct) AS avg_cpu,
       max(cpu_pct) AS max_cpu
FROM metrics
GROUP BY bucket, device_id;

-- Add refresh policy
SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset => INTERVAL '3 hours',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

Data Retention Policy

-- Drop chunks older than 90 days automatically
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- Check chunks
SELECT show_chunks('metrics');

-- Manual chunk drop
SELECT drop_chunks('metrics', INTERVAL '90 days');

Compression

-- Enable compression on old chunks
ALTER TABLE metrics SET (timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id');

-- Auto-compress chunks older than 7 days
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- Check compression ratio
SELECT * FROM chunk_compression_stats('metrics');

Production Patterns: Chunk Sizing, Compression, and Retention

TimescaleDB's defaults are sensible for development but suboptimal for production. The three knobs that matter most — chunk interval, compression schedule, and retention policy — need to be tuned per workload. Getting them right unlocks the 90-95% compression and sub-second query latency TimescaleDB is known for. Getting them wrong creates chunk-explosion (too many small chunks slow planning) or memory pressure (chunks too big to fit in cache).

Sizing the Chunk Interval

The chunk_time_interval determines how much time-series data lives in each chunk. Default is 7 days. Production rule: each uncompressed chunk should fit in ~25% of available RAM. If your buffer pool is 32GB, target chunks of ~8GB. Calculate: estimated rows per day × row size × indexes. For a metrics workload at 10M rows/day with 200-byte rows + 2 indexes (≈ 4GB/day), chunk_time_interval = INTERVAL '2 days' is right. For high-volume IoT at 100M rows/day, chunk_time_interval = INTERVAL '6 hours' is appropriate. Set this at hypertable creation: SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '6 hours');. Changing it later only affects new chunks — old ones keep their interval.

Compression Policy Timing

Compression in TimescaleDB is one-way: once a chunk is compressed it becomes effectively read-only (UPDATE/DELETE require decompressing the chunk first, which is expensive). Set the compression policy to wait long enough that the data is unlikely to change. For most observability workloads, 7-14 days is the sweet spot — late-arriving data from agents has typically arrived, dashboards have already cached their queries against hot data, and the compression gains (90-95% size reduction) compound for the years the data sits in the cold tier.

Configure with: SELECT add_compression_policy('metrics', INTERVAL '14 days');. The first run compresses everything older than 14 days; subsequent runs compress newly-eligible chunks daily. The compression itself runs in the background and is throttled, so it won't impact write performance during business hours. For columnar workloads where queries do range scans across compressed chunks, consider SET timescaledb.enable_chunk_skipping = on to skip chunk decompression when min/max metadata says no rows match.

Retention Policies and Multi-Tier Storage

Retention is the third pillar. Hot data lives uncompressed on fast NVMe; warm data lives compressed on the same disk; cold data either gets dropped or migrated to S3 (via the timescaledb_tools backup extension). Configure retention with SELECT add_retention_policy('metrics', INTERVAL '180 days');. For workloads with regulatory retention (financial 7 years, healthcare 6 years), avoid dropping data — instead use TimescaleDB's tiered storage (Timescale Cloud) or manual chunk export to S3 + re-import for queries that need historical data.

Multi-Hypertable Patterns

A common mistake is putting everything in one hypertable. For mixed workloads — high-cardinality metrics + low-cardinality summary tables + dimensional data — use multiple hypertables joined at query time. TimescaleDB handles cross-hypertable JOINs natively; the planner pushes down predicates and chunk-prunes. A typical production setup has 3-5 hypertables: one per data source (sensor_readings, error_events, billing_meters) each with its own chunk interval, compression policy, and retention. This is the pattern we run for nationwide IoT clients processing 5+ billion rows per day.

Continuous Aggregates at Scale

For dashboards, continuous aggregates pre-compute time-bucketed rollups (1-minute, 1-hour, 1-day). The query planner transparently uses them when applicable. Real-time continuous aggregates (TimescaleDB 2.7+) combine pre-computed buckets with newer raw data on-the-fly for zero-lag dashboards. For terabyte-scale deployments, build aggregates hierarchically: 1-second raw → 1-minute aggregate → 1-hour aggregate → 1-day aggregate, with retention policies dropping the lower tiers as data ages. Query the appropriate aggregate based on the dashboard's time range — TimescaleDB's continuous aggregate views handle this dispatch automatically when configured correctly.