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

Relationships

#1497 Push down the two cheap leaderboard scans: ghost-gate dictionary and eventCounts owner filter

Opened by keeb · 8/2/2026· Shipped 8/15/2026

Context

Measured 2026-08-01 against local ClickHouse system.query_log (dataset 17.35M events; prod swamp.events is 74.75M rows / 6.72 GiB).

One /leaderboard render fires 6 ClickHouse queries, totalling ~2.36M rows read and ~660 MiB peak memory, four of them concurrent via Promise.all in lib/app/leaderboard-view.ts:100-105. A 12-concurrent-viewer test produced 48 queries, 7.66 GiB summed memory, and page latency of 0.12s -> 0.99s. Every score read runs under a 5s max_execution_time (lib/infrastructure/clickhouse/clickhouse-score-reads.ts:462, SCORE_READ_CEILING_S) with a 7s client abort, behind a 3-strike / 30s-cooldown breaker.

The big win (materializing active days for the streak board) is filed separately. This issue covers two smaller, independent pushdowns on the same page — both cheap to do, neither requiring a new rollup of scale.

1. Ghost gate: make CLI_DEVICES a dictionary

lib/infrastructure/clickhouse/clickhouse-score-reads.ts:71:

SELECT DISTINCT distinct_id FROM swamp.cli_daily

This subquery is embedded in the WHERE of every board / rank / slice query via resolvedRows (:149-191). It exists to keep web-only anonymous devices ("ghosts") off the boards — a correct gate, but it rebuilds a ~30k-row DISTINCT set from scratch per query, i.e. 6x per page load, per viewer.

Proposal: back it with a ClickHouse dictionary, or a tiny ReplacingMergeTree keyed by distinct_id, so the gate becomes a lookup instead of a repeated aggregation. Behavior is unchanged; only the cost of asking moves.

Related: there is an existing item on file about hidden_usernames (partition-per-60s) wanting exactly the same dictionary treatment. Same class of fix — worth doing both together so the pattern lands once.

2. eventCounts: filter on distinct_id, not the derived owner

lib/infrastructure/clickhouse/clickhouse-score-reads.ts:1731-1761.

The query scans all of swamp.cli_daily (159k rows, measured — and it runs twice per page load) and only then filters:

SELECT owner, sum(cnt) AS ev FROM (
  SELECT
    coalesce(nullIf(im.username, ''), cd.distinct_id) AS owner,
    cd.cnt AS cnt
  FROM swamp.cli_daily AS cd
  LEFT JOIN (...) AS im ON cd.distinct_id = im.distinct_id
)
WHERE owner IN {owners:Array(String)}
GROUP BY owner

owner is a coalesce over the identity_map LEFT JOIN, so it is computed after the scan. The filter therefore cannot push into the table scan — ClickHouse has to read every cli_daily row, join it, and throw away almost all of it.

Proposal: resolve the page's owners to their distinct_ids app-side first (the identity resolution is already available on this path), then filter cd.distinct_id IN {ids:Array(String)}. cli_daily is ORDER BY (distinct_id, command, subcommand, day), so that predicate pushes straight into the primary key — a full scan becomes a keyed lookup.

Constraint: identity stays late-bound. Resolving owner -> ids is a read-time expansion of the current identity set for the operatives on the page, not a frozen mapping stored anywhere. The resulting counts must remain the sum over all of an operative's machines, not one of them.

Notes

Both changes are behavior-preserving; the payoff is per-viewer cost, which is what makes the concurrent-viewer numbers above scale badly. One of three issues from the same /leaderboard investigation — the others cover the streak-board materialization and the shared score-read circuit breaker.

02Bog Flow
✓OPEN✓TRIAGED✓IN PROGRESS✓SHIPPED+ 1 MOREASSIGNED+ 2 MOREREVIEW

Shipped

8/15/2026, 1:43:34 AM

No activity in this phase yet.

03Sludge Pulse
keeb assigned keeb8/14/2026, 10:04:39 PM
Editable. Press Enter to edit.

keeb commented 8/2/2026, 2:12:33 AM

Siblings from the same 2026-08-01 /leaderboard investigation: #1495 (materialize active-days-by-id for the streak board) and #1496 (shared score-read circuit breaker).

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

Cross-link: this is now part of epic #1572 (leaderboard + profile at 10B events).

Both pushdowns here remain valid and are not superseded by the epic. The epic's phases 1-3 (#1573, #1574, #1575) attack the per-viewer recompute shape — caching, removing the live score_grants tail, and pre-aggregating to owner grain. None of that removes the cost of a cache miss, which is exactly what these two pushdowns reduce.

One calibration note: this issue cites prod swamp.events at 74.75M rows (2026-08-01). As of 2026-08-10 it is 225,037,546 rows / 18.19 GiB.

Also worth recording against item 1 here: the CLI_DEVICES ghost gate was measured as a distinct component of one board render at 157,490 rows / 6.61 MiB, re-evaluated per query. The dictionary treatment proposed here is confirmed as a real, separable win.

Sign in to post a ripple.