Re: Allow aggressive VACUUM to freeze without a cleanup lock

Hi, Melanie.

Thanks for looking.

> Do you know if in your use case this would have been able to do the
> freeze (i.e. you didn't have too-old dead tuples on the page) with
> needing pruning?

In this case, I believe the page would have been able to make freeze
progress without pruning. Whenever an anti-wraparound VACUUM became
stuck, we had to terminate every SELECT FOR UPDATE session accessing that
page before it could acquire the cleanup lock and proceed past the page
using the normal prune-and-freeze path. That pass would have removed any
removable DEAD tuples on the page. Across many subsequent
anti-wraparound VACUUM runs, the same page and the same row repeatedly
became the blocking point again. The recurring workload continuously
ran SELECT FOR UPDATE on that row, and later anti-wraparound VACUUM runs
again waited on BufferPin at the same page and row.

SELECT FOR UPDATE does not create a new tuple version. It only locks
the existing tuple, so repeated locking of the same logical row continues
to access the same physical tuple and the same heap page. There is no
mechanism that moves this hotspot to another page.

UPDATE behaves differently. Without pruning, old tuple versions cannot
have their space recycled on the current page. As the page fills, a
later update can no longer be HOT and must place the new current version
on another page. The original page is then no longer the hotspot for
that row, so the sustained pins can move away and it can get a chance to
be pruned. SELECT FOR UPDATE has no comparable escape path.

---
Regards,
Jingtang