Re: New IndexAM API controlling index vacuum strategies

On Thu, Apr 15, 2021 at 5:12 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > https://thebuild.com/blog/2019/02/08/do-not-change-autovacuum-age-settings/
>
> Not at all convinced. The issue of needing to modify a lot of
> all-visible pages again to freeze them is big enough to let it be a
> problem even after the freeze map. Yes, there's workloads where it's
> much less of a problem, but not all the time.

Not convinced of what? I only claimed that it was much less common.
Many users live in fear of the extreme worst case of the database no
longer being able to accept writes. That is a very powerful fear.

> > As I said, we handle the case where autovacuum_freeze_max_age is set
> > to something larger than vacuum_failsafe_age is a straightforward and
> > pretty sensible way. I am curious, though: what
> > autovacuum_freeze_max_age setting is "much higher" than 1.6 billion,
> > but somehow also not extremely ill-advised and dangerous? What number
> > is that, precisely? Apparently this is common, but I must confess that
> > it's the first I've heard about it.
>
> I didn't intend to say that the autovacuum_freeze_max_age would be set
> much higher than 1.6 billion, just that that the headroom would be much
> less. I've set it, and seen it set, to 1.5-1.8bio without problems,
> while reducing overhead substantially.

Okay, that makes way more sense. (Though I still think that a
autovacuum_freeze_max_age beyond 1 billion is highly dubious.)

Let's say you set autovacuum_freeze_max_age to 1.8 billion (and you
really know what you're doing). This puts you in a pretty select group
of Postgres users -- the kind of select group that might be expected
to pay very close attention to the compatibility section of the
release notes. In any case it makes the failsafe kick in when
relfrozenxid age is 1.89 billion. Is that so bad? In fact, isn't this
feature actually pretty great for this select cohort of Postgres users
that live dangerously? Now it's far safer to live on the edge (perhaps
with some additional tuning that ought to be easy for this elite group
of users).

--
Peter Geoghegan