Downsampling and retention tiers for time-series data

Goal

Answer long-range questions (a year of latency, three years of meter readings) at a fraction of the storage and scan cost of raw samples, while keeping raw resolution for the period in which it is actually inspected.

Prerequisites

A table or store partitioned by time, and a decision on what each tier must be able to answer. The Prometheus storage documentation (cited) shows the simplest policy: a retention time for samples (--storage.tsdb.retention.time, default 15 days when neither it nor a size limit is set) or a size limit that removes the oldest blocks first. Thanos (cited) describes downsampling as rewriting series to reduce resolution without losing accuracy over longer ranges, storing per downsampled chunk the count, sum, min, max and a counter series so that the coarser data can still be aggregated. PostgreSQL's date_bin (cited) bins a timestamp into a stride aligned with a specified origin, which is the primitive a SQL rollup needs.

Steps

1. Write down the tiers as (resolution, retention, purpose): for example raw for 14 days for incident analysis, 5-minute bins for 13 months for capacity trends, hourly bins for 5 years for reporting. Retention periods are a decision to record, not a number to copy from elsewhere.
2. For each bin store only re-aggregatable statistics: count, sum, min, max, and for counters the increase. Derive averages as sum divided by count at query time; do not store averages or percentiles, since they cannot be combined into a coarser bin. If percentiles are required, store a histogram with fixed bucket boundaries.
3. Align bins with date_bin(stride, ts, origin) using one fixed origin (the Unix epoch, or midnight UTC) so that bins from different runs and tables coincide.
4. Schedule the rollup for a bin only after the late-data window for that bin has passed, and make it replace the bin rather than add to it, so that a rerun is safe.
5. Expire a tier by dropping whole partitions or blocks, never by row-wise deletes; keep the rollup of a partition verified (row count, sum of a measure) before its raw partition is dropped.
6. Expose the tiers through one view or query layer that picks the finest tier covering the requested range.

Expected result

Long-range queries scan the coarse tier and return in bounded time; storage grows with the coarse tiers only; raw data remains for the period in which it is used.

Limits and test basis

Downsampled data cannot answer questions about sub-bin spikes. A tier boundary crossed by one query yields two resolutions in one result and must be labelled. The protocol is derived from the cited documentation; no measurement is claimed.