The Transaction ID Horizon and What Happens at Wraparound

A transaction identifier is a 32-bit counter. Every transaction that writes takes the next value, and at a sustained rate the counter is exhausted in a time that is not geological: two to the thirty-second is about four billion, so a system committing a thousand write transactions per second reaches the limit in roughly fifty days of continuous operation. An engine that stored transaction identifiers on every row and compared them numerically would therefore become incorrect after about seven weeks.

PostgreSQL’s answer is freezing: old tuples are rewritten so that their creator is a marker visible to every snapshot, which removes them from the comparison. The cost of that answer is a background process that must eventually visit every page in the database, and a failure mode that is worse than any other operational problem in the system: the engine stops accepting writes.

Why comparison is circular

Visibility testing compares transaction identifiers. A tuple is visible if its creator is committed and its deleter is not, both judged against a snapshot’s recorded set of in-progress identifiers. With 32-bit values the comparison must be modular: xid1 is older than xid2 if their difference, taken as a signed 32-bit integer, is negative. That convention works as long as no two live identifiers are more than two billion apart.

The wraparound point is where the modular comparison becomes ambiguous. If the counter passes the maximum and starts again at a small value, a tuple written near the maximum has an identifier that now compares as newer than a freshly issued one, and the engine would treat a row from the past as belonging to the future. Every visibility decision in the database becomes wrong at once.

The engine prevents this by refusing to hand out identifiers that would make the ambiguity reachable. It computes how far the oldest unfrozen identifier is from the wraparound point, and as that distance shrinks it escalates: first a warning in the log, then a refusal to assign new identifiers to read-write transactions. Reads continue, and writes fail with an error that names the database and tells the operator to vacuum it.

That refusal is the correct behaviour, since it preserves the data, and it is also a complete outage for any application that writes. Because the refusal stops the allocation of new identifiers, the margin stops shrinking rather than closing; nothing recovers by waiting, and the repair is an aggressive vacuum of the databases holding the oldest unfrozen identifiers, run until their age falls back inside the safe range. On a large cluster that pass reads every page of every such database, which is why the warning that precedes the refusal is the deadline that matters.

Freezing, precisely

Freezing replaces a tuple’s creator identifier with a special value that is older than every real identifier, so no comparison can ever place it in the future. In current versions the marker is carried in the tuple header’s infomask bits rather than by overwriting the identifier field, which allows the original identifier to be preserved for forensic purposes while the visibility logic treats the tuple as frozen.

Vacuum performs the freezing opportunistically. Any page it visits whose tuples are older than a threshold set by the vacuum_freeze_min_age parameter, which defaults to fifty million transactions, has its eligible tuples frozen. Setting the threshold lower means more pages get frozen sooner and more write amplification during vacuum; setting it higher means fewer writes but a larger backlog of unfrozen tuples to clear in an emergency.

The threshold that triggers an actual vacuum pass for freezing purposes is different: vacuum_freeze_table_age defaults to a hundred and fifty million, and a table whose oldest unfrozen identifier is older than that gets a full sweep of all its pages rather than only those the visibility map marks as dirty. That is the mechanism that makes a neglected table expensive to fix, because the pass cannot skip pages, so it reads the whole table regardless of how clean it is.

-- age in transactions of the oldest unfrozen tuple, per table
SELECT relname,
       age(relfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age
  FROM pg_class
 WHERE relkind = 'r'
 ORDER BY xid_age DESC
 LIMIT 10;

The age function returns the distance from the oldest unfrozen identifier to the current one, which is the number that matters. A table at a hundred million is routine. A table at a billion is approaching the point where an aggressive vacuum becomes mandatory, and a database near two billion is within hours of refusing writes at high transaction rates.

The autovacuum override

The freeze trigger overrides the dead-tuple thresholds. A table with no churn at all, such as an append-only archive that is never updated, accumulates no dead tuples and would never be vacuumed by the ordinary trigger, yet it consumes transaction identifiers with every insert and must be frozen. Autovacuum therefore schedules a freeze-only pass when a table’s age exceeds autovacuum_freeze_max_age, whose default is two hundred million transactions, and that pass runs even if autovacuum is otherwise disabled for the table.

This override is why a database with many large, static tables needs its autovacuum workers to be available and not perpetually busy elsewhere. Each such table gets a full sweep every two hundred million transactions, and the sweeps cannot be skipped or deferred indefinitely.

The failure mode when they are deferred is compounding. A sweep over a table that has not been frozen in a long time must rewrite a large fraction of its pages, generating a volume of log proportional to the table’s size. Several such sweeps running close together saturate I/O, slow the sweeps further, and push the horizon closer to the limit. The system enters a state where the repair work itself prevents the repair from completing in time, which is why the operational rule is to keep the oldest age bounded well below the limit rather than to react when the warning appears.

MultiXact identifiers are a second counter

Row-level locks are recorded in tuple headers as a reference to a multixact, a set of transaction identifiers, rather than as a single identifier. The multixact identifier space is also 32-bit and also wraps, with its own freezing mechanism and its own thresholds, and it is exhausted by SELECT ... FOR SHARE and FOR KEY SHARE traffic rather than by ordinary writes. A system with heavy foreign-key checking, which takes a key-share lock on the referenced row for every insert into the referencing table, consumes multixact identifiers quickly.

The failure mode is parallel to the transaction identifier case: the engine refuses to create new multixacts, which manifests as failures on statements that take row locks, including ordinary foreign-key inserts, while plain reads and writes continue. The remedy is the same vacuum mechanism, applied to a counter that monitoring usually does not watch.

Monitoring the number that matters

Two quantities describe the risk. The first is the age of the oldest unfrozen transaction identifier across the whole cluster, which is the distance to the refusal point. The second is the rate at which identifiers are being consumed, which converts that distance into time.

The rate is not the same as the commit rate. Identifiers are assigned to any transaction that writes, a transaction that writes to several databases consumes one identifier in the shared cluster-wide space, and each subtransaction consumes one of its own, so a workload that saves points frequently advances the counter faster than its statement rate suggests. A workload dominated by long analytical reads consumes almost none.

The warning signs in the log are ordered and unmistakable. As the distance closes the engine emits warnings naming the database that needs vacuuming and the number of transactions remaining before it will refuse writes, repeating them at decreasing intervals until the refusal happens. The exact thresholds have changed between major versions, so the number in the message is the authority rather than any figure quoted elsewhere. Treating the first warning as the deadline rather than the last one is the entire discipline: a full-database vacuum started with tens of millions of transactions of margin can complete, and the same operation started with a few million may not.

Why the horizon interacts with everything else

The oldest snapshot any live session holds determines how much garbage vacuum can remove, and the oldest unfrozen identifier determines how much work a freeze pass must do. Both are properties of the cluster’s transaction activity, not of any one table.

A replication slot that no consumer is advancing is the classic way to hold the horizon back: a logical slot retains write-ahead log, and a physical slot retains segments, so the primary cannot recycle them and, for logical slots, cannot consider old catalog versions dead. An abandoned slot can therefore pin both storage and the vacuum horizon simultaneously. The mechanism is described where it belongs, in replication slot retention and WAL pinning.

The horizon also explains why long-running transactions are an operational hazard independent of the locks they hold. A session that has been open for an hour holds back reclamation for an hour, and the cost is paid by every table it has touched, in the form of pages that cannot be cleaned and freezing work that accumulates. The lock consequence of the same session is a different failure with a different remedy, covered in metadata locks and the lock queue.