Re: autovacuum not prioritising for-wraparound tables

Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-01-29 00:11:12 +1100, Josh Berkus wrote:
>>
>>> So I think we need to sort by age(relfrozenxid) in tables that
>>> are over the anti-wraparound limit. Given your code that
>>> doesn't seem to be that hard?
>>
>> I might also suggest that we think about changing the defaults
>> for wraparound vacuum behavior.  Partcularly, the fact that
>> vacuum_freeze_min_age is 50% of autovacuum_freeze_max_age by
>> default is optimal for absolutely nobody, and forces
>> re-wraparound vacuuming of wraparound tables which were just
>> recently wraparound-vacuumed. We should lower
>> vacuum_freeze_min_age to something sane, like 1000000.
>
> I have to admit, I fail to see why this is a good idea. There
> isn't much of an efficiency bonus in freezing early (due to hint
> bits) and vacuums over vacuum_freeze_table_age are considerably
> more expensive as they have to scan the whole heap instead of
> using the visibilitymap. And if you don't vacuum the whole heap
> you can't lower relfrozenxid. So changing freeze_min_age doesn't
> help at all to avoid anti-wraparound vacuums.
>
> Am I missing something?

IMO, anything which changes an anti-wraparound vacuum of a
bulk-loaded table from "read the entire table and rewrite nearly
the complete table with WAL-logging" to rewriting a smaller portion
of the table with WAL-logging is an improvement.  Anyone who has
run an OLTP load on a database which was loaded from pg_dump output
or other bulk load processes, has probably experienced the pain
related to the WAL-logged rewrite of massive quantities of data.
Of course, since it triggers based on transaction count, the higher
your transaction rate at any moment, the more likely it is to kick
in at that moment.  Whatever we can do to reduce that pain helps.

-Kevin