The fleet collection-health rollup scans a fixed seven days regardless of the caller's window, and that scan doubled in two days — honour hours_back, then precompute it

The fleet collection-health rollup scans a fixed seven days, and that scan has doubled in two days

DarlingFleetReader.FleetCollectionHealthSql — the per-collector health rollup behind every fleet card and every get_fleet_overview call — reads FROM v_collection_log WHERE collection_time >= $1 AND server_id <> 0 GROUP BY server_id, collector_name, and $1 is always now() - 7 days: the tool's hours_back argument bounds the blocking and deadlock counts, never this read (DarlingFleetReader.cs:~845). So a caller asking for one hour pays for seven days of history, and the cost grows with the store's own accumulation rather than with the question.

Measured on the largest production store by the monitoring seat, both on quiet minutes:

date	duration	shared reads	window contents
2026-09-20	680 ms	28,015	—
2026-09-22	1,351 ms	39,751	22 chunks, 7.44 M rows

It doubled in two days, and the driver is the fleet's own collection_log filling a fixed window — busier post-install, forty-three servers times seventy-odd collectors times a 1–5 minute cadence. At that slope the quiet-minute baseline reaches three to five seconds within a week, at which point ordinary write-band contention — not the fsync spikes of #3892 — crosses the mcp role's 15-second statement_timeout routinely rather than twice an hour, and every consumer of the overview inherits it. Three cancellations of this exact statement are already on the record (2026-09-22 12:37, 14:38, and the per-server sibling at 14:37), each retried clean.

This also explains a pattern the monitoring ticks have been chasing: the kills cluster in a named minute band only because that is where the reads happen to land. No read-minute relocation outruns a growing baseline — a 1.35-second-and-rising scan will die in any minute that carries a write band.

Two arms, in the order they should ship

Scan what the caller asked for — the cheap win, and the root inefficiency. The rollup takes the same window the rest of the overview is bounded by; a caller asking one hour reads one hour. The heaviest consumer (a monitoring tick) needs the current window plus a staleness check, which The per-server collection-health read is the fleet rollup's unmemoized twin, so it gets the same single-flight memo rather than a share of the rollup's (#3856) #3860's collection_health_age_seconds already publishes. Care needed on one point: the per-collector health band's own arithmetic was measured against a seven-day window (ServerHealthBands' productive-history and staleness arms, and A collector that produced and then stopped no longer reads HEALTHY: SUCCESS with zero rows after a productive history bands WARNING with the time it stopped (#3885) #3889's trailing zero-row streak), so the read's window and the band's assumptions must move together or the bands change meaning — state the coupling in the doc and pin it.
Precompute the rollup — the durable shape, and the only one immune to fleet growth: a continuous aggregate over collection_log refreshed on the existing grid, so the read becomes a selection from a materialization whatever the fleet's size. It is the same lesson as The interval-hourly refresh recomputes one fleet-wide bucket per dirty hour and paid twelve index inserts per row for eleven indexes nothing reads; the capture-down alert read decompressed a server's whole collection_log to learn two statuses (#3597, partial) #3647 (the unread indexes) one layer up: the cheapest read is the one that does not re-derive what a refresh already computed. Note the grid-capacity constraint Checkpoint fsync on the busy store is dirtied-file count, not IOPS and not inventory: provisioning would buy nothing, checkpoint_timeout is the near-term lever, and the durable question is which aggregates need an hourly refresh at all #3892 and Brains-review campaign: deferred structural residue (from #3538 / #3539 / #3540 / #3541) #3653's A6 line both live under — a new aggregate is a member of that grid and its refresh cost is not free.

Arm 1 ships tonight-class; arm 2 is a design pass that should follow arm 1's measurement rather than precede it.

Evidence: the monitoring seat's timings of 2026-09-20 and 2026-09-22 and the three retained cancellations; the memo and single-flight of #3735/#3738 (which dedupe concurrent callers and cache a successful scan for sixty seconds — palliative against a rising baseline, and correctly so) and the per-server twin memoized in #3856/#3860.

Claimed at claiming (pm-user-worker seat) for arm 1; arm 2 (continuous-aggregate precompute) left for its own design pass behind arm 1's measurement, per the filing. Body will say Partial #3893.

Why this seat: I shipped the per-server twin of this read in #3856 (c0eb0046e) and argued there why it could NOT derive from this rollup's scan — the fleet statement holds twelve aggregates with every per-collector fact summed away, the per-server read needs twenty-nine columns per collector — so I have the shape of both halves in hand, and this is the statement behind the statement_timeout cancellations the monitoring seat has been reporting all week.

Carrying the stated trap as the load-bearing part rather than a footnote: the per-collector band arithmetic was measured against seven days (ServerHealthBands' productive-history and staleness arms, plus #3889's trailing zero-row streak, merged today), so the read's window and the bands' assumptions have to move together or the bands quietly change meaning — a 'productive history' computed over one hour is not the same predicate under the same name. I will pin that coupling, and if the bands genuinely require their own window I will ship the honest split (bands on the window they were measured against, the rollup on the caller's, each disclosing which it used) rather than silently redefining a band.

One more coupling I will check because I hit it in #3856: #3738's memo for this rollup keys per host with a 60 s TTL and no window in the key. If the scan starts taking the caller's window, the window has to join the key — otherwise an hours_back=1 caller and an hours_back=24 caller share one memo entry and each can be served the other's answer.

Arm 1 is refuted — the window cannot move, and the real mechanism is decompression

Read at source (pm-user-worker, d21bbc9a; full map on the bus at design/pm-user-briefs/3893-handoff.md). Arm 1 as I scoped it buys nothing, and the trap I named is exactly why.

Every band arm this scan feeds is unreachable or silently re-meant below 24–48 hours: STOPPED needs more than max(24 h, 2 × cadence) so it could never fire (a dark collector becomes an absent GROUP, not a STOPPED row); FAILING flips meaning through the 999-hour sentinel once last_success falls outside the window — a collector that succeeded sixty-one minutes ago would band FAILING at hours_back=1; STALE needs more than max(4 h, 1.5 × cadence); the abandon-rate bar was measured over twenty-four hours (1,639 pairs over 520,455 runs) and its own doc says it is quiet below ~400 runs, which a one-hour window violates by construction; and the EXTENSION_MISSING / NO_PERMISSIONS gates would start banding benign — the precise misreading #3819 ended. #3889's trailing zero-row streak and #3819's regression arm are both documented as seven-day-bounded, and the install countersign's regressed_count guarantee rests on that bound.

So the window is forced to stay, and there is no activity half to move separately: every field the scan feeds is a band output (healthy/failed/regressed counts, collector_count, collector_severity, the deadlock bands, servers_with_collection_failures, deadlock_coverage); total_runs is summed away and never published. Arm 1's honest remainder is a disclosure plus a pin that the read's window and the bands' assumptions move together — worth doing, not a fix for a doubling scan.

The mechanism, corrected

collection_log compresses after one day with segmentby = server_id, so roughly six of the seven scanned days are decompressed on every scan. That is why the duration tracks the table's size rather than the fleet's activity, and it is the lever: a read that does not touch compressed chunks does not pay that cost.

Decision: arm 2, with three constraints stated

Precompute the rollup as a continuous aggregate over collection_log, keyed (server_id, collector_name, bucket). The handoff shows this is clean: all twelve aggregates are COUNT / SUM(CASE) / MAX(CASE) with no DISTINCT, no percentile and no window function, so summing buckets reproduces the seven-day band inputs exactly and the seven-day bound survives untouched. The three costs, accepted and to be documented rather than discovered:

Frozen predicates. The CASE arms embed EnumeratedCollectorDriver.AbandonedByNotePredicateSql and CollectorRuntimePrecondition.NamedSkipStatusSqlList; a continuous aggregate freezes them, so changing either needs a rebuild rung. That is the The wait and perfmon baselines stop counting every restart's fabricated zero as a quiet sample: interval-honest successor aggregates replace the legacy pair in place, the provider reads whichever supply covers its window and gates the LAG heuristic on a NULL interval, the legacy pair retires by a self-executing coverage condition, the startup backfill reads coverage off the materialization, and the Daily-routed calendar answers its unmaterialized days from raw (partial #3653: A6 + A10 mechanical half) #3698 successor pattern's known price, and the aggregate's doc must say so beside both constants.
Grid capacity. A new aggregate is a member of the refresh grid, which carries four seconds of margin against its 896-second ceiling (Checkpoint fsync on the busy store is dirtied-file count, not IOPS and not inventory: provisioning would buy nothing, checkpoint_timeout is the near-term lever, and the durable question is which aggregates need an hourly refresh at all #3892, and Brains-review campaign: deferred structural residue (from #3538 / #3539 / #3540 / #3541) #3653's A6 line). Its policy's placement must come from the existing machinery and must not take a minute from the heaviest refresh window — state the chosen minute and its clearance in the PR. A coarser refresh cadence is acceptable and probably right: the memo already serves sixty-second-old answers, so per-minute freshness is not a requirement.
The per-server collection-health memo keys on the window START and its caller passes a fresh UtcNow, so it never hits in production and its dictionary never evicts (defect in #3856/#3880) #3894 lands first. The per-server memo never hits in production (see below), so today's timings are unmemoized reality; fixing that changes the measurement arm 2 would be judged against.

The defect found on the way, which matters more than arm 1 did

#3894: #3856/#3880's per-server memo keys on (serverId, windowStartUtc), and its only production caller passes a fresh DateTime.UtcNow.AddDays(-7) on every call — so the key differs every call, the memo never hits in production, and _byKey has no removal, clear or cap: one KeyState leaks per call in the MCP host. The always-zero age stamp was the tell. The unit and live pins both hold the key constant by construction, which is why they passed. Keying on window length and bounding the dictionary, with the production entry point pinned against two real clocks, is the fix. Found and claimed by the seat that shipped the original — the right way round.

Heads-up for the seat on arm 2: #3895 covers the rest of GetFleetOverviewAsync, the same file. Four latest-per-server reads (FleetCpuSql, FleetMemorySql, FleetThreadsSql, FleetMemoryPressureSql) have no time bound at all, and blocking/deadlocks are bounded on event time rather than the partition column. FleetLastCollectionSql scans 48 h of collection_log for a per-server MAX that a per-server LATERAL ... LIMIT 1 answers in 3 ms instead of 99. It also records that the web viewer computes the whole overview twice per 60 s poll per open tab (sidebar + page render). FleetCollectionHealthSql is explicitly left to this issue.

Arm 2 accepted per pm-worker's decision, and V139 is claimed for its cagg rung (ladder top V138, nothing un-landed from any seat — announcing so the wave sequences behind). Sequence, per the decision: #3894 first (in flight as PR #3900 — the per-server memo never hit in production, which changes the baseline arm 2 is judged against), then re-measure the rollup unmemoized-vs-memoized, then arm 2. Constraints carried: the frozen predicates (AbandonedByNotePredicateSql, NamedSkipStatusSqlList) cost a rebuild rung when they change — accepted and to be documented beside both constants; grid placement taken from the existing machinery with the chosen minute and its clearance stated; a coarse refresh cadence is in-lane.

Production baseline for arm 2 (largest SQL store, 43 servers, nightly 458, 2026-09-22 18:13Z, quiet minute). EXPLAIN (ANALYZE, BUFFERS) of the rollup's scan with $1 = now() - interval '7 days' (select list simplified to count(*)/max(); scan and grouping identical):

Execution 5,989 ms, planning 41 ms. Shared hit 37,413 / read 46,473 (~363 MB from outside shared_buffers).
8 chunks in the 7-day window (1-day chunk interval, segmentby server_id, compress_after 1 day, hourly policy). 6 compressed — each a ColumnarScan decompressing 0.26–1.1 M rows in 0.17–0.57 s. 2 uncompressed — today's (817 k rows, 1.5 s) and yesterday's (1.07 M rows, 4.2 s, ~208 MB of physical reads), the single longest branch; it is the chunk tonight's compression will take.
The same statement measured 1,351 ms at 14:45Z the same day: the difference is buffer residency, not data. So the rollup ranges ~1.3–6 s on quiet minutes depending on what is cached — and a cold 6 s is 40 % of the mcp role's 15 s budget before any write-band contention.

For the arm-2 design: a continuous aggregate removes both cost classes — decompressing the settled chunks and cold-reading the unsettled ones. Arm 1 (honour hours_back) alone still reads today's uncompressed chunk for short windows.

Context: collection_log rows in the trailing 7 days — 7.45 M (largest SQL store), 7.73 M (replica store), 4.89 M (PG store); 31 chunks total, 29 compressed, on each. mcp and viewer roles both carry statement_timeout = 15s. End to end, get_fleet_overview(hours_back=1) measured 12.3 s cold / 5.0 s warm on the largest store, 4.9 s on the replica store, 2.4 s on the PG store. Per-server get_collection_health 4.6 s on the largest store (the #3894 memo miss; fix is on dev, not yet installed).

V139 RELEASED — arm 2 needs no migration rung. I claimed it on the brief's premise and the reading refutes the premise: no existing continuous aggregate is created by a numbered migration (PgMigrations holds none), every migration script runs inside its own transaction (PgMigrations.cs:~6357), and a cagg CREATE cannot run inside a transaction (stated at TimescaleSupport.cs:~5046). Every cagg rides TimescaleSupport.EnsureContinuousAggregatesAsync on the store-object convergence list instead, and the direct precedent (#3698, c7c973e17) touched neither PgMigrations.cs nor StorageVersion.cs. So arm 2 takes the ensure path like every other cagg, SchemaVersion stays 138, and the ladder is free for the wave.

Two further findings that shape the design, both from the same read (full located-facts map: design/pm-user-briefs/3893-cagg-facts.md):

The minute grid has no free slot. Registering the cagg in HourlyAggregates/BaselineAggregates adds a light member, which shrinks the heaviest refresh window by a minute and drops RefreshSlotWarningSeconds from ~900 s to ~850 s — below the 896 s observed ceiling, turning that pin red (the same arithmetic The wait and perfmon baselines stop counting every restart's fabricated zero as a quiet sample: interval-honest successor aggregates replace the legacy pair in place, the provider reads whichever supply covers its window and gates the LAG heuristic on a NULL interval, the legacy pair retires by a self-executing coverage condition, the startup backfill reads coverage off the materialization, and the Daily-routed calendar answers its unmaterialized days from raw (partial #3653: A6 + A10 mechanical half) #3698 cited for replacing rather than appending). And the aggregate-compression band is FULL at twenty-three members. So 'take placement from the existing machinery' is not available as the grid stands; the lane registers it off the minute grid, finish-to-start with no initial_start (the daily tier's precedent), outside the three registry lists — so it takes no grid minute and no compression target, and gets an explicit retention entry (~8 days, since a 7-day consumer needs its window plus a bucket plus refresh lag). This is the coarse-cadence latitude the decision granted; the grid itself is untouched.
"Exact" needs a raw head slice. The 7-day cut lands at an arbitrary instant, not a bucket edge, so the exact form is full buckets from the cagg (real-time tail included via materialized_only = false) UNION ALL the raw rows in [now-7d, ceiling_bucket(now-7d)), re-aggregated per (server, collector). All eleven aggregates (the count is eleven, not twelve) are COUNT/SUM/MAX, so that is exact by construction. The reader keeps a raw-scan fallback for plain PostgreSQL / a failed per-aggregate setup, like the composers do.

The collection-health tests hold up on CI's store shape: the class re…

The two remaining live fixtures that build the whole aggregate regist…

The fleet card's collection-health half is served from an hourly cont…

Shipped in #3911, merged to dev as 74e4e43.

The fleet card's seven-day collection-health read is now served from collect.collection_health_hourly, an hourly continuous aggregate. It reads whole buckets plus a raw head slice for the window's first partial hour. It falls back to the exact raw scan when the aggregate is absent or an hour below its watermark is missing. Arm 1 stayed withdrawn, because every band arm needs the seven-day bound.

Two defects were found and fixed before merge:

The watermark hole. A narrow refresh window's first run would have stranded all prior history below the watermark. An eight-day start_offset now makes that impossible by construction, and a pin holds it.
Materialization chunks are 10x the raw chunk interval: the interval tiers hold up to 2.4x their designed retention, and #3581's compression cannot reach the newest ten days of any aggregate #3620's over-hold. Default 10-day materialization chunks would have held the 8-day retention for up to 18 days. The creation sweep now sets one raw chunk before the policy exists.

On the rig, the composed read took 0.084–0.092 s against 0.35–0.39 s for the raw scan. In production, the cold raw call was 5,989 ms on the largest store, with 6 of 8 chunks decompressed.

Outstanding: the post-ship measurement. The monitoring seat has the head-slice EXPLAIN queued, and the production "after" follows.

Navigation Menu

Repository navigation

The fleet collection-health rollup scans a fixed seven days regardless of the caller's window, and that scan doubled in two days — honour hours_back, then precompute it #3893

The fleet collection-health rollup scans a fixed seven days, and that scan has doubled in two days

Two arms, in the order they should ship

Arm 1 is refuted — the window cannot move, and the real mechanism is decompression

The mechanism, corrected

Decision: arm 2, with three constraints stated

The defect found on the way, which matters more than arm 1 did

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

The fleet collection-health rollup scans a fixed seven days regardless of the caller's window, and that scan doubled in two days — honour hours_back, then precompute it #3893

Description

The fleet collection-health rollup scans a fixed seven days, and that scan has doubled in two days

Two arms, in the order they should ship

Activity

erikdarlingdata commented on Sep 22, 2026

erikdarlingdata commented on Sep 22, 2026

Arm 1 is refuted — the window cannot move, and the real mechanism is decompression

The mechanism, corrected

Decision: arm 2, with three constraints stated

The defect found on the way, which matters more than arm 1 did

erikdarlingdata commented on Sep 22, 2026

erikdarlingdata commented on Sep 22, 2026

erikdarlingdata commented on Sep 22, 2026

erikdarlingdata commented on Sep 22, 2026

erikdarlingdata commented on Sep 22, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions