In PostgreSQL, why do 32-bit transaction IDs make freezing necessary, and what does VACUUM's freezing actually change on a row?
answer
- a counter that runs out
- comparison on a circle
- two billion behind, two billion ahead
- a header flag meaning always past
basics
~20 sPostgreSQL 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 sEvery 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
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.
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.
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.
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.