Transaction ID Wraparound

Transaction ids are 32 bits, and Postgres compares them in a circle rather than on a line. That works because only half the circle is ever meaningful at once: from any point, roughly two billion ids look like the past and the other two billion look like the future. An id that falls off the back of that window does not become old — it becomes future, and every row it created stops being visible to anyone.

Vacuum prevents that by freezing: marking sufficiently old rows as visible to everyone, forever, so their creating id no longer needs to be compared with anything. Everything else in this chapter has been a budget problem, where falling behind costs performance you can recover at leisure. This is a deadline. Nothing degrades, nothing gets slower, and then the cluster stops accepting writes.

The Circle

Ids are compared with modulo-2³² arithmetic, so at any moment about 2.1 billion values look like the past and 2.1 billion look like the future. A row carrying an xmin older than that window would be read as created by a transaction that has not happened yet, and a query would correctly conclude it cannot see it. The row is still on disk, intact, and invisible. That is data loss with no error message, which is why the entire freeze machinery exists and why the server would rather stop than let it happen.

The consumption rate is what turns that abstraction into a date. Cartwheel assigns roughly ten million transaction ids a day, and only writing transactions take one, so the figure sits far below the query rate and puts the two-billion boundary about seven months out. The mechanism that keeps the debt down runs entirely without supervision, so teams meet it for the first time as an incident. Nothing on this cluster is anywhere near seven months of freezing debt today.

Freezing

When vacuum finds a row version old enough and visible to every running transaction, it freezes it. Since 9.4 that is a flag bit in the tuple header rather than a rewrite of xmin, so the original id is preserved for forensics and a frozen row costs nothing extra to store. A frozen row is visible to every transaction from then on, regardless of what happens to the id counter.

The result is tracked in two catalogue columns. pg_class.relfrozenxid holds the oldest unfrozen id remaining in a table, and pg_database.datfrozenxid holds the minimum across all tables in a database. The visibility map's all-frozen bit is what makes this affordable at scale: a page marked all-frozen never needs to be visited again by a freezing pass, so the work over a table's lifetime is proportional to the pages that changed rather than to the pages that exist. The distance from the current id to datfrozenxid is the freezing debt, and it is a single number per database.

The Ages That Drive It

When freezing happens is decided by three settings, and they are all measured in transactions rather than in time. vacuum_freeze_min_age, 50 million, is how old a row version must be before an ordinary vacuum bothers to freeze it. vacuum_freeze_table_age, 150 million, is the table age at which the next vacuum becomes aggressive and scans every page not already marked all-frozen instead of skipping ahead. And autovacuum_freeze_max_age, 200 million, is the age at which autovacuum starts a vacuum on that table whether or not it has a single dead tuple.

Multixact ids, which Chapter 6 introduced as the ids Postgres allocates when several transactions lock the same row, have a parallel set with their own numbers: a minimum age of 5 million, a table age of 150 million, and autovacuum_multixact_freeze_max_age at 400 million. They wrap for the same reason and are monitored the same way, and a workload heavy in shared row locks can reach the multixact limit long before it reaches the transaction one.

Version 18 added a quieter improvement worth knowing about: an ordinary vacuum now freezes some all-visible pages eagerly rather than leaving every one of them for a later aggressive pass, with vacuum_max_eager_freeze_failure_rate, 0.03 by default, deciding how much wasted effort it tolerates before it stops trying. The effect is that the enormous one-off freeze on a big cold table is smaller when it eventually arrives.

The Anti-Wraparound Vacuum

Once a table passes autovacuum_freeze_max_age, autovacuum starts on it. This is the part people are not expecting: it happens even if autovacuum has been switched off for that table, and even if autovacuum is switched off cluster-wide. There is no setting that disables it, because it is not a maintenance policy — it is the mechanism that keeps the data readable.

It also arrives at an arbitrary moment and runs for as long as the table takes. On delivery_events at 1.2 billion rows that is hours of sustained I/O on a table nobody has queried recently, which is exactly how it gets misdiagnosed. Cancelling it is the worst available response: the work already done is kept, the vacuum restarts later, and the deadline has moved closer in the meantime. The right response is capacity — know when your large tables are due, and do not schedule a migration against the same window.

This is also where the insert-triggered autovacuum from earlier in the chapter earns its keep. An append-only table with the insert path enabled is frozen continuously, in pieces small enough to disappear into the background I/O. With the insert path disabled, all of that work is being saved up for one run.

The Failsafe and the Wall

Since 14 there is a last line of defence before the deadline. When a table's age passes vacuum_failsafe_age, which is 1.6 billion transactions and has a matching multixact setting, vacuum abandons politeness: the cost delay stops applying, index vacuuming is skipped entirely, and the buffer access strategy that normally keeps vacuum from evicting the cache is disabled. The log says so plainly, with a line reading index scan bypassed by failsafe. It stops trying to be a good citizen and concentrates on advancing relfrozenxid.

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

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

The warning starts at 40 million transactions from wraparound and keeps appearing, so it is impossible to miss in a log that anyone reads. The error arrives at 3 million remaining, and at that point the server refuses every command that would assign a new transaction id. Reads still work. Writes do not, for the whole database, and the fix is a vacuum on a cluster that can no longer serve its application. The window in which this was a five-minute job closed 197 million transactions earlier.

The Number to Alert On

One query answers the whole question, and it is cheap enough to run every minute. age(datfrozenxid) per database gives the freezing debt directly; the per-table version does the same for individual relations, and it has to consider each table's TOAST relation alongside the table itself, because the TOAST side ages independently and is the half people forget.

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

SELECT c.oid::regclass AS relation,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_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 LIMIT 10;

Alert at half of autovacuum_freeze_max_age, which is 100 million on the shipped setting, and treat the alert as a page rather than a ticket. Not because 100 million is close to anything, but because the number tells you nothing about how long the fix will take, and on delivery_events the fix is a six-hour vacuum. Risk here is proportional to the size of the table that is behind, not to the age itself: a small database at 180 million is a minor chore, and a 1.2-billion-row table at 180 million is a plan.