Time-series schema design: partitioning, retention and the queries that fall over

01Why a plain table stops working

Appending to a single large table means every insert updates one B-tree index whose right-hand edge is a contention point, and whose depth grows with the dataset. Once the index exceeds available memory, each insert becomes a random read from disk to find the leaf page, and ingestion throughput falls off a cliff that has nothing to do with your write volume changing.

Deletion is worse. `DELETE FROM metrics WHERE ts < now() - interval '90 days'` on a billion-row table generates an enormous amount of write-ahead log, holds a long transaction, leaves dead tuples that vacuum must reclaim while the table is still taking writes, and does not return the disk space to the operating system. The retention job you wrote as a one-line cron entry becomes the thing that causes your ingestion incidents.

02Partition by time, and size the interval by retention

Range partitioning on the timestamp column is the correct default. Each partition is a separate physical table with its own indexes, so the index you write into is only as large as the current partition, and dropping expired data is `DROP TABLE` rather than a delete.

Choose the interval so that your retention period spans a manageable number of partitions - somewhere between 20 and a few hundred. Too few and each drop discards more data than you intended, or the current partition grows large enough to reintroduce the original problem. Too many and the planner pays a real cost: query planning time grows with partition count, and thousands of partitions make every query, including trivial ones, measurably slower.

03Pruning only works if the query lets it

Partitioning gives you nothing unless the planner can eliminate partitions, and elimination requires a literal or parameter-bound predicate on the partition key. A query filtering on `ts >= now() - interval '1 hour'` prunes correctly; one that filters on a wrapped expression such as `date_trunc('day', ts) = '2026-08-15'` frequently does not, because the planner cannot prove which partitions the expression covers.

The second common failure is a query that filters on something else entirely - device ID, tenant, metric name - with no time bound at all. That touches every partition and every index, and it is usually written by someone building a dashboard who did not realise the table was partitioned. Make a time predicate mandatory at the API layer with a sensible default window, because relying on every future query author to remember is not a control.

• Always filter on the raw partition column, never a function applied to it.
• Verify pruning with EXPLAIN - count the partitions actually scanned.
• Enforce a default time window in the query layer; reject unbounded scans.
• Watch planning time as partition count grows; it is a real and often-missed cost.

04Cardinality is what actually kills you

Row count is manageable; distinct series count often is not. A series is a unique combination of your identifying dimensions, and cardinality multiplies: 5,000 devices times 40 metrics times 10 regions is two million series, each needing index entries and, in a dedicated time-series store, its own in-memory state. Adding one dimension with 100 values multiplies your cardinality by 100.

The failure is nearly always an unbounded dimension used as a label - request ID, session ID, user ID, a raw URL, or an error message. These have no natural ceiling, so cardinality grows with traffic rather than with your fleet. Those values belong in the payload or in logs, where you can search them, not in the identifying dimensions, where they multiply the index.

05Roll up early, query the rollup

Nobody looking at a year of data needs 10-second resolution, but the query will happily scan every raw row to produce a chart with 400 pixels of width. Precompute aggregates at coarser intervals - one minute, one hour, one day - storing min, max, sum, count and the percentile sketches you need, then route queries to the coarsest table that satisfies the requested resolution.

Two things to get right. Aggregate incrementally from the previous level rather than re-reading raw data at every level, or your rollup job's cost grows with history. And store sketches rather than computed percentiles: a p99 cannot be averaged, so a table of hourly p99 values cannot produce a correct daily p99. Keep a t-digest or HDR histogram per bucket and merge those instead.

• Build each rollup level from the level below, not from raw data.
• Store sums and counts, never pre-divided averages - averages of averages are wrong.
• Store percentile sketches, not percentiles; percentiles do not compose.
• Route queries by requested resolution automatically, not by asking the caller to choose.

06Retention as a tier, not a delete

Retention is rarely a single cliff. The usual shape is raw data for a short window, minute rollups for a medium one, hourly or daily rollups kept for years, and compressed archival in object storage for anything with a compliance requirement. Each tier is dramatically cheaper than the one above it, and the rollups mean expiring raw data costs you almost no analytical value.

Automate the whole lifecycle - create the next partition ahead of time, compress partitions once they stop receiving writes, drop them when they age out - and alert if the job has not run. The classic incident is a failed partition-creation job discovered when inserts start erroring at midnight because the partition for today does not exist. Create partitions several intervals ahead so a failed run is a warning rather than an outage.

07Late data and the assumptions it breaks

Time-series systems are built around the assumption that writes arrive in roughly increasing timestamp order. Mobile clients buffering offline, batch imports and retried ingestion all violate it, and a row arriving three days late lands in a partition that has been compressed, rolled up and possibly already archived.

Decide the policy explicitly rather than discovering it. Define a lateness window - say, 24 hours - within which late data is accepted and the affected rollup buckets are recomputed, and outside which it is rejected and counted. Track that rejection counter as a first-class metric; a silent increase means an upstream producer has broken its clock or its retry logic, and you want to know before someone notices a gap in a chart.