Skip to main content
← Back to list
01Issue
FeatureOpenSwamp Club
AssigneesNone

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○SHIPPED

Open

7/18/2026, 5:34:39 PM

No activity in this phase yet.

03Sludge Pulse

Sign in to post a ripple.