Re: Parallel vacuum: check XID wraparound failsafe between indexes

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).

Right.

- * Do a postcheck to consider applying wraparound
failsafe now. Note
- * that parallel VACUUM only gets the precheck and this
postcheck.
+ * Do a postcheck to consider applying wraparound failsafe now.
*/

There was probably little incentive to do this for manual VACUUM. Since
v12, users have been able to disable the main nonessential work
themselves, e.g. with INDEX_CLEANUP off, TRUNCATE off, etc.

> 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.

Yes, I agree.

> 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.

I think the shared flag is the right way. Without it, the only other
viable place to
do a recheck would be vacuum_delay_point(), which as you say would mean
repeatedly taking XidGenLock and MultiXactGenLock locks. I do not think that
is a good idea.

One logging caveat I noticed while testing is that per-index
VERBOSE/log_autovacuum lines can include indexes vacuumed in an earlier
pass of the same VACUUM, even if those indexes were skipped in the pass
in which failsafe is triggered. This is current behavior, so I do not think
there is anything to do here.

But I would suggest one small comment tweak.

/*
* Reset all index status back to initial (while checking that we have
- * vacuumed all indexes). Skipped indexes have no status of
their own, so
- * once the failsafe has triggered we assume any index left
unfinished was
- * skipped by it.
+ * vacuumed all indexes). When the failsafe interrupts this index vacuum
+ * round, an index can already have stats from an earlier index vacuum
+ * round, but still be left at NEED_BULKDELETE because it was skipped in
+ * this round. Cleanup work must still complete normally.
*/

I think that is better. Skipped in this index vacuum round does not
necessarily mean
no stats at all, since an earlier index vacuum round may already have
filled them in.

The test also looks necessary to me. No comments on that.

--
Sami Imseih
Amazon Web Services (AWS)