Re: autovacuum not prioritising for-wraparound tables

> 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.  Second, you're not doing any arithmatic.

Let's do this by example.  TableA is a large table which receives an
almost constant stream of individual row updates, inserts, and deletes.

DEFAULTS:

XID 1:  First rows in TableA are updated.
XID 200m: Anti-wraparound autovac of TableA.    All XIDs older than XID 100m set to FROZENXID.
XID 300m: Anti-wraparound autovac of TableA    All XIDs older than XID 200M set to FROZENXID.
XID 400m: Anti-wraparound autovac of TableA    All XIDs older than XID 300M set to FROZENXID.
XID 500m: Anti-wraparound autovac of TableA    All XIDs older than XID 400M set to FROZENXID.
XID 600m: Anti-wraparound autovac of TableA    All XIDs older than XID 500M set to FROZENXID.


vacuum_freeze_min_age = 1m

XID 1:  First rows in TableA are updated.
XID 200m: Anti-wraparound autovac of TableA.    All XIDs older than XID 199m set to FROZENXID.
XID 399m: Anti-wraparound autovac of TableA    All XIDs older than XID 398M set to FROZENXID.
XID 598m: Anti-wraparound autovac of TableA    All XIDs older than XID 597M set to FROZENXID.


vacuum_freeze_min_age = 1m, autovacuum_freeze_max_age = 500m

XID 1:  First rows in TableA are updated.
XID 500m: Anti-wraparound autovac of TableA.    All XIDs older than XID 499m set to FROZENXID.

As you can see, the current default settings cause 80% more wraparound
autovacs per table than vacuum_freeze_min_age of 1m would, and almost
500% more than what I consider sane settings would.  Just so that we can
preserve XIDs which are 90m transactions old.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com