Explained vacuum freeze and wraparound in Postgres Database

 

Why PostgreSQL Needs VACUUM FREEZE

The concept comes from Transaction IDs (XIDs).

Every transaction in PostgreSQL gets a transaction ID:

Transaction A = XID 100

Transaction B = XID 101

Transaction C = XID 102

...

An inserted row stores:

xmin = creator XID

xmax = deleting/updating XID

Example:

Row: John

xmin = 100

xmax = NULL

Meaning:

Created by transaction 100

Not deleted

---

The Wraparound Problem

PostgreSQL transaction IDs are 32-bit numbers.

Maximum:

2^32 = 4,294,967,296

Eventually:

4294967295

is reached and numbering wraps:

4294967295

        ↓

0

1

2

3

...

This is called XID Wraparound.

---

Why Is Wraparound Dangerous?

Suppose a row was created long ago:

xmin = 100

Current transaction:

XID = 4,294,967,000

After wrap:

Current XID = 100

Now PostgreSQL could incorrectly think:

xmin = 100

means

Created by current transaction

instead of

Created years ago

Visibility rules would become wrong.

This could lead to:

Data appearing deleted

Rows disappearing

Corruption of visibility decisions

---

PostgreSQL's Solution: Freezing

For very old rows PostgreSQL stops caring about the original XID.

Instead it marks them as:

Frozen

Conceptually:

xmin = FrozenXID

Meaning:

This row is definitely visible to everyone forever

PostgreSQL no longer needs to compare that old XID against current transaction IDs.

---

Example

Before freeze:

id = 1

name = John

xmin = 150

After freeze:

id = 1

name = John

xmin = FrozenXID

Now:

No wraparound risk

because original XID is irrelevant.

---

VACUUM vs VACUUM FREEZE

Regular VACUUM

Cleans dead tuples:

UPDATE

DELETE

Example:

John(dead)

David(active)

Regular VACUUM:

removes John

---

VACUUM FREEZE

Does:

Remove dead tuples

+

Freeze old tuples

Example:

John xmin=100

becomes:

John xmin=FrozenXID

---

Autovacuum and Freezing

Autovacuum has two jobs:

Job 1

Clean bloat.

Dead tuples

Job 2

Prevent wraparound.

Freeze old tuples

Many Oracle DBAs coming to PostgreSQL focus only on dead tuples and miss the second task.

The freeze activity is actually more critical.

---

Important Parameters

autovacuum_freeze_max_age

Default:

SHOW autovacuum_freeze_max_age;

Typically:

200000000

(200 million transactions)

Meaning:

If tuples approach this age,

force vacuum freeze.

---

vacuum_freeze_min_age

Default often around:

50000000

(50 million)

Rows older than this become candidates for freezing.

---

Transaction Age Example

Imagine:

Current XID = 300,000,000

Row:

xmin = 80,000,000

Age:

300M - 80M

= 220M

Older than:

autovacuum_freeze_max_age

Autovacuum launches an aggressive freeze vacuum.

---

What Happens If Autovacuum Is Disabled?

One of PostgreSQL's most dangerous situations.

Eventually:

Transaction age

      ↓

Approaches wraparound limit

PostgreSQL starts warnings:

database must be vacuumed soon

Later:

database is not accepting commands

to avoid wraparound data loss

The database can effectively stop normal write operations until vacuum catches up.

---

Oracle Analogy

For an Oracle DBA:

Oracle Concern

Undo retention too small

Result:

ORA-01555 Snapshot Too Old

---

PostgreSQL Concern

Autovacuum cannot freeze old tuples

Result:

Transaction ID wraparound risk

---

Monitoring Freeze Risk

Check database age:

SELECT datname,

       age(datfrozenxid)

FROM pg_database;

Example:

database      age

-----------   ----------

prod_db       180000000

Near:

200000000

requires attention.

---

Check tables:

SELECT

    relname,

    age(relfrozenxid)

FROM pg_class

WHERE relkind='r'

ORDER BY age(relfrozenxid) DESC;

This identifies tables needing freeze vacuum.

---

Interview Answer

Transaction ID wraparound occurs because PostgreSQL uses 32-bit transaction IDs that eventually cycle back to the beginning. To prevent old transaction IDs from becoming ambiguous, PostgreSQL periodically freezes very old tuples, replacing their original transaction IDs with a special frozen marker that is always considered visible.

VACUUM FREEZE therefore serves two purposes:

1. Remove dead tuples created by MVCC.
2. Freeze old tuples to prevent transaction ID wraparound.

In one sentence:

Regular VACUUM fights table bloat; VACUUM FREEZE protects the database from transaction ID wraparound.