TimescaleDB

Why This Exists

Every time series database requires learning something new. InfluxDB has InfluxQL and Flux. Prometheus has PromQL. Druid has its own JSON query format. That is fine if the team has the bandwidth, but a lot of organizations already have years of PostgreSQL experience and tooling. TimescaleDB's pitch is simple: what if time series performance were possible without leaving PostgreSQL?

TimescaleDB is a PostgreSQL extension, not a fork. It runs inside the existing PostgreSQL process, uses the same WAL, same replication, same backup tools, same pgAdmin or DBeaver or whatever is in use today. Applications connect with a standard PostgreSQL driver. Existing ORMs work unchanged. DBAs already know how to operate it.

The tradeoff is real though. Being an extension means inheriting PostgreSQL's single-writer architecture, MVCC overhead, and vacuum requirements. A purpose-built TSDB like InfluxDB will beat it on raw write throughput. But if the workload involves mixing time series queries with relational data (which is surprisingly common), TimescaleDB provides that in one database instead of two.

How Hypertables Work

Calling SELECT create_hypertable('conditions', 'time') causes TimescaleDB to replace the underlying table with a virtual table that routes data to chunks. Each chunk is a regular PostgreSQL table covering a specific time range. The default chunk interval is 7 days, but TimescaleDB has an adaptive algorithm that adjusts this based on the write rate.

Inserts go to the chunk covering the current time window. If the chunk does not exist yet, TimescaleDB creates it automatically. Each chunk has its own indexes, statistics, and storage settings. This is the key insight: instead of one massive table that gets slower as it grows, the database has many smaller tables that each stay fast.

Queries benefit because the chunk exclusion optimizer eliminates chunks that cannot contain matching data. A query with WHERE time > now() - interval '1 hour' only touches the current chunk, not the 500 chunks from last year. This is aggressive constraint exclusion, and it happens automatically.

Under the hood, TimescaleDB uses PostgreSQL's table inheritance and custom scan nodes in the executor. The query planner sees the hypertable, TimescaleDB's custom planner hook kicks in, figures out which chunks to scan based on the WHERE clause, and generates a plan that only touches relevant chunks. For range queries on time, this is dramatically faster than scanning a single large table.

Compression Deep Dive

Compression is where TimescaleDB gets its biggest storage wins. When a compression policy is enabled, TimescaleDB takes a row-oriented chunk and converts it into a columnar format. The physical layout changes from rows of (timestamp, device_id, temperature, humidity) to separate columns of all timestamps, all device_ids, all temperatures, and all humidities.

Each column uses type-appropriate compression. Timestamps get delta-of-delta encoding (since they are usually evenly spaced, the delta-of-delta is often zero, which compresses extremely well). Floats use Gorilla compression, borrowed from Facebook's time series paper. Low-cardinality strings use dictionary encoding. Everything then gets wrapped in LZ4. The result is typically 90-95% compression.

The gotcha: queries on compressed chunks have to decompress data first. For simple aggregations like avg(temperature) WHERE time > X, TimescaleDB can push down the aggregation and decompress only what it needs, which is efficient. But complex queries with multiple JOINs on compressed data will be slower than the same query on uncompressed chunks. The standard pattern is to compress chunks older than the hot query window (maybe 24-48 hours) and keep recent data uncompressed.

Production Architecture

A solid single-node production setup starts with tuning PostgreSQL for time series workloads. Set shared_buffers to 25% of RAM (standard PostgreSQL advice). Set effective_cache_size to 75% of RAM. Increase work_mem to 64-256MB for aggregation-heavy queries. Set max_parallel_workers_per_gather to 4-8 to leverage parallel chunk scanning. Disable jit unless on PostgreSQL 15+ where it has fewer performance regressions.

For TimescaleDB-specific settings, timescaledb.max_background_workers controls how many workers handle compression and continuous aggregate refresh. Set it to at least 4 for a production workload. Configure a compression policy that kicks in after data cools off:

SELECT add_compression_policy('conditions', INTERVAL '2 days');
SELECT add_retention_policy('conditions', INTERVAL '90 days');

This compresses chunks older than 2 days and drops chunks older than 90 days. Combined with continuous aggregates that roll up data into hourly and daily summaries, the system can keep years of aggregated data while only storing days of raw granularity.

For high availability, use PostgreSQL streaming replication with a synchronous standby. This is standard PostgreSQL HA, nothing TimescaleDB-specific. For multi-node deployments, TimescaleDB's distributed hypertables spread chunks across multiple PostgreSQL instances. Data is routed based on time and an optional space partition key. Queries that span multiple nodes use the access node to coordinate. It works, but it adds operational complexity. Most workloads fit on a single well-tuned node for longer than people expect.

Capacity Planning

Write throughput on a single node depends heavily on batch size. Individual INSERT statements cap out at around 20-50K rows/sec. But using COPY or multi-row INSERT with 1000+ rows per statement, throughput can reach 200-400K rows/sec on an 8-core machine with NVMe SSDs. Each "row" here is a full measurement with multiple columns.

Storage: with compression enabled, expect 2-4 bytes per value for typical metrics data. At 100K measurements/sec with 5 fields each, that is roughly 40-80GB/day compressed. On a 2TB NVMe drive with 90-day retention and compression, roughly 100 days of this workload fits comfortably.

Memory sizing: keep shared_buffers at 25% of RAM. For queries, work_mem multiplied by max_connections should not exceed 50% of remaining RAM. A 32GB machine with 100 connections, shared_buffers at 8GB, and work_mem at 128MB uses 8GB + (100 * 128MB) = 20.5GB, leaving headroom for the OS page cache. The OS cache is important because TimescaleDB benefits from cached chunk files for range scans.

Failure Scenarios

Scenario 1: Chunk bloat from missing retention policy. The team sets up TimescaleDB, enables compression, but forgets to add a retention policy. After 18 months, the database has 1500 chunks across the hypertable. Query planning alone takes seconds because PostgreSQL's planner has to evaluate chunk exclusion for each one. Vacuuming the catalog tables takes minutes. Detection: monitor SELECT count(*) FROM timescaledb_information.chunks and alert when it exceeds a threshold (200-300 for most workloads). Recovery: add a retention policy and run SELECT drop_chunks('conditions', older_than => INTERVAL '90 days'). This drops old chunks immediately and frees disk space. Lesson: always define retention at the same time as creating the hypertable.

Scenario 2: Compression job blocks writes during peak hours. Compression rewrites a chunk from row format to columnar format. During this rewrite, the chunk is locked. If the chunk is large (maybe the chunk interval is too big at 30 days) and compression runs during peak write hours, new inserts to that time range block. Detection: monitor pg_stat_activity for long-running compression workers and lock waits. Recovery: cancel the compression job, resize the chunk interval to something smaller (1-7 days), and reschedule compression during off-peak hours. With smaller chunks, each compression job finishes in seconds instead of minutes.