Transaction ID wraparound and anti-wraparound vacuum · PostgreSQL

This is the failure that stops a PostgreSQL cluster accepting writes. It is fully preventable, it announces itself for weeks, and it still takes production systems down — because the warnings arrive on a database nobody was watching, or on a table nobody knew existed.

Why freezing is necessary

Transaction ids are 32 bits. About four billion values, and they wrap.

Visibility, from lesson VII-03, works by comparing a tuple’s xmin against the current transaction id: older means potentially visible, newer means not. That comparison is done in a circular space, where any id is “in the past” for half the range and “in the future” for the other half.

So a row written two billion transactions ago would, without intervention, become invisible — not deleted, not corrupted, simply unreadable, because its xmin has drifted into what now looks like the future.

Freezing is the fix. A frozen tuple is marked as visible to everything, permanently, and its xmin is no longer compared against anything. Vacuum does this, and relfrozenxid records how far back the guarantee extends for each relation.

The escalation ladder

Four thresholds, and the behaviour changes at each.

Age	Setting	What happens
50,000,000	`vacuum_freeze_min_age`	A vacuum that visits a page freezes tuples older than this
150,000,000	`vacuum_freeze_table_age`	The next vacuum on the table scans it in full rather than skipping all-visible pages
200,000,000	`autovacuum_freeze_max_age`	Autovacuum forces an anti-wraparound vacuum regardless of anything else
1,600,000,000	`vacuum_failsafe_age`	Vacuum abandons throttling and skips index cleanup
~2,000,000,000	—	The server refuses new transactions

The margin between 200 million and the shutdown point is roughly 1.8 billion transactions. On a cluster consuming a thousand transactions per second that is about three weeks.

Wraparound outages are never caused by a tight margin. They are caused by three weeks of a warning nobody read.

What an anti-wraparound vacuum looks like

This was reproduced on a test cluster by lowering autovacuum_freeze_max_age to 100,000 and restarting — waiting for 200 million real transactions is not practical, and the log output is identical.

Service impact possiblethe log line to recognise— Real server log from 18.6 with log_autovacuum_min_duration = 0 and autovacuum_freeze_max_age lowered to force the behaviour. Both settings were reverted after the capture.

$ docker logs rbpg-stor 2>&1 | grep "to prevent wraparound"

LOG:  automatic aggressive vacuum to prevent wraparound of table "postgres.public.narrow": index scans: 0
pages: 0 removed, 443 remain, 443 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 683715, which was 0 XIDs old when operation ended
new relfrozenxid: 683715, which is 682961 XIDs ahead of previous value
frozen: 443 pages from table (100.00% of total) had 100000 tuples frozen
visibility map: 0 pages set all-visible, 443 pages set all-frozen (443 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 30.324 MB/s, avg write rate: 29.789 MB/s
buffer usage: 480 hits, 453 reads, 445 dirtied
WAL usage: 887 records, 445 full page images, 3676020 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.11 s

The phrase is automatic aggressive vacuum to prevent wraparound of table. Put it in your log alerting.

Read tuples: 0 removed, 100000 remain. This vacuum deleted nothing. It existed purely to freeze, and it rewrote every page of the table to do it.

Every database’s age fell to zero after the pass, including template0 and template1, which were vacuumed in the same sweep:

LOG:  automatic aggressive vacuum to prevent wraparound of table
      "template1.pg_toast.pg_toast_3600": index scans: 0

The cost of freezing, measured

Freezing a table costs roughly its own size in WAL

From the capture above:

Table: 443 pages, about 3.5 MB.
WAL generated: 445 full page images, 3,676,020 bytes.

Freezing changes a page’s tuple headers, which is a change to the page, which after a checkpoint means the whole page is written to WAL as a full page image. A table being frozen for the first time therefore generates WAL roughly equal to its own size.

The planning consequences:

A 2 TB table’s first freeze generates about 2 TB of WAL. That has to be archived, shipped to every replica, and replayed.
Your archive will grow by the size of the table, in a burst.
Replicas will lag, because replay is largely single-threaded.
max_wal_size will be exceeded repeatedly, forcing checkpoints more often than usual during the operation.

This is why an unplanned anti-wraparound vacuum on a large, never-frozen table is a capacity event as well as a locking one, and why freezing incrementally is so much better than deferring it.

PostgreSQL 18’s vacuum_max_eager_freeze_failure_rate exists precisely to spread this: normal vacuums now freeze some all-visible pages opportunistically rather than leaving everything for the aggressive pass.

What anti-wraparound vacuum does differently

Three behaviours that ordinary autovacuum does not have.

It ignores autovacuum_enabled = false. A table with autovacuum disabled is still force-vacuumed at autovacuum_freeze_max_age. This is the backstop that prevents that setting from being fatal.

It does not yield its lock. Ordinary autovacuum cancels itself when a session requests a conflicting lock. This one does not. A DROP TABLE or ALTER TABLE behind it waits, and everything behind that waits too.

It scans the whole table. It cannot skip all-visible pages, because freezing is exactly what those pages need.

Do not kill an anti-wraparound vacuum

The instinct during an incident is to find the blocker and terminate it. Here that is wrong, and the reasoning is worth being explicit about.

Terminating it loses all the progress it has made. The launcher will start it again within autovacuum_naptime, from the beginning. The age continues climbing in the meantime, so each cycle starts from a worse position. On a large table this can be repeated indefinitely without ever completing.

Identify it before you act:

SELECT pid, backend_type, now() - xact_start AS running_for, query
  FROM pg_stat_activity
 WHERE query LIKE 'autovacuum:%';

An anti-wraparound worker’s query text contains (to prevent wraparound). That string is the one to check for before reaching for pg_terminate_backend.

If the vacuum genuinely must be stopped — the lock it holds is causing a larger outage — then it must be immediately replaced by a manual VACUUM (FREEZE, VERBOSE) on the same table, run to completion, in a window where it will not be interrupted. Stopping it and doing nothing guarantees the incident recurs, worse.

The response, in order

When a database’s age is climbing toward the threshold:

1. Find the relation. Include TOAST relations and shared catalogues.

SELECT c.oid::regclass AS relation, c.relkind,
       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;

2. Determine why it is not being frozen. This is one of the six modes from lesson VIII-04, and mode 1 — a held horizon — is the most common. Freezing cannot advance past the oldest snapshot either.

3. Remove the obstacle. End the transaction, drop the slot, stop the DDL.

4. Freeze deliberately, in a window.

-- one table, supervised, with a cost delay so it does not saturate storage
SET vacuum_cost_delay = '2ms';
VACUUM (FREEZE, VERBOSE) big_table;

vacuumdb --freeze --jobs=N --analyze handles a whole database with parallelism, which is the practical tool when several relations are behind.

5. Verify. age(relfrozenxid) should fall to near zero for that relation, and age(datfrozenxid) for the database should follow once the minimum across all its relations moves.

What you must never do

Never use pg_resetwal to address a wraparound warning. It exists to make an unstartable cluster start, at the cost of discarding transaction state. Using it on a cluster that is merely approaching the limit converts a preventable, recoverable situation into permanent, silent data loss.

If a cluster has already stopped accepting transactions, read the error before reaching for anything. These are the strings PostgreSQL 18.6 carries in its own binary:

database is not accepting commands that assign new transaction IDs
to avoid wraparound data loss in database "appdb"
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.

Note what it does not say. Older PostgreSQL releases instructed the operator to stop the postmaster and vacuum in single-user mode, and a great many runbooks and blog posts still carry that instruction. PostgreSQL 18 does not: the hint names a database-wide VACUUM, and it names the two things most likely to be holding the horizon while you run it.

Single-user mode still exists and still works:

# Substitute the affected database name for "appdb".
postgres --single -D "$PGDATA" appdb
# then, at the backend> prompt:
#   VACUUM FREEZE;

Treat it as the fallback for a cluster that genuinely will not accept a connection, not as the first step. Following an old runbook here sends you into an outage-mode procedure when an ordinary VACUUM would have done — and it costs you the ability to run the horizon-holder query, which is usually what you actually need.

Multixact wraparound: the same problem, a different counter

There is a second wraparound with its own space, its own age, its own threshold, and considerably less documentation attached to it in the places people look.

When several transactions hold row-level locks on the same row simultaneously — SELECT … FOR SHARE, or foreign key checks, or SELECT … FOR UPDATE from multiple sessions — a single xmax cannot name them all. PostgreSQL allocates a multixact id instead: an entry in pg_multixact listing the transactions and their lock modes.

Multixact ids are also 32 bits, also wrap, and are also governed by their own settings:

autovacuum_multixact_freeze_max_age, default 400,000,000
vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age
age(relminmxid) per relation, mxid_age() as the function

The reason this matters operationally: a workload can burn multixacts far faster than transaction ids. A schema with many foreign keys and concurrent updates to referenced rows generates a multixact for each contended row, and a cluster can hit multixact wraparound while its transaction age looks entirely comfortable.

The symptoms are identical — aggressive vacuums, then warnings, then refusal — but a monitoring dashboard that only tracks age(datfrozenxid) sees none of it coming.

Track both:

SELECT datname,
       age(datfrozenxid)   AS xid_age,
       mxid_age(datminmxid) AS mxid_age
  FROM pg_database
 ORDER BY greatest(age(datfrozenxid), mxid_age(datminmxid)) DESC;

pg_multixact also consumes disk in $PGDATA/pg_multixact, and a cluster generating them heavily can find that directory surprisingly large.

What to take from this

Transaction ids are finite and wrap; freezing makes old rows permanently visible.
The margin from forced vacuuming to shutdown is roughly 1.8 billion transactions — weeks, not hours.
automatic aggressive vacuum to prevent wraparound of table is the log string to alert on.
Freezing costs roughly the table’s size in WAL, in a burst.
Anti-wraparound vacuum ignores the per-table disable and does not yield. Do not kill it without a plan to replace it.
pg_resetwal is never the answer to a warning. Single-user mode is the answer to an actual shutdown.
Track multixact age alongside transaction age.

Cross-course references

Observability for Production Sysadmins — Part XIII (Rates and counters) covers projecting a deadline from an age series, and Part XVIII (Alerting rules) covers alerting on it early enough that the fix is a maintenance window rather than an outage.
Linux for Production Sysadmins — Part LXXXI (Incident command) covers running the response to a cluster that has stopped accepting writes.