Repository navigation
Custom Views panels over a recent window scan raw hypertables with no bound; a Query Store panel spilled ~23 GB and tore collector writes #4605
Description
Claude posting for Erik Darling.
What happened
A Custom Views panel run from the web dashboard on one production monitoring store took the store's collector writes down with it for about 14 minutes (14:53–15:07 UTC, 2026-09-28, on a store running the Sep 28 nightly).
-
The panels. 19 composed-panel reads hit the
viewerrole's 60 sstatement_timeout, each running with parallel workers. The prior 71 hours had 5. The same store'scollect.read_latency(Raise the viewer and mcp roles' 15 s statement_timeout to 60–90 s, and measure read durations for p9x #4442) has these rows for the 15:00 hour:route reads over 30 s over 60 s max compose:query_store_stats26 22 14 63.9 s compose:query_stats3 3 3 61.6 s compose:procedure_stats3 3 3 61.5 s -
The cost. The Query Store panel's statement completed 13 times as
viewer, with a mean of 29.6 s, a max of 51 s, and 3,044,304 temp blocks written (about 23 GB). The cancelled runs aren't counted, so the real total is higher. -
The collector writes. At 14:57:43Z three of the service's own store connections were torn mid-COPY, and 9 collector runs ended in ERROR over the next 10 minutes ("store-write COPY data phase (rows in flight under the collection-sweep deadline): Exception while reading from stream"):
procedure_stats×7,spinlock_stats,query_snapshots. Those sweeps' rows are lost. This was the store's only collector error since the install.
Why
- The raw route.
ComposeSourceRouterpicks the source tier by the age of the window's oldest point, never by the display grain (Darling/PerformanceMonitor.Darling.Service/Compose/ComposeSourceRouter.cs, the class summary). A window whose oldest point is withinRawRouteMaxAge(3 days:RetentionTierRouter.RawMaxAge, inside the 4-day raw retention) reads the raw hypertable, whatever its grain or mode. - The Query Store panel dedupes the whole raw window. On the raw route,
ComposeCompiler.BuildFactRelationwrapscollect.query_store_statsinROW_NUMBER() OVER (PARTITION BY server_id, server_name, database_name, query_id, plan_id, runtime_stats_interval_id, first_execution_time, execution_type_desc, replica_role ORDER BY collection_time DESC, execution_count DESC)over every row in the window (ComposeCompiler.cs~188). On a fleet store that's a sort of millions of wide rows before anything is grouped. - What bounds it today:
- the 10,000-row cap, applied AFTER the aggregate (
ComposeCompiler.cs~452–459), so it limits the result but not the scan or the sort; - the 5,000-bucket ceiling, a real pre-flight refusal;
- the 90-day window limit.
- the 10,000-row cap, applied AFTER the aggregate (
- Nothing bounds the pre-aggregate scan, sort or spill of a raw-route panel, and the
viewer/mcproles carry notemp_file_limit,work_memormax_parallel_workers_per_gatherof their own (DarlingManagedRoles.cssets onlystatement_timeout,log_min_duration_statementandlog_parameter_max_length). - Why it runs longer now: Raise the viewer and mcp roles' default statement_timeout from 15 s to 60 s (#4442) #4447 raised that ceiling from 15 s to 60 s, so a heavy panel now runs four times longer before it's cut (Revisit the WPF viewer's and storage-layer MCP readers' client deadlines against the 60 s store ceiling, with measured read durations #4450).
This is the ComposeCompiler site #4310 closed as bounded ("site 5"), because no saved view had a Query Store panel then: "Revisit it if a saved view gains a Query Store panel." An ad hoc panel over a recent window is enough to reach it, and the siblings over raw query_stats/procedure_stats (ranked top-N sums by query_hash/object_name) show the same raw-scan cost without the dedupe.
Fix direction (to be settled on the PR)
- Bound the damage a dashboard read can do to collection: a role-level guard on
viewer/mcp(e.g.temp_file_limit, which fails a runaway read with SQLSTATE 53400 instead of letting it spill for 60 s), withReadOutcomeClassifierand the composer's error text naming it. - Stop reading raw where a rollup answers the same panel: route ranked/scalar and ≥ 1 h-grain panels over a recent window to the corrected hourly rollup, stitched with raw only where it must be; and/or read the latest-snapshot-per-interval table (
collect.query_store_interval_latest, PLAN_REGRESSION's read deduplicates the whole raw Query Store slice every pass; keep the latest snapshot per interval as it is written #3953/Route the Performance Trends duration chart through the interval table at 48 h and wider (#4310) #4382) instead of deduping raw, for the Query Store panel's raw route. - Pins: the router's choice per grain and mode, the guard's presence on both roles (and its reassert on start), and a measured EXPLAIN (ANALYZE, BUFFERS) of the Query Store panel before/after on a fleet-sized raw window.
Activity
Compose: live exactness proof for the Query Store wide-table route (#…
Drop internal step labels from the ceiling comments; plain #4605 refe…
Plain wording in the Query Store wide-route comments (#4605)
Store: a 1 GB temp_file_limit on the viewer and mcp roles (#4610)
Query Store trend: take the hourly rollup's range from the cached cov…
Custom Views: Query Store panels over a recent window read the per-in…
erikdarlingdata commentedon Sep 28, 2026
Claude posting for Erik Darling.
Status: three parts of this are merged. #4610 puts a 1 GB temp_file_limit on the viewer and mcp store roles, so a runaway Custom Views read fails at once with a message naming the limit. #4618 carries the rollup materialization ceiling. #4617 routes Custom Views Query Store panels of 12 hours or more to the per-interval table when it fully covers the window, with raw-versus-table exactness checked live. The query_stats and procedure_stats routing is still in progress, and this issue stays open for it.
Add the SUM/MAX hybrid tier to the composer's source router (#4605)
Compile the SUM/MAX hybrid as a rollup-middle plus raw-edges union (#…
Pin the hourly-plus-raw-edges route to raw rows with a live exactness…
erikdarlingdata commentedon Sep 28, 2026
Claude posting for Erik Darling.
Status, and why the query-stats/procedure-stats rollup route does not ship.
Done:
viewerandmcpstore roles carry a 1 GBtemp_file_limit, so a runaway panel fails at once instead of spilling for 60 s and slowing collector writes.collect.query_store_interval_wideinstead of deduping raw snapshots. It's proven exact against raw byComposeQueryStoreWideExactnessLiveTests. This was the panel shape behind the incident.Not shipping: the hourly-rollup-plus-raw-edges route for
query_stats/procedure_statspanels (#4630). The rule for this issue is that a panel leaves raw only when the new route returns exactly raw's rows. That route needs proof that the rollup's rows for every hour it serves are identical to raw's, and no proof is both exact and cheap:The refresh-policy proof (its last successful run covered the hour) isn't exact. A refresh cut short after consuming its invalidation entries leaves a hole or a stale bucket, and a later successful plain refresh passes over it (
TimescaleSupport.MaterializationHoles.cs, and the forced-refresh case inMaterializationHoleRepairTests).A per-hour row-count guard is exact. Raw is append-only, no product path updates these rows in place, and the rollup's
sample_countcounts the same rows. But it isn't cheap: its raw side is a full scan of the window. Measured on a local TimescaleDB rig at 10 M raw rows over 23 h (median of 3):The guarded route (~487 ms) is slower than plain raw.
So those panels keep reading raw, bounded by #4610's limit. #4630 is closed without merging.
Also settled, as a consequence:
sample_interval_seconds = 0) that the rollup excludes. AVG would need the delta columns to be provably non-NULL, and they're nullable, with no CHECK constraint.Left for this issue: the measured before and after of the same panel shapes after the next release candidate install.
The first_execution_time floor's plan pin keeps only the two indexes …
The fleet-wide Custom Views route reads without the first_execution_t…
The Custom Views route's no-floor comment states the measured slowdow…
44 remaining items
ReadScope.Note: say it also serves expected states that are not fault…
Hour-ledger docs: the guard's rollup half still sums the window's rol…
Hour-ledger docs: say what the writer census sees and name the one fa…
Merge #5289's final head into #5290 (part of #4605)
Census doc: say it reads this build's source only, and word the guard…
Merge dev into #5290 (part of #4605)
Darling store: an hourly row-count ledger for query_stats (V164) (par…
Merge dev after #5289 landed: keep this branch's versions of the thre…
Custom Views: the query_stats count guard reads the hour ledger (part…
erikdarlingdata commentedon Oct 7, 2026
Claude posting for Erik Darling.
The last open part of this issue now passes on the same large store (about 43 monitored servers). That part was panels grouped by object name over a fleet window. The store runs a build with #5179 (module names from
collect.module_map), #5289 (the hourly row-count ledger) and #5290 (the count guard reads the ledger).All panels below are
query_statssum of worker time, top 20, run throughrun_custom_view_panel, cold and then warm:Each one returned 20 rows with no refusal and no notice. The service log showed no gate failure, no guard fallback and no temp-space line during the runs. The 24-hour fleet panel is the one that failed on 2026-10-05.
The count guard now takes 169 ms, down from 55.4 s, and reads about 11,800 buffers, down from 1.18 million. Most of that is the guard reading the rollup rows that the panel reads straight after. That is 0.7% to 2.6% of the panel's time. The ledger agreed with raw for all 1,032 server-hours checked over 24 closed hours.
procedure_statspanels stay on the raw route, as measured on 2026-10-04.Closing this as done.