Skip to main content
← Back to list
01Issue
FeatureTriagedSwamp Club
Assigneeskeeb

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_grants per 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 MINUTE

So 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() - 2 is 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_id re-stamped at a later granted_at moves 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 a UNION ALL branch, 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.

02Bog Flow
✓OPEN◉TRIAGED○IN PROGRESS○SHIPPED+ 1 MOREASSIGNEDCLASSIFICATION

Triaged

8/11/2026, 5:36:01 PM

Click a lifecycle step above to view its details.

03Sludge Pulse
keeb assigned keeb8/11/2026, 5:31:45 PM
Editable. Press Enter to edit.

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.