Skip to main content
← Back to list
01Issue
FeatureOpenSwamp Club
AssigneesNone

Relationships

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

Opened by keeb · 8/2/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

Open

8/2/2026, 2:12:19 AM

No activity in this phase yet.

03Sludge Pulse
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).

Sign in to post a ripple.