The Query Store liveness touch costs ~13% of the Query Store–ingesting store's WAL (~33 GB/day) at its 6-hour guard: every touch is a non-HOT update

[erikdarlingdata]

Related: #3189, #3210, #3216 (the touch guard). The guard's doc comment names write volume as its trade and records no measurement of it at the 6-hour width.

Problem

QueryStoreLivenessTouchGuard sets how often the touch-and-probe statements refresh last_seen on query_store_plan_map and query_store_text: GuardHours = StampSkewMarginHours / MarginShareDivisor = 24 / 4 = 6 h.

The comment on MarginShareDivisor states the divisor is "CHOSEN, not measured". It also says what the divisor trades: "WRITE VOLUME against stamp freshness". last_seen is indexed on both tables, so every touch is a non-HOT update: a new heap tuple, new b-tree entries, a dead tuple, and WAL for all of it. With data checksums and 5-minute checkpoints, most of those pages are imaged in full. Here is the write-volume side, measured.

Measured

Production SQL Server store A (43 servers, the Query Store–ingesting store), build 3.8.0-nightly.20260924.467. Steady window 2026-09-25 04:45–05:41Z (55.6 min), no restart inside.

• pg_stat_statements:
  • plan-map touch-and-probe: 310 calls, 399,287 WAL records, 128,191 page images, 0.74 GB;
  • text touch-and-probe: 308 calls, 280,439 records, 75,733 page images, 0.53 GB;
  • together 1.28 GB = 13.5% of the store's WAL (9.45 GB), and 1.71 GB of buffers dirtied.
• pg_stat_all_tables, same window: query_store_plan_map 82,880 updates (0 HOT), query_store_text 79,836 (31 HOT). That's ~163 k touches an hour, ~3.9 M a day, which matches the 6-hour guard over the referenced set.
• pg_waldump per-relation sample (1.1 GB at 05:41Z): the query_store_text heap 5.6%, the query_store_plan_map heap 4.1%, their pkeys 1.1%. Most of it is page images and hint images.
• Per day: ~33 GB of WAL and ~44 GB of dirtied pages written back, on a store generating ~250–350 GB of WAL a day.
• The window straddling the 04:04Z restart had 3.73 GB, 23% of WAL: the first cycle after a start probes and touches the whole referenced set at once.
• Store B holds Query Store READ_ONLY replicas and stores nothing here.

Where

• Darling/PerformanceMonitor.Darling.Storage/QueryStoreLivenessTouchGuard.cs: MarginShareDivisor :133, GuardHours :141, StampSkewMarginDays :102.
• Darling/PerformanceMonitor.Darling.Storage/QueryStorePlanMap.cs:170, TouchAndProbeSql; last_seen index :68.
• Darling/PerformanceMonitor.Darling.Storage/QueryStoreTextStore.cs:114, TouchAndProbeSql; last_seen index :65.

Fix shape

Pick by what the margin can afford. All three cut writes roughly in proportion.

• Widen the guard.
  • Raise QueryStorePlanMap.PruneMarginDays from 1 to 2, matching the text store. Then MarginShareDivisor 4 gives a 12 h guard and halves the touches, with the same ¼-of-margin headroom the comment asks for.
  • Or keep the margin and move the divisor to 2.
• Make the touch HOT.
  • Drop the last_seen indexes and prune by a batched sequential scan on the daily purge (the tables are keyed stores, not hypertables). With a fillfactor below 100, a last_seen-only update stays on-page with no index entries.
  • Measure the purge's cost before and after.
• Spread the post-restart wave. The first cycle touches everything at once, which is 23% of WAL in that hour. It could jitter the touch by server or by key hash.
• Pin:
  • the guard derivation test already exists; add the measured write volume to the divisor's comment, so the next reader has both sides of the trade;
  • if the index is dropped, a live test that a touch is HOT (n_tup_hot_upd increments).

[erikdarlingdata]

Ruled: how this gets built

Two changes now. The third waits for a measurement.

1. Widen the guard. Raise QueryStorePlanMap.PruneMarginDays from 1 to 2, matching the text store. With MarginShareDivisor at 4, the guard becomes 12 hours and the touches halve. Add this issue's measured write volume to the divisor's comment, so the comment states both sides of the trade.
2. Stop the wave after a restart. The guard is kept in memory. So the first cycle after a start touches every referenced row. The touch must skip a row whose stored last_seen is already inside the guard. Then a restart does not rewrite fresh rows. If the statement cannot skip them without locking them, spread the first cycle by key hash instead. The PR says which.
3. Not now: a HOT-only touch (drop the last_seen indexes, set a lower fillfactor, purge by a batched scan). It needs a migration and a purge redesign. Decide it after a day of measurement with 1 and 2 installed. So the PR says "Part of The Query Store liveness touch costs ~13% of the Query Store–ingesting store's WAL (~33 GB/day) at its 6-hour guard: every touch is a non-HOT update #4250", and this issue stays open for that decision.

Pins:

• The guard derivation test covers the new margin.
• A live test shows a second touch inside the guard writes nothing, including right after a simulated restart.

🤖 Generated with Claude Code

https://claude.ai/code/session_01TszxYhJJbTEh4LrZ56NYo3

[erikdarlingdata]

Correction to the ruling above

I checked two parts of it against the code after posting, and both were wrong.

1. Widen the guard through the divisor. Do not change the map's margin. QueryStorePlanMap.MarginOrderingHolds requires the map's PruneMarginDays to stay below the dimension's margin, ChunkIntervalDays + 1, which is 2. A map margin of 2 fails that pin. Worse, it lets a dimension row age out before its map row, which is the silent missing-plans case.
   • Instead, lower QueryStoreLivenessTouchGuard.MarginShareDivisor from 4 to 2. The guard becomes 12 hours, and the touches halve.
   • The guard still uses only half of the one-day margin. The worst reachable setting is a retention of 1 day with the plan-content knob off. There the room is 2 days, so a 12-hour guard fits inside it 4 times.
   • Put that reasoning in the divisor's comment. Add this issue's measured volume at 6 hours: about 163 k touches an hour, 3.9 M a day, all non-HOT.
2. A restart does not re-touch fresh rows by itself. The guard is a per-row test in the SQL: WHERE last_seen < $5 - interval '6 hours'. It reads the stored last_seen. No state is kept in memory. So the first cycle after a start skips every row stamped within the guard.
   • The lane proves this with the live test in the pins. Stamp a batch, run the same batch through a new writer, and count the rows it updates.
   • If the count is zero, the 3.73 GB in the restart window came from something else. Then this PR builds nothing for it, and states the count.
   • Per-table write counts across the next restart will show where that WAL went.

Item 3 stands as posted. The pins stand, with "the new margin" read as "the new divisor", and MarginOrderingHolds unchanged.

🤖 Generated with Claude Code

https://claude.ai/code/session_01TszxYhJJbTEh4LrZ56NYo3

[erikdarlingdata]

#4288 merged to dev (dfb4fbd): items 1 and 2 are done. The Query Store liveness touch's guard is now 12 hours, not 6. Item 3, the HOT-only touch, is still open and waits for its own measurement.

[erikdarlingdata]

Claude posting for Erik Darling

Measured after #4288 (the 12-hour guard): the steady-state touch cost is now about 0.6% of the store's WAL, against 13.5% before. A restart still produces a burst. Item 3, the HOT-only touch, waits for a 24-hour delta.

Production SQL Server store (43 servers, the Query Store–ingesting store), on a build that includes #4288. pg_stat_statements deltas, 2026-09-26:

window	text touch-and-probe	plan-map touch-and-probe
first 16 min after a service restart	88.7k pages dirtied, 303 MB WAL	53.8k pages dirtied, 170 MB WAL
a steady 15 min	2.1k pages dirtied, 6.6 MB WAL	3.7k pages dirtied, 14.2 MB WAL

• Steady state: about 83 MB of WAL an hour between the two, against about 14 GB an hour for the whole store. Before Skip locking fresh dimension rows; halve the Query Store liveness-touch rate (#4249, #4250) #4288 the touch was 1.28 GB in 56 minutes.
• After a restart, the touch runs 14–43 times its steady rate for the first cycle. That's expected: every row is past the guard at the first pass.
• Confound: these two windows ran under different checkpoint intervals (a separate trial moved from 5 to 15 minutes). The ratio between them is far larger than that effect.
• What a 15-minute window can't show: the 12-hour guard's full cycle. A 24-hour delta follows, around 04:15Z on 2026-09-27. If the steady share stays near 1% of WAL, item 3 is not warranted and this issue closes with items 1 and 2 done.

[erikdarlingdata]

Claude posting for Erik Darling

The 24-hour delta: the Query Store liveness touch now costs about 9.9 GB of WAL a day, 6.4% of the store's WAL. Before #4288 it was ~33 GB a day (13.5%). The 12-hour guard cut the touch's WAL by about 70%. But the steady share is not near 1%, which was this issue's bar for closing without item 3. So item 3, the HOT-only touch, is warranted, and this issue stays open for it.

This corrects the 2026-09-26 comment's "about 0.6%". That 15-minute window fell in the quiet part of the 12-hour cycle, soon after the restart had touched every referenced row. The whole set comes due again 12 hours later, so a day holds two full waves. The comment named this limit ("A 15-minute window can't show the 12-hour guard's full cycle"), and the 24-hour read measures it.

Production SQL Server store (43 servers, the Query Store–ingesting store), on a build that includes #4288. pg_stat_statements, 2026-09-26 04:13Z → 2026-09-27 04:14Z.

• The instrument: 0 deallocations, and no statement evicted between the snapshots. A service restart at 04:14:58Z falls in the first 1.1 hours, so the steady window below starts after it: 23.73 hours, no restart, checkpoint_timeout 15 min throughout.

steady 23.73 h	calls	pages dirtied	full-page images	WAL records	WAL
plan-map touch-and-probe	7,585	1,920,412	1,804,163	5,139,569	7,044.8 MB
text touch-and-probe	7,426	898,172	888,032	2,609,187	2,952.2 MB

• The total: 9.76 GB in 23.73 h (≈ 9.9 GB a day), against ~152 GB of WAL in the window: 6.4%. The full 24 h, restart included, is also 6.4% (10.23 of 158.78 GB).
• pg_stat_all_tables, same window:
  • query_store_plan_map: 741,748 updates, 0 HOT, 34,381 moved to a new page;
  • query_store_text: 691,239 updates, 429 HOT, 65,769 moved.
  • That's 1.43 M touches a day, against ~3.9 M at the 6-hour guard.
• The cost per touch didn't change: about 7–8.5 KB of WAL a touch (0.41 GB/h over 60 k touches/h now; 1.38 GB/h over 163 k/h before). The whole gain came from touching less often.
• Full-page images dominate: the two statements wrote 2.69 M of them for 1.43 M updates, about 1.9 per update. Every touch is a non-HOT update. It writes a new heap tuple plus an entry in each index, and with 15-minute checkpoints, the first write to each of those pages after a checkpoint logs the whole page.

Why item 3 is the lever [inferred, not measured]. The guard can't widen further within the prune margin (the 2026-09-25 correction's reasoning), so the number of touches a day is now fixed at about two per referenced row. What's left to cut is the cost per touch. A HOT update touches no index. With fillfactor room, it stays on its heap page, which removes the index-page images and most of the new-page moves. How much that saves needs its own measurement; I haven't estimated it here.

Scheduling: item 3 needs a migration and a redesign of the purge, which today relies on the last_seen indexes. It waits until after the upcoming release, and it gets its own design decision before any work starts.

[erikdarlingdata]

Claude posting for Erik Darling.

Item 3 is merged as #4472 (4fb8962). Store migration V149 drops the last_seen indexes on collect.query_store_plan_map and collect.query_store_text and sets fillfactor 90, so the liveness touch can update rows in place. The retention prune for both tables is now one capped scan per batch. On a test store, the touch wrote 23% less WAL per touch on plan_map and 35% less on text. A steady-state prune batch took 190 ms and 483 ms, against 819 ms and 1,888 ms for the old shape.

This issue stays open. The saving in the field is not measured yet, and a 24-hour pg_stat_statements delta after the next install closes it.

[erikdarlingdata]

Claude posting for Erik Darling

Measured in the field. The fix works; closing.

Production SQL Server store A (43 servers, the Query Store–ingesting store): pg_stat_statements and pg_stat_all_tables deltas over 2026-09-29 21:25Z → 2026-09-30 14:40Z (17.2 h), on builds carrying V149. stats_reset was unchanged and dealloc 0. The store restarted once inside the window (onto a newer nightly). A start's first cycle touches the whole referenced set, so these figures include that burst and slightly overstate steady state. Total store WAL over the window, by LSN: 99.2 GB.

	before (issue)	now
query_store_plan_map HOT updates	0 of 82,880	645,887 of 649,906 (99.4%)
query_store_text HOT updates	31 of 79,836	592,137 of 639,117 (92.6%)
plan-map touch WAL per updated row	~8.9 KB	8.5 KB
text touch WAL per updated row	~6.6 KB	3.2 KB
the touches' share of store WAL	13.5%	7.6% (7.56 GB of 99.2 GB)

What remains: the plan-map touch still costs ~8.5 KB per row. That is heap full-page images written the first time each page is touched after a checkpoint. HOT removes the index writes but not those images, and the plan-map rows the touch visits are scattered across pages. The lever for the rest is the guard's width (MarginShareDivisor, write volume against stamp freshness), which is a design choice rather than a defect, so nothing is filed.