Measure transaction ID wraparound risk (XID age per database and table)

Command

-- Per database: distance to autovacuum_freeze_max_age and the ~2^31 limit
SELECT datname,
       age(datfrozenxid) AS xid_age,
       mxid_age(datminmxid) AS mxid_age,
       round(100.0 * age(datfrozenxid) / current_setting('autovacuum_freeze_max_age')::int, 1) AS pct_of_freeze_max_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 1) AS pct_of_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Oldest tables in the current database (including TOAST)
SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age,
       mxid_age(c.relminmxid) AS mxid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       s.last_autovacuum,
       s.last_vacuum
FROM pg_class c
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
LEFT JOIN pg_stat_all_tables s ON s.relid = c.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 2 DESC
LIMIT 20;

What it does

Shows each database's oldest unfrozen XID and multixact age relative to autovacuum_freeze_max_age and the wraparound limit; in the current database, the oldest tables (including TOAST) with size and last vacuum time.

When to use it

In routine monitoring (typical thresholds: pct_of_wraparound 50% warning / 75% critical), when the log shows 'must be vacuumed within N transactions', and to prioritize freeze work.

Caution

For tables where pct_of_freeze_max_age exceeds 100, autovacuum starts a mandatory (anti-wraparound) vacuum; it is I/O-heavy and restarts even if terminated. If the age does not drop, the cause is almost always something holding back the xmin horizon (long transaction, prepared transaction, replication slot, hot_standby_feedback); fix that first, otherwise even VACUUM FREEZE cannot lower the age. Close to the limit the server first warns, then refuses to assign new XIDs (writes) when only a few million remain. For a targeted freeze: VACUUM (FREEZE, VERBOSE) <table>;