Re: Proposal: vacuum and autovacuum parameters to control freezing

Tom Lane wrote:

> * It might seem that there's no point in per-table adjustment of
> critical_age, since only the system-wide maximum means anything for
> resource consumption. I'm not so sure though --- for a really large
> table, the time needed to finish vacuuming it could be significant,
> meaning it would need a lower critical age than other tables. With the
> current one-process-at-a-time autovac infrastructure, this probably
> isn't very important, but we've been talking about allowing multiple
> parallel autovacuums specifically to deal with the problem of some
> tables being much larger than others.

I think a global critical_age parameter is just fine. If you have one
huge table that takes a long time to vacuum, just adjust critical_age so
that there's enough time for the huge table vacuum to finish before
wrap-around. That means that other smaller tables are vacuumed more
frequently than would otherwise be necessary, but that's not a big deal
if the other tables really are much smaller.

> pg_autovacuum.freeze_distance: per-table vacuum_freeze_distance setting
> for autovacuum to use.

Shouldn't this be used for manual vacuums as well?

> I'd propose default values of 200 million for autovacuum_freeze_limit
> and half that for vacuum_freeze_distance, resulting in a maximum pg_clog
> size of 50MB and forced autovacs about every 100 million transactions.

Sounds fine to me.

> One minor point is that while the values of these variables have to have
> sane relationships to each other, the GUC infrastructure doesn't really
> allow us to enforce such a constraint directly (the behavior would be
> too dependent on which variable got set first). I'd suggest making
> vacuum just silently limit the effective freeze_distance to not more
> than half of the system's autovacuum_freeze_limit, rather than trying
> to enforce any relationship within GUC.

Makes sense.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com