Transaction ID wraparound

A counter that goes round

Every transaction that writes gets a transaction ID, and that ID is a 32-bit number. Row visibility is decided by comparing the ID stored on a tuple against the ID of the transaction doing the looking, using modulo arithmetic: any given transaction considers roughly two billion IDs to be in its past and the other two billion to be in its future.

That works indefinitely, provided no tuple is ever left carrying an ID that has drifted more than two billion transactions into the past. If one is, the comparison flips. A row that was committed and visible becomes, from the point of view of new transactions, a row from the future, and it disappears. Not deleted, not corrupted on disk, simply invisible.

The defence is freezing. Vacuum rewrites the visibility information on old tuples to say “this row is older than everything, always visible”, removing them from the arithmetic entirely. relfrozenxid on a table records the oldest ID that has not yet been frozen in it, and datfrozenxid on a database records the oldest across all its tables. As long as vacuum keeps freezing, the counter can turn over forever and nothing breaks.

When it does not keep up, PostgreSQL protects the data by refusing to issue new transaction IDs. The database stops accepting writes. This is the only common Postgres failure mode that ends in an outage you cannot shrug off, and it is entirely preventable, which is why it deserves a page of its own.

How far along are you

Two queries, thirty seconds, and you will know. The first is at the database level:

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_of_budget
FROM pg_database
ORDER BY xid_age DESC;

The second finds the individual relation responsible, checking its TOAST table alongside it, because a table whose own rows are freshly frozen can still be held back by the out-of-line storage hanging off it:

SELECT c.oid::regclass AS relation,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age,
       greatest(mxid_age(c.relminmxid), mxid_age(t.relminmxid)) AS mxid_age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 20;

Read the numbers against three landmarks. autovacuum_freeze_max_age, two hundred million by default, is where autovacuum stops being optional: past that point a wraparound-prevention vacuum is launched on the table whether or not autovacuum is switched on for it, and whether or not the table has any dead rows at all. At forty million transactions from the wall the server starts logging a warning naming the database and the exact number of transactions remaining. At three million from the wall it stops assigning transaction IDs and answers write attempts with an error telling you to run a database-wide vacuum.

An age in the low hundreds of millions on a busy cluster is normal. An age climbing steadily past autovacuum_freeze_max_age and not coming back down is the thing to act on, because it means the freezing work is not completing, and the gap between “not completing” and “not started” is the whole diagnosis.

Multixacts have their own counter

There is a second counter with the same failure mode and a fraction of the attention. When several transactions hold row-level locks on the same row at once, PostgreSQL replaces the single locking transaction ID with a multixact ID, which is also 32 bits and also wraps. mxid_age() in the query above is how you read it, autovacuum_multixact_freeze_max_age is its threshold, and the warning it produces looks almost identical to the transaction-ID one.

Workloads that generate multixacts quickly are the ones taking many concurrent SELECT ... FOR SHARE or foreign-key locks on the same parent rows. If your multixact age is rising faster than your transaction ID age, that is where to look, and the queue table with one hot parent row is the usual culprit.

Why the freezing stopped

The mechanism is the same one that blocks ordinary cleanup, so the diagnosis overlaps with autovacuum and bloat, but the consequences are sharper. Vacuum can only advance relfrozenxid as far as the oldest transaction anyone might still need. If something is pinning that horizon, the freeze horizon cannot move, the age keeps rising, and every wraparound vacuum that runs completes successfully having achieved nothing.

The things that pin it are a long-running transaction, an abandoned replication slot, a prepared transaction that was never resolved, and on a primary with hot_standby_feedback switched on, a long query on a standby reaching back through the feedback message. That last one catches people out, because the query causing the problem is not running on the server having the problem:

SELECT application_name,
       client_addr,
       state,
       age(backend_xmin) AS feedback_xid_age
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL
ORDER BY feedback_xid_age DESC;

A large feedback_xid_age here means a standby is holding your primary’s horizon open. Either shorten the query on the standby, or accept replication conflicts and turn the feedback off, but do not do the second one without reading the note on it in replication lag and slot health.

The other common cause is much duller: the table is enormous, the wraparound vacuum genuinely takes many hours, and it is being cancelled or interrupted before it finishes. Watch for that with pg_stat_progress_vacuum, and note that a wraparound vacuum ignores autovacuum_vacuum_cost_delay once the age crosses vacuum_failsafe_age, dropping its own throttling and its index cleanup in order to finish sooner. Seeing the failsafe engage in the log is a signal that you are much later than you thought. What the log line looks like, and why the setting behind it has a floor that quietly overrides whatever you set, is on its own page.

Getting the age back down

If you are in the warning band and the horizon is clear, the fix is to let the vacuum finish, and to help it: raise maintenance_work_mem for the session, use VACUUM (FREEZE, VERBOSE) on the specific relations the second query named rather than a database-wide vacuum, and run several in parallel on separate tables if the I/O budget allows. vacuumdb --freeze --jobs does that for you.

If the server has already stopped assigning transaction IDs, it will still accept a VACUUM. Work through the highest-age relations from the second query rather than firing a database-wide vacuum and waiting; the database starts issuing IDs again the moment datfrozenxid has moved far enough.

Afterwards, the change that stops it recurring is almost never raising autovacuum_freeze_max_age. It is finding out what pinned the horizon and removing it, and on very large append-mostly tables, letting the visibility map do the work by making sure inserts are being vacuumed at all. PostgreSQL 18 helps here with eager freezing of pages that vacuum is already visiting, tuned by vacuum_max_eager_freeze_failure_rate, which spreads freezing work out instead of saving it all for the emergency.

If what you actually want is the number rather than the argument, the wraparound countdown takes your oldest age, your transaction rate and your vacuum throughput and answers the only question that matters on the night: whether the freeze can finish before the stop limit arrives, on the major version you are running.