#1494 score-rollup-projector: O(all-time ledger) closure + per-pod fan-out saturates prod ClickHouse CPU

Summary

score-rollup-projector.sql is the dominant CPU consumer on the prod ClickHouse cluster. Two independent multipliers stack: it reads ~112x more rows than its working set requires, and every telemetry-api pod runs it redundantly. Under load this starves user-facing SELECTs, which trip their 5s max_execution_time and surface as product 5xx and tier-detection-loop alerts.

Measured 2026-08-01 against clickhouse-production (v26.3.17.4).

Evidence

Read amplification. Working set vs. rows actually scanned, per run:

178K rows needed, 20M read — ~112x. The file's own header names the mechanism: "ClickHouse INLINES every CTE reference (no materialization — this statement is ~10+ scans of score_grants per tick)". The nested closure stages (touched → member_grants → write_grains → scope_rows) are each re-evaluated inline via IN (SELECT …).

Duty cycle — fraction of wallclock each node spends executing this one statement, 90-minute window:

The droplets are 2 vCPU. A duty cycle above 1.0 means multiple copies running concurrently and continuously.

Fan-out. system.query_log shows 10 distinct initial_address values executing this fingerprint — one per telemetry-api pod. All 10 compute an identical result from identical input and write duplicate rows into a ReplacingMergeTree. That is redundancy, not parallelism. telemetry-api is currently pinned at HPA max (10/10, bound on the memory metric), so the fan-out scales up under exactly the conditions where ClickHouse can least afford it.

Self-amplifying. Average duration on .22 by 3h bucket:

07-31 12:00    7.6s
08-01 06:00   15.8s
08-01 12:00    9.7s
08-01 18:00   20.9s
08-01 21:00   27.0s   (max 90.8s)

TIMEOUT_EXCEEDED (code 159, the 5s read limit) tracks it: 3 → 34 → 61 per 3h bucket. Runs arrive every ~23s per node and take ~41s, so they stack.

Impact

• User-facing reads (leaderboard, quest grants, identity-map lookups, tier detection) starve and blow their 5s limit → product 5xx + [sc] Tier-detection loop erroring.
• Cost is O(all-time ledger), not O(delta). score_grants grows ~70K rows/day and that rate has roughly doubled in two weeks, so this degrades continuously regardless of traffic.
• No horizontal headroom. system.clusters reports 1 shard × 3 replicas, so any single INSERT…SELECT executes on exactly one 2-vCPU node. Replicas add read fan-out and zero write/compute scaling.

Worth noting the projector's input does not scale with event volume: score_grants generation held flat at ~1.5K/hr straight through a 7x spike in swamp.events (200K/hr → 1.4M/hr). The scaling axis is ledger size, not traffic.

Suggested direction

1. Materialize the closure stages (staging/temp table) instead of relying on inlined CTEs, so cost tracks the delta (~400K rows) rather than history (~20M). Single biggest win, and it changes the growth curve rather than just shifting the constant.
2. Make the tick a singleton (leader lock or single-replica CronJob) to remove the 10x redundancy. Only meaningful after (1) — one leader running the 20M-row form still doesn't fit in the tick interval.
3. Longer term, consider deleting the batch pass. An AggregatingMergeTree keyed by (distinct_id, grant_id) holding argMaxState(day, granted_at) makes the day a value rather than part of the key, so a grant moving grains becomes a state merge — no closure, no tombstones, O(insert) forever.

Context

The closure + tombstone machinery was the fix for #1082 (shipped). This issue is about its cost profile, not its correctness — the projector's output is correct, it just pays O(ledger) per tick to produce it. Option 3 above would subsume the mechanism #1082 introduced rather than optimize it.

Related: #929 (incremental activity projector) is the sibling problem on activity-asof-projector.sql, which is the #2 consumer here (avg 4.3s, 349 runs/2h on .22).