Re: Defining (and possibly skipping) useless VACUUM operations

On Tue, Dec 14, 2021 at 1:16 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> I think that you'd agree that the arguments in favor of skipping are
> strongest ...

Well I just don't understand why you insist on using the word
"skipping." I think what we're talking about - or at least what we
should be talking about - is whether relation_needs_vacanalyze() sets
*wraparound = true right after the comment that says /* Force vacuum
if table is at risk of wraparound */. And adding some kind of
exception to the logic that's there now.

> What I see with the draft patch series is that the oldest XID just
> isn't that old anymore, consistently -- we literally never fail to
> advance relfrozenxid, in any autovacuum, for any table. And the value
> that we end up with is consistently quite recent. This is something
> that I see both with BenchmarkSQL, and pgbench. There is a kind of
> virtuous circle, which prevents us from ever getting anywhere near
> having any table age in the tens of millions of XIDs.

Yeah, I hadn't thought about it from that perspective, but that does
seem very good. I think it's inevitable that there will be cases where
that doesn't work out - e.g. you can always force the bad case by
holding a table lock until your newborn heads off to college, or just
by overthrottling autovacuum so that it can't get through the database
in any reasonable amount of time - but it will be nice when it does
work out, for sure.

> I guess that that makes avoiding useless vacuuming seem like less of a
> priority. ISTM that it should be something that is squarely aimed at
> keeping things stable in truly pathological cases.

Yes. I think "pathological cases" is a good summary of what's wrong
with autovacuum. When there's nothing too crazy happening, it actually
does pretty well. But, when resources are tight or other corner cases
occur, really dumb things start to happen. So it's reasonable to think
about how we can install guard rails that prevent complete insanity.

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