Skip to main content
← Back to list
01Issue
FeatureClosedSwamp ClubPublic
Assigneeskeeb

Relationships

#1256 Profile stats: per-user daily rollup table + MV in ClickHouse

Opened by keeb · 7/18/2026

Problem

The profile OPERATIVE_PERFORMANCE rail, operative card, and active-users/roster reads now compute from raw swamp.events at request time (the /v1/stats → ClickHouse migration, done deliberately with no schema change because prod CH deploys are painful right now). Correct, but structurally expensive:

  • swamp.events is ORDER BY (event, created_at) — per-username reads have no primary-key support (the bloom filter covers distinct_id only), so each is a scan bounded only by the 8s ceiling and a 60s per-pod TTL cache.
  • The 2026-07 profile perf audit already flagged per-profile event scans as the dominant cost class on /u/{username}.
  • Query cost grows with event volume forever (and with the planned forever-TTL on swamp.events, history never ages out).

Proposal

A per-user daily stats rollup, maintained as a stored object pair:

  • swamp.user_stats_daily — SummingMergeTree keyed (username, day, event, command, subcommand) with count + error-excluded count.
  • user_stats_daily_mv — per-event MV over swamp.events (9th MV on the insert path).

Readers (clickhouse-telemetry-stats.ts, clickhouse-metrics-reads.ts member-activity/active-users) switch their scans to the rollup; identity late-binding (username ∪ identity_map devices) stays read-time.

Rollout constraints (the reason this is deferred)

  • New table + MV must land via the giga-swamp clickhouse-migrations Job (hash-bump both names) on all 3 nodes of the external cluster before the reading code deploys — the #839 two-step ordering rule.
  • One-time backfill INSERT INTO user_stats_daily SELECT … FROM swamp.events — safe w.r.t. the grant MVs (it does not insert into swamp.events), but must run node-aware on the replicated setup.
  • Dedup caveat: blocks skipped by insert_deduplication_token still fire MVs — the events consumer's no-reexecute discipline already covers this; the new MV must not add any new insert path.

Alternatives

  • Status quo (cache-bounded scans): fine at today's volume, degrades linearly.
  • Add username to the events sort key / a projection: rewrites the base table — strictly worse to deploy than an additive MV.
02Bog Flow
✓OPEN✓TRIAGED○IN PROGRESS◉CLOSED+ 1 MOREASSIGNED+ 2 MOREREVIEW

Closed

8/15/2026, 1:01:17 AM

No activity in this phase yet.

03Sludge Pulse
keeb assigned keeb8/14/2026, 10:04:45 PM
Editable. Press Enter to edit.

keeb commented 8/10/2026, 7:22:09 PM

Cross-link: related to epic #1572.

This issue's core premise — "query cost grows with event volume forever" — is now measured rather than predicted. Prod swamp.events went from 74.75M rows (2026-08-01) to 225,037,546 rows / 18.19 GiB (2026-08-10), and the epic projects the read paths against 10B.

Scope boundary so the two do not collide: this issue covers the OPERATIVE_PERFORMANCE rail / active-users / roster reads scanning raw swamp.events. #1576 covers a different defect on the same page — the candidateIds prune in the score-ledger read path building its candidate set via two full table scans. Different queries, different tables, both on /u/{username}.

The rollout constraints written up here (hash-bump both Job and ConfigMap, all 3 nodes, two-step ordering, node-aware backfill) apply verbatim to #1575 and #1576, and are restated in the epic.

Sign in to post a ripple.