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