Re: Report oldest xmin source when autovacuum cannot remove tuples

On Wed, Jul 15, 2026 at 4:38 AM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> Sorry for the delay, I spent time on this. Thank you Shinya-san for
> the off-list chat.

Thank you for posting the patches. To answer your off-list question
here as well, I would be very happy to develop this with both of us as
authors, and getting it into PG20 is my goal too.

I built both patch sets, my v9 and Bharath's v1, and ran them side by
side. Rather than posting a v10 right away, I would like to agree on
the design first, since the thread now has three proposals (the
per-table log line, capture inside ComputeXidHorizons() with the
WARNING detail, and Sami's recurring launcher line). Here are the
decision points as I see them.

1. Where the blocker is resolved

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

I fully agree with this requirement. The open question is only where
the blocker is identified, inside the ComputeXidHorizons() loop or by
a scan against the final horizon, and the in-loop form has a
correctness problem that I could measure.

Reproduction: session 1 prepares a transaction while holding the
oldest xid, session 2 holds a REPEATABLE READ snapshot at the same
xmin but no xid. With Bharath's v1 applied, VACUUM (VERBOSE) reports:

```
tuples: 0 removed, 20000 remain, 10000 are dead but not yet removable
removable cutoff: 661027, which was 2 XIDs old when operation ended
oldest xmin held back by: running transaction
```

pg_stat_activity shows backend_xid NULL for every session, so there is
no running transaction to find, and the answer sits in
pg_prepared_xacts. This is Sami's January case [1] surviving the
switch to category-only reporting. The loop folds xid and xmin into
one value before anything can be classified and adopts a blocker only
on strict improvement, so among entries tied at the eventual minimum
the first one scanned keeps the blame, and a prepared transaction can
never win because its dummy PGPROC is scanned after all regular
backends.

The category half is fixable in place. Keep the raw xid and the raw
xmin separately, and resolve by equality against the final horizon
after the loop. I tried that variant on top of Bharath's v1, and it
fixes the case above and passes its TAP test. But ties among snapshot
holders still resolve by scan order. With one active and one
idle-in-transaction session pinning the same xmin, the reported pid
flipped depending on which connected first. Ranking them needs the
idle versus active state in PgBackendStatus, and its accessor takes
ProcArrayLock itself, so it cannot be consulted from inside the loop.
Once the loop keeps every tied candidate and ranks after the locks are
dropped, it has become the algorithm in my v9-0001, computed inside a
function whose other callers do not need it.

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

I do not read those messages that way. The first is a question from
Scott, which I answered the next day. The second is Jacob preferring
in-loop tracking as a user, and the same message says the approach
"leads to a collision issue" and that he "put the patch down before
PGConf.dev, trying to figure out how best to solve that" [2]. The
collision he means is exactly the tie above, and Fujii hit another
instance in November when a prepared transaction and standby feedback
shared one xmin. So my position is to resolve with a separate scan
against the final horizon, as my v9-0001 does.

2. When the blocker is captured and reported

You are right that end-of-run reporting alone is too late, and I
measured the mirror problem in both patch sets. With Bharath's v1, I
terminated the only blocking session partway through a throttled
VACUUM, and the log line at the end still said "oldest xmin held back
by: running transaction" although no session was holding anything back
by then. With my v9, the same scenario ends with "100000 are dead but
not yet removable" and no blocker line at all, since the report-time
scan finds no match, which is the "I don't know, you better run VACUUM
again" answer Jacob does not want. My proposal is to do both. Resolve
once right after OldestXmin is computed in vacuum_get_cutoffs() and
keep it in VacuumCutoffs, resolve again at report time, prefer the
fresh answer, and when the blocker has gone, fall back to the
cutoff-time one, labeled as of when the cutoff was taken. That reports
the transaction that actually determined the cutoff the dead tuples
were judged against, instead of a corpse or silence.

3. What the scans cost and what gates them

Capturing at cutoff time changes what I wrote earlier about the scan
running only when the log line is emitted. It would run once per
instrumented vacuum, that is VACUUM (VERBOSE) or an autovacuum with
log_autovacuum_min_duration >= 0, so with the default of 10 minutes it
runs once per autovacuumed relation whether or not a line is printed
at the end. The report-time rescan is the one my v9 already had. Each
is one LW_SHARED acquisition and one pass over the proc array, a few
microseconds with 1000 backends, next to the dozen or so ProcArrayLock
acquisitions a table vacuum already performs, and it is the envelope
Laurenz judged acceptable upthread. Separately from the scans, the log
line itself should stay gated on recently_dead_tuples > 0. In my runs
Bharath's v1 printed a blocker line on vacuums that reported "0 are
dead but not yet removable", and a line that appears on every record
is noise rather than signal.

4. Which output surfaces we ship

On Wed, Jul 15, 2026 at 7:13 AM Sami Imseih <samimseih(at)gmail(dot)com> wrote:
> If I have many tables with
> quick vacuums that are getting blocked, I have to set autovacuum
> logging quite aggressively to get any information, even though dead
> tuples are piling up.

That gap is real and per-table logging cannot close it. My hesitation
is only about the launcher as the computation site, since it is not
connected to a database and can only compute the most conservative
cluster-wide minimum, and even within one database a logical slot can
hold back the catalog horizon while an ordinary session holds back the
data horizon, so a single recurring line cannot attribute either. For
the continuous watching you describe, I think Scott's proposed
pg_xmin_horizon view [3] is the right place, and the infrastructure
here returns the full candidate set precisely so that a view like that
can be built on it. What this series should carry is the following.

- The per-table log line and the cutoff-time capture in core, as
points 1 and 2 describe.

- The blocker named in the existing "cutoff for removing and freezing
tuples is far in the past" WARNING, based on Bharath's v1-0002, which
fires independently of any logging configuration.

- A pg_stat_progress_vacuum column on top of the cutoff-time capture,
with the documented meaning that it describes the cutoff this vacuum
is using rather than the current horizon.

Is this the right set of outputs for this series?

If we can converge on these points, I will post v10 implementing them.

Thoughts?

[1] https://www.postgresql.org/message-id/CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT%3D9RmiBkAxQkbQ%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAOYmi%2BmKfzcj%3DGbtDhyu49kGwoN5811FqPzFfgvS7R6mzVs4aQ%40mail.gmail.com
[3] https://www.postgresql.org/message-id/AT-erSWn0vFw8TYLb84jqyfxgTV21gjwEo4YJDcses8qWtDkQK_pS6g2OIfVh4-L1PPn94w8-tE1mHZactVAgxPJ8vL96MXsyAnUg2Xz-Ks%3D%40scottray.io

--
Best regards,
Shinya Kato
NTT OSS Center