Transaction ID Wraparound: Why Freezing Is a Deadline

The transaction id counter wrapping around is not the failure. It wraps every 4 billion transactions and nothing happens. The failure is a row version whose insertion id is more than 2 billion transactions old, because Postgres compares transaction ids modulo 2³¹ and such a row would suddenly look like it came from the future. VACUUM prevents that by marking old rows frozen, and the counter is a deadline: every table in every database must be frozen at least once every 2 billion transactions, whether anyone writes to it or not.

A transaction id — XID — is the 32-bit number Postgres assigns a transaction the first time it writes. Every row version records the XID that created it in a header field called xmin. To decide whether a row version is visible, a transaction compares its own snapshot against that xmin, and the comparison is modular: for any XID, roughly 2 billion others count as older and 2 billion as newer. That is what makes the counter circular and safe to reuse — and what puts a hard expiry date on every unfrozen row.

Freezing is the escape. A frozen row version is treated as if its xmin were FrozenTransactionId, a reserved value that does not follow the normal comparison rules and is always older than every ordinary XID. From PostgreSQL 9.4 onward, freezing sets a flag bit and leaves the original xmin in place for forensics. Once frozen, a row is permanently in the past, and the table's pg_class.relfrozenxid — the oldest XID that might still be unfrozen anywhere in that table — can move forward.

So the number to watch is not the counter. It is age(relfrozenxid): how many transactions have been assigned since the oldest unfrozen row in a table was written. Postgres forces an anti-wraparound vacuum on any table whose age exceeds autovacuum_freeze_max_age, 200 million by default, and it does that even on a table with no dead rows and even when autovacuum is turned off for that table. If that pass never finishes, or finishes without being able to advance relfrozenxid, the age keeps climbing, and 3 million transactions short of the wraparound point the cluster stops assigning new XIDs entirely.

The panel runs 120 days of a cluster with three tables: a 400 GB append-mostly archive, a 20 GB table under constant update, and a 50 MB settings table nobody ever writes. It models the freezing deadline and the arithmetic that decides whether a pass can beat it. The slider that carries the lesson is autovacuum_vacuum_cost_delay: it does not change what has to be frozen, only how fast the pass is allowed to go. Push it to 20 ms with the archive at 2 TB and watch a maintenance setting turn into a write outage.

At the defaults nothing dramatic happens: the archive's first pass reads all 52 million of its pages in 3.2 hours, every later pass reads only the 1.4 GB written since and finishes in about a minute, the age sawtooths between 50 and 200 million, and the counter itself wraps completely several times during the run without anyone noticing. That is the point — wrapping is normal. Now drag archive table size to 2,000 GB and autovacuum_vacuum_cost_delay to 20 ms. The pass that used to take minutes now needs days, the age never comes back down, and the decision log names the exact hour the cluster stops accepting writes.

Why a table nobody writes to gets vacuumed

Three settings decide when freezing happens, and they are not interchangeable. Reading them in the order they fire is the only way they make sense.

• vacuum_freeze_min_age, default 50 million — how old a row version's XID must be before a vacuum bothers to freeze it. This is the floor an ordinary pass leaves behind: freeze anything older than this, and relfrozenxid ends up about 50 million behind the counter.
• vacuum_freeze_table_age, default 150 million — when a vacuum that was going to run anyway is upgraded to an aggressive scan. An ordinary vacuum only visits pages that might hold dead rows; an aggressive one also visits every page that is all-visible but not all-frozen, which is the only way relfrozenxid can move.
• autovacuum_freeze_max_age, default 200 million — when Postgres stops waiting for a reason and forces a pass on the table regardless. The documentation is explicit that this happens "even if autovacuum is disabled".

That last line is the one that surprises people, and the panel makes it concrete twice over. The app_settings table is 50 MB, has no updates and therefore no dead rows, and no ordinary vacuum ever has a reason to run on it — yet it collects a forced pass every 0.87 days, because the deadline belongs to the row versions, not to the workload. A table written once in 2019 and never touched since is exactly the table whose rows are oldest. Then tick autovacuum_enabled = false on orders: the hot table loses the ordinary vacuums that were being upgraded to aggressive scans, so it now waits for the forced pass instead, and the anti-wraparound passes readout jumps from 272 to 408. Disabling autovacuum did not stop the freezing. It only removed the cheaper path to it — and, in a real cluster, left the dead rows behind as well.

The distinction between the second and third setting explains the shape you see in the archive lane. The archive is append-mostly, so ordinary vacuums rarely have a reason to run, and its passes are all forced ones at autovacuum_freeze_max_age. The orders table is vacuumed constantly for dead rows, so its first vacuum after age 150 million is silently upgraded to aggressive and it never reaches the forced threshold at all. Same deadline, two completely different paths to meeting it — and only one of them shows up in your logs as (to prevent wraparound).

Freezing is not a full rewrite of the table. A page whose rows are all frozen gets its all-frozen bit set in the visibility map, and the next aggressive pass skips it entirely. That is why the archive's first pass is expensive and every later one is cheap: it only scans the pages written since last time. The same map is what makes index-only scans possible, which is why one structure serves both.

The race, and the thing that decides it

An anti-wraparound pass has to read every not-all-frozen page of the table. The counter does not stop while it reads. So the question is arithmetic: can the pass finish before the age climbs another 1.9 billion?

Vacuum's speed is not set by your disk. It is set by vacuum_cost_limit and the cost delay: vacuum accumulates cost units — 1 for a page already in shared buffers, 2 for one it must read, 20 for one it dirties — and every time it has spent vacuum_cost_limit units it sleeps for autovacuum_vacuum_cost_delay. At the defaults, 200 units per 2 ms with 22 units for each page it reads and freezes, that is about 4,500 pages a second, or 35 MB/s. A 2 TB table with nothing yet frozen is 262 million pages: 16 hours. Set the delay to 20 ms and the same pass takes 6.7 days.

Set the simulation to a 2 TB archive, 20 ms of cost delay and 10,000 XIDs per second. The age never returns to its floor. Each pass is still running when the next deadline arrives, the sawtooth turns into a ramp, and around 1.6 billion the log reports something new: the failsafe. When age(relfrozenxid) reaches vacuum_failsafe_age, default 1.6 billion, vacuum abandons its manners. The cost delay is set to zero and index vacuuming is skipped entirely, so the pass runs at whatever the disk can do. In the model that a jump from 3.6 MB/s to 195 MB/s, and it is enough: the peak age tops out at 1.69 billion and writes are never refused. Untick vacuum_failsafe_age active and the same run ends with the cluster refusing writes for 4.5 days — which is what these clusters looked like before PostgreSQL 14 added the mechanism.

Two conclusions from the same panel. First, the setting that causes wraparound outages is almost never autovacuum_freeze_max_age — raising it just moves the sawtooth up. It is the cost delay, the number of autovacuum workers, and the size of the largest table: throughput problems, addressed in autovacuum tuning. Second, killing an anti-wraparound vacuum because it is "hammering the disk" restarts the pass from the beginning of the table. Two or three rounds of that is how a healthy cluster gets into a state nobody understands.

The pass that cannot help you

Now the case where speed is irrelevant. Drag horizon held open by a replication slot to 40 days, with everything else at its default. The passes still run, still finish quickly, and relfrozenxid does not move. The age climbs in a straight line to the refusal threshold and the cluster stops accepting writes on a 400 GB database that has plenty of I/O to spare.

Freezing is subject to exactly the same visibility rule as removing dead rows, described in MVCC and vacuum. A vacuum may not declare a row version permanently visible if any transaction might still need to see the state before it. So relfrozenxid cannot be advanced past the oldest xmin the cluster is obliged to retain, and that horizon is held by any of:

• a transaction that has written and not committed, at any isolation level, or an open REPEATABLE READ or SERIALIZABLE snapshot — see isolation levels for why an idle READ COMMITTED reader is harmless and the other two are not;
• a replication slot's xmin or catalog_xmin, which applies across the whole cluster and survives restarts;
• a standby running with hot_standby_feedback = on;
• a prepared transaction in pg_prepared_xacts that no coordinator ever resolved.

This is why bloat and wraparound are the same incident at two different stages. The dead rows pile up first because vacuum cannot remove them; the freezing deadline arrives later for exactly the same reason. If you are already looking at a table whose dead tuples will not go away, you are on the clock, and the amount of time you have is (2^31 − age) ÷ your XID rate. At 2,000 XIDs a second that is 11 days from the first forced pass to the outage.

What actually happens at the deadline

Forty million transactions out, the server starts warning on every connection:

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

Three million out, it stops assigning XIDs. On PostgreSQL 17 and later the message is:

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

On 16 and earlier the same condition reads database is not accepting commands to avoid wraparound data loss, with a hint telling you to stop the postmaster and vacuum in single-user mode. Do not follow that hint. The current documentation states plainly that stopping the postmaster "is no longer necessary, and should be avoided whenever possible", and that single-user mode is riskier because it disables the very safeguards that just protected you. The only reason to use it is to DROP or TRUNCATE tables you do not want to spend time vacuuming — which is precisely what the three-million margin was reserved for.

What the refusal actually does is narrower than "the database is down". Transactions already running continue. New read-only transactions start normally. VACUUM runs. What fails is anything that needs an XID: every INSERT, UPDATE, DELETE and TRUNCATE. And because nothing can take a new XID, the counter stops advancing — the deadline stops moving while you fix it. The simulation models this, which is why the age line goes flat at the threshold instead of continuing off the top.

The recovery, in the order the documentation gives it:

1. Resolve old prepared transactions — SELECT gid, age(transaction) FROM pg_prepared_xacts ORDER BY 2 DESC;
2. End long-running transactions — pg_stat_activity where age(backend_xid) or age(backend_xmin) is large, then pg_terminate_backend(pid).
3. Drop stale replication slots. If the replica still exists and reconnects later, it may need rebuilding; that is the trade you are making.
4. Run a plain database-wide VACUUM, or target the tables with the oldest relfrozenxid first.

Two things not to reach for. VACUUM FULL needs an XID of its own, so in this state it fails outright — and as a superuser it succeeds by consuming one, making the problem marginally worse while holding an ACCESS EXCLUSIVE lock; see the alternatives to VACUUM FULL for what it costs in ordinary times. VACUUM FREEZE is also the wrong tool here: it freezes everything rather than the minimum needed to lift the refusal, so it takes longer to give you back your cluster.

Multixact ids have a parallel deadline that is easy to miss. When more than one transaction locks the same row, the lock information is stored as a multixact id, also a 32-bit counter, tracked per table in pg_class.relminmxid and forced at autovacuum_multixact_freeze_max_age, 400 million by default. The warning and refusal thresholds are the same 40 million and 3 million. One difference matters in an incident: running out of XIDs blocks every write, while running out of multixacts blocks only writes that need a row lock shared by several transactions. Heavy SELECT … FOR SHARE or foreign-key-checking workloads reach that ceiling first, and mxid_age(relminmxid) is the column that shows it.

Checking it on a real system

One query tells you how much runway the whole cluster has. Run it on every database, and put an alert on its output:

SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age,
       mxid_age(c.relminmxid) AS mxid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
       round(100 * greatest(age(c.relfrozenxid), age(t.relfrozenxid))
             / 2147483648.0, 1) AS pct_to_wraparound
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;

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

The LEFT JOIN onto reltoastrelid is not decoration. A table's out-of-line TOAST storage has its own relfrozenxid and can be the oldest thing in the cluster while the table itself looks fine; a query that ignores it will tell you everything is healthy right up to the outage. Alert when xid_age exceeds roughly 500 million — comfortably above the 200 million sawtooth, far below the 2.1 billion cliff — and treat anything over 1 billion as an incident rather than a ticket.

When a forced pass is running, watch it rather than guessing:

SELECT p.pid, p.phase, a.query,
       p.heap_blks_total, p.heap_blks_scanned,
       round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS pct,
       now() - a.xact_start AS running_for
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid);

Two numbers decide what to do. pct against running_for gives you the finish time; compare it with (2147483648 − xid_age) ÷ XIDs per second, your remaining runway. If the pass will not finish in time, raise vacuum_cost_limit or drop autovacuum_vacuum_cost_delay to 0 for that table — both can be changed on a running server with ALTER TABLE … SET (…) and a reload, though the currently running worker keeps its old settings until it restarts. Do not kill the worker unless you intend to restart it with better settings immediately, because the next pass starts from the top of the table.

And check who is holding the horizon before you conclude the pass is slow, because a stuck horizon looks identical from the outside:

SELECT pid, state, age(backend_xid) AS xid_age, age(backend_xmin) AS xmin_age,
       now() - xact_start AS open_for, left(query, 60)
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 NULLS LAST;

SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_age
FROM pg_replication_slots ORDER BY 3 DESC NULLS LAST;

Finally, turn the passes into evidence. Set log_autovacuum_min_duration to something like 1s. Every completed pass then logs how far relfrozenxid advanced and how many pages it newly froze. A pass that reports no advancement is the pinned-horizon case, and it is the single most valuable line in the log — it tells you that adding I/O will not help and that you are looking for a session, a slot or a prepared transaction instead.

The mechanism this one depends on is the visibility rule that decides what vacuum may touch at all — MVCC, dead tuples and what VACUUM does not do — and the throughput arithmetic that decides whether a pass finishes in time is autovacuum tuning.