Storing a year of metrics in TimescaleDB without it falling over

A client's internal monitoring system was writing roughly 40 million rows a day into a single plain PostgreSQL table, and after about four months the dashboards that queried it had gone from instant to unusably slow. TimescaleDB, a PostgreSQL extension rather than a separate database, solved this without requiring a migration to a different query language or losing any of the SQL tooling already in use.

The problem with metrics in plain PostgreSQL

Time-series data has a specific shape: mostly inserts, almost never updates, and queries that nearly always filter by a recent time range. A single growing table handles this poorly — indexes get larger and slower to maintain as the table grows, and queries that only care about the last 24 hours still have to navigate an index covering a year of history.

Hypertables: automatic partitioning

TimescaleDB's core feature is the hypertable — a table that's automatically partitioned into time-based chunks behind the scenes, while still looking like one ordinary table to every query you write:

Converting a table into a hypertable

SELECT create_hypertable('metrics', 'recorded_at',
  chunk_time_interval => INTERVAL '1 day');

Each day's worth of data lives in its own chunk under the hood. A query filtering for the last 24 hours only touches the relevant chunk's index, not the index for the entire year — which is the single biggest reason dashboard queries went from multi-second to near-instant after the migration.

Compression policies

Older chunks that are no longer being actively written to are good candidates for compression, which TimescaleDB handles natively with a columnar format optimized for time-series data:

Enabling compression and a policy

ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'server_id'
);

SELECT add_compression_policy('metrics', INTERVAL '7 days');

Chunks older than seven days compress automatically in the background. For this client's dataset, compression reduced storage for older chunks by roughly 92% — metrics data compresses unusually well because consecutive readings from the same source tend to be similar.

Retention without manual cleanup jobs

Before TimescaleDB, retention meant a cron job running a DELETE against old rows — slow, and prone to running during peak hours if not scheduled carefully. A retention policy on a hypertable drops entire old chunks instead, which is nearly instantaneous regardless of how much data is in them:

SELECT add_retention_policy('metrics', INTERVAL '13 months');

Dropping a chunk is a metadata operation, not a row-by-row delete — there's no equivalent of the table bloat that a large recurring DELETE would otherwise cause, which ties back to the autovacuum concerns covered in the earlier post on vacuum tuning.

Continuous aggregates for fast dashboards

Dashboards rarely need raw per-second metrics — they usually want hourly or daily rollups. Continuous aggregates precompute these automatically and keep them updated incrementally as new data arrives, rather than recalculating from scratch on every dashboard load:

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', recorded_at) AS bucket,
       server_id,
       avg(cpu_percent) AS avg_cpu,
       max(cpu_percent) AS max_cpu
FROM metrics
GROUP BY bucket, server_id;

The dashboard queries against this view instead of the raw table, and the view stays current automatically via a refresh policy. For a year-long retention window with hourly granularity, this is the difference between a dashboard that loads instantly and one that re-aggregates millions of rows on every page load.