ClickHouse retention scans OOM-killed the analytics store — root cause and fix (2026-07-24)

What happened

All analytics dashboard loads got slow after the Fly migration ("Railway was faster") — retention on big games took 40–110s or timed out. Real cause was not Fly vs Railway: ClickHouse (quest-system-clickhouse, 8 GB Fly box) was hitting its server-wide ~7 GiB memory cap on retention reads. The OvercommitTracker killed the biggest queries — and sometimes innocent bystanders, including 2 MiB INSERTs, so ingested events silently landed Postgres-only and CH drifted. Every killed/timed-out read silently fell back to the near-empty Postgres mirror, which is why dashboards were slow and sometimes wrong rather than erroring.

Root cause (three factors, all required)

1. retention_events_ch filtered only game_id + occurred_at >= now()-30d. The raw TTL is also 30 days, so the time filter matched ~every row of the game.
2. quest.game_events sort key is (game_id, event_type, occurred_at, id) — ORDER BY occurred_at can't stream in index order across event-type runs, so LIMIT 50000 applies only after a full sort. Planner-verified: 206,005,819 rows read for one retention call on the 189M-row world-cup-event-testing game; with an event-type filter: 5,025,217 (41×).
3. ClickHouse's memory cap is server-wide (shared with merges + inserts + all concurrent reads) — one runaway read starves everything else.

Fix (PR #9, fix/ch-retention-memory)

• retention_events_ch now requires event_types and pins event_type IN (...): session/round markers + the game's catalogued "meaningful action" types. Uncatalogued types can't contribute to any retention metric, so semantics are unchanged for uncapped samples. Same filter on the PG fallback.
• Per-query guards on the shared CH client: max_memory_usage = 3 GiB, max_bytes_before_external_sort = 1 GiB (sorts spill to disk). Ingest batches are ≤500 rows, so writes have ~1500× headroom.
• D1/D7 now compute from the player_days rollup even when the filtered raw sample is empty.

Context / when to revisit

• Data as of 2026-07-24: quest.game_events = 215M rows / 6.8 GiB compressed; the event-testing floods (world-cup-event-testing 189M, fish-a-brainrot-event-testing 6.9M) are most of it. The 8 GB box was sized for a ~68M-row backfill.
• General lesson: on ClickHouse, a LIMIT is not a cost cap — if your ORDER BY doesn't match the table's sort-key order, the whole filtered set is read and sorted first. Design queries to lead with sort-key prefixes (here: pinning event_type, the key's 2nd column, turned a full-game scan into tight ranges).
• Follow-ups: CH is missing rows for the killed-INSERT period (diff CH vs PG per game/day and backfill); consider purging the test floods; watch system.query_log WHERE exception_code = 241 after deploy — it should go quiet. Full runbook: docs/clickhouse-rollups-runbook.md §Incident 2026-07-24.