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