Skip to content

CONCEPT Cited by 1 source

Per-hour + per-minute rollup tables

Per-hour + per-minute rollup tables is the pattern of pre-aggregating time-series telemetry at two different time granularities so that queries of different window sizes hit a table whose row count is bounded by the intersection of window and grain.

A zoom-out dashboard query ("show me the last 30 days") hits the per-hour rollup: 30 × 24 = 720 buckets per series, scannable in milliseconds. A zoom-in drilldown query ("show me the last hour in detail") hits the per-minute rollup: 60 buckets per series, still bounded.

(Source: sources/2026-04-21-planetscale-storing-time-series-data-in-sharded-mysql-to-power-query-insights.)

PlanetScale's canonical usage

Rafer Hazen, 2023-08-10: "We store the query pattern data in both per-hour and per-minute roll-up tables to serve large time periods efficiently and small periods with high granularity."

PlanetScale Insights ingests query-pattern aggregates every 15 seconds per VTGate, but stores them at minute and hour grain. Each Kafka message is an aggregate-per-pattern-per-15s; the consumer merges up to the minute and hour grains on write.

Why two granularities, not raw

The two-grain design pays for itself on:

  • Zoom-out queries: one row per pattern per hour scales linearly in window size. Writing raw 15s-grain rows would require scanning 4 × 60 × N-hours rows instead — 240× more rows per hour window.
  • Zoom-in queries: one row per pattern per minute gives enough resolution for debug workflows without pulling raw per-query data.
  • Write amplification: every Kafka aggregate message hits two tables on write, so write load is roughly 2× the per-15s rate — still manageable at ~5k writes/s total.
  • Query cost: dashboard queries operate on rollup tables only; no fanout to raw query data.

Why not three granularities

The rule of thumb on this pattern is pick the coarsest and finest grain your UI actually needs and skip intermediate grains. Adding a per-10-minute or per-5-minute rollup would add write amplification without reducing the zoom-out or zoom-in cost beyond what per-hour and per-minute already cover.

Insights' product UI is consistent with this: the dashboard shows hours-to-days-wide time series, and the drilldown shows sub-hour resolution. Two rollups match the two UI modes.

Relationship to TSDB engines

Prometheus, InfluxDB, and TimescaleDB all have first-class support for continuous aggregates / recording rules that produce this exact shape automatically. This concept canonicalises the equivalent hand-rolled version in a sharded relational database (MySQL) — because the high-cardinality per-pattern axis disqualifies Prometheus (sources/2026-04-21-planetscale-storing-time-series-data-in-sharded-mysql-to-power-query-insights).

Seen in

Last updated · 470 distilled / 1,213 read