timescaledb

name: TimescaleDB

description: Store and query time-series data with hypertables, compression, and continuous aggregates.

metadata: {"clawdbot":{"emoji":"⏱️","requires":{"anyBins":["psql"]},"os":["linux","darwin","win32"]}}

Hypertables

• Convert table to hypertable: SELECT create_hypertable('metrics', 'time')

• Must have time column (TIMESTAMPTZ recommended)—partition key for chunks

• Call BEFORE inserting data—converting large tables is expensive

• Can't undo easily—plan schema before converting

Chunk Interval

• Default 7 days per chunk—tune based on data volume

• SELECT set_chunk_time_interval('metrics', INTERVAL '1 day') for high-volume

• Chunks should be 25% of memory—too small = overhead, too large = slow queries

• Check chunk sizes: SELECT * FROM chunks_detailed_size('metrics')

time_bucket

• time_bucket('1 hour', time) groups timestamps—like date_trunc but with arbitrary intervals

• Use in GROUP BY for aggregation: GROUP BY time_bucket('5 minutes', time)

• Origin parameter for offset: time_bucket('1 day', time, '2024-01-01'::timestamptz)

• Beats date_trunc for non-standard intervals—15min, 4h, etc.

Continuous Aggregates

• Materialized views that auto-refresh—pre-compute expensive aggregations

• CREATE MATERIALIZED VIEW hourly_stats WITH (timescaledb.continuous) AS SELECT ...

• Add refresh policy: SELECT add_continuous_aggregate_policy('hourly_stats', ...)

• Query aggregate view instead of raw hypertable—orders of magnitude faster

Real-Time Aggregates

• Continuous aggregates include recent data automatically—no stale reads

• WITH (timescaledb.continuous, timescaledb.materialized_only = false) for real-time

• Combines materialized historical + live recent—transparent to queries

• Small performance cost for real-time—disable if batch-only acceptable

Compression

• Compress old chunks to save 90%+ storage: ALTER TABLE metrics SET (timescaledb.compress)

• Add compression policy: SELECT add_compression_policy('metrics', INTERVAL '7 days')

• Compressed chunks are read-only—can't update/delete individual rows

• Decompress for modifications: SELECT decompress_chunk('chunk_name')

Retention

• Auto-delete old data: SELECT add_retention_policy('metrics', INTERVAL '90 days')

• Drops entire chunks—efficient, no row-by-row delete

• Retention runs on scheduler—data persists slightly past interval

• Combine with compression: compress at 7d, drop at 90d

Indexing

• Time column auto-indexed in hypertable—don't add redundant index

• Add indexes on filter columns: CREATE INDEX ON metrics (device_id, time DESC)

• Composite indexes with time last—enables chunk exclusion

• Skip indexes on rarely-filtered columns—each index slows writes

Insert Performance

• Batch inserts critical—single-row inserts are slow

• Use COPY or multi-value INSERT: INSERT INTO metrics VALUES (...), (...), ...

• Parallel COPY with timescaledb-parallel-copy tool—saturates I/O

• Out-of-order inserts work but slower—prefer time-ordered writes

Query Patterns

• Always include time range in WHERE—enables chunk exclusion

• WHERE time > now() - INTERVAL '1 day' skips old chunks entirely

• ORDER BY time DESC with LIMIT for "latest N"—index scan, fast

• Avoid SELECT * on wide tables—fetch only needed columns

Distributed Hypertables

• Multi-node for horizontal scale—data sharded across nodes

• Create access node + data nodes—access node coordinates queries

• More operational complexity—start single-node, distribute when needed

• Not needed for most workloads—single node handles millions of rows/sec