Full graph →

Rollups as Query Output

Any dimensional event store — Prometheus samples, time-tracking spans, audit events, anything append-only with key/value labels — grows without bound. At some horizon you compact or archive the raw data, and the compaction question "what can we discard?" is really a different question in disguise: which queries must remain answerable after the raw data is gone? The robust way to answer it is to define your aggregation query API first, then design the rollup schema as the materialized output of that API. Old raw data can then be deleted, and the same query API transparently reads rollups past the horizon.

Encountered at SFI planning primetime's long-term storage: high-cardinality labels are an encouraged pattern there (feat: issue-12345 mirroring the forge), which makes unbounded growth a design commitment rather than an accident — sfi/PrimeTime#99 (compaction strategy) and sfi/PrimeTime#109 (nested sum by (x, y) charts) are where the pattern below crystallized.

What Prometheus already knows

Prometheus and its long-term-storage ecosystem solved this problem for metrics, and the solution has two halves worth stealing.

Recording rules precompute expensive aggregations continuously. A rule like job:http_requests:rate5m = sum by (job) (rate(http_requests_total[5m])) runs on every evaluation interval and writes its result back as a new, cheaper series. The insight is in the shape: a recording rule's output is a query result, stored. Dashboards that would otherwise fan out over thousands of raw series read one precomputed series instead — and crucially, they read it through the same query language.

Downsampling in the long-term-storage layer (Thanos, Mimir, VictoriaMetrics) keeps lower-resolution rollups — 5-minute and 1-hour aggregates alongside raw samples — and serves long-range queries from the coarse data. Raw resolution ages out; year-over-year queries keep working because the query engine transparently selects the resolution appropriate to the range. SFI runs this layer on the observability hub described in multi-cluster-metrics: the Thanos Compactor produces the 5-minute and 1-hour rollups and enforces a separate retention per resolution, so raw samples can age out of the bucket while the coarse series keep answering the long-range dashboards.

Both halves share one property: the stored rollup has the same shape as a query result, so the read path doesn't fork. That property is the whole pattern.

The pattern: API first, schema second

Concretely, for a labeled span or event store:

  1. Define the aggregation API your views actually need. For time-shaped data it converges on the PromQL sum(x) by (y) shape:

    /// The one aggregation seam. Everything the History views render
/// is an instance of this call.
func aggregate(
    range: DateInterval,
    interval: Interval,          // .day, .week, .month — bucket width
    by dimensions: [String],     // label keys to group by
    matchers: [LabelMatcher]     // pre-aggregation filter
) -> [Bucket: [DimensionTuple: Seconds]]

  2. Design the rollup schema as this call's materialized output: one row per (bucket, dimension-tuple) with a total. For per-day rollups of labeled time spans:

    CREATE TABLE span_rollup_daily (
    day        DATE NOT NULL,
    dimensions TEXT NOT NULL,  -- canonical encoding of the label tuple,
                               -- e.g. sorted JSON: {"proj":"primetime","type":"review"}
    seconds    INTEGER NOT NULL,
    PRIMARY KEY (day, dimensions)
);

  3. Compact by materializing, then deleting. Past the horizon, run aggregate over the raw rows at the finest interval you intend to keep, insert the output, delete the raw rows. The read path branches only on the horizon: aggregate reads raw data for recent ranges, rollup rows for old ones, and unions the two for ranges that straddle it. Callers never know which path answered.

The ordering is the point. Designed schema-first ("let's keep per-day totals per project"), a rollup encodes today's dashboard. Designed API-first, it encodes the class of questions you've committed to answering, and every future view built on the same API works on compacted history for free.

The same reframing settles archival economics honestly. In PrimeTime's client/server split, "connect a server for full history" is a real value-add rather than an artificial gate: the client stays fully capable on un-compacted data through the same aggregate seam, and the server carries multi-year queries over rollups plus archived raw spans. Compaction and long-range views become one feature, not two.

The dimensionality trap: marginals are not cross-products

The subtle way to get the rollup schema wrong is to store marginals — independent per-key totals — instead of tuples.

Marginals feel natural because single-dimension views are what dashboards start with: total per type, total per project, one donut each. But marginals are lossy in a non-obvious way: you can never reconstruct sum by (type, project) from sum by (type) and sum by (project). The joint distribution is gone; there are infinitely many cross-products consistent with the same pair of marginals.

The concrete instance that surfaced this: traggo's stats endpoint (inherited by the PrimeTime server as server/statistics/summary.go) groups GROUP BY key, string_value per requested key — marginals only. It can serve two side-by-side donuts, "time by type" and "time by project", but it structurally cannot answer "each type broken down by project" — which is exactly the nested view sfi/PrimeTime#109 needed, forcing that aggregation client-side. A rollup schema copied from that endpoint would bake the limitation into compacted history permanently: once raw spans are deleted, no amount of re-querying recovers the tuples.

The rule: rollup rows must be keyed by the dimension tuple for every grouping combination you want to keep answerable. From tuple rows over (type, project) you can derive both marginals by summing — the reverse direction does not exist.

The trade-off: tuple rollups buy answerability with cardinality

Tuple rollups are not free. Row count per bucket grows with the number of observed combinations of the materialized dimensions — bounded by the product of the label cardinalities. This is the same economics as Prometheus label cardinality (see the discipline in metrics): every distinct label combination is a distinct series there, a distinct rollup row here.

For genuinely high-cardinality dimensions the product bites. A store with 10 type values and 500 issue values can hit 5,000 tuple rows per day — at which point a year of "compacted" data may rival the raw data it replaced. The escape is the same one Prometheus operators use: you choose which aggregations to record; you don't record everything. Materialize the dimension sets your committed queries need — (type), (project), (type, project) — and accept that dimensions left out of every materialized set become unanswerable past the horizon. That is a product decision wearing a schema, and it deserves to be made explicitly, in writing, before the first DELETE. Choosing tuple sets is choosing recording rules.

Two mitigations short of dropping a dimension entirely: bucket its values (roll issue: 12345 up to repo: primetime past the horizon — a coarser label, not a lost one), or lengthen the interval as data ages (per-day for a year, per-month beyond), which divides row count by the bucket ratio without touching dimensionality.

The semantic parity tax

The pattern assumes one aggregation semantics served from multiple sources. The corollary: if the same aggregation is implemented in more than one place, the implementations must agree bit-for-bit, or results silently depend on which path answered.

This tax is easy to underestimate because the divergence hides in edge cases, not the happy path. From the PrimeTime planning discussion, the edge cases that must match between a client-side Swift fold and server-side SQL:

  • Running spans — a span with no end time must clip to "now", and both sides must agree on what "now" was for the query.
  • Interval boundary clipping — a span crossing midnight contributes to two buckets, split at the boundary, in the query's timezone.
  • Empty and missing values — is key="" the same bucket as "key absent"? Dropped, or a (no value) tuple?
  • Multi-match — when a grouping dimension can match a span more than once (tag-set groupings), does the span count once per match (double-counting across the group) or land in exactly one cell?
  • Timezone and week-start conventions — a "week" bucket is meaningless until both sides agree on Monday-vs-Sunday and whose midnight.

Each divergence is invisible until a user switches backends and a chart changes. Three defenses, in order of strength: implement the aggregation once and ship it to both sides (shared library, or push it entirely to one tier); write the edge-case semantics down as a spec the way PromQL's staleness and range semantics are specified; and run golden tests — one fixture of pathological spans, every implementation must produce identical output. PrimeTime's interim answer (sfi/PrimeTime#109) was the first defense: keep the fold client-side and backend-agnostic until the aggregation seam pays for its parity tax.

Things That Go Wrong

Rollups built from a dashboard, not an API. The schema mirrors today's charts; the next chart needs a dimension pair nobody materialized, and the raw data is gone. The horizon makes this class of mistake permanent — hence API-first.

Marginals discovered too late. The rollup "works" — every existing view renders — until the first nested/combined view. Test the rollup schema against sum by (x, y) queries before deleting anything, even if no current view issues them.

Cardinality explosion in the rollup itself. Materializing every dimension combination "to be safe" reproduces the raw data's growth curve with extra steps. Count observed tuples per bucket on real data before committing.

Drift between duplicate implementations. No spec, no golden tests, and six months later the SQL path handles running spans differently than the client fold. The bug report reads "charts change when I connect to the server."

Compaction that outruns retention needs. Deleting raw data is irreversible in a way most storage decisions aren't; backup-strategies apply doubly to the pre-compaction snapshot. Archive raw data to cold storage before the first destructive rollup run, so a schema mistake costs a re-import instead of history.

References