Re: New criteria for autovacuum

On Fri, Apr 4, 2025 at 1:53 AM Konstantin Knizhnik <knizhnik@garret.ru> wrote:
>
> What is needed to reproduce the problem?
> 1. Table with populated data
> 2. Presence of transaction with assigned XID which prevents vacuum from
> marking pages of this table as all visible
> 3. Vacuum or autovacuum processed this table (to eliminate dead tuple
> and reset number of inserted tuples since last vacuum).
>
> After this 3 steps autovacuum will never be called for this table (at
> least until forced vacuum caused by wraparound).
> And IOS will not be used or be very inefficient fot this table.

ISTM, this is more an issue of ins_since_vacuum being reset to 0 in
pstat_report_vacuum() even though those inserted tuples weren't
necessarily frozen and their pages not set all-visible. I don't know
exactly how we could modify that logic, but insert-triggered vacuums
are meant to set pages all-visible and freeze tuples, and if they
don't do that, it doesn't quite make sense to zero out the counter
that could trigger another one.

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.

- Melanie