Freezing and transaction ID wraparound

conceptThe concept

XIDs are compared modulo 2³²: each transaction sees about 2 billion XIDs in the past and 2 billion in the future. A row whose xmin falls more than 2 billion behind would suddenly look like it was created in the future and disappear. Freezing marks a tuple's xmin as "committed and older than everything", so its age no longer matters.

Every table has relfrozenxid: all XIDs older than it are frozen. age(relfrozenxid) is how many transactions have run since. When it exceeds autovacuum_freeze_max_age (200 million), autovacuum runs an aggressive anti-wraparound vacuum on that table, even if autovacuum is disabled, and that vacuum will not cancel itself for lock conflicts.

Near 2 billion, PostgreSQL warns, then stops assigning new XIDs ("database is not accepting commands that assign new transaction IDs") until a VACUUM freezes enough rows. The visibility map's all-frozen bit lets later vacuums skip frozen pages.

whyWhy it matters

Wraparound shutdowns are among the most painful PostgreSQL incidents: high-write systems can burn through 200 million XIDs in days, and the forced vacuum of a multi-terabyte table can take many hours.

Use caseReal-world scenario

A monitoring alert fires when age(datfrozenxid) exceeds 1 billion. The DBA finds a stuck replication slot pinning xmin, drops it, and lets the aggressive vacuum complete.

Try itExamples

Run and edit these in the interactive lesson.

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 6) AS pct_to_shutdown,
       mxid_age(datminmxid) AS multixact_age
FROM pg_database
ORDER BY xid_age DESC;
SHOW autovacuum_freeze_max_age;

SELECT c.relname, age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't') AND c.relnamespace <> 'pg_catalog'::regnamespace
ORDER BY age(c.relfrozenxid) DESC
LIMIT 8;

SELECT lp, t_xmin, raw_flags, combined_flags
FROM heap_page_items(get_raw_page('employees', 0)),
     LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
LIMIT 3;

internalsUnder the hood

Freezing sets a flag in the tuple header (both committed and invalid bits, called frozen) instead of rewriting xmin. Multixact IDs, used when several transactions lock the same row, have their own wraparound counter (mxid_age(datminmxid)) and the same kind of protection. Since 16, vacuum_freeze_min_age also interacts with opportunistic page-level freezing.

Avoid / preferDo this, not that

SELECT pg_cancel_backend(pid) FROM pg_stat_activity
WHERE query LIKE 'autovacuum: VACUUM%(to prevent wraparound)';

-- let it finish; raise autovacuum_vacuum_cost_limit if it is too slow

-- an abandoned replication slot or prepared transaction left in place

SELECT slot_name, active FROM pg_replication_slots;
SELECT gid, prepared FROM pg_prepared_xacts;

PitfallsCommon mistakes

• Ignoring XID age in monitoring because "autovacuum handles it". It does, until a long transaction or stale slot stops it.
• Restarting the server or killing the anti-wraparound autovacuum to "fix" slowness. It will start again and the age keeps growing.
• Running single-user mode first in an emergency. Modern guidance: remove the blocker (old slots, prepared transactions, long sessions), then run VACUUM in normal mode.

LimitsWhen not to use it

Do not run VACUUM FREEZE on every table every night "to be safe". It writes every page again, generating heavy I/O and WAL. Monitor age and let autovacuum freeze incrementally.

SecuritySecurity notes

Reporting and BI roles that leave transactions open can block freezing for the whole cluster. Set idle_in_transaction_session_timeout and statement timeouts for such roles.

ChallengeTry it yourself

SELECT c.oid::regclass AS table_name,
       age(c.relfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint - age(c.relfrozenxid) AS xids_until_forced
FROM pg_class c
WHERE c.relkind IN ('r', 'm') AND c.relnamespace = 'public'::regnamespace
ORDER BY xid_age DESC;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Routine vacuuming
• Autovacuum settings