Hi, I encountered a case where an aggressive VACUUM remained waiting for a buffer cleanup lock on a page containing a frequently locked row. There were many concurrent SELECT FOR UPDATE transactions on the same row. The transactions were individually short-lived, but their buffer pins overlapped continuously, so VACUUM did not get an opportunity to acquire the cleanup lock. The system was consuming XIDs quickly, so leaving an anti-wraparound VACUUM blocked indefinitely would allow the age of unfrozen XIDs to advance rapidly toward the wraparound danger threshold.
I also noticed a previous report of a similar problem, in which a VACUUM FREEZE waited on BufferPin for several days while buffer pins from successive readers overlapped [1]. A similar cleanup-lock starvation problem was discussed in 2011 [2]. This seems particularly undesirable for an anti-wraparound VACUUM. Skipping pruning can leave dead tuples and unused space behind, but failing to freeze old XIDs can eventually prevent the database from accepting writes. The 2011 discussion [2] raised the related question of whether VACUUM could make progress after failing to acquire a cleanup lock. It found that changing line pointers while holding only an exclusive buffer content lock would be unsafe, because another backend can inspect a line pointer while holding only a buffer pin. However, freezing tuple headers is different: an exclusive buffer content lock is sufficient for that. The idea here is deliberately narrow. It applies only to aggressive VACUUM. After failing to acquire a cleanup lock, and before waiting for one, VACUUM makes one freeze-only attempt under an exclusive buffer content lock (which is less prone to starvation than a cleanup lock). It only makes a best-effort attempt to freeze tuple headers. Dead tuples are left untouched, but their XIDs and MultiXactIds are considered when determining whether the relation freeze horizon can advance. If no dead tuple prevents that progress, VACUUM can freeze the tuples that can be safely frozen and continue. Otherwise, it falls back to waiting for a cleanup lock and running the existing combined prune-and-freeze path. Patch 1 refactors the per-page VACUUM path, where pruning and freezing are currently coupled, to separate freeze planning and execution from pruning. It does not change behavior: the pruning path still uses the same combined prune-and-freeze WAL record. This prepares for freezing a page independently of pruning. Patch 2 adds the freeze-only path after cleanup-lock acquisition fails. The existing path is unchanged when a cleanup lock is available. The new path is used only when cleanup-lock contention would otherwise make an aggressive VACUUM wait. I have included a small reproducer using concurrent SELECT FOR UPDATE transactions on one row. Without the patch, VACUUM FREEZE remains waiting on BufferPin while the workload continues. With the patch, it freezes the live tuple and completes while the workload is still running. I would appreciate feedback on this approach. --- Regards, Jingtang Alibaba Cloud [1] https://www.postgresql.org/message-id/flat/1059371874.2807306.1706727919318%40mail.yahoo.com [2] https://www.postgresql.org/message-id/flat/BANLkTinmWFR1-mPu4nduUjxUfvWXZni-7Q%40mail.gmail.com
v1-0001-vacuum-Separate-heap-page-freezing-state.patch
Description: Binary data
v1-0002-vacuum-Allow-freezing-without-a-cleanup-lock.patch
Description: Binary data
