Relationships
#1577 Leaderboard phase 5: score-rollup-projector write cost at 10B — ~430M rows scanned per 60s tick
Opened by keeb · 8/10/2026
Phase 5 of #1572. The successor to #1494 at 10B scale.
Why this is in the epic
Phases 1-4 (#1573, #1574, #1575, #1576) all fix the read side. None of them touch the write side, and the write side has already caused a production incident once.
#1494 (shipped) found score-rollup-projector was the dominant CPU consumer on the prod ClickHouse cluster, starving user-facing reads into their 5s ceiling. The fix was a leader lease — correct, and it removed the per-pod fan-out — but it bounded concurrency, not cost. One leaseholder still pays the full price every tick.
Fixing reads without fixing this just relocates the starvation: the boards get cheap, and the projector still eats the cluster.
The cost shape
The projector's own docstring states it plainly:
this statement is ~10+ scans of
score_grantsper tick, not one per CTE
because ClickHouse inlines every CTE reference with no materialization. The touched window is:
WHERE toDate(granted_at) >= today() - 2
OR created_at >= now64(3) - INTERVAL 30 MINUTESo per tick the work is roughly 10 x (3 days of ledger), on a 60s interval, on one node.
Scaling that with the measured 0.145 grants-per-event ratio:
| Event rate | ~grants/day | 3-day window | ~rows scanned per tick |
|---|---|---|---|
| today (~16M/day) | ~2.3M | ~7M | ~70M |
| 10B era (~100M/day) | ~14.5M | ~43M | ~430M |
Every 60 seconds, on one node, forever.
What needs deciding
This phase is deliberately open rather than prescriptive, because the right answer depends heavily on #1578 (grant grain). Directions worth evaluating:
- Reduce the input. If #1578 lands, the ledger shrinks by roughly 30x and much of this problem evaporates. This is why #1578 should be decided first.
- Reduce the re-scan factor. The ~10x comes from CTE inlining. Materializing the closure into a temp/staging table, or restructuring so the membership closure is computed once, attacks the multiplier directly rather than the input size.
- Narrow the touched window.
today() - 2is generous. Whether it can shrink depends on how late late-arriving activity chunks actually are — that is a measurable question, not a guess. - Split the tick. Today's grains and the re-stamp closure have very different cost profiles and freshness requirements; they may not need the same cadence.
Constraints that must not be broken
- Membership closure and tombstones are load-bearing. They exist because of #1082 — a day-less
grant_idre-stamped at a latergranted_atmoves grains, and recomputing only recently-touched grains strands a permanent double count. Any restructuring must preserve: every grant resolved over all its ledger rows, and amount-0 tombstones for emptied non-badge grains. - Badge grains deliberately stay stale-but-benign — a tombstone at a re-stamped badge's old day would pay the badge at 0, because the read-side fold uses
argMin(amount, day). Do not "fix" this. - Must remain a single INSERT statement — the transport posts the whole file as one
command(), and the tombstone arm is aUNION ALLbranch, never a second statement. - Idempotent and self-healing at any cadence. The current design's recovery contract is that the next tick reconciles after a stall; that must survive.
- The lease must keep failing open — a lease that cannot be acquired must never freeze the projector.
Verification
- Measure the current per-tick cost on a prod-shaped mirror before changing anything, so the improvement is arithmetic rather than assertion.
- Replay the #1082 grain-move scenario and assert no stranded double count.
- Confirm self-healing after a simulated stall.
Risk
High relative to the rest of the epic — this is the system of record's write path. Sequencing note from #1572: do not start this until #1578 is decided, because #1578 changes what this has to be.
Triaged
Click a lifecycle step above to view its details.
keeb commented 8/11/2026, 4:34:35 AM
Related: filed #1598 — the score-rollup projector runs once per cadence PER REPLICA (the run gate is per-replica process memory; withLease serializes runs but does not throttle them), so prod does ~N runs per 60s of the same INSERT … SELECT. Observed 2026-08-11: two runs 13s apart against a 60s cadence, with no run requested. Dividing the run count by the replica count is the cheapest available progress on this issue, and it needs no new machinery — the shared score_rollup_signal document #1574 added already gates the poke path this way.
keeb commented 8/11/2026, 5:37:51 PM
Triaged — feature, high confidence. Parked pending #1578, with one measurement that sharpens what it is parked for.
Classified feature, not a regression: #1494's lease bounded the projector's concurrency, not its cost, and the cost is a function of ledger size. Nothing broke.
The scan multiplier, read off the statement
score-rollup-projector.sql builds the closure as four CTEs, each an IN subquery over swamp.score_grants: touched → member_grants → write_grains → scope_rows. ClickHouse CTEs are textual substitution, and scope_rows is referenced twice (deduped, occupied) while write_grains is referenced three times. Expanded, the docstring's "~10+ scans of score_grants per tick" is if anything conservative.
Since PR #1061 the same tick also runs two full-rewrite projections (board-totals-projector.sql, owner-devices-projector.sql) under the same lease, before markRunServed advances board_freshness.
At 10B the statement does not get slow — it cannot start
Measured, not projected. The projector statement rewritten as a read-only SELECT count() over the perf10b synthetic in the dev ClickHouse ([IP-1]; 14.5M score_grants, 3.35M devices, 6M score_daily rows):
| memory cap | outcome |
|---|---|
| 5.59 GiB | MEMORY_LIMIT_EXCEEDED, ExceptionBeforeStart, 18.6s |
| 11.18 GiB | MEMORY_LIMIT_EXCEEDED — While executing CreatingSetsTransform, 38s |
The closure's IN sets are (distinct_id, grant_id) pairs materialized in RAM, sized by grants-in-window, and — per CreatingSetsTransform and the ExceptionBeforeStart at the lower cap — built before execution begins. This is the same MEMORY_LIMIT_EXCEEDED class the board read had before #1575, except on the write path, where there is no cache to hide behind and prod is three 2-vCPU droplets.
Two honest caveats. perf10b's tail is a single day (2026-08-10, 14.5M rows); the real touched window is today() - 2, i.e. roughly 3x that set — so this is the optimistic end. And perf10b has no owner_devices table and its score_grants carries no multi-day created_at spread, so it cannot yet measure the whole tick. Extending it is a prerequisite for the measure-before-changing-anything step this issue already asks for.
This reframes the phase slightly: the epic's cost table treats the write side as ~430M rows scanned per tick, which reads as a CPU-and-throughput problem. The set-building memory ceiling is a harder wall than that, and it is the thing that decides whether the closure can survive at all or has to be materialized into a staging table.
Staying parked
Confirming the sequencing rather than working around it: this stays blocked on #1578, which is open with no decision recorded. The measurement above is one of the inputs — if the grain change lands and the ledger shrinks ~30x, the set that just OOMed shrinks with it and much of this becomes tuning; if it does not, the closure needs restructuring and the memory ceiling above is the constraint to design against.
Proceeding instead with #1606 + #1607, which are this issue's territory but grain-independent, and share one fix.
keeb commented 8/11/2026, 5:58:50 PM
Unblocked — #1578 is decided, and the answer makes this the re-architecture branch
The ledger stays full resolution: per-event, monotonic, immutable, permanently. Views into the data are fine; the data itself never changes. Recorded on #1578.
That resolves the sequencing note this issue was parked behind, and it resolves it in the harder direction. The epic's framing was explicit — a ~30x smaller ledger would have made this tuning; no shrink makes it a re-architecture. The input is now a fixed constraint: ~1.5B score_grants at 10B events, growing ~1:1 with the event rate.
What that removes from the options list
Of the four directions this issue left open, the first is gone:
Reduce the input.Not available. Ever.- Reduce the re-scan factor — now the primary line of attack, and the measurement below says it is not merely the largest win but the only one that addresses the actual failure.
- Narrow the touched window — still a measurable question, still worth answering, but it scales the set down rather than changing its shape.
- Split the tick — still open, and cheaper than it was: today's grains and the re-stamp closure have genuinely different freshness requirements.
The measurement to design against
From this issue's triage: the projector statement, rewritten as a read-only SELECT count() over the perf10b synthetic (14.5M grants — a single day, against a real three-day touched window), fails MEMORY_LIMIT_EXCEEDED While executing CreatingSetsTransform at an 11.18 GiB cap, and ExceptionBeforeStart at 5.59 GiB.
The operative detail is where it fails. The closure's IN sets are (distinct_id, grant_id) pairs materialized in RAM and built during set creation, before execution begins — so the wall is not throughput, and the epic's ~430M-rows-scanned framing understates it. A statement that cannot start cannot be tuned into starting, and with the input now fixed by #1578, materializing the closure (a staging table, or a restructure that computes the membership closure once) is the direction that attacks the thing that actually breaks.
Constraints from the issue body are unchanged and all still binding: membership closure and tombstones stay load-bearing (#1082), badge grains stay stale-but-benign, one INSERT statement, idempotent and self-healing at any cadence, lease keeps failing open.
Two things it inherits are being handled first
#1606 (write amplification) and #1607 (the fail-open lease guard) are this issue's territory but grain-independent, so they proceeded while this was parked. Both are triaged and planned; the shared plan is one probe answering two questions — how old the projections are, read from ClickHouse rather than Mongo. Neither touches what a single rollup run costs, so neither reduces the wall above.
Before this is planned
Two harness gaps to close first, because perf10b cannot yet measure the whole tick honestly: its score_grants holds a single day (2026-08-10) with no multi-day created_at spread, so the three-day touched window and the late-arrival arm are untested, and it carries no owner_devices table. Extending it is the prerequisite for the "measure on a prod-shaped mirror before changing anything" step this issue already asks for.
Sign in to post a ripple.