Postgres: Autovacuum and autovacuum wraparound. When does each start?

Question

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?

Answer 1

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.

Answer 2

Made a query which shows the dead turple (when simple vacuum is started) and when vacuum wrapadaround:

 with dead_tup as ( 
 SELECT st.schemaname || '.' || st.relname tablename,
         st.n_dead_tup dead_tup,
         current_setting('autovacuum_vacuum_threshold')::int8 +
         current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples
         max_dead_tup,
         (current_setting('autovacuum_vacuum_threshold')::int8 +
         current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples - st.n_dead_tup) as left_for_tr_vacuum,
         st.last_autovacuum,
         c.relnamespace,
         c.oid
  FROM   pg_stat_all_tables st,
         pg_class c
  WHERE  c.oid = st.relid
  AND    c.relkind IN ('r','m','t')
  AND    st.schemaname not like ('pg_temp%'))
SELECT  c.oid::regclass as table,
        current_setting('autovacuum_freeze_max_age')::int8 -
        age(c.relfrozenxid) as xid_left,
        pg_relation_size(c.oid) as relsize, 
        dt.dead_tup,
        dt.max_dead_tup,
        dt.left_for_tr_vacuum,
        dt.last_autovacuum 
from (pg_class c 
      join pg_namespace n on (c.relnamespace=n.oid)
      left join dead_tup dt on (c.relnamespace=dt.relnamespace and c.oid=dt.oid))
where c.relkind IN ('r','m','t') --and (age(c.relfrozenxid)::int8 > (current_setting('autovacuum_freeze_max_age')::int8 * 0.8))
      AND n.nspname not like ('pg_temp%')
order by 2