skip to content

XID Wraparound

PostgreSQL's 32-bit transaction IDs wrap around, so vacuum must freeze old tuples before the server stops assigning XIDs. Interviewers probe wraparound as the classic Postgres outage.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL, why do 32-bit transaction IDs make freezing necessary, and what does VACUUM's freezing actually change on a row?

level: juniorimportance: must knowfreq 72%

answer

  1. a counter that runs out
  2. comparison on a circle
  3. two billion behind, two billion ahead
  4. a header flag meaning always past

basics

~20 s

PostgreSQL compares 32-bit transaction IDs modulo 2^32, so a row's inserting XID looks past for only about two billion transactions. VACUUM freezes old rows, setting a header flag that makes them visible to every future transaction.

solid answer

~40 s

Every row version records the XID that inserted it, and visibility asks whether that XID is in the past. XIDs are 32-bit and compared with modulo-2^32 arithmetic, so the space is a circle: any XID has about two billion older and two billion newer neighbours. A row left unfrozen for more than two billion transactions would suddenly look like it came from the future and vanish from queries. **Freezing** prevents that: `VACUUM` marks old row versions frozen, which since 9.4 is a flag bit in the tuple header (the original `xmin` is kept), and a frozen row counts as older than every normal XID. So every table in every database must be vacuumed at least once every two billion transactions, and autovacuum forces it at 200 million (`autovacuum_freeze_max_age`) even when autovacuum is off.

go deeper

for a junior

Recall that PostgreSQL XIDs are 32-bit and compared on a circle, that unfrozen rows would turn invisible after about two billion transactions, and that VACUUM freezing prevents it.

for a middle

Explain the modulo-2^32 comparison, why only half the space counts as past, that freezing sets a header flag while keeping xmin, and that XIDs are consumed only by writing transactions.

for a senior

Tie the two-billion limit to the 200-million autovacuum_freeze_max_age default, explain why anti-wraparound vacuums ignore autovacuum = off, and show how write-transaction rate sets the clock.

for a principal

Weigh freezing's cost, rewriting cold pages and generating WAL, against the risk of a cluster-wide write outage, and argue for spreading freezing work out instead of deferring it.

## The problem: a circular 32-bit counter PostgreSQL gives every transaction that writes a **transaction ID (XID)** from one counter shared by all databases in the cluster. A transaction receives its XID when it first writes; read-only transactions only get a virtual ID, so the counter advances with write transactions, not with queries. Each row version (a **tuple**) records the XID that inserted it in its `xmin` header field, and the visibility rules ask a simple question: did that XID commit before my snapshot, or is it in my future? XIDs are **32 bits wide**, about 4 billion values. Rather than treating the counter as a line that eventually ends, PostgreSQL compares normal XIDs with **modulo-2^32 arithmetic**, which turns the XID space into a circle: - for any normal XID, about **two billion** XIDs count as older and about two billion count as newer; - a tuple inserted by XID *N* looks like it is in the past for the next two billion transactions; - after that, the same unchanged `xmin` starts to look like it is in the **future**, so the row would become invisible to every new transaction. The data would still be on disk, but queries could no longer see it. That event is **transaction ID wraparound**, and preventing it is one of VACUUM's jobs alongside removing dead rows. ## What freezing does to a row **Freezing** is how VACUUM takes a row out of the circular comparison. Once the inserting transaction committed long enough ago that every current and future transaction must see the row, VACUUM marks the tuple **frozen**. PostgreSQL reserves a special value, `FrozenTransactionId`, that is always older than every normal XID, and a frozen tuple is treated as if that value had inserted it. Two implementation details come up in interviews: 1. **Since 9.4, freezing sets a flag bit** in the tuple header instead of overwriting `xmin`. The original `xmin` stays readable for forensic use. 2. Clusters upgraded with `pg_upgrade` from before 9.4 can still contain rows whose `xmin` literally equals `FrozenTransactionId` (2), and system catalogs can hold rows with `BootstrapTransactionId` (1) from `initdb`. A frozen row is not read-only: it can still be updated or deleted like any other row. Freezing only records that its insertion is visible to everyone, forever. ## Who has to freeze, and how often The manual's rule is blunt: **every table in every database must be vacuumed at least once every two billion transactions**. The server enforces a much earlier deadline through three settings (PostgreSQL 18 defaults): | Age of the oldest unfrozen XID | Setting | What PostgreSQL does | |---|---|---| | over 50 million | `vacuum_freeze_min_age` | rows this old are frozen when a VACUUM processes their page | | over 150 million | `vacuum_freeze_table_age` | the next VACUUM runs **aggressively**, scanning every page not already all-frozen | | over 200 million | `autovacuum_freeze_max_age` | autovacuum forces an anti-wraparound vacuum, **even if autovacuum is turned off** | The 200-million default is deliberately low compared with the two-billion limit. It leaves a wide safety margin and also lets old segments of the commit-status directory `pg_xact` be removed. ## What happens if nobody freezes If freezing falls behind anyway, because something holds back the oldest XID horizon or vacuums keep being cancelled, PostgreSQL protects the data by degrading service rather than letting rows vanish: - once fewer than about **40 million** XIDs remain, it emits `WARNING: database "..." must be vacuumed within N transactions`; - once fewer than **3 million** remain, it refuses to assign new XIDs, so write transactions fail while reads continue. That write outage, not silent data loss, is what people mean by the wraparound outage. How to recover from it, and how vacuum escalates before it, are separate questions; the point here is the mechanism that makes them necessary. ## Common misunderstandings - **It is about transactions, not rows.** A small table on a server running thousands of write transactions per second ages just as fast as a huge one, because the counter is cluster-wide. - **The 4-billion range is not usable history.** Only half the circle counts as the past, so the effective horizon is about two billion. - **VACUUM FULL is not the routine tool.** It does freeze everything it rewrites, but it takes an `ACCESS EXCLUSIVE` lock; routine freezing is plain `VACUUM` and autovacuum. - **Turning autovacuum off does not stop anti-wraparound vacuums**; the server launches them anyway. - **The `xid8` values from `pg_current_xact_id()` do not wrap**, because they carry an epoch, but tuple headers still store 32-bit `xmin` and `xmax`, so freezing is still required.

  • If PostgreSQL XIDs are 32-bit, why does pg_current_xact_id() return a 64-bit value?
    `pg_current_xact_id()` returns `xid8`, which combines the 32-bit XID with an epoch that increments at each wraparound, so the value increases strictly and never repeats in the life of the cluster. Tuple headers still store 32-bit `xmin` and `xmax`, so on-disk visibility checks remain modulo-2^32 and freezing is still required.
  • Does a read-only PostgreSQL transaction consume an XID, and why does that matter for wraparound?
    No. An XID is assigned only when a transaction first writes; read-only transactions use a virtual transaction ID. Wraparound pressure therefore tracks write transactions, so a workload committing one tiny write per transaction, such as an ingest doing autocommit inserts row by row, burns XIDs far faster than one batching thousands of rows per transaction.

A 12-hour clock face: a note stamped 'four o'clock' only reads as earlier today while less than half a turn has passed; leave it long enough and it reads as later today. Freezing crosses out the time and writes 'long ago', which no turn of the hands can make look like the future.

  • Wraparound only threatens databases holding billions of rows.
  • Modern freezing overwrites every row's xmin with FrozenTransactionId.
  • Setting autovacuum = off stops anti-wraparound vacuums from running.
  • VACUUM FULL is the normal way PostgreSQL freezes tables.
  • At wraparound PostgreSQL deletes the oldest rows to reuse their XIDs.
  • A frozen row becomes read-only and can no longer be updated.
open as a page

Your PostgreSQL IoT telemetry database logged 'must be vacuumed within' warnings and now rejects writes to avoid wraparound; how do you restore service?

level: seniorimportance: must knowfreq 58%

basics

~20 s

Stay in normal multi-user mode. Clear what pins the oldest XID, old prepared transactions, long-running sessions and stale replication slots, then run a plain VACUUM as a superuser in the named database, oldest tables first.

open as a page

In PostgreSQL, how do you measure how close each database and table is to XID wraparound, and which freeze settings act at which age?

level: middleimportance: should knowfreq 55%

basics

~10 s

Query age(datfrozenxid) from pg_database and age(relfrozenxid) from pg_class, taking the larger of each table and its TOAST table. Compare with vacuum_freeze_table_age (150M), autovacuum_freeze_max_age (200M) and the roughly 2.1-billion hard limit.

open as a page

On a PostgreSQL telemetry database, an autovacuum marked '(to prevent wraparound)' is blocking an ALTER TABLE; how does it differ from normal autovacuum, and what changes once vacuum_failsafe_age is crossed?

level: seniorimportance: should knowfreq 42%

basics

~20 s

An anti-wraparound autovacuum is forced by relfrozenxid age, runs even with autovacuum off, normally scans every not-all-frozen page, and is not interrupted by conflicting locks. Past vacuum_failsafe_age (1.6 billion) VACUUM stops throttling and skips index vacuuming.

open as a page

In PostgreSQL, how can MultiXact IDs wrap around independently of transaction IDs, and how do their limits and recovery differ?

level: seniorimportance: should knowfreq 30%

basics

~20 s

MultiXact IDs, created when several transactions lock one row, are a separate 32-bit counter with their own member storage. VACUUM freezes them by relminmxid age; exhaustion blocks only writes needing a new MultiXact, and slots do not pin them.

open as a page

In PostgreSQL 18, what does eager freezing change for an append-mostly telemetry table, and what does vacuum_max_eager_freeze_failure_rate control?

level: middleimportance: nice to knowfreq 24%

basics

~10 s

Before 18, normal vacuums skipped all-visible pages, leaving freezing to one huge aggressive vacuum. PostgreSQL 18 lets normal vacuums eagerly freeze some all-visible pages; vacuum_max_eager_freeze_failure_rate (default 0.03) caps failed attempts before eager scanning pauses.

open as a page