PostgreSQL problems

Transaction ID Wraparound in PostgreSQL: How Close You Are, and the Fix

22 November 2026

Wraparound is the one PostgreSQL problem that stops every write in the instance, and it never arrives suddenly: the age of the oldest unfrozen transaction ID climbs for weeks while something keeps vacuum from freezing old rows. Three queries show how close each database is, which tables carry the age and what holds vacuum back.

What PostgreSQL says

Transaction IDs are 32-bit numbers, so a row can be compared with about two billion transactions on either side of it. Vacuum marks old rows as frozen, which takes them out of that comparison. When freezing falls behind, the server first warns and then protects itself:

Multixact IDs, which record row locks shared by several transactions, have the same limit and the same protection.

How close each database is

SELECT datname,
       age(datfrozenxid)                AS xid_age,
       mxid_age(datminmxid)             AS mxid_age,
       round(100.0 * age(datfrozenxid)
             / 2147483648, 1)           AS pct_of_limit
FROM   pg_database
ORDER  BY xid_age DESC;

A healthy database stays under autovacuum_freeze_max_age, 200 million by default: at that age autovacuum starts a freeze of its own, whatever its other settings say. An age that keeps climbing far past it means the freeze does not finish or is not allowed to remove anything.

Which tables carry the age

A database is as old as its oldest table. Run this in the database from the first query:

SELECT c.oid::regclass                  AS table_name,
       greatest(age(c.relfrozenxid),
                age(t.relfrozenxid))    AS xid_age,
       pg_size_pretty(
         pg_total_relation_size(c.oid)) AS size
FROM   pg_class c
LEFT   JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE  c.relkind IN ('r', 'm')
ORDER  BY xid_age DESC
LIMIT  10;

What holds vacuum back

Vacuum cannot freeze or remove a row that some transaction might still see. Whatever holds the oldest xmin of the instance holds every vacuum. There are four places to look:

-- 1. Sessions: long or idle transactions
SELECT pid, datname, usename, state, xact_start,
       greatest(age(backend_xmin),
                age(backend_xid)) AS held_age
FROM   pg_stat_activity
WHERE  backend_xmin IS NOT NULL
OR     backend_xid IS NOT NULL
ORDER  BY held_age DESC
LIMIT  5;

-- 2. Prepared transactions nobody finished
SELECT gid, prepared, owner, database,
       age(transaction) AS xid_age
FROM   pg_prepared_xacts
ORDER  BY prepared;

-- 3. Replication slots
SELECT slot_name, slot_type, active,
       age(xmin)         AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age
FROM   pg_replication_slots;

-- 4. Standbys with hot_standby_feedback
SELECT application_name, client_addr, state,
       age(backend_xmin) AS xmin_age
FROM   pg_stat_replication
WHERE  backend_xmin IS NOT NULL;

The row with the largest age across the four is the holder. When all four are empty and the age still climbs, autovacuum itself is the reason: turned off for the table, or every worker busy on other tables.

SELECT c.oid::regclass AS table_name, c.reloptions
FROM   pg_class c
WHERE  c.reloptions::text LIKE '%autovacuum_enabled=false%';

The fix, in order

  1. Remove the holder. End the session with SELECT pg_terminate_backend(pid);, finish a forgotten prepared transaction with ROLLBACK PREPARED 'gid';, or drop a slot nobody reads with SELECT pg_drop_replication_slot('name'); Until the holder is gone, no vacuum lowers the age.
  2. Freeze the oldest tables first. VACUUM (FREEZE, VERBOSE) public.orders; for the tables at the top of the second query. The age of the database falls when its oldest table is done.
  3. When time is short, skip what can wait. VACUUM (FREEZE, INDEX_CLEANUP off, TRUNCATE off) public.orders; freezes the heap and leaves the indexes for later. From an age of 1.6 billion, vacuum_failsafe_age, vacuum does this by itself.
  4. If writes already stopped, vacuum in a normal session. VACUUM assigns no transaction ID, so it still runs. Remove the holder, freeze the oldest tables, and writes return when the age is back under the limit.
  5. Give autovacuum the means. Turn it back on where a table has it off. Raise autovacuum_max_workers when all of them are always busy, and autovacuum_vacuum_cost_limit when a freeze of a large table takes days.
  6. Stop the next holder early. idle_in_transaction_session_timeout ends sessions that sit in an open transaction, and max_slot_wal_keep_size bounds what an abandoned slot can keep.

See it coming

PostgreSQL's own warning comes 40 million transactions before the stop, which a busy system uses in hours. Radar Engine, the detection layer of Scops, follows the age of every database and how fast it rises, with no agent on the server:

DetectorWhen it fires
Transaction ID wraparoundOn the transaction ID and multixact age of each database and the days left at its real rate. Watch from an age of 500 million. Warning from 1 billion, or from 500 million with writes stopping within 30 days. Critical from 1.5 billion, or from 1 billion with 7 days left.
Xmin horizon heldVacuum cannot remove dead rows: the finding names the session, the prepared transaction, the replication slot or the standby that holds the oldest xmin.
Vacuum falling behindA table with a million dead rows and three times its autovacuum trigger, on Health Check passes in a row, with the reason autovacuum does not keep up.

The wraparound finding says when writes stop at the present rate and why the age does not fall: a holder of the xmin horizon, autovacuum turned off, every worker busy, or a freeze that runs now. A database that reaches its planned freeze age on schedule stays a Watch.

Questions

At what age does PostgreSQL stop accepting writes?
3 million transactions before 2 to the power of 31, so at an age of about 2.144 billion. It starts to warn in the server log 40 million transactions before the limit.

Does autovacuum prevent wraparound by itself?
Yes, when nothing holds it. At autovacuum_freeze_max_age, 200 million by default, it starts a freeze even when autovacuum is turned off. It cannot freeze rows that a long transaction, a prepared transaction or a replication slot still needs.

Do I need single-user mode to fix wraparound?
No. On current PostgreSQL versions a VACUUM in a normal session works even after writes stopped, because VACUUM assigns no transaction ID.

How do I check the transaction ID age?
SELECT datname, age(datfrozenxid) FROM pg_database shows it per database, and age(relfrozenxid) in pg_class per table.

Related problems

Sources