Re: Default setting for autovacuum_freeze_max_age

On Thu, Oct 27, 2016 at 6:55 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 10/21/2016 10:29 AM, Robert Haas wrote:
>> On Fri, Oct 21, 2016 at 1:17 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> 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 ...
>>
>> That's a very good point, although I hope that vacuum is mostly being
>> triggered by vacuum_freeze_table_age rather than
>> autovacuum_freeze_max_age.
>
> Well, depends on the nature of writes to the table. For insert-mostly
> tables, vacuum_freeze_table_age is pretty much never triggered. Isn't
> there a patch for that somewhere?
>
>>
>> On Bruce's original question, there is an answer written into our
>> documentation: "Vacuum also allows removal of old files from the
>> pg_clog subdirectory, which is why the default is a relatively low 200
>> million transactions."
>
> Point.

It also affects pg_commit_ts size (if enabled), and it uses 40x more
space per xid than pg_clog if I've read the code right. I have
wondered before[1] if we should document that.

[1] https://www.postgresql.org/message-id/CAEepm=3PM05_T__3PSXBacDLm7WwMYrbR_3mYFcKE2tRkXK8LQ@mail.gmail.com

--
Thomas Munro
http://www.enterprisedb.com