Re: New criteria for autovacuum

On Fri, Apr 4, 2025 at 12:11 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
> That being said, long-running transactions are a problem for
> autovacuum in general. Even if you track this stat you are proposing
> about heap fetches by index only scans, you won't know if the long
> running transaction is over and thus if it makes sense to try and
> trigger an autovacuum for that table again anyway.

This. It would be really useful to have some kind of a system for
figuring out when -- in terms of XIDs -- we ought to vacuum which
table. I think that's a hard problem, but it would help a lot of
people.

I do not think the approach in the proposed patch is correct at all.
The proposed new check would have exactly the same problem as the
existing one -- this could easily trigger vacuuming at a time when the
relevant tuples can't yet be made all-visible, in which case we'd just
do a lot of VACUUM work for nothing. That's already a problem with
autovacuum in some scenarios, and I bet this would be way worse.

--
Robert Haas
EDB: http://www.enterprisedb.com