Transaction ID wraparound: why must PostgreSQL freeze old tuples? Advanced

Problem

A catalog table is almost never updated and has n_dead_tup = 0, so the operations team assumes it needs no VACUUM. Yet PostgreSQL still runs an autovacuum “to prevent wraparound” on that table.

The reason: every row version carries the XID of the transaction that created it, and a normal XID is only 32 bits.

After enough transactions the counter wraps around; a tuple old enough that is still compared as a normal XID would be mistaken as coming from “the future” and become invisible.

PostgreSQL prevents this by freezing sufficiently old tuples. This is data-safety maintenance, not a performance optimization.

This article only contains read-only status queries. It does not simulate billions of transactions, lower the protection thresholds, or turn off autovacuum.

The XID age scale

age() counts the number of XIDs that have passed, not time. The thresholds below are PostgreSQL 17 defaults (all from the official docs), read top to bottom as the age grows:

XID age (age)	Threshold	What happens
0	Just frozen	Nothing to do
50 million	vacuum_freeze_min_age	When VACUUM runs, it freezes tuples older than this
150 million	vacuum_freeze_table_age	The next VACUUM switches to an aggressive scan to advance relfrozenxid
200 million	autovacuum_freeze_max_age	Anti-wraparound autovacuum starts for that table, even one with no dead tuples
1.6 billion	vacuum_failsafe_age	Failsafe: VACUUM drops cost-based delay and skips index vacuuming to run as fast as possible
40 million XIDs left	(from the wraparound point, i.e. age about 2.107 billion)	The server starts logging WARNING ... must be vacuumed within N transactions
3 million XIDs left	(age about 2.144 billion)	The server refuses to assign new XIDs: every write stops

The absolute limit is 2^31 = 2,147,483,648, i.e. about two billion XIDs “in the past”; the other half of the circle is treated as the future. Note that the configuration thresholds cap one another: for example, vacuum_freeze_min_age is effectively at most half of autovacuum_freeze_max_age.

Multixacts (used when several transactions lock the same row, see row-level locks) have their own counter and thresholds: autovacuum_multixact_freeze_max_age defaults to 400 million, vacuum_multixact_freeze_table_age to 150 million, failsafe at 1.6 billion.

The warning at 40 million remaining and refusal at 3 million remaining apply likewise. Systems with many foreign keys and FOR SHARE should also track mxid_age().

Core concepts

• Freezing marks a sufficiently old tuple as always visible, instead of relying on XID comparison. Since PostgreSQL 9.4 it is a status bit; the displayed xmin may keep the old number.
• pg_class.relfrozenxid is the oldest XID that may still be unfrozen in one relation; pg_database.datfrozenxid is the lowest across the whole database. It is a conservative marker maintained by VACUUM and cannot be replaced with MIN(xmin); do not scan xmin across the whole table to investigate.
• Insert-only tables still need aggressive VACUUM because old tuples still carry old XIDs. PostgreSQL 18 lets a normal VACUUM eagerly freeze some all-visible pages (eager freezing, tuned with vacuum_max_eager_freeze_failure_rate), which reduces large freeze waves but does not remove the need to freeze.
• XIDs in use are 32-bit on PostgreSQL 17.

How it works

XID age grows; VACUUM freezes before the counter wraps around

Enlarge diagram

32-bit XID

◀ Prev▶ PlayNext ▶↺ RestartStep 1/4

Every transaction that needs an ID gets an XID from a 32-bit space. The ID cannot grow forever in a 32-bit field.

1. A transaction that needs an XID receives a number in the 32-bit space.
2. Tuples it creates carry that XID; as new transactions appear, their age(xmin) grows.
3. VACUUM freezes eligible tuples, so their visibility no longer depends on ordinary XID comparison.
4. If the table is not frozen in time, anti-wraparound autovacuum starts even without dead tuples; if that still fails, the server warns and then blocks statements needing new XIDs.

Alerting: queries and thresholds

SELECT datname,
       age(datfrozenxid)                  AS xid_age,
       2147483648 - age(datfrozenxid)     AS xid_remaining
FROM pg_database
ORDER BY xid_age DESC;

Alert on xid_age per database, and add the largest age(relfrozenxid) within each database:

Level	Suggested threshold (with the 200M default)	Action
Observe	Above 200 million and dropping after each autovacuum round	Normal: autovacuum is doing its job
Warning	Above 500 million, or not dropping after an autovacuum round	Find what is holding back the horizon (below)
Page on-call	Above 1 billion	Not far from failsafe (1.6 billion); handle it the same day

The 500 million and 1 billion thresholds are this article’s operational suggestions, not PostgreSQL numbers.

Also measure the rate of XID consumption (sample age(datfrozenxid) every few minutes) to convert to “how many days are left”: an age of 10 million can mean months on a light-write system or hours on a heavy-write one.

A script in the repo prints the configured thresholds, each database’s age, and the 10 oldest relations (with TOAST joined in):

docker compose -f examples/docker-compose.yml up -d postgres
docker compose -f examples/docker-compose.yml exec -T postgres psql -U hub -d hub < examples/src/postgres-wraparound/check-xid-age.sql

When the age does not drop: find what holds the horizon

Long-open transactions, prepared transactions, stale replication slots (and replicas with hot_standby_feedback on) hold back the XID horizon, so VACUUM finishes yet the age does not drop. Raising configuration thresholds only postpones the warning. Start with observation queries:

SELECT pid, state, xact_start, age(backend_xid) AS xid_age,
       age(backend_xmin) AS xmin_age, query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY xact_start NULLS LAST;

SELECT gid, prepared, age(transaction) AS xid_age
FROM pg_prepared_xacts ORDER BY prepared;

SELECT slot_name, active, age(xmin) AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

XID columns can be NULL, and age(NULL) is also NULL: not an error. Dropping a slot may force a replica to be rebuilt; confirm with whoever operates the replica.

If the server is already emitting wraparound warnings, deal with whatever is holding the horizon, then run VACUUM on the right database with privileges sufficient to process the system catalogs too, following the instructions for your exact version.

VACUUM FULL is not a rescue: it rewrites the table, takes a strong lock, and may itself need an XID.

Real-world example

An insert-only audit table causes a surprise at 3 a.m.

The system has a constantly updated jobs table (familiar autovacuum territory) and an insert-only audit_log table with 2 billion rows. The audit table never has dead tuples, so nobody pays attention.

That lasts until its XID age hits 200 million and anti-wraparound autovacuum scans the entire table, spending hours of I/O during off-peak time.

The team alerts on age(datfrozenxid) (warning at 500 million, page at 1 billion), records the daily XID growth rate, and checks long transactions, prepared transactions, and replication slots first.

They do not kill the autovacuum worker processing the large table even though it generates I/O. The numbers in the example are an illustrative scenario.

Production checklist

1. Alert on age(datfrozenxid) per database: warning at 500M, page at 1B (and the equivalent for mxid_age()).
2. Monitor the largest age(relfrozenxid) per table, including TOAST.
3. Record the daily XID consumption rate to know “how long is left”, not just the absolute number.
4. Periodically review prepared transactions, inactive replication slots, and idle in transaction sessions.
5. Huge insert-only tables: schedule VACUUM (FREEZE) off-peak instead of waiting for aggressive autovacuum.
6. Do not turn off autovacuum, and do not use VACUUM FULL as a rescue.
7. Have a runbook for when a WARNING already exists: who may terminate sessions, drop slots, run VACUUM.

Trade-offs

Freezing on schedule

• Rows outlive the life cycle of a 32-bit XID with correct visibility.
• Anti-wraparound autovacuum leaves a large buffer before the real limit.
• age(relfrozenxid) and age(datfrozenxid) reveal trends through read-only queries.

Costs and operational risks

• Aggressive VACUUM scans many pages and costs more I/O than a normal VACUUM.
• Transactions, slots, or prepared transactions holding the horizon keep freezing from advancing.
• Raising thresholds or disabling autovacuum only delays the problem and shortens reaction time.

Common mistakes

Common pitfall

“No dead tuples means no VACUUM needed.” XID freezing is independent of reclaiming space. Insert-only or static tables still have to be frozen.

Common pitfall

“After freezing, xmin must equal 2.” Since 9.4, freezing is a status bit and the displayed xmin may stay the same. Judge by relfrozenxid and the VACUUM result, not by a random row.

Common pitfall

“Close to wraparound, run VACUUM FULL to be safe.” Strong lock, table rewrite, may consume an XID. Follow the recovery instructions for the version you run.

Summary

Key takeaways

• Normal XIDs are 32-bit: old tuples must be frozen before age comparison loses its meaning.
• The scale: 50M freeze, 150M aggressive, 200M mandatory autovacuum, 1.6B failsafe, 40M left warns, 3M left refuses new XIDs.
• age() measures XIDs, not time; alerts should include the XID consumption rate.
• If the age does not drop, find what holds the horizon; do not change thresholds to hide the symptom.

Self-check: (1) Why does an insert-only table still need aggressive VACUUM? (2) Does age(datfrozenxid) = 10 million tell you how many safe days remain? (3) How does the 1.6 billion failsafe change VACUUM’s behavior?

References

• Preventing Transaction ID Wraparound Failures — PostgreSQL 17
• Client Connection Defaults — PostgreSQL 17
• Automatic Vacuuming — PostgreSQL 17
• System Columns — PostgreSQL 17
• Monitoring Database Activity — PostgreSQL 17
• Release Notes — PostgreSQL 18