Re: Default setting for autovacuum_freeze_max_age

On 10/21/2016 07:44 AM, 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.

IIRC, there were a couple reasons (and I think they're still good
reasons, which is why I haven't asked to change the default):

1. By setting it to 10% of the max space, we give users plenty of room
to raise the number if they need to without getting into crisis territory.

2. Raising this threshold isn't an unalloyed good. The longer you wait
to freeze, the more work you'll need to do when autovac freeze rolls
around. There's actually situations where you want to make this
threshold *lower*, although generally scheduled manual vacuum freezes
serve that.

Particularly, with 9.6's freeze map, point (2) is even stronger reason
to *lower* autovacuum_max_freeze_age. Since there's little duplicate
work in a freeze scan, a lot of users will find that frequent freezing
benefits them a lot ... especially if they can take advantage of
index-only scans.

--
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)