Re: Default setting for autovacuum_freeze_max_age

Bruce Momjian wrote:
> On Fri, Oct 21, 2016 at 10:44:41AM -0400, Tom Lane wrote:
> > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > Why is autovacuum_freeze_max_age's default set to 200 million, rather
> > > than something like 2 billion? It seems 2 billion is half way to
> > > wrap-around and would be a better default. Right now, the default seems
> > > to freeze 10x more often than it has to.
> >
> > Please see the archives. I do not remember the reasoning, but there
> > was some, and you need to justify why it was wrong not just assert
> > that you think it's silly.
>
> I think the reasoning was to avoid checking old clog files, but with
> tuple transaction status bits, e.g. HEAP_XMIN_COMMITTED, which were
> added long ago, I don't remember why either.

HEAP_XMIN_COMMITTED existed way before autovacuum, so that doesn't add
up, does it. As I recall, the reason was to be able to truncate
pg_clog. I suppose nowadays it's possible to claim that we're not
really bothered by a gigabyte or two of pg_clog?

*If* we're to raise the default then it should not be to 2 billion.
That gives users no breathing room if they find themselves struggling
with the freezing; with the current default, it's possible to increase
it 2x or 4x if you're in trouble, which gives some breathing room until
a permanent solution is found (better vacuuming). That disappears if
you set the max to its max.

> I remember asking years ago and not getting a good answer, and giving
> up.

[citation needed]

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services