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.

keeb commented 8/11/2026, 5:26:59 AM

#1575 + #1576 approved as one change — and one scope exclusion worth recording here

Both children are triaged, planned and approved (#1575 feature, #1576 bug — regression verdict downgraded: no commit broke it, the arms were always unkeyed and merely cheap when the tables were small). They ship as one swamp-club PR rather than the two the suggested order implied, with the DDL + backfill runbook as an explicit pre-merge gate rather than a second merge.

Reproduced, not taken on faith

Against the perf10b synthetic in the dev ClickHouse:

probe rows read
score_daily candidate arm, one username 5,994,392 (whole table)
score_grants today-tail arm 14,500,000 (whole tail)
the same CTE referenced 3× 61,610,154

The third row confirms per-reference inlining, and it is worse than #1576 estimated — dailyHistory references cand five times.

The exclusion

The locate/rank/slice family stays out of scope: findScoreTarget, scoreRankOf, sliceByScoreRank, windowRankOf, sliceByStreakRank all still read OWNED_GRANTS — the live ledger form with today's tail. Each is a whole-population owner-grain aggregation, none is pinned, so none is cacheable. That is the same shape measured at 3,596 ms / 10.85 GiB in this epic, still carrying the term #1574 removed from the boards, and it backs /api/v1/leaderboard/locate — the ranks that are supposed to agree with the board a viewer just looked at.

Two reasons it is being left alone rather than folded in: the board/locate divergence already exists post-#1574 and this change does not widen it, and board_totals covering it would grow an already-large DDL change. But it is the second-largest win available from board_totals, so it is a decision, not an omission. It probably belongs with #1577 or as its own child.

Two other things settled

  • Streak stays out. board_totals covers the three score boards; #1495 remains the streak fix. A stored streak decays with the clock rather than with grants, so it needs a different invalidation contract — and after #1574 the streak board is already the one board with a live freshness source.
  • board_totals and owner_devices get opposite delete stories, in the same change: board_totals must write a zero row for a departed owner or leave a frozen score on the board, while owner_devices must never remove a row, because candidateIds is a prune and a stale pair is a harmless superset entry.

keeb commented 8/11/2026, 5:09:33 PM

Phases 3 and 4 shipped — #1575 + #1576 live in prod

PR https://github.com/swamp-club/swamp-club/pull/1061, deployed 2026-08-11. DDL and backfill landed on the droplet cluster ahead of the merge.

Verified live
/leaderboard 200 in 0.62s, 12,670 board owners
page 1 → 2 seam ranks 6-10, contiguous, no overlap
today / week 278 / 980 owners
/u/webframp 200 in 0.65s, canonicalScore 125,238,010

The last row is the one that matters: the profile's number matches the board's top row exactly, so board_totals and the owner_devices-pruned keyed read agree through two different substrates. A narrowed candidate set would have shown up here as a quietly smaller number.

Measured, on the 10B synthetic

board substrate rows bytes peak memory ms
live aggregate 9.39M 207 MiB 1.70 GiB 737
board_totals 3.73M 99 MiB 96 MiB 30

At 1.70 GiB/query five concurrent viewers OOM an 8 GiB node; at 96 MiB, sixty-four fit under 6 GiB. Candidate resolution went 16,954 rows → 579 for the same answer, and profile reads are never cached, so that was paid on every /u/{name} view.

One regression caught pre-merge

The asOf pin had stopped bounding the board by day — board_totals holds one instant, so a yesterday pin returned today's totals and silently voided #1055's freeze. Caught by leaderboard-live-harness.ts, not by review. A pin from an earlier day now falls back to aggregating grains.

What #1577 inherits

Two gaps went out knowingly, both on the write side:

  1. Write amplification is unmeasured at prod scale. Both projections are full rewrites, so volume is owners × runs/day — ~18M rows/day at 12,670 owners on a 60s cadence, all collapsing on merge. The cadence knob is unused; the projections do not need rewriting on every rollup tick.
  2. ADV-6 unmitigated. withLease fails open, so a Mongo outage has every replica running both full rewrites. The planned ClickHouse-side version guard was not implemented.

Also still open: the locate/rank/slice family (ADV-5, whole-population live-ledger aggregations, uncached because they never pin — on current numbers the most expensive thing left on the page), the streak board (#1495), and dailyHistory's range pushdown, split to #1599 with the measurement that deferred it.

keeb commented 8/11/2026, 5:16:13 PM

Filed the two write-side gaps from PR #1061 as children:

  • #1606 — projector write amplification. Both projections are full rewrites, so volume is owners × runs/day: ~18.2M rows/day into board_totals at the 12,670 owners prod now reports, on the 60s cadence, all collapsing on merge. Unmeasured in prod. Includes the system.part_log query to measure it before tuning, and the reason the rewrite cannot be made partial (window decay, late-bound owner, rename).
  • #1607 — guard the rewrites against the fail-open lease (ADV-6). withLease fails open by design, so a Mongo outage has every replica running both full rewrites concurrently. Correctness is unaffected; it is cluster pressure at the worst moment.

They share a fix: a ClickHouse-side check that skips the rewrite when the projection is already newer than the last rollup write. That works during the outage that triggers the fail-open, and in the steady state it also makes a tick where the rollup wrote nothing cost a comparison instead of two full rewrites. Worth building together rather than separately.

Both are #1577's territory — it is the projector-at-10B issue and these are its starting conditions.

Sign in to post a ripple.