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

Relationships

#1599 dailyHistory range pushdown — deferred from #1576, and the measurement says why

Opened by keeb · 8/11/2026

Split out of #1576 during implementation, under the escape clause its own plan step carried ("if the carry-in proves fiddly, split it out to a follow-up issue rather than holding the migration").

What was proposed

dailyHistory (lib/infrastructure/clickhouse/clickhouse-score-reads.ts:753-858) runs its two queries over FULL history and then truncates in TypeScript with series.slice(-limitDays). routes/u/[username].tsx:214 calls it with historyDays: 1, so every profile render pays the whole walk to display one row. #1576 proposed pushing the range into SQL.

Why it was deferred

It is under 1% of the read it lives in. Measured on the dev ClickHouse (206,803-row score_daily), the cand CTE alone reads 209,296 rows / 6.92 MiB to resolve a 12-device candidate set — and dailyHistory builds that CTE twice, referencing it twice in the deltas query and five times in the days query. The heaviest operative on that dataset (keeb) has 2,045 grains across 900 active days. So the operative's entire history is ~1% of what one call reads; the other 99% is the #1576 bug itself.

After #1576's owner_devices fix lands, cand becomes a keyed lookup and the picture inverts — but into numbers too small to matter: roughly 2,900 rows becomes roughly 950, both trivial for a keyed read.

And the carry-in is not free

The walk produces RUNNING cumulatives, so a windowed SQL query needs an opening balance: pre-window totalScore, pre-window per-category totals (a category last seen before the window must still appear in the first windowed row), pre-window activeDays, pre-window totalEvents — and the trailing streak run, which can extend arbitrarily far back. That last one still has to read every pre-window day row to compute, so the days query does not get cheaper; it only returns fewer rows. The complexity is real and the saving it buys is not.

What would make this worth revisiting

Do #1576 first, then re-measure. This becomes worth doing if either holds:

  • an operative's own history grows enough that a few thousand grain rows is no longer trivial (the grain decision in #1578 moves this directly), or
  • the wire transfer rather than the scan becomes the complaint — a profile render currently ships ~900 day rows plus ~2,045 delta rows to display one, which is a payload argument, not a read-cost one, and would be better solved by returning the tail plus a carry-in row than by restructuring the walk.

Note lib/app/profile-score-reads.ts:281 calls dailyHistory(owner) with no limit for standings, so full history genuinely is needed on that path — any pushdown must keep the unbounded callers reading everything.

Parent: #1572. Sibling: #1576, which retains the countIssueShipped half of the same fold-in — that one measured 55.76 MiB / 42.08 MiB peak down to 2.78 MiB / 441 KiB by replacing FINAL with an explicit grant_id dedupe, and is being implemented.

02Bog Flow
◉OPEN○TRIAGED○IN PROGRESS○SHIPPED

Open

8/11/2026, 5:29:01 AM

No activity in this phase yet.

03Sludge Pulse

Sign in to post a ripple.