Re: autovacuum not prioritising for-wraparound tables

Jim Nasby <jim(at)nasby(dot)net> wrote:

> the only reason to freeze is XID wrap

The conclusions you draw seem to be based on a slightly different
premise than stated here; the conclusions are only supported by the
assumption that "the only reason to freeze at any particular moment
is to assure that all tuples are frozen in time to prevent
transaction wraparound."  In a mostly write-only database, that has
to happen for nearly every tuple at some point -- but *which* point
is best is not necessarily "as late as possible".

> then the *ideal* time to start a freeze vacuum on a table is so
> that the vacuum would end *exactly* as we were about to hit XID
> wrap.

For a tuple which you know is going to survive long enough to be
frozen, the *ideal* time to freeze a tuple, from a performance
standpoint, is when the tuple is written, to eliminate a separate
WAL-log operation.  The *next best* time to freeze is when the hint
bits are set, to avoid a separate page write.  If you are doing
differential backups, the *third best* time to freeze is before the
first differential backup of the tuple, to avoid a separate backup
after the freeze.  And so on.

-Kevin