The I/O-latency baseline re-reads 30 days of raw per-file rows every hour for every server and spills ~50 MB to disk each time (store B: ~50–95 GB/day of temp writes) #4248

Related: #2820 (the MAD-tier join fix on this arm), #3941 (baseline cache sharing), #1757

Problem

PgBaselineProvider's MetricNames.IoLatency arm reads the raw file_io_stats hypertable at per-file grain over the full 30-day baseline window (BaselineMath.BaselineWindowDays). It feeds the rows through RobustTierScaffold:

• GROUPING SETS over hour × day-of-week, hour, and all;
• a keyed_tiers UNION ALL that triples the rows;
• percentile_cont twice (median, then MAD).

Those sorts don't fit in work_mem, so each call writes a temp file. The window ends on the analysis hour (#3941), so every server's baseline is recomputed once an hour. A 30-day baseline moves by about 1/720 per hour.

Measured

Production SQL Server stores, build 3.8.0-nightly.20260924.467, 2026-09-25, pg_stat_statements (owner role). work_mem is 64 MB on both stores.

• On store B this one statement was the largest temp writer:
  • 13.5 of 22.1 GB of all temp since the stats reset;
  • 3.6 of 6.0 GB in the first window;
  • 86% of temp in the steady window.
• Does it reach the disk? Store B's volume took 440 GB of writes on Sep 24 (CloudWatch). The store-side sum of WAL, data, extend and temp writes came to 98% and 111% of the volume's writes in the two windows, so on that store the temp files do reach the disk, give or take ~10%.
• Share of disk writes: in the steady window this read was ~16% of store B's volume writes (1.5–2 GB of 9.8 GB), and ~10–20% across all the readings.

Where

• Darling/PerformanceMonitor.Darling.Analysis/PgBaselineProvider.cs:
  • :1249-1256, the IoLatency arm (raw file_io_stats, per-file rows);
  • :977-1015, RobustTierScaffold;
  • :805-807, the window windowEnd.AddDays(-BaselineWindowDays) on the rounded hour;
  • :133, CacheTtl, 1 h.
• PerformanceMonitor.Analysis/Baselines/BaselineMath.cs:26: BaselineWindowDays = 30.

Fix shape

Any one of these removes most of it. They compose.

• Recompute less often. A 30-day baseline doesn't need an hourly recompute. Key the cache on the day, or on the hour-of-week bucket, for the raw-reading arms. Check the Every scheduled analysis pass recomputes every 30-day baseline: a fresh DarlingAnalysisService per pass never reuses (or shares with MCP) the bucket cache #3941 sharing semantics: every caller in the period still gets the same rows.
• Stop the spill. SET LOCAL work_mem sized for this statement: the measured spill is ~50 MB, so the in-memory sort needs roughly 2–4× that. Add a live rig pin that EXPLAIN (ANALYZE) of the arm over a seeded 30-day, per-file dataset reports no external merge.
• Shrink the input. Collapse per-file rows to one ratio per collection (sum of stall over sum of reads) before the scaffold, if the per-file grain isn't load-bearing for the anomaly semantics. This changes Lite parity, so it's the maintainer's call.
• Pin: the arm's pg_stat_statements.temp_blks_written over a full analysis pass on the rig is zero, or under a stated bound.