Re: Behavior change in PostgreSQL 14Beta3 or bug?

On Mon, Sep 6, 2021 at 8:59 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Assuming that that choice was made appropriately, I think the advice you
> propose here will just cause people to waste lots of cycles on VACUUM
> runs that have only marginal effects.

Right. The advice that they should receive (if any) is to tune
autovacuum aggressively, and enable autovacuum log output. The log
output reports on whether or not the implementation applied the
optimization in each case.

As I pointed out to Laurenz just now, users that care about index-only
scans are actually the big beneficiaries here. Now they can set
autovacuum_vacuum_insert_threshold very aggressively, without doing a
useless round of index vacuuming just because one inserting
transaction out of a million aborted. Once indexes are removed from
the equation (to the extent that that makes sense), each round of
vacuuming by autovacuum only needs to do work that is proportional to
the number of unset-in-vm heap pages.

I believe that that trade-off makes a lot of sense. Autovacuum has
little chance of keeping anything like 100% of all pages set in the VM
anyway. But it can get a lot closer to it in some cases now.

--
Peter Geoghegan