Proposal: vacuum and autovacuum parameters to control freezing - Mailing list pgsql-hackers

I wrote:
> It's usually going to be the case that the oldest datvacuumxid is
> template0's, meaning that it will never be possible to truncate clog
> until autovacuum decides that template0 is at risk of wraparound and
> goes and vacuums it.  Shortening the freeze horizon will reduce the size
> that pg_clog occupies just *after* that happens, but we're still going
> to see pg_clog bloating up to something close to 256MB before autovacuum
> kicks in.

After further thought I see that there are actually two parameters
involved in this process:

1. the critical age (currentXID - relfrozenxid) beyond which autovacuum
will force a vacuum of a particular table to forestall XID wraparound.
(Note: as the 8.2 code stands, the system will launch autovacuums even
when autovac is nominally disabled in order to fix tables that have
exceeded a hard-wired critical age.)

2. the freeze distance vacuum (whether auto or normal) uses to determine
the new cutoff point, ie, the new relfrozenxid for the table.

We can make a few observations:

* For a table that otherwise goes unvacuumed, the interval between
forced anti-wraparound vacuums will be critical_age - freeze_distance.
Therefore, for large static tables there is value in being able to
adjust this difference to be as large as possible.

* The size of pg_clog is determined by the system-wide maximum of
critical_age + number-of-transactions-needed-to-finish-vacuuming.
Therefore, critical_age is the knob we must expose if we want to
provide user control of pg_clog growth.

* 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.

So it seems to me that an argument can be made for creating two new
GUC variables and adding two columns to pg_autovacuum:

vacuum_freeze_distance: number of transactions back from current
that a VACUUM will use as the freeze cutoff point, ie, XIDs older
than that will be replaced by FrozenXID, and the cutoff point will
become the table's new relfrozenxid value.  Valid range zero to
perhaps 1 billion.  VACUUM FREEZE is a shorthand for doing a
vacuum with vacuum_freeze_distance = 0.

autovacuum_freeze_limit: maximum age of relfrozenxid before autovacuum
will force a vacuum for anti-wraparound purposes.  Valid range perhaps
100 million to (2 billion - 100 million).

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

pg_autovacuum.freeze_limit: per-table autovacuum_freeze_limit for
autovacuum to use.

(I'm not wedded to these names, anyone have better ideas?)

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.

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.

This is kind of a lot to be inventing in late beta, but if we want to
have a really credible solution to the WAL-versus-freezing problem
I think we need to do all of this.

Comments?
        regards, tom lane