Repository navigation
Darling viewer: built-in tabs silently lose history past 4 days - CAGG read-routing only covers Custom Views #1661
Description
Found by the issue audit and verified directly. Not yet in a release — it is dev-only, so it can be fixed before 3.3.0 ships. That is the good news; the bad news is that it is a silent data-loss regression, not an error anyone will see.
What happens
The three-tier retention from #1623 applies automatically. EnsureRetentionPoliciesAsync is called unconditionally on every service start (DarlingWorker.cs:651), and raw retention is 4 days (TimescaleSupport.cs:585, RawRetentionInterval = "4 days").
The CAGG read-routing that preserves older windows (#1625/#1626/#1627) lives only in the Custom Views v2 compose path — Compose/ComposeSourceRouter.cs and ComposeCompiler.cs.
The viewer's built-in tabs never go through that path. They read v_query_stats, which is a plain passthrough over the raw hypertable:
CREATE OR REPLACE VIEW v_query_stats AS SELECT * FROM query_stats;(PgMigrations.cs:233)
Confirmed production readers, all taking a caller-supplied window:
ViewerDataService.DailySummary.cs:64ViewerDataService.FinOps.Inventory.cs:80ViewerDataService.FinOps.Storage.cs:144ViewerDataService.FinOps.Workload.cs:37, :202, :279, :435
So a user asking Daily Summary or FinOps for the last 30 days now silently gets 4 days of query data. Before the retention tiers those tables held ~30 days. Nothing errors; the numbers are just quietly wrong, which is the worst failure mode for a cost/workload view whose entire purpose is trend over time.
FinOps is the sharpest case — spend and workload attribution over a 4-day sample presented as a month is actively misleading.
Why it slipped through
#1623 (write side: drop raw at 4 days) and #1625-#1627 (read side: route old windows to the rollups) were designed as a pair, and the read side was scoped to the composer because that was the surface being built at the time. The built-in tabs predate Custom Views and were never revisited. Each PR was correct in isolation.
Worth noting for the record: I had this recorded as "nothing falls back to raw," which was true of the compose path and which I over-generalized. The built-in tabs are exactly the thing that still falls back to raw.
Options
- Route the built-in readers through the same age-based logic the composer uses — most correct, and reuses the proven
ComposeSourceRouterrules rather than a second implementation. - Make
v_query_statsa UNION view over raw + hourly + daily with dedup. Fixes every reader at once with no C# change, but the CAGG columns are pre-aggregated (x_sum,sample_count) and do not have the same shape as raw rows, so this is likely not expressible without lossy assumptions. - Raise the raw horizon so built-in tabs keep working. Cheapest, but gives back the storage win that motivated Darling: three-tier retention (raw 4d / hourly CAGGs 21d / daily CAGGs kept) #1623.
Option 1 looks right; option 3 is the safe stopgap if 3.3.0 is close.
Also verify before shipping
The same passthrough pattern exists for the other tiered tables — procedure_stats and query_store_stats both have raw retention and hourly/daily CAGGs. Any built-in reader over their v_* views has the identical problem and should be swept in the same change.
Activity
erikdarlingdata commentedon Jul 26, 2026
Scoped the fix. It is not "route everything to the CAGGs" — the readers split into two groups with different answers, and one of them can never be fully served by a rollup.
Group 1 — aggregate-only. Routable, no product question.
These aggregate additively over exactly the dimensions the CAGGs already materialize, so routing them is mechanical and lossless:
ViewerDataService.DailySummary.cs:64—COUNT(DISTINCT query_hash)per day. The hourly CAGG groups byquery_hash, so this works over the rollup.ViewerDataService.FinOps.Workload.cs:37—SUM(delta_worker_time),SUM(delta_logical_reads),SUM(delta_physical_reads),SUM(delta_logical_writes),SUM(delta_execution_count)GROUP BYdatabase_name.ViewerDataService.FinOps.Storage.cs:144andFinOps.Inventory.cs:80— same additive shape.Mcp/DarlingHealthReader.cs— sameCOUNT(DISTINCT query_hash)/SUM(delta_wait_time_ms)shape as Daily Summary.Group 2 — needs per-row text. NOT routable, ever.
FinOps.Workload.csreferencesquery_text12 times andquery_plan5 times;ItemHistory.csreadsquery_plan. The CAGGs carry neither — they group byquery_hashand sum deltas, deliberately. No amount of read-routing recovers text that was never rolled up.So "top queries by CPU over the last 30 days, with their SQL text" is genuinely unanswerable once raw drops at 4 days. That was arguably always the accepted trade (#1623 said as much about plan XML), but it was never surfaced — and right now those panels silently show 4 days while the picker says 30.
What I am doing
Group 1: route by window age, reusing the composer's existing age-based rules rather than writing a second set.
Group 2: clamp and label. Keep them on raw, clamp the effective window to the raw horizon, and say so in the UI — the same honesty pattern already used for
HardwareUnavailableReasonand the Excluded Databases fallback note. Silently returning 4 days of a 30-day request is the actual defect; returning 4 days clearly labelled "query text is only retained for N days" is a real answer.The alternative for Group 2 — rank by
query_hashfrom the rollup and show no text — is worse: a top-queries list you cannot act on. Going with clamp-and-label unless overridden.Rejected: making
v_query_statsa UNION viewTempting because it fixes every reader with no C# change. It breaks on Group 2 (no text in the rollup) and it is a correctness trap for any future non-additive aggregate — an
AVGover synthetic pre-summed rows is silently wrong. Not worth the landmine.Also in scope
procedure_statsandquery_store_statshave the same raw-drop + CAGG structure. Theirv_*readers need the same sweep, or they are the identical bug waiting to be reported.erikdarlingdata commentedon Jul 26, 2026
A constraint worth knowing before any further CAGG work: reshaping a continuous aggregate is now destructive.
TimescaleDB cannot ALTER columns into a CAGG — a shape change means DROP + recreate, which
DropStaleContinuousAggregatesAsyncautomates (TimescaleSupport.cs:460). That was lossless when #1624/#1627 used it, because retention had not been activated and raw still held ~30 days, so the rebuild re-materialized everything.Retention is active now. Raw drops at 4 days. So a reshape today rebuilds the CAGG from 4 days of raw and permanently loses the 21-day hourly and indefinite daily history — exactly the data the tiers exist to preserve. On the field box that is not recoverable.
Why it came up
FinOps.Workload.cs:37sumsdelta_logical_reads,delta_physical_readsanddelta_logical_writes. No CAGG carries any of them — the rollups were built to the composer's measure set, and the composer never exposed I/O. Verified: those column names appear nowhere inTimescaleSupportorMeasureCatalog.So the FinOps I/O rollup cannot be routed without adding columns, and adding columns now costs the existing history.
What this splits #1661 into
Routable today, no reshape, non-destructive — these only need columns the CAGGs already have:
COUNT(DISTINCT query_hash)—query_hashis a CAGG GROUP BY column.DarlingHealthReader's identical shape.worker_time_sum,execution_count_sum,sample_count.Blocked on a decision — the FinOps I/O columns:
query_stats_io_hourlyCAGG. Additive, so it does not drop anything existing; new history starts accumulating immediately. Costs another pair of objects and a second refresh policy.Option 1 looks right: it is the only one that is both correct going forward and non-destructive. It does mean I/O history starts from the deploy date rather than being backfilled, which is honest and unavoidable either way.
Proceeding with the non-destructive routing now; the I/O question is genuinely a store-shape call.
Route the Daily Summary query count to the CAGGs (#1661)
Unify the duplicated daily-summary SQL, and route the MCP reader too (#…
Add the per-database query_stats rollup carrying the I/O sums (#1661)
Route the FinOps aggregate readers and the MCP health reader (#1661)
Clamp and label the one reader that projects per-row text (#1661)
Merge pull request #1664 from erikdarlingdata/feature/1661-builtin-ca…
Share the retention tier decision between the composer and the viewer (…
erikdarlingdata commentedon Jul 26, 2026
Fixed by #1664, merged to
dev.Every reader that can be routed now is: the Performance Calendar's query count, the
get_daily_healthMCP reader, and three FinOps aggregates. The FinOps database-grain view needed I/O sums no rollup carried, so it got a new additivequery_stats_db_hourly/_dailypair rather than a widened existing one — TimescaleDB cannot ALTER columns into a continuous aggregate, and with retention live a drop-and-recreate would have rebuilt from 4 days of raw and permanently destroyed the history the tiers exist to preserve.The one reader that projects per-row
query_text/query_plancannot be routed at any tier (no rollup carries text), so it clamps to the text horizon and says so instead of presenting a few days as a month.The tier decision now has a single definition in
RetentionTierRouter, and the composer delegates to it rather than keeping its own thresholds. An invariant test asserts each route threshold stays strictly inside the retention horizon it protects, so changing a retention interval without moving the margin fails the build — otherwise this same bug returns one tier up.Two things the work surfaced, both fixed here: a second hand-copied duplicate of the entire daily-summary query living in the MCP health reader (unified — routing one and not the other would have had the calendar and the MCP tool disagree about the same day), and my own earlier mis-scoping of
ItemHistoryas a text reader when it projects onlyquery_plan_hash.Never released — caught in
devbefore 3.3.0.Compose: keep the NEWEST buckets when the row cap truncates, and say …
Three hourly rollups counted every restart's fabricated zero as a sam…
Three hourly rollups counted every restart's zero as a sample and a >…