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:

quantity	rows
`touched` grains	74,671
`member_grants` (closure)	178,124
`score_grants` total	1,308,981
rows read per run	~20,000,000 (~1.45 GiB)

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:

node	runs	busy seconds	duty
[IP-1]	224	3,677	0.68
[IP-2]	201	4,244	0.79
[IP-3]	211	7,910	1.46

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

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.
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.
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).

02Bog Flow

Shipped

8/2/2026, 1:30:14 AM

Click a lifecycle step above to view its details.

03Sludge Pulse

keeb assigned keeb8/2/2026, 12:09:19 AM

Document --restricted-commands flag for swamp serve

vault put: pass tags through to extension provider CreateSecret/setSecret calls

Docs: update manual for 23 new --server commands

Update VaultProvider interface in @systeminit/swamp-testing to support optional tags in put()

Docs: document --grants-file flag for swamp serve

Docs: document --server support for serve reload

Docs: add --tag flag to vault put reference

Docs: update manual for --detach-runs deprecation and HA auto-detection

Document the Codex plugin in swamp docs

Add --server support to CLI commands missing it

Document the swamp Claude Code plugin in the swamp documentation

Make --server a global CLI option

Add --server support to model cancel (server-side run cancellation)

Load grants from external source on swamp serve startup

swamp vault put should support tags for secret governance

Improve hot reload of the deamon, include extension source/bundle changes

Authenticated users must re-login after swamp serve restart

Add --server support to serve reload

Harden queue claim semantics: atomic claims + status-guarded markDelivered/markFailed across app queues

Auto-detect HA mode and deprecate --detach-runs flag

version-drift check (from #236) is a raw string-inequality, false-positives on multi-model manifests and metadata-only bumps

Resend newsletter audience sync: contacts, segments, backfill, unsubscribe sync-back

Move per-login token secrets from vault to encrypted control-plane store

Docs: document macOS TLS trust limitation and SSL_CERT_FILE workaround

Validate swamp serve deployment stack on startup

Docs: serve config file (.swamp/serve.yaml) and --config flag

Serve config file — read swamp serve configuration from YAML

Deno runtime does not trust ISRG Root YR — extensions fail with valid Let's Encrypt certs

Docs: update hot-reload reference to include trust list refresh

Cross-instance client re-attach for multi-instance swamp serve

vercel codegen: inline rawSyncResult in non-unwrap sync path

vercel codegen: unused listAll import blocks extension publish for 4 models

vercel codegen: add round-trip idempotency test for the pipeline

Operatives button on Collectives page does nothing

Track active runs in the control-plane store for cross-instance visibility

validateGlobalArgInputRefs checks step-level inputs but step schema rejects 'inputs' key

Docs: document continuous reconciliation and multi-instance serve flags

Docs: update workflow guard skip output to include guardExpression and guardResult

Make it aware hot reload on the deamon does not take into account trust list changes, or do take into account the trust list for the hot reload.

workflow validate: new 'GlobalArgument input references' check resolves model-definition ${{ inputs.* }} against workflow inputs, false-failing steps that supply the input

Continuous reconciliation for multi-instance swamp serve

Split the score-read circuit breaker so leaderboard pressure can't blank profile scores

oauth_sign_in telemetry records provider as the literal ":id" for every event

Ghost profile bypasses the score-read seam and 500s on a slow ledger

Profile score section blocks the render on a ClickHouse read

Council: set the scoring balance — command tiers, farm-decay parameters, cross-category ratio

Scoring coverage gaps: instrument drop-off, score ripples/mentions, reward invite-converts

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

Materialize an active-days-by-id table for the streak board

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

Startup cache hydration for swamp serve instance replacement

Output a log line for steps skipped due to guards

extension quality scorer misreads a template-literal import specifier in generated code as an unresolvable bare import, hard-erroring the whole extension as UNSCORABLE

Add @swamp/kubernetes/statefulset model type

Docs: access token commands reference says plaintext is 'shown once'

Document --restricted-model-types flag for swamp serve

swamp auth server-login ignores SWAMP_SERVE_URL

s3-datastore extension: pullChanged writes namespace-stripped paths to local cache

serve oauth: an unrecognized device-token error kills the whole serve process instead of the bootstrap

s3-datastore: bidirectional sync pull writes namespace-stripped copies to cache root, wedging push with no supported recovery

CLI reports 'HTTP/2 not supported by this client' when the server actually returned 401 — masks auth failures as a transport error

access token mint: allow reusing a revoked token name

extension quality --json exits 1 with 'rubric factors missing' even when a full score was computed (unearned factor, not uncomputable)

serve oauth: startDeviceGrant discards the OAuth error field, making device-code failures undiagnosable

access token rotate: add --vault flag to override inherited vault

Extensions without a repository can't receive issues — including the issue asking them to add one

serve oauth bootstrap: device flow rate-limited to a 45-second window by validateClient counter, shared globally across tenants

workflow edit: after saving an invalid workflow, re-running edit reports "Workflow not found"

Authentication via Google Broken

Plus-addressed emails stripped on invite acceptance, breaking collective join and quest reward

Profile edit broken on Safari

Configurable write paths for object-creating commands (repo-relative swamp root)

Docs: document rollbackOnFailure with safety guidance for external-dependency models

Remote execution tutorial: sample runner output is missing the stdout line that #1462 restored

seadex-latent-bugs: latent product bugs pinned by the wave-2b test backfill

Remote step stdout is not streamed to the workflow runner (log artifact is transferred correctly)

Redactor false positives: HH:MM:SS timestamps masked as IPs, SHA-256 digests masked as secrets (both inside code fences)

Remote execution tutorial: worker token list output and inline workflow-summary block no longer match CLI output

Add rollbackOnFailure for method-level write atomicity

docs: working-with-data tutorial uses removed depends_on step key, blocking the tutorial