I've got 'autovacuum_freeze_max_age' which is 200 000 000 by default. And in theory I found a rule that autovacuum wraparound starts when:

If age(relfrozenxid) > autovacuum_freeze_max_age

But when then usual autovacuum is started? How can I count a moment:

1. When usual autovacuum on a table is started?
2. When autovacuum becomes autovacuum wraparound? Really after age(relfrozenxid) > autovacuum_freeze_max_age?

CodePudding user response：

As the documentation states, normal autovacuum is triggered

if the number of tuples obsoleted since the last VACUUM exceeds the “vacuum threshold”, the table is vacuumed. The vacuum threshold is defined as:

vacuum threshold = vacuum base threshold   vacuum scale factor * number of tuples

where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is autovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples.

The table is also vacuumed if the number of tuples inserted since the last vacuum has exceeded the defined insert threshold, which is defined as:

vacuum insert threshold = vacuum base insert threshold   vacuum insert scale factor * number of tuples

where the vacuum insert base threshold is autovacuum_vacuum_insert_threshold, and vacuum insert scale factor is autovacuum_vacuum_insert_scale_factor.

The second part applies only to PostgreSQL v13 and later.

Furthermore,

If the relfrozenxid value of the table is more than vacuum_freeze_table_age transactions old, an aggressive vacuum is performed to freeze old tuples and advance relfrozenxid; otherwise, only pages that have been modified since the last vacuum are scanned.

So an autovacuum worker run that was triggered by the normal mechanism can run as an anti-wraparound VACUUM if there are old enough rows in the table.

Finally,

Tables whose relfrozenxid value is more than autovacuum_freeze_max_age transactions old are always vacuumed

So if a table with old live tuples is never autovacuumed during normal processing, a special anti-wraparound autovacuum run is triggered for it, even if autovacuum is disabled. Such an autovacuum run is also forced if there are multixacts that are older than vacuum_multixact_freeze_table_age, see here. From PostgreSQL v14 on, if an unfrozen row in a table is older than vacuum_failsafe_age, an anti-wraparound autovacuum will skip index cleanup for faster processing.

Yes, this is pretty complicated.