On Tue, Jun 30, 2026 at 12:23 AM Bharath Rupireddy <[email protected]> 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
