Skip to content

PATTERN Cited by 1 source

Dual-granularity rollup tables

Problem. A time-series UI has two modes: a "show me last month" zoom-out and a "show me last hour" zoom-in. A single-granularity store punishes one or the other — per-minute rows make month-scale queries expensive (43,200 rows per series per month); per-hour rows make hour-scale queries imprecise (1-hour resolution is useless for debugging a 3-minute spike).

Solution. Maintain two rollup tables at two different time grains and route each query to the one whose row count is bounded by the intersection of window and grain. Per-hour rollup serves the zoom-out; per-minute rollup serves the zoom-in.

┌──────────────────┐    ┌────────────────────┐
│ per_hour_rollup  │    │ per_minute_rollup  │
│ PK (tenant,      │    │ PK (tenant,        │
│     pattern,     │    │     pattern,       │
│     hour_bucket) │    │     minute_bucket) │
│ count, sum, blob │    │ count, sum, blob   │
└──────────────────┘    └────────────────────┘
       ↑                         ↑
       │                         │
    large-window             small-window
    queries                  queries

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

Canonical PlanetScale Insights 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."

Mechanism

  1. Pick two grains. The coarse grain bounds the row count on the largest window the UI supports; the fine grain gives enough resolution for the smallest debug window. PlanetScale's choice: per-hour for month-scale zoom-out, per-minute for hour-scale zoom-in.
  2. Write to both tables from every consumer message. The cost is 2× write amplification; at Insights' 5k writes/s this is still modest.
  3. Route reads. The UI picks the table based on the selected time range: grain = coarse if window_length > threshold else fine.
  4. Keep the aggregates mergeable. Counts, sums, and DDSketches naturally merge across time buckets (same monoid as the original aggregates). Percentiles derived from non-mergeable sketches would need a different design.

Why not raw + on-the-fly aggregate

The single-table alternative is to store per-minute rows and aggregate up to hour grain at read time. This works if query cost is acceptable at the largest window the UI supports. Insights' fleet-wide constraint rules this out — tens of millions of patterns × 60 minutes × 24 hours × 30 days = hundreds of billions of rows per month even if you only store the patterns a customer looks at. Per-hour pre-aggregation is a two-order-of-magnitude row-count reduction for the zoom-out queries.

Why not N grains

Adding per-10-minute and per-day rollups would work but buys little: the write amplification goes up linearly, the read speedup is marginal, and the code path for "which table do I query?" gets more conditionals. Two grains aligned to the UI's two modes is the cheapest design that handles both modes well.

Cross-grain consistency

Both rollup tables are derived from the same Kafka input. If the per-minute and per-hour writes commit as separate transactions, cross-grain sum / count can temporarily disagree by a minute's worth of data. Acceptable for UI reads; applications requiring strict equivalence must either single-transaction the dual write or use a trigger-based computed column.

Relationship to continuous aggregates in TSDBs

Prometheus (via recording rules), InfluxDB (via continuous queries), and TimescaleDB (via continuous aggregates) all provide this pattern built-in. Sharded relational databases do not — the rollup maintenance logic has to be in the consumer. PlanetScale's 2023 choice to build it in MySQL rather than adopt a TSDB was driven by the cardinality- profile mismatch (patterns/hybrid-timeseries-store-prometheus-plus-sharded-mysql).

Seen in

Last updated · 470 distilled / 1,213 read