Hi, I propose for PG20 that parallel vacuum check the XID wraparound failsafe after each index, as non-parallel vacuum does. Currently the check happens only after the parallel workers have vacuumed all the indexes, and cost-based delays are applied until then (they are otherwise ignored once the failsafe has triggered, so that the vacuum finishes faster). With large indexes, this can delay advancing relfrozenxid and relminmxid, bringing the database closer to XID wraparound and eventually to downtime for writes. With parallel autovacuum in PG19 and its default cost limit and delay, the advancement can be delayed even further.
Please find the attached patch implementing the following idea. The table's cutoffs are shared with the parallel workers, and each parallel worker and the leader check the wraparound failsafe after each index they vacuum and set a shared flag if the failsafe has triggered. They check this shared flag at every vacuum delay point and, once it is set, ignore the cost-based delays, so that the index being vacuumed finishes sooner, and they skip the remaining indexes, so that relfrozenxid and relminmxid can be advanced sooner to avoid XID wraparound. I chose to set the shared flag in the parallel worker or leader that detects the failsafe, instead of having each one check the failsafe on its own, to avoid taking XidGenLock and MultiXactGenLock so often. Thoughts? -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
v1-0001-Check-XID-wraparound-failsafe-between-indexes-in-.patch
Description: Binary data
