PostgreSQL transaction IDs are 32-bit and wrap around. To keep old rows visible, vacuum has to freeze them before about 2 billion newer transactions have been assigned. Usually autovacuum does this without anyone noticing. When something stops it, the database warns at 40 million transactions from the limit and stops accepting writes at 3 million. Reads still work, but the application is effectively down until vacuum catches up.

The lab reproduced both stages on PostgreSQL 18.6 in Docker. Burning 2 billion transaction IDs takes days, so the script used pg_resetwal -x to move the next transaction ID close to the limit. That is a lab shortcut only; never run pg_resetwal on a database you care about. Everything else is ordinary server behavior. The script and full output are public.

The setup: one forgotten prepared transaction

A transaction prepared for two-phase commit (PREPARE TRANSACTION) and never committed or rolled back. That happens when an XA transaction manager in a Java application server or message broker loses track of one. It holds the oldest transaction ID in the cluster and survives restarts:

     gid      | transaction |           prepared
--------------+-------------+-------------------------------
 xa-ledger-42 |         756 | 2026-10-22 03:55:36.081571+00

A session left idle in a transaction or an abandoned replication slot does the same; this page on autovacuum blockers shows how to find each. Prepared transactions are the easiest to miss, because they do not appear in pg_stat_activity.

Stage 1: the warning

With the next transaction ID moved to 2,120,000,000, every database was at 98.7% of the way to wraparound, and any statement that needed a new transaction ID got this:

WARNING:  database "template1" must be vacuumed within 27484391 transactions
HINT:  To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

The warning named template1, not the database the application was using. It names whichever database the server is tracking as oldest, so check them all. Applications rarely show database warnings to anyone; set up the monitoring below so you do not rely on someone reading the log.

Autovacuum ran, and could not fix it

Autovacuum started an anti-wraparound vacuum straight away, and from PostgreSQL 14 the failsafe (vacuum_failsafe_age, 1.6 billion by default) made it skip index cleanup and throttling to finish faster:

WARNING:  bypassing nonessential maintenance of table "postgres.public.events" as a failsafe after 0 index scans
LOG:  automatic aggressive vacuum to prevent wraparound of table "postgres.public.events": index scans: 0
	removable cutoff: 756, which was 2119999245 XIDs old when operation ended
	new relfrozenxid: 756, which is 4 XIDs ahead of previous value

The removable cutoff is 756, the prepared transaction. Vacuum cannot freeze anything newer than the oldest transaction still running, so relfrozenxid moved forward by 4 and the age did not fall. A manual VACUUM (FREEZE, VERBOSE) said so directly:

WARNING:  cutoff for removing and freezing tuples is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.

This is the most important point on this page: near wraparound, vacuum settings are not the fix if something holds the horizon. More workers, a higher cost limit or a manual freeze all run, and they all stop at the same cutoff.

Stage 2: writes stop

With the next transaction ID at 2,145,000,000, fewer than 3 million from the limit:

INSERT INTO events VALUES (100002, 'y');
ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "postgres"
HINT:  Execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

SELECT count(*) FROM events;
 count
--------
 100001

Reads work and writes fail. For most applications that means an outage.

Recovery: remove the blocker, then plain VACUUM

The PostgreSQL documentation is explicit that you do not need to stop the server or use single-user mode, contrary to what older advice says. The steps, in order:

  1. Resolve old prepared transactions in pg_prepared_xacts, after confirming with the owning application whether to commit or roll back.
  2. End long-running transactions: rows in pg_stat_activity with a large age(backend_xid) or age(backend_xmin).
  3. Drop stale replication slots: large age(xmin) or age(catalog_xmin) in pg_replication_slots. A replica or CDC consumer that still uses the slot will need rebuilding or a re-snapshot.
  4. Run plain VACUUM in the affected database. Not VACUUM FULL, which needs a transaction ID and fails. Not VACUUM FREEZE, which does more work than needed to get writes back.
-- Every blocker type in one list, oldest first
SELECT 'prepared xact' AS kind, gid AS name, age(transaction) AS xid_age FROM pg_prepared_xacts
UNION ALL
SELECT 'backend', pid::text || ' ' || coalesce(application_name, ''), greatest(age(backend_xid), age(backend_xmin))
FROM pg_stat_activity
WHERE (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL) AND pid <> pg_backend_pid()
UNION ALL
SELECT 'replication slot', slot_name, greatest(age(xmin), age(catalog_xmin)) FROM pg_replication_slots
ORDER BY xid_age DESC NULLS LAST;

In the lab it returned one row, the prepared transaction:

     kind      |     name     |  xid_age
---------------+--------------+------------
 prepared xact | xa-ledger-42 | 2119999257

After ROLLBACK PREPARED 'xa-ledger-42', one VACUUM of the table did what autovacuum and the manual freeze could not:

removable cutoff: 2145000000, which was 0 XIDs old when operation ended
new relfrozenxid: 2120000000, which is 2119999244 XIDs ahead of previous value

Then vacuumdb --all for the other databases. Ages fell to 25 million and writes worked again. The lab tables were tiny, so this took under a second. On a multi-terabyte database a vacuum that has to read every page can take hours, and writes stay down for that time. Vacuum the tables with the oldest relfrozenxid first, and do not stop the anti-wraparound autovacuum that may already be running on them.

Monitor it before the warning

-- Per database: transaction ID and multixact age
SELECT datname, age(datfrozenxid) AS xid_age, mxid_age(datminmxid) AS mxid_age,
       round(100.0 * age(datfrozenxid) / 2147483648, 1) AS pct_to_wraparound
FROM pg_database ORDER BY xid_age DESC;

-- Per table, in the current database: what the next aggressive vacuums will hit
SELECT c.oid::regclass AS table_name, 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')
ORDER BY age(c.relfrozenxid) DESC LIMIT 20;

With the defaults, autovacuum forces a freeze on a table at autovacuum_freeze_max_age, 200 million. An age that settles somewhere above that and falls back after each anti-wraparound vacuum is normal. An age that keeps climbing past it means something is wrong. Multixact IDs, used for row locks shared by several transactions, wrap around the same way and are frozen by the same vacuums.

  • RDS and Aurora: alarm on the CloudWatch metric MaximumUsedTransactionIDs. RDS adaptive autovacuum starts making autovacuum more aggressive when it reaches 500 million or autovacuum_freeze_max_age, whichever is greater, and logs an event when it does. That event is worth a page. RDS for PostgreSQL also provides postgres_get_av_diag(), which reports blockers older than 500 million transactions and suggests commands to resolve them.
  • Self-managed: alert on the per-database query above. Pick a threshold that leaves time to investigate during working hours, well below the 2 billion limit.

Checklist

  1. Alarm on transaction ID age well before the warning stage.
  2. Check pg_prepared_xacts; if nothing should use two-phase commit, set max_prepared_transactions = 0.
  3. Set idle_in_transaction_session_timeout for application roles.
  4. Drop replication slots that nothing consumes.
  5. If it happens: resolve the blockers first, then plain VACUUM, oldest tables first.