PostgreSQL Transaction ID Wraparound: How to Spot It Early and Recover

PostgreSQL transaction ID wraparound is one of the few problems that can stop a healthy-looking database from accepting writes. It doesn't arrive out of nowhere: for weeks or months the XID age climbs, then the log fills with WARNING: database "mydb" must be vacuumed within N transactions, and if nothing changes PostgreSQL refuses new write transactions to protect your data. The good news is that it is predictable, easy to monitor and, if you catch it early, entirely avoidable.

What wraparound is, in plain terms

Every transaction that writes gets a transaction ID (XID). PostgreSQL uses XIDs to decide which row versions each transaction can see. The XID is a 32-bit number, so the documentation explains that IDs are compared in a circle: for any XID, about two billion are "in the past" and two billion are "in the future". If an old row version kept its original XID for more than two billion transactions, it would suddenly look like it came from the future and become invisible. The data would still be on disk, but you couldn't see it.

VACUUM prevents this by freezing old rows: marking them as committed so long ago that they are visible to everyone, whatever the counter says. So the rule is simple: every table in every database has to be vacuumed, and its old rows frozen, at least once every two billion transactions.

The safety net, and when it runs out

PostgreSQL watches the age of the oldest unfrozen XID in each table (relfrozenxid) and each database (datfrozenxid):

• At autovacuum_freeze_max_age (default 200 million) autovacuum starts an anti-wraparound vacuum on the table, even if autovacuum is disabled. In pg_stat_activity it shows up with "(to prevent wraparound)" in the query text.
• At vacuum_failsafe_age (default 1.6 billion) VACUUM switches to its "strategy of last resort": it stops honouring cost-based delays, skips non-essential work such as index vacuuming and may use all of shared buffers. It wants to finish freezing as fast as possible.
• 40 million transactions before the limit the warnings start: must be vacuumed within N transactions.
• 3 million transactions before the limit PostgreSQL stops assigning new XIDs: ERROR: database is not accepting commands that assign new transaction IDs to avoid wraparound data loss. Read-only queries still work; inserts, updates, deletes and truncates fail.

At 200 million, a busy system is still very far from trouble. The point of the gap is to give autovacuum time to work. Wraparound incidents happen when something stops autovacuum from finishing that work for a long time.

Step 1: measure XID age

Per database:

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

Per table, including TOAST tables, based on the query in the docs:

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 c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 20;

A database steadily above 200 million that never drops back after anti-wraparound vacuums is the pattern to worry about. It means vacuums run but can't advance the frozen horizon, or a few huge tables never finish.

Step 2: find what is holding the horizon back

VACUUM can only freeze rows that no running transaction might still need. Anything that keeps an old snapshot alive keeps the horizon old, no matter how often vacuum runs. The usual suspects are exactly the ones the documentation lists in its recovery steps:

-- old transactions
SELECT pid, state, age(backend_xid) AS xid_age, age(backend_xmin) AS xmin_age,
       now() - xact_start AS running_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY greatest(age(backend_xid), age(backend_xmin)) DESC
LIMIT 10;

-- forgotten prepared transactions
SELECT gid, prepared, age(transaction) AS xid_age FROM pg_prepared_xacts;

-- replication slots holding back the horizon
SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

• Long-running or idle-in-transaction sessions. A report that runs for a day, or an application connection that opened a transaction and went quiet.
• Prepared transactions from two-phase commit that nobody committed or rolled back. They survive restarts and are easy to forget, because most teams don't know they use them.
• Replication slots for a replica or CDC consumer that is gone or far behind. A slot with an old xmin or catalog_xmin holds back cleanup for the whole cluster. The same slots can also fill the disk with WAL.
• hot_standby_feedback on a standby with long queries, which passes the standby's old snapshot back to the primary. We covered that trade-off in our PostgreSQL replication lag guide.

Step 3: if writes have already stopped

The current PostgreSQL documentation is explicit that you should not stop the server and go into single-user mode, as older advice suggested. VACUUM still runs normally. The recommended order is:

1. Commit or roll back old prepared transactions (pg_prepared_xacts).
2. End long-running open transactions, with pg_terminate_backend if necessary.
3. Drop old replication slots that belong to servers that no longer exist or have been down for a long time. Dropping a slot that a live replica still uses means that replica may need to be rebuilt, so check first.
4. Run VACUUM in the affected database, as a superuser so it can also process the system catalogs. A database-wide VACUUM is simplest; to save time, vacuum the tables with the oldest XID age first.

Two things to avoid: VACUUM FULL needs a new XID and will fail, and VACUUM FREEZE does more work than needed to get you back online. Single-user mode is only worth it if you want to DROP or TRUNCATE large unneeded tables to skip vacuuming them, and it disables wraparound safeguards, so it is riskier.

Preventing it

• Let autovacuum keep up. On large, busy tables the default throttling is often too slow. Raise autovacuum_vacuum_cost_limit, add workers if needed and use per-table settings for the biggest tables. Our autovacuum tuning guide covers the knobs.
• Limit how long transactions can stay open. Set idle_in_transaction_session_timeout, and give reporting users a statement_timeout.
• Clean up slots and prepared transactions. Alert on inactive replication slots, and on any prepared transaction older than a few minutes. Set max_prepared_transactions = 0 if you don't use two-phase commit.
• Watch insert-only tables. Tables that are only appended to don't need vacuuming for dead rows, but they still need freezing. They tend to get their first serious vacuum as an anti-wraparound one, often at the worst moment and on the biggest table.
• Remember multixacts. Row locks shared by several transactions use multixact IDs, which are also 32-bit and need the same care. Check mxid_age(relminmxid) alongside XID age.

What to alert on

1. age(datfrozenxid) per database, with a warning around 500 million and a critical alert well before 1 billion. Adjust to how fast you burn XIDs.
2. The oldest transaction and oldest backend_xmin age in pg_stat_activity.
3. Inactive replication slots and their xmin age.
4. Any must be vacuumed within message in the log, which should page someone immediately.
5. Anti-wraparound autovacuums that have been running for hours on the same table.