Transaction ID wraparound

Symptom: log warnings such as database must be vacuumed within N transactions, autovacuum workers labelled "to prevent wraparound", or in the worst case database is not accepting commands.

Transaction IDs are 32-bit and reused. Rows must be frozen by vacuum before the counter comes around, or Postgres stops accepting writes to protect your data.

Confirm

select datname, age(datfrozenxid) as xid_age from pg_database order by 2 desc;

select relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_total_relation_size(oid))
from pg_class where relkind in ('r', 'm', 't') order by 2 desc limit 10;

The hard limit is about 2.1 billion. autovacuum_freeze_max_age (default 200 million) is when Postgres forces an aggressive vacuum. Healthy databases stay well below it.

Fix

1. Find what blocks freezing. It is the same list as in Vacuum and bloat: long transactions, abandoned replication slots, prepared transactions.
2. Vacuum the oldest tables first, in a session with plenty of memory:

   set maintenance_work_mem = '2GB';
   vacuum (freeze, verbose) big_old_table;
   

3. Never cancel an anti-wraparound autovacuum. Let it finish, and raise autovacuum_vacuum_cost_limit if it is throttled.

Multixact IDs wrap in the same way: check mxid_age(relminmxid) too.

Prevent

Alert when age(datfrozenxid) passes 50% of autovacuum_freeze_max_age, and keep transactions and replication slots from lingering.