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

Relationships

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

Opened by keeb · 8/2/2026

Problem

The streak board is the single most expensive query behind /leaderboard.

Measured 2026-08-01 against local ClickHouse system.query_log (dataset 17.35M events; prod swamp.events is 74.75M rows / 6.72 GiB):

  • One /leaderboard render fires 6 ClickHouse queries, totalling ~2.36M rows read and ~660 MiB peak memory. Four of them run concurrently via Promise.all in lib/app/leaderboard-view.ts:100-105.
  • The streak board alone accounts for 940k rows read / 182 MiB peak, versus ~366k rows / ~150 MiB for a score board.
  • A 12-concurrent-viewer test produced 48 queries, 7.66 GiB summed memory, and page latency of 0.12s -> 0.99s.

All of this runs under a 5s max_execution_time (lib/infrastructure/clickhouse/clickhouse-score-reads.ts:462, SCORE_READ_CEILING_S) with a 7s client abort, behind a 3-strike / 30s-cooldown breaker — so the headroom between "slow" and "board goes unavailable" is thin.

Cause

ownedDays in lib/infrastructure/clickhouse/clickhouse-score-reads.ts:246-280 unions three day sources:

  1. score_daily (via ownedGrants)
  2. swamp.cli_daily
  3. swamp.days_active

streaksSql (same file, :291-310) then does groupUniqArray(toInt32(day)) per owner for the gaps-and-islands streak computation. The triple union is what makes it the heaviest read on the page.

Measured overlap — the union is buying almost nothing

swamp.days_active is a 99.96% superset of the other two:

  • Of 30,757 distinct (distinct_id, day) pairs in cli_daily, only 8 are absent from days_active.
  • Of 197k pairs in score_daily, only 104 are absent.

So the three-way union triples the scan in order to recover 112 pairs.

Proposal

Add a compact table:

swamp.active_days_by_id (distinct_id String, day Date)
ENGINE = ReplacingMergeTree
ORDER BY (distinct_id, day)

Fed by materialized views:

  • from swamp.events — covers the days_active + cli_daily day sets
  • from swamp.score_grants — covers the grant-only days

ownedDays then becomes one ~257k-row keyed scan plus the existing identity_map join, instead of 940k rows across three sources.

Constraint that must hold

Identity stays late-bound. Key the table by distinct_id, never by username/owner. Resolution to owner happens at read time via identity_map, exactly as today — an operative is many machines, and freezing the owner into the rollup would re-break that.

An MV is not a cache: it fires on INSERT, so the streak board stays realtime. No freshness regression.

Notes

  • Prod ClickHouse is an external 3-node replicated cluster and the MVs are local, so the DDL has to reach all three nodes — same shape as prior schema changes, see infrastructure/clickhouse/CLAUDE.md.
  • This is one of three issues from the same 2026-08-01 /leaderboard investigation. The other two cover the shared circuit breaker and two cheaper scan pushdowns.
02Bog Flow
✓OPEN✓TRIAGED○IN PROGRESS◉CLOSED+ 3 MOREASSIGNED+ 2 MOREREVIEW

Closed

8/24/2026, 11:59:46 PM

No activity in this phase yet.

03Sludge Pulse
keeb assigned keeb8/14/2026, 10:04:42 PM
keeb unassigned keeb8/15/2026, 1:47:28 AM
swamp_lord assigned swamp_lord8/24/2026, 8:37:42 PM
Editable. Press Enter to edit.

keeb commented 8/2/2026, 2:12:29 AM

Siblings from the same 2026-08-01 /leaderboard investigation: #1496 (shared score-read circuit breaker) and #1497 (ghost-gate dictionary + eventCounts pushdown).

keeb commented 8/10/2026, 7:21:48 PM

Cross-link: this is now part of epic #1572 (leaderboard + profile at 10B events).

Two notes from the 2026-08-10 measurements that bear on this issue:

  1. The baseline moved. This issue cites prod swamp.events at 74.75M rows (2026-08-01). Nine days later it is 225,037,546 rows / 18.19 GiB — 3x, in nine days. The analysis here is still correct; the urgency is higher than it reads.

  2. Still worth doing, and it composes. Materializing active-days attacks the streak board's scan cost, which remains real on a cache miss. #1575 proposes an owner-grain board_totals maintained by the projector, and active_days_by_id is a natural input to it rather than a competitor. #1575 flags an open question the two should settle together: whether board_totals carries a streak column computed from this table (one read serves all four boards), or the streak board reads this table directly (keeps gaps-and-islands logic in one place).

The late-bound-identity constraint stated here — key by distinct_id, resolve to owner at read time — is preserved in #1575, which is explicit that its owner-keyed projection is a derived rebuild, never a system of record.

swamp_lord commented 8/24/2026, 9:06:30 PM

Rollout plan

Create and backfill the new active-day projection in one additive production rollout before swamp-club reads it.

  1. Add swamp.active_days_by_id, keyed by (distinct_id, day), using ReplicatedReplacingMergeTree, monthly partitions, and no owner field so identity remains late-bound.
  2. Add two ON CLUSTER clickhouse_production materialized views: swamp.events -> toDate(created_at) and swamp.score_grants -> toDate(granted_at).
  3. Synchronize the durable source replicas on the DDL host, then backfill in the same SQL execution from days_active UNION cli_daily UNION score_grants, grouped by (distinct_id, day). cli_daily remains in the historical seed because the investigation measured eight CLI day pairs absent from days_active; score_grants covers grant-only days and the unprojected live edge.
  4. Make retries and live-MV/backfill overlap harmless through set semantics: ReplacingMergeTree at the raw key and downstream owner/day deduplication. No ingestion pause or T0 cutoff is required.
  5. Add fail-fast source-minus-target parity assertions and write one idempotent schema_migrations row only after they pass. Mirror the table and both MVs in giga-swamp’s schema.sql.
  6. Apply the one SQL file through clickhouse-production-apply-sql: all-replica preflight, one full backup, manual approval, one-host apply, then per-replica verification of table/MV definitions, replication health, migration-row uniqueness, and membership parity.
  7. Roll back by dropping the two MVs first and leaving the unused target table in place. Do not restore the full-cluster backup for an additive-object failure.
  8. Gate the later swamp-club reader change on owner-level parity. The current reader also has stamped grant-label fallback and owner-level badge folding; a two-column raw set must either prove those cases unchanged or document an explicit behavior decision before the reader deploys.

Implementation branch: infra/clickhouse-active-days-by-id in giga-swamp. The production schema/backfill rollout is deliberately separate from the swamp-club reader deployment.

swamp_lord commented 8/24/2026, 9:43:56 PM

Production rollout completed on 2026-08-24. Branch infra/clickhouse-active-days-by-id, commit 29ff13d. clickhouse-production-apply-sql completed after standalone backup premig-active-days-by-id-2026-08-24-r2 reported BACKUP_CREATED. Migration installed swamp.active_days_by_id plus the events and score_grants MVs, backfilled the durable union, and passed source-minus-target assertions. Post-apply verification on ch-01/ch-02/ch-03: both MVs present, 650657 physical rows / 650611 distinct device-day memberships on each replica, and 0 unfinished mutations. Reader switch remains separately gated on owner-level parity and badge/label fallback behavior.

swamp_lord commented 8/24/2026, 11:59:46 PM

Completed by swamp-club PR #1136. The reader now uses swamp.active_days_by_id while preserving late-bound identity resolution, stamped grant-label fallback, ghost visibility gating, collective exclusion, and founder exclusion. Adversarial live ClickHouse parity passed, CI and UAT passed, merge commit a30e9284 deployed successfully on 2026-08-24, and the production streak endpoint is serving results after deployment.

Sign in to post a ripple.