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

Relationships

#1575 Leaderboard phase 3: owner-grain board_totals so a cache miss is survivable at 10B

Opened by keeb · 8/10/2026· Shipped 8/11/2026

Phase 3 of #1572. First DDL of the epic. Needed before 10B, not urgent at 225M.

Problem

Even with phases 1-2 (#1573, #1574) landed, a cache miss still pays a full recompute of the entire owner population. At current scale that is a 143 MiB annoyance. At 10B it is fatal.

Measured against a 10B-scale synthetic built from the real SHOW CREATE TABLE output:

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

The failure is in the GROUP BY owner, before any concurrency. Prod is 3 replicas, single shard, with no Distributed table — there is no sharding escape hatch to fall back on.

The structural point: the board query reduces 368K rows to 10,722 owners to display 25. The answer is tiny; the work to produce it is proportional to all history.

Component breakdown at current scale: the aggregation to owners costs ~32 MiB; the three row_number() OVER window functions plus countIf() OVER () take it to 143 MiB. Roughly 75% of the memory is spent ranking a set that could have been pre-ranked.

Proposal

An owner-grain table maintained by the existing lease-gated projector:

board_totals (owner, t_all, t_week, t_today, active_days, is_ghost, version)
ENGINE = ReplacingMergeTree(version)
ORDER BY owner

Board reads become a scan bounded by owner count, permanently decoupled from event count.

Measured effect

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

The synthetic is deliberately pessimistic at 3.23M owners. Real prod has ~10K board owners, so the uncached read there would be ~1 ms. The fill (the write side, paid once per bust by one leaseholder, not per viewer) measured 2.28 s.

Relationship to #1495

#1495 proposes active_days_by_id to make the streak board cheaper, on the measurement that days_active is a 99.96% superset of the other two day sources. That remains true and useful — it is a natural input to this table rather than a competitor. Two options worth deciding between:

  • board_totals carries the streak column, computed by the projector from #1495's table. One read serves all four boards.
  • board_totals covers the three score boards; the streak board reads #1495's table directly.

The first is fewer reads; the second keeps the streak's gaps-and-islands logic in one place. Worth settling when this is picked up, ideally with #1495's owner in the room.

Constraints and traps

  • Identity stays late-bound. This table is keyed by resolved owner, which is a departure from the distinct_id-keyed rule that governs the ledger — and that is only safe because it is a derived projection, rebuilt by the projector, never a system of record. The ledger keeps distinct_id. If a device rebinds, the next projector run corrects the projection. This must be stated in the DDL comment or someone will later mistake it for a frozen identity mapping.
  • Prod DDL must land via the giga-swamp clickhouse-migrations Job with both Job and ConfigMap hash-bumped, on all 3 nodes, and verified per-IP — a load-balanced Service hides per-node drift, and that gap silently dropped every issue_shipped grant after the last cutover.
  • Two-step ordering rule: the table must exist and be backfilled before the reading code deploys.
  • The exclusion anti-join (hidden_usernames) and the founder set still apply at read time and must not be baked into the projection — hiding is app policy that changes without a grant changing.

Verification

  • Parity: board_totals-backed board output byte-identical to the live query across ghosts, multi-device operatives, hidden/enterprise operatives, and founders.
  • A ranking-drift check after a projector stall, proving self-healing.
  • Per-replica verification of the DDL, by IP, not through the Service.

Risk / rollback

Medium. Rollback is a read-path flag back to the live query; the table can be left in place and dropped later.

02Bog Flow
✓OPEN✓TRIAGED✓IN PROGRESS✓SHIPPED+ 1 MOREASSIGNED+ 10 MOREREVIEW+ 3 MOREPR_MERGED+ 1 MORENOTIFICATION_SKIPPED

Shipped

8/11/2026, 5:08:54 PM

Click a lifecycle step above to view its details.

03Sludge Pulse
keeb assigned keeb8/11/2026, 4:42:46 AM

Sign in to post a ripple.