Re: autovacuum not prioritising for-wraparound tables

Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-01-29 16:09:52 +1100, Josh Berkus wrote:
>>
>>> 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?
>>
>> Yep.  First, you're confusing vacuum_freeze_table_age  and
>> vacuum_freeze_min_age.
>
> Don't think I did. I was talking about vacuum_freeze_table_age
> because that influences the amount of full-table scans

Not any more than vacuum_freeze_min_age does.

http://www.postgresql.org/docs/9.2/interactive/routine-vacuuming.html#AUTOVACUUM

| a whole table sweep is forced if the table hasn't been fully
| scanned for vacuum_freeze_table_age minus vacuum_freeze_min_age
| transactions.

So reducing vacuum_freeze_min_age not only helps minimize the
writes that are needed when autovacuum needs to scan the entire
heap, but also decreases the frequency of those full-table scans.

-Kevin