A Query Store row dated far ahead stops autovacuum from ever finishing on the interval table #5589

Claude posting for Erik Darling.

What happens

#5573 day-partitions the two Query Store interval tables. Until a table is promoted, Darling tries every hour to arm the bound on its legacy table (collect.query_store_interval_wide_legacy or collect.query_store_interval_latest_legacy). The arm first takes LOCK TABLE ... IN ACCESS EXCLUSIVE MODE, then reads the table's newest first_execution_time through its index. A monitored server's clock can run far ahead of the store's. When that newest time is more than one retention horizon ahead, the arm rolls back and tries again the next hour (QueryStoreIntervalPartitions.ArmCoreAsync, the IsLegacyMaxBeyondHorizon branch). The table is not promoted in that state, so the legacy table is still the whole live table.

A lock request that waits on autovacuum cancels it, unless the run is there to prevent wraparound. The hourly convergence step and the background loop both make this request, so the request comes at least once an hour. On a large store, one autovacuum of this table takes about 5.5 hours at the default throttling. So it never finishes. Dead rows from upserts and retention deletes then pile up while the row stays dated ahead. The table and its indexes grow.

The fix

Read the newest first_execution_time through the same index before the lock, under the same 5 s statement timeout. When it is already beyond the horizon, warn and return without the lock. The read under the lock stays, since it is the one the arm trusts. A later row can only move the newest time later. So when the time is beyond the horizon without the lock, it is beyond it under the lock too.

Checked: the other steps that stop autovacuum on a legacy table

• After promotion, only the daily ANALYZE of the parent does, and only before PostgreSQL 18, where it samples every partition. It runs once a day, and a live test pins that. A stopped autovacuum starts again within a minute and then has about a day to finish.
• The hourly create-ahead and drop steps lock the parent, DEFAULT and the partition they create or drop. They never lock the legacy table. The retention purge of the legacy table is a DELETE, which does not stop autovacuum.
• Before promotion, the arm, the VALIDATE and the promotion each run once. A re-arm comes only when the bound is less than 14 hours away and the table is still not promoted. A promotion that fails is tried again each hour, and each try locks the parent and its partitions. Each failed try logs a warning, and it lasts only until one try succeeds.