The rollup coverage probe writes about 170 MB of temp every five minutes by sorting two rollups' compressed batches #4539

Claude posting for Erik Darling

The rollup coverage probe writes about 170 MB of temp files every five minutes on a large store, because finding two Query Store rollups' oldest bucket sorts every compressed batch of their oldest chunk.

Seen

On one production store (pg_stat_statements plus EXPLAIN (ANALYZE, BUFFERS) of the exact statement text):

• The statement is TimescaleSupport.RollupCoverageProbeSql (one SELECT of a min(bucket) per rollup plus a min(collection_time) per rolled raw table). ComposeStoreAvailability runs it at most every five minutes per store.
• The cost: 94 calls since the stats reset, a mean of 739 ms and a max of 16.6 s. Every call writes about 21,600 temp blocks (about 169 MB).
• Where the temp comes from: two subqueries, on query_store_stats_hourly and query_store_stats_corrected_hourly.
  • For each, TimescaleDB's ordered append visits only the oldest materialization chunk, but it then sorts all of that chunk's compressed batches (59,044 of them, about 59 million rows) by their batch metadata before it decompresses the first batch.
  • The sort is external merge Disk: ~86 MB each, at a 64 MB work_mem.
• The other rollups stay in memory. One is a 47 MB in-memory sort, close to spilling as it grows.
• The raw-table minimums are cheap: 10–26 ms, shared hits only.

Why

min(bucket) on a compressed chunk is answered through a sort of the chunk's compressed batches, and that sort carries each batch's compressed payload. Its size grows with the oldest chunk's batch count, so a large early chunk makes every probe spill.

Fix shape

The floor only moves when the oldest chunk changes (retention, or an older backfill). So one option is to re-measure a rollup's floor only when its oldest materialization chunk changes, and to reuse the last measured floor otherwise. A reused floor can only be later than the true one, so it never claims coverage a rollup lacks. An exact query shape that avoids the batch sort would be better still, if one exists. Either way, pin it on a rig with a compressed materialization chunk of tens of thousands of batches.