Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and possible memory issue)

On Thu, Dec 15, 2022 at 1:57 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I could get behind manual VACUUM not invoking vac_update_datfrozenxid
> by default, perhaps. But if it can never call it, then that is a
> fairly important bit of housekeeping that is unreachable except by
> autovacuum. No doubt the people who turn off autovacuum are benighted,
> but they're still out there.

I wouldn't mind adding another option for this to VACUUM. We already
have a couple of VACUUM options that are only really needed as escape
hatches, or perhaps as testing tools used by individual Postgres
hackers. Another one doesn't seem too bad. The VACUUM command should
eventually become totally niche, so I'm not too concerned about going
overboard here.

> Could we get somewhere by saying that manual VACUUM calls
> vac_update_datfrozenxid only if it's a full-DB vacuum (ie, no table
> was specified)? That would fix the problem at hand.

That definitely seems reasonable.

> Or maybe we could modify things so that "autovacuum = off" doesn't prevent
> occasional cycles of vac_update_datfrozenxid-and-nothing-else?

That's what I was thinking of. It seems like a more natural approach
to me, at least offhand.

I have to imagine that the vast majority of individual calls to
vac_update_datfrozenxid have just about zero chance of updating
datfrozenxid or datminmxid as things stand. There is bound to be some
number of completely static tables in every database (maybe just
system catalogs). Those static tables are bound to be the tables that
hold back datfrozenxid/datminmxid approximately all the time. To me
this suggests that vac_update_datfrozenxid should fully own the fact
that it's supposed to be called out of band, possibly only in
autovacuum.

Separately, I wonder if it would make sense to invent a new fast-path
for the VACUUM command that is designed to inexpensively determine
that it cannot possibly matter if vac_update_datfrozenxid is never
called, given the specifics (the details of the target rel and its
TOAST rel).

--
Peter Geoghegan