The Clock You Can't Stop: PostgreSQL XID Wraparound

There's a clock counting down in the background of PostgreSQL, and most teams notice it for the first time when it has nearly run out.

The clock is the transaction ID — XID for short. Every write takes an XID, and the counter is 32-bit, so it wraps after four billion transactions. In the circular scheme the documentation describes, each XID has roughly two billion "older" and two billion "newer" neighbours; when the counter wraps, old transactions suddenly appear to be in the future, which means catastrophic data loss.

That's why PostgreSQL carries a safeguard: it freezes rows. VACUUM marks sufficiently old row versions as frozen; frozen rows appear to be in the past to all normal transactions no matter what the counter does, and stay valid until deleted.

This article is about that clock: how to read it, what happens at each threshold, and what not to do when the alarm goes off.

What the queries return on a trouble-free cluster

Real output from a PostgreSQL 16 instance on my own server:

$ psql -At -F'|' -c "select name, setting from pg_settings where name in
    ('server_version','autovacuum_freeze_max_age','vacuum_freeze_min_age',
     'vacuum_freeze_table_age','vacuum_failsafe_age',
     'autovacuum_multixact_freeze_max_age') order by name;"
autovacuum_freeze_max_age|200000000
autovacuum_multixact_freeze_max_age|400000000
server_version|16.13
vacuum_failsafe_age|1600000000
vacuum_freeze_min_age|50000000
vacuum_freeze_table_age|150000000

$ psql -At -F'|' -c "select datname, age(datfrozenxid) from pg_database order by 2 desc;"
postgres|4259
burcu_mutfak|4259
template1|4259
template0|4259

This output comes from a trouble-free cluster: an age of 4,259 is about two hundred-thousandths of the forced-intervention threshold at 200 million. To be honest, this table proves the cluster is young and quiet rather than proving how well freezing works — all four databases sitting at the same value is the giveaway. What matters more is knowing what these two queries say. age(datfrozenxid) gives the distance between the oldest unfrozen XID in that database and the current XID. As the number grows, the clock advances.

For a per-table view the documentation provides the query:

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')
ORDER BY 2 DESC;

A common misconception needs correcting here: datfrozenxid is, in the documentation's words, just the minimum of the per-table relfrozenxid values in that database. So the database age equals the age of its oldest table; "the database looks fine but one table is behind" can't happen. The per-table query's value is different: the database-level number tells you something has fallen behind, while only this list tells you which.

The greatest(...) in the query is no accident either: a table's TOAST table can be the one that's actually behind, so the larger of the two is taken. One more note: datfrozenxid is only recomputed when a vacuum updates it, so the number you see isn't live.

The thresholds: four separate lines

The parameter names look alike, so they get confused; in fact the four look at four different moments.

vacuum_freeze_min_age (default 50 million) is the minimum age a row version needs before it can be frozen. A lower value means more freezing work, a higher one less work but later protection.

vacuum_freeze_table_age (default 150 million) is the threshold where a normal VACUUM switches to aggressive mode — visiting every page that might contain unfrozen XIDs, not just those that might contain dead tuples. The documentation notes its effective maximum is 0.95 × autovacuum_freeze_max_age.

autovacuum_freeze_max_age (default 200 million) is where forced anti-wraparound autovacuum kicks in. It runs even if autovacuum is disabled; at that point PostgreSQL doesn't consult your preference.

vacuum_failsafe_age (default 1.6 billion) is the strategy of last resort: at that age any cost-based delay stops applying, index cleanup is skipped, and the buffer access strategy is disabled so vacuum can use all of shared buffers. If you're here, you're in recovery. There's a linked behaviour too: raise autovacuum_freeze_max_age and the failsafe is silently adjusted to at least 105% of it.

Multixacts have their own counter as well: autovacuum_multixact_freeze_max_age, 400 million in my instance. On systems making heavy use of SELECT ... FOR SHARE or foreign key locks this counter can fill before the XID one — and hardly anyone watches it.

What the server says when the alarm rings

Once you enter the warning phase, the server starts speaking on every transaction. But you need to know how late that warning is: in the source, the warning limit is set 40 million transactions before the wraparound point — that is, when age reaches 2.1 billion. The hard limit follows just 3 million transactions later. This isn't an early warning, it's the last exit sign. (In the PostgreSQL 19 development branch that window was widened to 100 million; still late.)

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

At the hard limit, work stops:

ERROR:  database is not accepting commands that assign new transaction IDs
to avoid wraparound data loss in database "mydb"

The documentation's wording is precise: transactions already in progress can continue, but only read-only transactions can be started; operations that modify records or truncate relations fail. VACUUM still runs normally — that's your way out. And don't wait on that vacuum blindly: the pg_stat_progress_vacuum view shows which phase it's in and how far it has got.

These messages also vary by version; the ones above are from the current documentation. PostgreSQL 16 words the error slightly differently, so if you're writing log matching, check your own version's text.

The logic in the source code describes the same three tiers: past xidVacLimit autovacuum starts being forced, past xidWarnLimit warnings are issued, and past xidStopLimit executing transactions is refused outside single-user mode.

Why does age grow? Usually vacuum isn't the culprit

The most common mistake here is diving into autovacuum settings when the alarm rings. Vacuum is usually ready to work; something is blocking it. The documentation lists these blockers among the recovery steps, and in practice you read the list backwards:

• Unfinished prepared transactions: a row in pg_prepared_xacts with a large age means nothing beyond that XID can be considered freezable.
• Long-running transactions: large age(backend_xid) or age(backend_xmin) values in pg_stat_activity. A forgotten BEGIN, a psql session left open.
• Stale replication slots: large age(xmin) or age(catalog_xmin) in pg_replication_slots. A replica that was torn down may have left a slot holding things back.
• Feedback from replicas: with hot_standby_feedback on, rows the standby needs are held on the primary; pg_stat_replication.backend_xmin shows how far behind that is.

Until those three are cleared, age doesn't drop however much vacuum runs. Tuning autovacuum itself is another matter; the thresholds I covered in the PostgreSQL VACUUM and bloat article apply to the bloat side, while on the wraparound side the priority is removing blockers.

Insert-only tables: the classic trap

One classic source of wraparound trouble is tables that are never updated. Event logs, time series, audit trails — tables that constantly receive rows but are almost never updated or deleted.

Classic autovacuum ignored those tables, because its trigger was the dead tuple count and there were no dead tuples. Yet the freezing need was there: every inserted row carries an XID. autovacuum_vacuum_insert_threshold, added in PostgreSQL 13, closed that gap; the default is 1000 inserted tuples, and -1 disables it entirely.

On clusters upgraded from older versions, or setups that turned this off as "unnecessary I/O", the trap is still there: inserted rows are never frozen, age grows quietly, and when the alarm rings the table that's behind is usually one of these. Remember that partitioned time series carry a relfrozenxid per partition — partitions show up as separate rows in the table list.

PostgreSQL 18 changed this picture somewhat: with vacuum_max_eager_freeze_failure_rate (default 0.03), a normal vacuum will try to proactively freeze all-visible but not-all-frozen pages. So on 18 and later the risk of append-only tables quietly falling behind is reduced; on older versions it's unchanged.

Lock conflicts: what stops vacuum

Another subtle mechanism lives in locks. Under normal conditions autovacuum doesn't block other commands: if a process requests a lock conflicting with the SHARE UPDATE EXCLUSIVE lock autovacuum holds, the autovacuum is interrupted and gets out of the way.

The subtlety: that behaviour doesn't apply to anti-wraparound vacuum. The documentation is explicit — if the autovacuum is running to prevent wraparound (its query name in pg_stat_activity ends with "(to prevent wraparound)"), it is not automatically interrupted. The docs also warn that regularly running commands which take locks conflicting with SHARE UPDATE EXCLUSIVE — ANALYZE, for instance — can effectively prevent autovacuum from doing its job.

The operational translation runs both ways. On one hand, if your scheduled maintenance keeps interrupting vacuum, table age grows quietly. On the other, once things reach the anti-wraparound stage vacuum no longer yields; an ALTER TABLE starts waiting and the team panics that "the database is locked up". Two ends of the same story.

What not to do

Two reflexes during recovery make things worse.

First, VACUUM FULL. The documentation warns plainly: it requires an XID and will therefore fail — except in super-user mode, where it instead consumes an XID and thus increases the wraparound risk. Second, VACUUM FREEZE: it does more work than needed and burns time you don't have. The right command is plain VACUUM, run as a superuser so system catalogues are processed too.

A third, older reflex: dropping into single-user mode. The documentation says this is no longer necessary in typical scenarios, should be avoided since it takes the system down, and is riskier — because that mode disables the wraparound safeguards designed to prevent data loss.

The cost on the storage side

Raising autovacuum_freeze_max_age is tempting: vacuum runs less often, I/O drops. The price is growth in pg_xact and pg_commit_ts. The documentation gives concrete sizes: at the maximum (2 billion), pg_xact is about 500 MB and pg_commit_ts about 20 GB; at the default 200 million, about 50 MB and 2 GB respectively.

So this parameter isn't a "performance setting" but a deliberate trade between disk and vacuum work. And the mandatory rule doesn't change: every table in every database must be vacuumed at least once every two billion transactions.

Monitoring: a one-line insurance policy

The nice thing about this topic is how cheap the monitoring is. One query and one threshold suffice:

SELECT max(age(datfrozenxid)) FROM pg_database;

But put the threshold in the right place. Alerting on a percentage of autovacuum_freeze_max_age is misleading: with the defaults, reaching 150 million (75% of that value) is normal — aggressive vacuum triggers exactly there. On a busy cluster, moving inside that band is a sign of healthy operation.

Three better signals: (1) is the max(age(datfrozenxid)) trend monotonically rising — i.e. does vacuum never bring the age down; (2) is there a vacuum ending in "(to prevent wraparound)" in pg_stat_activity; (3) is the age approaching vacuum_failsafe_age (1.6 billion by default). If you want absolute thresholds, put the warning around 1 billion and the critical below the failsafe.

As a checklist:

• Alert on max(age(datfrozenxid)); define the threshold as a percentage of autovacuum_freeze_max_age.
• Watch the multixact side with a query: select datname, mxid_age(datminmxid) from pg_database order by 2 desc; — separate counter, separate threshold (autovacuum_multixact_freeze_max_age).
• On a managed service (RDS, Aurora, Cloud SQL) there's no superuser and no single-user mode; you manage parameters through the provider's parameter group and put the provider's wraparound metric on your dashboard.
• Run a monthly blocker sweep: pg_prepared_xacts, long transactions, unused replication slots.
• Check autovacuum_vacuum_insert_threshold on insert-only tables; if it's disabled, age grows silently.
• Look for vacuums ending in "(to prevent wraparound)" in pg_stat_activity: that's emergency vacuum, not routine maintenance, and it makes lock requesters wait.
• Put the top five oldest tables on your dashboard; trouble usually starts in one table, not in the database average.
• Write "don't use VACUUM FULL or VACUUM FREEZE" into the recovery runbook; those are exactly what comes to mind in a crisis.
• Check the age before an upgrade: pg_upgrade preserves freeze information, but the post-upgrade analyze and the aggressive vacuum it triggers can stretch the maintenance window you planned.

Counters fill quietly

XID wraparound reminds me of the sneakiest part of running databases: some problems don't arrive as a slowdown, they arrive one day as a wall.

CPU and memory metrics warn you gradually; a counter filling up advances linearly and gives nothing away until the last moment. Then writes are refused and all you can do is wait for vacuum to finish.

So the question for your own setup: which of your counters advance linearly, and how many of them do you have alerts on? In PostgreSQL, XID is the best known — but not the only one.

Official Sources

• PostgreSQL: Routine Vacuuming — preventing transaction ID wraparound failures
• PostgreSQL: vacuum and autovacuum runtime parameters
• PostgreSQL source: XID limits and warning tiers (varsup.c)