Parallel vacuum: check XID wraparound failsafe between indexes

Started by Bharath Rupireddyabout 1 month ago13 messageshackers
Jump to latest
#1Bharath Rupireddy
bharath.rupireddyforpostgres@gmail.com

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

Attachments:

t254238_1
v1-0001-Check-XID-wraparound-failsafe-between-indexes-in-.patchapplication/octet-stream; name=v1-0001-Check-XID-wraparound-failsafe-between-indexes-in-.patchDownload+177-10
#2Sami Imseih
samimseih.pg@gmail.com
In reply to: Bharath Rupireddy (#1)
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)

#3Bharath Rupireddy
bharath.rupireddyforpostgres@gmail.com
In reply to: Sami Imseih (#2)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Hi,

On Sat, Oct 17, 2026 at 12:27 PM Sami Imseih <samimseih.pg@gmail.com> wrote:

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.

Yes, that's correct. I think it will be more useful for parallel
autovacuum at scale (hundreds of GBs table and indexes) with default
cost delay and cost limit.

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.

Right.

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.

Yeah, that requires some additional tracking and I don't think it's
worth the complexity just for logging.

But I would suggest one small comment tweak.

Agreed. That looks confusing. I changed it to the following:

        /*
         * Reset all index status back to initial (while checking that we have
-        * vacuumed all indexes).
+        * vacuumed all indexes). Indexes not yet started when the failsafe
+        * triggered are skipped, so those are still NEED_BULKDELETE
here and that
+        * is expected.
         */

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

Thanks for reviewing. Please find the attached v2 patch with the above
comment rewording. CF entry:
https://commitfest.postgresql.org/patch/7490/.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Attachments:

t254238_3
v2-0001-Check-XID-wraparound-failsafe-between-indexes-in-.patchapplication/octet-stream; name=v2-0001-Check-XID-wraparound-failsafe-between-indexes-in-.patchDownload+177-10
#4Sami Imseih
samimseih.pg@gmail.com
In reply to: Bharath Rupireddy (#3)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Thanks for reviewing. Please find the attached v2 patch with the above
comment rewording. CF entry:
https://commitfest.postgresql.org/patch/7490/.

Thanks for the update. v2 LGTM and I marked it as RFC.

--
Sami

#5Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Bharath Rupireddy (#3)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Hello

One mostly nitpick question:

+extern bool parallel_vacuum_failsafe_triggered(ParallelVacuumState *pvs);
extern void parallel_vacuum_bulkdel_all_indexes(ParallelVacuumState *pvs,

parallel_vacuum_bulkdel_all_indexes now might return quietly and process only part of the work. Previously it either errored out, or completed everything. Wouldn't it be be better to give it a return value callers can check? Existing functions, such as lazy_vacuum_all_indexes already do this. In this case the current patch seems to be safe because of the lazy_check_wraparound_failsafe call, but returning the status (and handling it in vacuumlazy) seems like a safer not heap specific API.

#6Bharath Rupireddy
bharath.rupireddyforpostgres@gmail.com
In reply to: Zsolt Parragi (#5)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Hi,

On Tue, Oct 27, 2026 at 3:24 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

One mostly nitpick question:

Thanks for taking a look at this.

+extern bool parallel_vacuum_failsafe_triggered(ParallelVacuumState *pvs);
extern void parallel_vacuum_bulkdel_all_indexes(ParallelVacuumState *pvs,

parallel_vacuum_bulkdel_all_indexes now might return quietly and
process only part of the work. Previously it either errored out, or
completed everything. Wouldn't it be be better to give it a return
value callers can check? Existing functions, such as
lazy_vacuum_all_indexes already do this. In this case the current
patch seems to be safe because of the lazy_check_wraparound_failsafe
call, but returning the status (and handling it in vacuumlazy) seems
like a safer not heap specific API.

Yeah, that's a good point. parallel_vacuum_bulkdel_all_indexes() can
return whether the failsafe was hit by either the leader or any of the
workers. Would something like the below work for you?

if (!parallel_vacuum_bulkdel_all_indexes(...))
{
/*
* The failsafe was hit by either the leader or one of the parallel
* workers, so do the reporting and the resetting work here, once per
* VACUUM.
*/
lazy_check_wraparound_failsafe(vacrel);
allindexes = false;

}

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

#7Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Bharath Rupireddy (#6)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

On Wed, 28 Oct 2026, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:

Yeah, that's a good point. parallel_vacuum_bulkdel_all_indexes() can
return whether the failsafe was hit by either the leader or any of the
workers. Would something like the below work for you?

yes, that looks good to me.

#8Bharath Rupireddy
bharath.rupireddyforpostgres@gmail.com
In reply to: Zsolt Parragi (#7)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Hi,

On Wed, Oct 28, 2026 at 4:53 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

On Wed, 28 Oct 2026, Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Yeah, that's a good point. parallel_vacuum_bulkdel_all_indexes() can
return whether the failsafe was hit by either the leader or any of the
workers. Would something like the below work for you?

yes, that looks good to me.

Thanks. I still think it's better for
parallel_vacuum_bulkdel_all_indexes() to return whether it processed
all the indexes or some. Although I'm a bit worried that changing the
return type of the function could impact anyone reusing parallel
vacuum code from the core, this is not a complex change to adopt. So,
I did that in the attached v3 patch. Please have a look.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Attachments:

t254238_8
v3-0001-Check-XID-wraparound-failsafe-between-indexes-in-.patchapplication/octet-stream; name=v3-0001-Check-XID-wraparound-failsafe-between-indexes-in-.patchDownload+198-22
#9Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Bharath Rupireddy (#8)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Looks good to me.

#10Zsolt Parragi
zsolt.parragi@percona.com
Pending
In reply to: Bharath Rupireddy (#8)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Looks good to me.

#11surya poondla
suryapoondla4@gmail.com
In reply to: Bharath Rupireddy (#8)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Hi Bharath,

Thank you for the patch, I tested v3 on top of c074737f687.

I have a few comments:
1. v3 now runs lazy_check_wraparound_failsafe() after the parallel index
pass only when the shared flag is set; master (and v1/v2) ran it
unconditionally. Was dropping it intentional? It's cheap, and it still
matters in one corner case which is parallel workers use the GUC values
serialized when the parallel context was set up, while an autovacuum
leader can reload vacuum_failsafe_age while it waits for them. If
vacuum_failsafe_age is lowered at that point, only the leader's recheck
would notice it. I can share a reproducer if that's useful (doesn't happen
in normal scenarios)

2. The TAP test only counts the per-index lines. Could it also check for
the "bypassing nonessential maintenance" WARNING? That confirms the
leader actually entered failsafe mode, not just that indexes were skipped.

Apart from that, the patch behaved as expected in the other cases I tried:
i) manual VACUUM (VERBOSE, PARALLEL 2) with 6 indexes: 3 vacuumed, 3
skipped, WARNING emitted, index reltuples left untouched;
ii) leader-only indexes (below min_parallel_index_scan_size) mixed with
parallel ones: the remaining leader-only and parallel indexes are both
skipped once the flag is set;
iii) failsafe triggered in the second index pass (maintenance_work_mem =
512kB): no "parallel index vacuum on index ... is not completed"
error, and the WARNING says "after 1 index scans";
iv) workers planned but none launched (max_parallel_workers = 0): the
leader sets the flag itself and skips the rest.

Regards,
Surya Poondla

#12Bharath Rupireddy
bharath.rupireddyforpostgres@gmail.com
In reply to: surya poondla (#11)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Hi,

On Mon, Nov 9, 2026 at 3:13 PM surya poondla <suryapoondla4@gmail.com> wrote:

I have a few comments:
1. v3 now runs lazy_check_wraparound_failsafe() after the parallel index
pass only when the shared flag is set;

matters in one corner case which is parallel workers use the GUC values
serialized when the parallel context was set up, while an autovacuum
leader can reload vacuum_failsafe_age while it waits for them. If
vacuum_failsafe_age is lowered at that point, only the leader's recheck
would notice it. I can share a reproducer if that's useful (doesn't happen in normal scenarios)

Yeah, that's right. And that window, where users can lower the
failsafe age to avoid wraparound, can be long in some cases. This can
happen when the leader waits for the workers to finish index vacuuming
(i.e. the leader gets small indexes compared to the workers).

I don't think the fix is to keep an extra check in the leader. The
issue is that we don't pass the failsafe age down to the workers like
we do for the other cost delay params. Without it, the workers can't
honor a changed failsafe age, and lowering it can be a valid way to
avoid wraparound.

So I think a better fix is to pass the failsafe age from the leader to
the workers. That way both the leader and the workers check after each
index. If the leader is waiting for the workers and the failsafe age
changes, the workers will catch it, set the shmem flag, and the leader
will report it. So we wouldn't need an extra check in the leader.

2. The TAP test only counts the per-index lines. Could it also check for the "bypassing nonessential maintenance" WARNING?

Yeah, that would ensure the leader reported the detected failsafe. I
will add it.

Apart from that, the patch behaved as expected in the other cases I tried:

Thanks for verifying.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

#13Bharath Rupireddy
bharath.rupireddyforpostgres@gmail.com
In reply to: Bharath Rupireddy (#12)
Re: Parallel vacuum: check XID wraparound failsafe between indexes

Hi,

On Wed, Nov 18, 2026 at 9:29 AM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

On Mon, Nov 9, 2026 at 3:13 PM surya poondla <suryapoondla4@gmail.com> wrote:

1. v3 now runs lazy_check_wraparound_failsafe() after the parallel index
pass only when the shared flag is set;

matters in one corner case which is parallel workers use the GUC values
serialized when the parallel context was set up, while an autovacuum
leader can reload vacuum_failsafe_age while it waits for them. If
vacuum_failsafe_age is lowered at that point, only the leader's recheck
would notice it. I can share a reproducer if that's useful (doesn't happen in normal scenarios)

Yeah, that's right. And that window, where users can lower the
failsafe age to avoid wraparound, can be long in some cases. This can
happen when the leader waits for the workers to finish index vacuuming
(i.e. the leader gets small indexes compared to the workers).

I don't think the fix is to keep an extra check in the leader. The
issue is that we don't pass the failsafe age down to the workers like
we do for the other cost delay params. Without it, the workers can't
honor a changed failsafe age, and lowering it can be a valid way to
avoid wraparound.

So I think a better fix is to pass the failsafe age from the leader to
the workers. That way both the leader and the workers check after each
index. If the leader is waiting for the workers and the failsafe age
changes, the workers will catch it, set the shmem flag, and the leader
will report it. So we wouldn't need an extra check in the leader.

Please find the attached v4 patch with the above change.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Attachments:

t254238_13
v4-0001-Check-XID-wraparound-failsafe-between-indexes-in-.patchapplication/x-patch; name=v4-0001-Check-XID-wraparound-failsafe-between-indexes-in-.patchDownload+224-27