Re: Report oldest xmin source when autovacuum cannot remove tuples

Hi,

On Tue, Jul 7, 2026 at 9:17 AM Shinya Kato <shinya11(dot)kato(at)gmail(dot)com> wrote:
>
> 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.
>
> > 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.

Sorry for the delay, I spent time on this. Thank you Shinya-san for
the off-list chat.

When VACUUM runs for minutes to hours on large tables, this
information is genuinely useful. Today, it is difficult to tell what
is keeping the oldest xmin horizon from advancing until it is already
held back to an unsafe degree, and even then VACUUM only warns that
the cutoff for removing and freezing tuples is far in the past and
lists the general possibilities (open transactions, prepared
transactions, replication slots), without naming what is actually
responsible. As a result, a long-running VACUUM can pass without the
user getting a chance to act in time, and afterwards there is no
record of why it could not prune XIDs or clean up bloat, which makes
it hard to answer customer questions as basic as "what exactly held
the database's VACUUM back?".

The way I think about approaching this problem is: 1/ we can only ever
report a possible cause, whether we capture it at the start or
reconstruct it at the end with a separate scan, since a transaction or
slot holding the xmin back may go away right after; given that it is
best-effort either way, I would rather report it as soon as VACUUM
computes its cutoffs, where it is most useful, so the user can act
while the VACUUM is still running and fix things for the next relation
in the same cycle or the next cycle, rather than learn at the end that
the run achieved nothing, 2/ the reporting stays cheap and adds no
contention on hot locks such as ProcArrayLock, and 3/ it carries the
minimum actionable detail, namely the backend PID for a running
transaction, the GID for a prepared transaction, the slot name for a
replication slot, and the walsender PID for standby feedback.

With this context, I attached two patches. 0001 reports the category
of the blocker (running transaction, prepared transaction, standby
feedback, replication slot, logical replication slot). It is computed
inside ComputeXidHorizons(), which already holds ProcArrayLock, so
there is no extra lock and no additional scan, and it is exposed in
pg_stat_progress_vacuum, in VACUUM (VERBOSE) and in the autovacuum log
when the run exceeds log_autovacuum_min_duration, and as extra detail
on the "far in the past" warning. 0002 adds the specific identity
(backend PID, prepared-xact GID, or slot name) to that warning only.
Finding the identity needs TwoPhaseStateLock or
ReplicationSlotControlLock, so it is done only for the warning and not
for routine progress reporting.

We already seem to have agreement on capturing the blocker during
ComputeXidHorizons() [1] [2].

[1] https://www.postgresql.org/message-id/3bnBUxwx2npXqvHL0trI11LOOvzQ7LI0GzWqbaj5SJnk7DTb1uzStGveKwj0JJmBW4ebzGIF3az7of4I4rQeaO_PRqDnnClCduPyjM6gPgM%3D%40scottray.io
[2] https://www.postgresql.org/message-id/CAOYmi%2BmKfzcj%3DGbtDhyu49kGwoN5811FqPzFfgvS7R6mzVs4aQ%40mail.gmail.com

Thoughts?

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