How Autovacuum Chooses What to Freeze to Avoid Transaction Wraparound

This blog contains the findings from a focused study on how PostgreSQL autovacuum chooses which tuples to freeze in order to prevent transaction ID wraparound. The goal is not to introduce autovacuum conceptually, but to describe the exact decision points, thresholds, and internal rules PostgreSQL applies while advancing the freeze horizon.

Three configuration parameters define the freezing behavior:

Each table also tracks its own wraparound state using relfrozenxid in pg_class.

Autovacuum first checks table metadata:

SELECT relfrozenxid FROM pg_class WHERE relname = 'table_name';

relfrozenxid is the oldest unfrozen transaction ID still present in that table.

It checks:

if age(relfrozenxid) >= autovacuum_freeze_max_age

If this condition is true, the table is vacuumed even if:

• There is no bloat
• There are no dead tuples
• There have been no recent updates

This is called an anti-wraparound vacuum. Its only purpose is to advance relfrozenxid and prevent wraparound.

FreezeLimit = NextXID - vacuum_freeze_min_age

Which means any tuple with xmin < FreezeLimit AND Visible to all active transactions can be frozen.

• Where Xmin is XID of the transaction that inserted the tuple and
• Xmax is XID of the transaction that deleted or updated the tuple

A tuple becomes a candidate for freezing when:

• xmin < FreezeLimit
• The inserting transaction is committed
• No active transaction could still see it as “in progress”

This depends on:

• OldestXmin
• Current snapshots
• Running transactions

A single long-running transaction can delay freezing for large parts of a table, even if the tuple age is very high.

Normal vs Aggressive Freezing

The autovacuum behaves differently depending on how old the table is.

Normal Mode

When: age(relfrozenxid) < vacuum_freeze_table_age

PostgreSQL:

• Freezes only eligible tuples it happens to visit
• Obeys normal cost limits
• May leave some old tuples unfrozen

Aggressive Mode

When: age(relfrozenxid) >= vacuum_freeze_table_age

PostgreSQL switches to aggressive vacuum:

• Tries to freeze every eligible tuple
• Ignores some cost delays
• Focuses on advancing relfrozenxid

This guarantees that the table-level horizon moves forward.

Note:

Relfrozenxid is the oldest unfrozen transaction id of the table.

ie, relfrozenxid = min(unfrozen xmin found)

What Actually Happens to Each Tuple

For each tuple, PostgreSQL examines both xmin (inserting transaction) and xmax (deleting/updating transaction).

These are what small xids are used for 0, 1, 2 and normal transaction IDs start with 2.

How Wraparound Is Safely Handled

PostgreSQL continues assigning XIDs normally until it reaches 2³² - 1.

If, by that time, no unfrozen tuple is older than 2³¹ transactions, the counter safely wraps and continues from 3, 4, 5…In case of reaching XID 2³² - 1 and there is some unfrozen xmin that is still visible due to long running queries or something , that is:

age(datfrozenxid) >= 2^31

Where datfrozenxid is the oldest unfrozen XID in the entire DB At this point, PostgreSQL stops generating new transactions or do not wrap around and throws an error:

ERROR: database is not accepting commands to avoid wraparound

But before reaching this end postgres does give some warnings:

1. Early warning (well before wrap)

WARNING: database "db" must be vacuumed within X transactions

This is logged when:

age(datfrozenxid) ˜ autovacuum_freeze_max_age

2. Emergency Autovacuum

Long before the hard limit, PostgreSQL may trigger emergency autovacuum:

• Cost limits are disabled
• The vacuum cannot be canceled
• Runs even on inactive tables
• Aggressively freezes tuples

The sole goal is to advance datfrozenxid and avoid system shutdown.

In summary, PostgreSQL’s freezing and autovacuum logic exists primarily to protect the system from transaction ID wraparound, not to improve query performance. The real risk is governed by how relfrozenxid and datfrozenxid advance, and this progress is often constrained by long-running transactions. Anti-wraparound and emergency vacuums therefore focus exclusively on moving the freeze horizon forward, even at the cost of normal vacuum behavior.