Re: Report oldest xmin source when autovacuum cannot remove tuples

On Tue, Jun 30, 2026 at 12:23 AM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> My main concern is the additional proc array scan and the lock it
> takes. ProcArrayLock is one of the most contended locks in production
> systems with hundreds or thousands of connections. With many workers
> vacuuming concurrently across hundreds of tables, I expect this extra
> lock acquisition to show up in the contention path.
> ComputeXidHorizons() already takes ProcArrayLock and has all the
> information about who is blocking the vacuum, so I think we can
> capture the blocker right there and get the reason almost for free,
> without a second scan.

Thank you for taking a look. Earlier versions of this patch took
exactly that approach, and Sami showed it can report the wrong PID
[1]. Identifying the root cause needs the complete set of procs and
slots matching the final horizon, which ComputeXidHorizons() does not
know until its loop has ended.

On the overhead, the extra scan runs only when the log line is
actually emitted (VACUUM VERBOSE, or log_autovacuum_min_duration
exceeded) and only when dead tuples were in fact left unremoved. One
additional LW_SHARED acquisition per logged vacuum, on top of the many
ProcArrayLock acquisitions a vacuum already performs, should not be
visible in the contention path.

> One thing worth mentioning is that we can only ever report a
> "possible" reason. There's an inherent race - by the time we report a
> blocker, it may already be gone. So this is best-effort information.

Agreed. The comments and the commit message state this explicitly.

> The other concern is timing. The patch reports the blocker after the
> vacuum has finished. With hundreds of GB of tables and indexes, where
> vacuum can run for hours, reporting up front is far more useful than
> learning at the end that vacuum couldn't remove the tuples. If we
> capture the blocker when ComputeXidHorizons() computes the horizon, we
> can tell the user early, while there's still a chance to act on it.

Whether the blocker is worth reporting is only known after the heap
scan, since it depends on whether any dead tuples were actually left
unremovable. For seeing the blocker while a long vacuum is still
running, I think a live view (for example pg_stat_progress_vacuum or a
SQL-callable function) fits better than a log line. The 0001 patch
keeps the infrastructure independent of vacuum so that such a view can
be built on it as a follow-up, which Sami also suggested in [1].

> I wrote a patch on the same idea about a month ago, then got busy and
> only noticed this thread now. If it helps, I'm happy to post it.

Please feel free to post it.

--
Best regards,
Shinya Kato
NTT OSS Center