Skip to main content
← Back to list
01Issue
FeatureOpenSwamp Club
AssigneesNone

Relationships

#1572 Epic: /leaderboard and /u/{username} do not survive 10B events — measured audit + phased plan

Opened by keeb · 8/10/2026

Parent issue for a measured performance audit of /leaderboard and /u/{username}, and the phased plan to make both survive 10B events. Children are listed at the bottom.

Why now

Prod events, measured 2026-08-10 against the read-only lake mirror:

Month Events Distinct devices
2026-05 1.9M 42,771
2026-06 6.3M 63,186
2026-07 53.1M 264,842
2026-08 (10 days) 162.9M 66,919

225,037,546 events lifetime / 18.19 GiB. Current rate ~15-18M events/day from 4-10K devices/day, i.e. **4,130 events per device per day**.

The shape that matters: events grew ~40x in two months; the population did not. Lifetime device-days across all of prod is 571,694, and the board query reduces to 10,722 owners to display 25 rows. Every cost below is paid to rank a population in the tens of thousands.

Worth noting for calibration: the baseline in #1495 / #1497 (2026-08-01, "prod swamp.events is 74.75M rows") is 3x stale nine days later. Those issues are still correct; the ground moved under them.

The verdict

Three findings, all measured against real DDL — the dev ClickHouse for current scale, and a purpose-built 10B-scale synthetic (perf10b: 6M score_daily, 14.5M-row today-tail, 3M cli_daily, 300K identity_map) for the projection.

1. The board recomputes the whole population per viewer, uncached

findTopScoreBoards scans score_daily FINAL (all history) UNION today's score_grants tail, joins identity_map FINAL, applies the cli_daily ghost gate, groups by owner, then runs three row_number() OVER window functions plus countIf() OVER () across the whole owner set — to return 25 rows.

There is no result cache and no request coalescing on this path. chReadSettings supports ClickHouse's server-side query cache, but only /metrics passes cacheTtlS; score reads do not. And initialFill mints asOf as parseAsOf(url) ?? new Date() — a fresh millisecond per request — which would defeat the cache even if enabled. That is exactly the unquantized-bound trap documented in read-protocol.ts.

Measured on the dev dataset (17.3M events), same query, varying concurrency:

Concurrent viewers avg latency peak mem/query summed mem
1 40 ms 143 MiB 143 MiB
8 124 ms 160 MiB 1.09 GiB
32 484 ms 171 MiB 5.12 GiB

Latency and memory both scale linearly with concurrency — N identical queries computing an identical answer. This is the Discord-announce spike, and it is 100% duplicated work.

Component breakdown of one render: the aggregation to 10,722 owners is ~32 MiB; the three window functions take it to 143 MiB. On a day with real grant volume (367K grants) one render is 264 MiB / 119 ms — the today-tail is the swing factor, and it grows with the daily event rate.

2. At 10B events the board does not get slow, it dies

Same query, real DDL, against the 10B-scale synthetic:

1 request, no concurrency
Current architecture 3,596 ms / 23.94M rows / 10.85 GiB
Same, capped at 8 GiB HTTP 500 — MEMORY_LIMIT_EXCEEDED at AggregatingTransform

That is the first request, before any concurrency. Prod is 3 replicas, single shard, no Distributed table — there is no sharding escape hatch in place.

3. The profile's "keyed" prune costs a full ledger scan to build itself

candidateIds resolves an owner's devices with WHERE username = {u} against both score_daily and score_grants. username is in neither table's sort key. Both arms are full scans, and the CTE is inlined three times. Filed as a child bug.

The fix, measured

Core move: the read path must stop touching score_grants, and must stop recomputing per viewer.

latency rows read memory
Current @ 10B 3,596 ms 23.9M 10.85 GiB (OOM at 8 GiB)
Owner-grain snapshot read 33 ms 3.78M 134 MiB
Snapshot + query cache (hit) 0.6 ms 25 4 MiB
64 concurrent, snapshot + cache 27 ms total wall 1,600 total 255 MiB summed

64 simultaneous viewers at 10B scale, served in 27 milliseconds total. The synthetic is deliberately pessimistic at 3.23M owners; real prod has ~10K board owners, so the uncached read there is ~1 ms.

The freshness contract

Caching is acceptable only if significant events bust it. Verified mechanism: a monotonic {epoch:UInt64} bound param changes the query-cache key (miss -> hit -> bump -> miss -> hit, confirmed on 24.10). Epochs must be monotonic; reusing an old value re-hits its stale entry.

TierCrossingWatchDeps.onTierReached already exists as an injected hook, and the watcher already does an atomic Mongo compare-and-advance in claimCrossing. Bumping an epoch in that same write is nearly free. This gives cached-hard-between-crossings, provably-fresh-at-them — the eager-tick-plus-announcer-poke shape, not a lazy TTL.

Why this cannot ship as one deploy

  • Prod ClickHouse is a 3-node cluster with node-local MVs, applied through a hash-bumped migrations Job, and verification must be per-IP across all three replicas — a load-balanced Service hides per-node drift, and that exact gap silently dropped every issue_shipped grant after the last cutover.
  • The ordering rule in CLAUDE.md is explicit: an object that shipped SQL references must land in a migration, hash-bumped and applied, before the referencing code deploys. Any table plus its reader is inherently two deploys.
  • The grant-grain change alters score values — user-visible and perceptually irreversible.

However Phase 1 needs no schema change at all and is reversible by deleting one query parameter, so it ships more or less immediately and buys the headroom to do the rest without a fire.

Phases

  1. Stop paying N times for one answer — query cache + epoch bust. No schema. Biggest bang for the buck.
  2. Take score_grants off the board read path. No schema.
  3. Owner-grain board_totals — make a cache miss survivable. First DDL.
  4. Fix the profile candidateIds full scan. Bug.
  5. The write side: the projector at 10B.
  6. Decision: grant grain (per-event vs day-grain ledger rows).

Sequencing rationale: phases 1-2 need no DDL, are individually reversible, and together remove both the concurrency multiplier and the event-rate-scaling term. Phases 3-4 are the DDL pair and can share one migration window. Phase 5 should not start until phase 6 is decided, because phase 6 changes what phase 5 has to be.

Relationship to existing issues

  • #1495 (active-days-by-id for the streak board) and #1497 (ghost-gate dictionary, eventCounts pushdown) — component pushdowns from the 2026-08-01 investigation. Both remain valid and reduce cache-miss cost. Neither changes the per-viewer recompute shape, which is what phases 1-3 address. #1495's table is a natural input to phase 3's board_totals.
  • #1494 (projector CPU, shipped) — phase 5 is its successor at 10B scale.
  • #1503 (profile score blocks render) — complementary to phase 4; that is a render-blocking concern, this is a read-cost concern. Phase 4's measurements partly answer its "measure which read dominates" question.
  • #1256 (per-user daily rollup) — adjacent; covers the OPERATIVE_PERFORMANCE rail, not the candidateIds scan.
  • #1496 (split breakers, shipped) — the frame-first render it introduced is what makes phase 1's cache safe to sit behind.

Method

All numbers reproducible. Current-scale measurements are against the dev ClickHouse (17.35M events, real scoring stack) via system.query_log. The 10B projection is against a synthetic built from the real SHOW CREATE TABLE output of score_daily / score_grants / identity_map / cli_daily / hidden_usernames, sized from the measured 0.145 grants-per-event ratio and the observed device-day growth curve. Prod itself was only read from, never written to.

02Bog Flow
◉OPEN○TRIAGED○IN PROGRESS○SHIPPED

Open

8/10/2026, 7:17:37 PM

No activity in this phase yet.

03Sludge Pulse
Editable. Press Enter to edit.

keeb commented 8/10/2026, 7:21:30 PM

Children

# Phase Type Schema? Notes
#1573 1 — cache the board read, bust on tier crossings feature no Start here. Removes the concurrency multiplier. Reversible by deleting one query param.
#1574 2 — take score_grants off the board read path feature no Removes the term that scales with daily event rate.
#1575 3 — owner-grain board_totals feature yes Makes a cache miss survivable at 10B. Can share a migration window with #1576.
#1576 4 — candidateIds full ledger scan bug yes Profile reads currently scale with platform events, not the operative's data.
#1577 5 — projector write cost at 10B feature tbd Largest and riskiest. Blocked on #1578.
#1578 6 — grant grain decision feature n/a Product decision. Decide early, implement whenever — it determines what #1577 has to be.

Suggested order

#1573 first, on its own. It needs no schema change, is reversible, and addresses the specific pain (Discord announce -> concurrent spike on a page that must be fresh). Everything after it is calmer with it in place.

Then #1574, separately rather than bundled — it changes what the board counts, and #1573 is the shield you want already deployed while validating it.

Then #1575 + #1576 as the DDL pair, sharing one migration window and one round of per-IP replica verification.

#1578 should be decided in parallel with all of the above, since it gates #1577.

  • #1495 and #1497 — component pushdowns from the 2026-08-01 investigation. Both still valid; they reduce cache-miss cost and compose with this epic rather than competing with it. #1495's active_days_by_id is a natural input to #1575's board_totals, and #1575 flags the streak-column question that the two should settle together.
  • #1494 (shipped) — #1577 is its successor. The lease bounded concurrency, not cost.
  • #1503 — complementary to #1576: render-blocking vs read-cost. #1576's numbers partly answer #1503's "measure which read dominates", though the Mongo side of that question is still open.
  • #1256 — adjacent to #1576 but distinct (OPERATIVE_PERFORMANCE rail vs the candidate scan).
  • #1496 (shipped) — the frame-first render it introduced is what makes #1573's cache safe to sit behind.

Reproducing the measurements

The 10B projection came from a synthetic (perf10b) built from the real SHOW CREATE TABLE output of score_daily, score_grants, identity_map, cli_daily and hidden_usernames, sized from the measured 0.145 grants-per-event ratio and the observed device-day curve. It currently lives in the local dev ClickHouse and is the right harness for validating #1575, #1576 and #1577. Current-scale numbers came from the dev ClickHouse via system.query_log. Prod was read from only.

Sign in to post a ripple.