· Development

PostgreSQL transaction ID wraparound: why VACUUM freezes rows and how to read age(datfrozenxid)

What happens when PostgreSQL's 32-bit transaction ID wraps: freezing, relfrozenxid/datfrozenxid, anti-wraparound autovacuum, warnings, write refusal and recovery, per the PostgreSQL 18 docs.

DevelopmentDatabasesPostgreSQL

Say your monitoring dashboard has a number called age(datfrozenxid), and it creeps up a little every day without anyone touching anything. This post covers what that number counts, what happens if it keeps growing, and what PostgreSQL does on its own before that point. It is based on section 24.1 Routine Vacuuming of the PostgreSQL 18 documentation, mainly 24.1.5 Preventing Transaction ID Wraparound Failures and 24.1.6 The Autovacuum Daemon. When checked on 19 October 2026, that page listed 18 as the current version and 19 as a development version. Other versions are out of scope, and the only setting values quoted here are the ones the page itself gives.

A 32-bit counter compared on a circle

PostgreSQL’s MVCC decides whether a row version is visible by comparing transaction IDs (XIDs). A row version whose inserting XID is greater than the current transaction’s XID is “in the future” and must not be visible. Which row versions each statement sees is a separate topic, covered in READ COMMITTED and snapshots.

The catch is that XIDs are 32 bits. According to the documentation, a cluster that runs long enough to process more than 4 billion transactions sees its XID counter wrap around to zero, and transactions that were in the past suddenly look like they are in the future. Their rows become invisible. The documentation calls this catastrophic data loss, adding that the data is actually still there, which is cold comfort if you cannot get at it.

Normal XIDs are compared using modulo-2³² arithmetic. So for any normal XID there are two billion XIDs that count as “older” and two billion that count as “newer”. In the documentation’s words, the normal XID space is a circle with no endpoint.

              current transaction's XID
                         │
   ◀── ~2 billion: past ─┼─ ~2 billion: future ──▶
   (it is a circle, so the two ends meet)

Once a row version is created with some XID, it looks like it is in the past for the next two billion transactions. If the row is still there after more than two billion transactions, it flips into the future. A fixed-width integer wrapping when it overflows is the same idea as in the integer overflow post, except that here what flips is not an arithmetic result but whether a row can be seen.

Freezing: an XID that is always in the past

The fix is freezing, done by VACUUM. A frozen row is marked as having been inserted by a transaction that committed far enough back that it is visible to every current and future transaction.

For this PostgreSQL reserves a special XID, FrozenTransactionId. It does not follow the normal comparison rules and is always treated as older than every normal XID. A frozen row version is handled as if its inserting XID were FrozenTransactionId, so it stays in the past regardless of wraparound until it is deleted.

The documentation also records an implementation detail. Before 9.4, freezing actually replaced the row’s inserting XID with FrozenTransactionId (value 2), which showed up in the xmin system column. Later versions just set a flag bit and keep the original xmin for possible forensic use. Databases that went through pg_upgrade from a pre-9.4 version may therefore still contain rows with xmin equal to 2. System catalogs can also contain rows with xmin = 1 (BootstrapTransactionId), inserted during the first phase of initdb, and that XID is likewise treated as older than every normal XID.

So every table needs VACUUM to freeze its old rows before two billion transactions go by. The documentation says every table in every database must be vacuumed at least once every two billion transactions.

What relfrozenxid and datfrozenxid count

To track the oldest XID that has not been frozen yet, VACUUM stores values in two system tables.

  • pg_class.relfrozenxid: the oldest unfrozen XID left in the table at the end of the most recent VACUUM that actually advanced this value (typically the most recent aggressive VACUUM).
  • pg_database.datfrozenxid: a lower bound on the unfrozen XIDs in that database. It is the minimum of the relfrozenxid values of the tables in it.

age() returns how many transactions have passed from that cutoff XID to the current transaction’s XID. That is the dashboard number from the start. The documentation gives these two queries:

SELECT c.oid::regclass as table_name,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');

SELECT datname, age(datfrozenxid) FROM pg_database;

The first one reports, per table, the larger of its own age and that of the TOAST table linked through reltoastrelid. The second works per database. When a database’s number is high, the first query tells you which tables are driving it.

When VACUUM advances relfrozenxid

Running VACUUM often does not necessarily bring this age down. VACUUM uses the visibility map to pick which pages to scan, and normally skips pages with no dead row versions, even if those pages still hold old XIDs. So a normal VACUUM is not guaranteed to freeze every old row in the table, and eventually an aggressive VACUUM is needed. An aggressive VACUUM freezes all eligible unfrozen XID and MXID values, including those on pages that are all-visible but not all-frozen.

The PostgreSQL 18 documentation also describes a middle step. If a table is building up a backlog of all-visible but not all-frozen pages, a normal VACUUM may deliberately scan some of those skippable pages to try to freeze them. These are called eagerly scanned pages, and raising vacuum_max_eager_freeze_failure_rate makes VACUUM try harder. Pages frozen this way can be skipped by the next aggressive VACUUM, but the documentation notes that most tables still need periodic aggressive vacuuming.

relfrozenxid only advances when every page that might contain unfrozen XIDs has been scanned. The documentation lists three ways that happens:

  • relfrozenxid is older than vacuum_freeze_table_age
  • VACUUM is run with the FREEZE option
  • every page that is not yet all-frozen happens to need vacuuming anyway to remove dead row versions

After such a full scan, age(relfrozenxid) should be a little more than the vacuum_freeze_min_age that was used, the difference being the number of transactions started since the VACUUM began. VACUUM records the oldest XID left in the table as relfrozenxid, so the result may be much more recent than strictly required.

The two settings involved do the following:

  • vacuum_freeze_min_age: how old an XID has to be before rows carrying it get frozen. Raising it can avoid wasted work on rows that will soon be modified again; lowering it increases how many transactions can pass before the table must be vacuumed again.
  • vacuum_freeze_table_age: when to vacuum aggressively. Setting it to 0 makes VACUUM always use the aggressive strategy. Its effective maximum is 0.95 × autovacuum_freeze_max_age; anything higher is capped.

Anti-wraparound autovacuum

The documentation says the longest a table can go unvacuumed is two billion transactions minus the vacuum_freeze_min_age in effect at the last aggressive VACUUM. To stay inside that, PostgreSQL launches autovacuum on any table that might contain unfrozen XIDs older than autovacuum_freeze_max_age. Section 24.1.6 puts it as: tables whose relfrozenxid is more than autovacuum_freeze_max_age transactions old are always vacuumed. The documentation states explicitly that this happens even if autovacuum is disabled.

This autovacuum differs from a regular one in two ways.

  1. A regular autovacuum is interrupted when another process asks for a lock that conflicts with its SHARE UPDATE EXCLUSIVE lock. An autovacuum running to prevent wraparound is not interrupted automatically.
  2. In pg_stat_activity, its query name ends with (to prevent wraparound).
SELECT pid, datname, query
FROM pg_stat_activity
WHERE query LIKE '%(to prevent wraparound)';

The query just turns that sentence from the documentation into a filter. A job with this label was started because of the table’s age, not because of its update volume. The documentation explains that even static tables, including ones that only receive inserts with no updates or deletes and so never need vacuuming to reclaim space, get this autovacuum roughly once every autovacuum_freeze_max_age minus vacuum_freeze_min_age transactions.

The documentation adds a warning as well: regularly running commands that take locks conflicting with SHARE UPDATE EXCLUSIVE (for example ANALYZE) can stop autovacuums from ever completing.

The cost of raising autovacuum_freeze_max_age

The one disadvantage the documentation names is storage. Raising autovacuum_freeze_max_age (and vacuum_freeze_table_age with it) means keeping commit status for every transaction back to that horizon in pg_xact, and, if track_commit_timestamp is on, timestamps in pg_commit_ts.

autovacuum_freeze_max_age pg_xact pg_commit_ts
200 million (default) about 50MB about 2GB
2 billion (maximum allowed) about 0.5GB about 20GB

If that is trivial next to your total database size, the documentation recommends the maximum; otherwise, set it according to how much pg_xact and pg_commit_ts storage you are willing to allow. Going the other way, setting vacuum_freeze_min_age too low makes VACUUM freeze rows that will soon be modified again, which is wasted work. The documentation gives no number that fits every system for either setting.

The warning, then the refusal to write

If autovacuum fails to clear old XIDs for some reason, warnings start once the database’s oldest XIDs are forty million transactions from the wraparound point:

WARNING:  database "mydb" must be vacuumed within 39985967 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.

At this stage a manual VACUUM, as the hint suggests, fixes the problem. The documentation notes that it should be run by a superuser; otherwise it cannot process the system catalogs and so cannot advance the database’s datfrozenxid.

If the warnings are ignored and fewer than three million transactions remain, the system refuses to assign new XIDs:

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "mydb"
HINT:  Execute a database-wide VACUUM in that database.

Transactions already in progress can continue, but only read-only transactions can be started. Operations that modify records or truncate relations fail. The VACUUM command still runs normally.

Recovering from the write-refusal state

The PostgreSQL 18 documentation says that, contrary to advice in earlier releases, there is no need to stop the postmaster or enter single-user mode, and that doing so is not desirable. Instead it gives these steps:

  1. Resolve old prepared transactions. Look in pg_prepared_xacts for rows where age(transactionid) is large, and commit or roll them back.
  2. End long-running open transactions. Look in pg_stat_activity for rows where age(backend_xid) or age(backend_xmin) is large, and commit or roll them back, or terminate the session with pg_terminate_backend.
  3. Drop old replication slots. Use pg_replication_slots to find slots where age(xmin) or age(catalog_xmin) is large. The documentation notes that such slots were often created for servers that no longer exist or have been down for a long time, and warns that dropping a slot for a server that still exists and may try to reconnect can mean rebuilding that replica.
  4. Run a database-wide VACUUM in the target database. Do not use VACUUM FULL, which requires an XID, and do not use VACUUM FREEZE, which does more than the minimum work needed to get back to normal.

The last step is to make sure autovacuum is properly configured in that database once things are back to normal. The documentation does not explain why steps 1 to 3 come before the VACUUM. But the multixact section says that “unlike transaction ID wraparound, replication slots do not directly hold back multixact cleanup”, which suggests that on the XID side, replication slots at least are treated as something that can hold cleanup back.

According to the documentation, single-user mode means taking the system down and is riskier, because it disables the wraparound safeguards that are there to prevent data loss. The only reason to use it here is to TRUNCATE or DROP tables you no longer need so they do not have to be vacuumed, and the three-million-transaction margin exists to let the administrator do that.

Multixact IDs work the same way

When several transactions lock the same row at once, that information is encoded as a multixact ID (MXID). MXIDs are also a 32-bit counter and need the same kind of management. Per table, pg_class.relminmxid holds the oldest MXID, and mxid_age() gives its age. The thresholds match the XID case: warnings at forty million from the wraparound point, and a refusal to generate new MXIDs below three million.

There are differences. XID exhaustion blocks all write transactions, while MXID exhaustion blocks only writes that involve row locks requiring an MXID. Recovery follows almost the same steps, with a few dropped. Running and prepared transactions can be ignored if there is no chance they appear in a multixact, and since replication slots do not directly hold back multixact cleanup, dropping slots is usually not relevant. MXID information is not directly visible in views such as pg_stat_activity, but the documentation says looking for old XIDs is still a good way to find the transactions causing the problem.

What to watch day to day

Keeping to what the documentation itself supports, that comes down to roughly this:

  • Track age() per database and per table with the two queries above. The numbers worth comparing against are the documented ones: autovacuum_freeze_max_age (forced autovacuum starts), forty million from wraparound (warnings), three million (writes refused).
  • Watch pg_stat_activity for (to prevent wraparound) autovacuums and note when and on which tables they run.
  • VACUUM (VERBOSE), and autovacuum log lines controlled by log_autovacuum_min_duration, report how far relfrozenxid and relminmxid advanced and how many pages were newly frozen.
  • The three things the recovery steps clear first, the age() values in pg_prepared_xacts, pg_stat_activity and pg_replication_slots, can be queried the same way before anything goes wrong.

The 200 million default for autovacuum_freeze_max_age is the figure the documentation uses when it explains pg_xact size. Whether to raise it depends on the storage involved and how often you are comfortable with anti-wraparound autovacuum running, and that call is yours.

Documentation used

Links and content checked on 19 October 2026.

Back to the blog