On Wed, Jul 15, 2026 at 4:38 AM Bharath Rupireddy <[email protected]> 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 <[email protected]> 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
