Skip to main content
← Back to list
01Issue
FeatureTriagedSwamp CLI
Assigneeskeeb

Relationships

#1606 Projector write amplification: board_totals/owner_devices full rewrites are owners × runs/day

Opened by keeb · 8/11/2026

Shipped knowingly with #1575/#1576 (PR #1061), recorded here so #1577 inherits it with the arithmetic rather than the surprise.

What

board-totals-projector.sql and owner-devices-projector.sql are full rewrites. Every run writes every owner. So sustained insert volume is:

owners × runs/day

At the 12,670 board owners prod now reports, on the 60s cadence:

  • ~18.2M rows/day into board_totals, collapsing on merge to ~12,670
  • a comparable figure for owner_devices (one row per owner×device pair)

All of it superseded by ReplacingMergeTree merges. The steady-state content of both tables is tiny; the write path to get there is not.

Why it matters

The cluster is three 2-vCPU droplets, and #1494 is precisely the story of the projector's own CPU starving user-facing reads. This change moved read cost off that cluster and put a smaller but continuous write cost back on it. Nobody has measured the merge pressure in prod.

There is also an inconsistency worth naming, because it was mine: owner_devices was deliberately NOT built as a materialized view partly on write volume — an MV would write ~2.4M rows/day at the measured 0.145 grants/event, which is the per-event drip shape #1587 deleted. The full rewrite writes more than that. The distinction I relied on is real — the MV scales with the event rate, which is the thing growing, while the rewrite scales with population and cadence, which we control — but I did not do the arithmetic before choosing, and the chosen design is not obviously the cheaper one on writes.

The knob nobody is using

The projections do not need rewriting on every rollup tick. The board's own staleness budget is already ~one projector cadence plus a 15s pin bucket, so the read cannot even show the difference. Options, cheapest first:

  1. Run the projections every Nth tick rather than every tick. One constant.
  2. Skip when the rollup touched nothing — the projector already knows whether its INSERT … SELECT wrote anything.
  3. Skip when max(version) in score_daily has not moved since the last projection. Purely ClickHouse-side, so it also works during a Mongo outage (see the sibling issue on the fail-open lease).

Option 3 composes with the lease-guard issue and is probably the one to build.

What to measure first

Do not tune blind. On prod:

SELECT event_type, count(), sum(rows)
FROM system.part_log
WHERE database = 'swamp' AND table IN ('board_totals','owner_devices')
  AND event_time > now() - INTERVAL 1 HOUR
GROUP BY event_type

and the same for merge duration. If merges are keeping up comfortably, this is a note rather than a task. The point of filing is that we shipped without knowing which.

Constraints on any fix

The full rewrite is not incidental — it is load-bearing for three separate reasons, and a naive incremental update breaks all three:

  • t_week and t_today decay with the clock, so an owner who was active yesterday and idle today must be rewritten with no grant to trigger it.
  • owner is late-bound through identity_map, so linking two accounts moves totals with no new grant.
  • A rename leaves score_daily rows carrying the old username label, which no touched-window pass would ever revisit — that is the 43%-undercount hole owner_devices exists to close.

Reducing how often the rewrite runs is safe. Making it partial is not, without solving those three.

Parent: #1572. Sibling: the fail-open lease guard. Shipped in PR #1061.

02Bog Flow
✓OPEN◉TRIAGED○IN PROGRESS○SHIPPED+ 1 MOREASSIGNED+ 8 MOREREVIEW

Triaged

8/11/2026, 5:39:30 PM

Click a lifecycle step above to view its details.

03Sludge Pulse
keeb assigned keeb8/11/2026, 5:38:03 PM

Sign in to post a ripple.