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:
- When usual autovacuum on a table is started?
- 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 thanvacuum_freeze_table_age
transactions old, an aggressive vacuum is performed to freeze old tuples and advancerelfrozenxid
; 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 thanautovacuum_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.