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

Relationships

#1576 Profile candidateIds prune costs a full ledger scan to build itself — 42.88M rows for one keyed read

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

Phase 4 of #1572. Filed as a bug because the documented behaviour of this path is not what it does at scale.

What

candidateIds (lib/infrastructure/clickhouse/clickhouse-score-reads.ts) resolves an owner's candidate devices so keyed profile reads become primary-key range reads. The module documents the result as:

a single-user read drops from 880K rows / 29 MiB to keyed-read class (~8ms / 4.4 MiB)

That measurement was taken when score_daily held ~210K rows. It does not survive scale, because two of the CTE's four arms are full table scans:

SELECT DISTINCT distinct_id FROM swamp.score_daily  WHERE username = {u}
SELECT DISTINCT distinct_id FROM swamp.score_grants WHERE granted_at >= today AND username = {u}

username is in neither table's sort key:

  • score_daily — ORDER BY (distinct_id, day, category, subject_id)
  • score_grants — ORDER BY (distinct_id, granted_at, grant_id)

So both arms scan. And cand is referenced three times in the enclosing query (the score_daily prune, the score_grants prune, and the cliDevices ghost gate) — ClickHouse inlines CTEs rather than materializing them, which the codebase already documents elsewhere.

Measured

Against a 10B-scale synthetic (6M score_daily, 14.5M-row today-tail):

rows read
score_daily arm alone 5.99M (the whole table)
score_grants arm alone 14.50M (the whole today-tail)
One "keyed" single-user profile read 42.88M rows / 107 MiB

42.88M rows to render one operative's profile.

Why this is a bug and not just slow

The prune makes the outer read cheap, which is what the original measurement captured. But the CTE that enables the prune is O(entire ledger). Net effect: profile reads scale with total platform events, not with the operative's own data — the exact opposite of the design intent stated in the module docstring.

It also degrades silently. loadProfileScore catches failures to EMPTY_PROFILE_SCORE, so past the 5s ceiling a profile renders a blank standing rather than an error.

Proposal

Replace the two scan arms with a maintained owner-to-devices projection:

owner_devices (owner String, distinct_id String, version DateTime64(3))
ENGINE = ReplacingMergeTree(version)
ORDER BY (owner, distinct_id)

Candidate resolution becomes a primary-key point lookup. The remaining arms (identity_map FINAL by username, and the owner-string arm for ghosts) are already keyed or trivial.

The superset property must be preserved. candidateIds is documented as a prune, never a correctness filter — it must remain a superset of the owner's devices, with the authoritative owner/coalesce filter still deciding which rows qualify. A narrower set silently under-counts; the docstring records a measured 43% under-count for exactly this mistake. The projection must therefore cover every source that can carry the owner, including devices known only to the ledger.

Fold in two adjacent items

  1. countIssueShipped does score_grants FINAL WHERE grant_type = 'issue_shipped'. grant_type is not in the sort key, so FINAL cannot prune — this is a whole-ledger merge bounded only by the keyed distinct_id IN and the epoch. Same class of problem, same file family.
  2. dailyHistory runs the full-history cumulative walk in SQL and then slices to historyDays in TypeScript (series.slice(-limitDays)). The profile requests historyDays: 1 and still pays the whole walk. Push the range into SQL.

Relationship to existing issues

  • #1503 (profile score blocks the render) is complementary — that is about when the read happens, this is about what it costs. #1503 asks for a measurement of which read dominates; the numbers above are part of that answer, though the Mongo side still needs its own pass.
  • #1256 (per-user daily rollup) is adjacent but distinct — it covers the OPERATIVE_PERFORMANCE rail reading raw swamp.events, not the candidateIds scan.
  • The profile's ~15 Mongo round trips with several serial hops are a separate concern and remain the felt latency floor. That is the still-unshipped 2026-07 audit (#921-#926); worth deciding whether it rides along with this.

Verification

  • Byte-identical profile output before/after, across a single-device operative, a device-heavy operative, a ghost, and the label-only case.
  • An explicit superset assertion: the new candidate set is never smaller than the old one for any fixture owner.
  • Row-count regression assertion, so a future edit that reintroduces an unkeyed arm fails in CI rather than in prod.

Risk / rollback

Medium — requires DDL + backfill, so it inherits the 3-node per-IP verification and two-step ordering constraints from #1572. Can share a migration window with phase 3.

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

Shipped

8/11/2026, 5:09:10 PM

Click a lifecycle step above to view its details.

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

Sign in to post a ripple.