Time Series Rollup
establishedSummary
Periodically aggregate raw time series data into coarser-grained summary rows (e.g., per-second metrics → per-minute → per-hour → per-day), then delete or compress the raw data, maintaining query performance and bounded storage growth as the dataset ages.
Problem
High-frequency raw time series data grows without bound. Queries over historical ranges degrade as raw data accumulates. Storage costs grow proportionally to retention period × write rate. Rollup controls storage growth and maintains query performance by trading historical granularity for bounded storage.
Description
Time series databases and metrics systems write raw data at high frequency (per second or per minute). Over time, raw granularity is rarely needed for historical data : a dashboard showing the last year of CPU utilization uses hourly averages, not the raw per-second samples from 11 months ago. Storing all raw data indefinitely creates unbounded storage growth, degrades query performance (scanning billions of raw rows to compute an annual trend), and consumes unnecessary memory and I/O.
Rollup compresses old data by aggregating it: sum, average, min, max, percentile, and count are computed per time bucket and stored as summary rows. Raw rows within the bucket are then deleted or archived. Multiple rollup tiers provide different retention granularities:
- Raw (1s resolution) → retain 24 hours
- 1-minute rollup → retain 30 days
- 1-hour rollup → retain 1 year
- 1-day rollup → retain 5 years
TimescaleDB's continuous aggregates, InfluxDB's downsampling tasks, and Prometheus recording rules all implement this pattern. In Cassandra, TTLs on raw rows with separate aggregation tables implement a similar lifecycle.
The rollup must be idempotent: re-running a rollup job for a time range already processed must produce the same result. This requires that the rollup stores a completion marker or uses an upsert (INSERT ON CONFLICT UPDATE) rather than a plain INSERT.
TimescaleDB continuous aggregates express this as a materialized view over a time bucket (CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) AS SELECT time_bucket('1 minute', ts), avg(value), max(value), count(*) FROM raw_metrics GROUP BY 1), paired with a refresh policy (add_continuous_aggregate_policy) that defines the refresh window and interval, and a separate retention policy (add_retention_policy) that drops raw rows once they age past the raw-tier window. Prometheus recording rules achieve a comparable effect declaratively, precomputing an expression like rate(http_requests_total[5m]) under a named recorded metric.
Tradeoffs
Bounded by the rollup retention policy, not raw data volume
Querying rolled-up tables is orders of magnitude faster than scanning raw data
Fine granularity for recent data, coarse granularity for historical data
Reconstructing original per-second data from hourly rollups is impossible
Scheduling, failure handling, idempotency, and completion tracking all add operational complexity
A rollup bucket that straddles a data gap must handle missing data correctly
Late-arriving (out-of-order) raw data means the rollup for a past bucket must be re-run
When to use
Write rate is high (>1000 data points/second) and retention window is long (>30 days)
At low write rates or short retention, raw data storage is affordable without rollup
Historical queries do not require raw granularity beyond a recent window
If users or dashboards require per-second resolution for data from 2 years ago, rollup is inappropriate
Query performance on historical ranges is degrading as dataset grows
Rollup reduces the row count for range queries on aggregated tables
Storage cost is a concern for long-retention time series
Rollup can reduce storage by 100–10,000× depending on aggregation ratio
When not to use
All raw data must be retained indefinitely for audit or regulatory compliance
Rollup deletes raw data after aggregation: incompatible with raw data retention mandates
Aggregation function is irreversible or unknowable at rollup time
If the aggregation semantics are not defined at write time, rollup cannot be performed correctly
Operational Requirements
Make rollup jobs idempotent
Re-running for a completed interval must produce the same result.
Monitor rollup lag
If the job runs less frequently than the aggregation window, gaps appear in rolled-up data.
Define NULL handling explicitly
Missing raw data in a bucket should produce NULL, not 0, in the rollup.
Test rollup with late-arriving data
Verifies backfill behavior before it is needed under real out-of-order arrivals.
Characteristics
Relationships
Complements
Basis
Time series rollup is a foundational pattern documented in TimescaleDB, InfluxDB, Prometheus, and Graphite documentation; implemented in production by every major observability platform
Related Architecture Knowledge
Outbound: this entity affects
Materialized views provide fast read access to pre-aggregated data; time series rollup applies tiered aggregation over time, feeding the materialized view at each resolution level.
Full relationship →Inbound: affects this entity
Batch ETL workloads that process historical time series data benefit from rollup pre-aggregation to reduce the data volume that must be scanned for each pipeline run.
Full relationship →Time series metrics workloads benefit from rollup to bound storage growth and maintain query performance for historical dashboards without retaining raw high-frequency data indefinitely.
Full relationship →Used In Architecture Scenarios
Financial Ledger
An append-only audit log architecture for capturing system actions: user operations, data access events, configuration changes, and financial operations: with cryptographic integrity chaining, immutable storage, and dual-path query serving. PostgreSQL stores the authoritative append-only event log in time-partitioned tables; ClickHouse serves historical aggregate queries and retention analytics; Kafka streams audit events in real time to SIEM integrations and security dashboards; Redis caches hot audit query results for compliance-facing read paths. No record is ever updated or deleted: only inserts are permitted. Each record includes a hash of the previous record, forming a tamper-evident chain verifiable without external tooling.
Realtime Collaboration
A real-time location tracking platform for moving entities: vehicles, delivery couriers, field workers, and assets: receiving position updates at 1–30 second intervals from thousands of simultaneously tracked objects. Redis geospatial indexes (GEOADD / GEOSEARCH) serve sub-10ms proximity queries against the live position surface; PostgreSQL stores durable entity metadata and historical location records; TimescaleDB stores high-frequency time-series location data with automatic hypertable chunking and retention policies; Kafka streams location events to downstream consumers (dispatch systems, customer tracking apps, analytics pipelines). Geofence evaluation runs at ingestion time: each incoming position update is tested against active geofences for the entity's region, and geofence entry/exit events are emitted as Kafka messages to downstream consumers.
Write-Heavy Application
A high-rate device telemetry ingestion architecture designed for millions of devices emitting metrics at 1–60 second intervals. Kafka absorbs device writes as an ingestion buffer, decoupling device-facing ingest endpoints from the storage write path so that downstream storage pressure never propagates back to devices. TimescaleDB provides time-series storage with automatic chunk partitioning by time range, native compression, and continuous aggregate views for rollup queries. ClickHouse serves as the OLAP layer for device fleet analytics queries. Redis caches last-known device state (current readings per device) for real-time alerting queries that must not scan historical storage. Backpressure on the Kafka consumer side prevents storage write throughput from being overwhelmed by burst ingestion events from device reconnect storms.
Analytics Pipeline
A metrics, logs, and traces ingestion and query platform built to absorb the telemetry output of a production system fleet: including the telemetry volume spikes that accompany the incidents the platform is meant to detect. ClickHouse stores metrics data with automatic time-based rollup via continuous materialized views; TimescaleDB provides complementary time-series storage for high-cardinality alert evaluation; Kafka buffers the ingestion stream against downstream write pressure, decoupling ingest acceptance rate from storage write throughput; Elasticsearch serves log full-text search and structured field filtering; Redis caches dashboard query results and active alert state for sub-100ms alert evaluation latency. The alert engine evaluates threshold and anomaly rules against pre-computed materialized views, not raw data, to bound alert evaluation cost independent of ingestion volume.