Saturday, November 14, 2026

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.

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

How Autovacuum Tuning works in PostgreSQL

 Autovacuum Tuning in PostgreSQL

Autovacuum is PostgreSQL's automatic background process that:

  1. Removes dead tuples created by UPDATE/DELETE.
  2. Updates optimizer statistics (ANALYZE).
  3. Prevents transaction ID wraparound, which is critical for database survival.

A DBA's first priority is often:

Ensure autovacuum is running effectively before changing application code.


How Autovacuum Decides to Run

For VACUUM:

Trigger =
autovacuum_vacuum_threshold
+
autovacuum_vacuum_scale_factor × number_of_rows

Default values:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2

Example:

Table:

10,000 rows

Trigger:

50 + (10000 × 0.2)
= 2050 changes

After about 2050 updates/deletes:

Autovacuum starts


Problem with Large Tables

Consider:

100 million rows

Default trigger:

50 + (100,000,000 × 0.2)
= 20,000,050 changes

Autovacuum waits until:

20 million row modifications

which is often far too late.

This causes:

Table bloat
Index bloat
Slow queries


Typical Production Setting

Instead of:

autovacuum_vacuum_scale_factor = 0.2

Many DBAs use:

autovacuum_vacuum_scale_factor = 0.01

or

0.005

Example:

100 million row table

Trigger:

50 + (100,000,000 × 0.01)
≈ 1,000,050

Much more aggressive.


Important Parameters

1. autovacuum_vacuum_scale_factor

Most important tuning parameter.

Check:

SHOW autovacuum_vacuum_scale_factor;

Typical values:

Table TypeValue
Small tables0.05 - 0.2
OLTP tables0.01
Heavy update tables0.005
Very busy tables0.001

2. autovacuum_vacuum_threshold

Default:

50

Formula:

Threshold + Scale Factor

Example:

autovacuum_vacuum_threshold=500

Useful when small tables receive frequent updates.


3. autovacuum_analyze_scale_factor

Controls statistics refresh.

Default:

0.1

For OLTP systems:

0.02

or

0.01

helps query planning.


4. autovacuum_max_workers

Default:

3

Number of autovacuum workers.

Large systems:

autovacuum_max_workers = 10

or

15

Common on servers with:

16-64 CPUs


5. autovacuum_naptime

Default:

1min

Worker wake-up interval.

Usually:

1min

is fine.

Rarely changed.


6. autovacuum_vacuum_cost_limit

Controls speed.

Default:

200

Many production systems:

1000

or

2000

Allows faster cleanup.


7. autovacuum_vacuum_cost_delay

Default:

2ms

Lower value:

1ms

or

0

means faster vacuum.

Tradeoff:

More IO
More CPU


Per-Table Tuning

Usually better than global tuning.

Example:

ALTER TABLE orders
SET (
  autovacuum_vacuum_scale_factor = 0.005,
  autovacuum_analyze_scale_factor = 0.005
);

Very common for:

orders
transactions
event_logs
audit tables


Monitoring Autovacuum

Check Dead Tuples

SELECT
  relname,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Look for:

dead tuples > 10%-20% of table


Last Vacuum

SELECT
  relname,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables;


Running Vacuums

SELECT *
FROM pg_stat_progress_vacuum;

Shows:

Current table
Blocks scanned
Blocks cleaned


Detect Table Bloat

Common symptom:

Table grows continuously

even though row count stays stable.

For example:

orders table
Rows: 20 million
Expected Size: 15 GB
Actual Size: 80 GB

Usually means:

Autovacuum not keeping up


Anti-Patterns

1. Disabling Autovacuum

ALTER TABLE mytable
SET (autovacuum_enabled = false);

Dangerous.

Can lead to:

Massive bloat
Transaction wraparound risk

Never disable without a specific reason.


2. Increasing Scale Factor

Bad idea:

autovacuum_vacuum_scale_factor=0.5

Large tables may never get cleaned in time.


3. Ignoring Long Transactions

Check:

SELECT pid,
       now()-xact_start
FROM pg_stat_activity
WHERE xact_start IS NOT NULL;

A transaction running for hours can prevent dead tuple removal even if autovacuum runs.


Transaction ID Wraparound (Most Important)

Autovacuum does more than reclaim space.

PostgreSQL transaction IDs are finite (~2 billion usable before danger).

Autovacuum freezes old tuples:

VACUUM FREEZE

Without this:

database shutdown risk

PostgreSQL will eventually force emergency vacuuming to avoid corruption.

So:

Oracle DBAs typically worry about UNDO_RETENTION, while PostgreSQL DBAs worry about whether Autovacuum can keep up with dead tuples and transaction ID freezing.

Practical OLTP Starting Point

For a busy application database:

autovacuum_max_workers = 8
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 1ms

Then monitor:

pg_stat_user_tables
pg_stat_progress_vacuum
pg_stat_activity

and tune high-update tables individually rather than applying extremely aggressive settings to the entire cluster.

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Explain vacuum versus undo retention

Think of VACUUM in PostgreSQL and Undo Retention in Oracle as solving the same problem from opposite directions.

  • PostgreSQL MVCC keeps old row versions in the table.
  • Oracle keeps old row versions in Undo segments.

The challenge for both is:

"How long should old versions be kept for active transactions?"


PostgreSQL VACUUM

What happens after UPDATE?

UPDATE emp
SET salary = 10000
WHERE id = 1;

Internally:

Old tuple  -> Dead
New tuple  -> Live

Table becomes:

Row Version 1 (dead)
Row Version 2 (active)

The dead row is still present because some transaction might still need it.

VACUUM's Job

VACUUM:

  1. Finds dead tuples.
  2. Checks whether any transaction can still see them.
  3. Removes them from future visibility.
  4. Marks space reusable.

Before VACUUM
John(dead)
David(active)
After VACUUM
David(active)


Why VACUUM Cannot Remove Rows Immediately

Assume:

T1 starts at 10:00
T2 updates row at 10:01

T1 still sees the old row version.

If VACUUM deletes it:

T1 loses consistency

Therefore VACUUM waits until all interested transactions finish.


Problem: Table Bloat

If VACUUM cannot clean:

100 updates
=
100 dead versions

Table grows:

50 GB live data
150 GB dead tuples

This is called table bloat.

Common causes:

Long-running transactions
Replication slots
Idle-in-transaction sessions


Oracle Undo Retention

Oracle stores previous values in Undo segments.

Update:

UPDATE emp
SET salary = 10000
WHERE id = 1;

Results:

Table:
salary=10000
Undo:
salary=8000


Undo Retention Parameter

Oracle has:

UNDO_RETENTION = 3600

Meaning:

Try to keep undo data
for at least 3600 seconds

Not a guarantee in all configurations.


Why Oracle Needs Undo Retention

Suppose:

Query started at 10:00

Update occurs:

10:05

Oracle reconstructs old data using undo.

If undo gets overwritten before query finishes:

ORA-01555
snapshot too old


More Undo Retention

UNDO_RETENTION = 7200

Benefits:

✅ Long reports succeed

✅ Flashback queries succeed

✅ Long-running transactions remain consistent

Cost:

❌ Larger undo tablespace


Direct Comparison

FeaturePostgreSQL VACUUMOracle Undo Retention
Stores old versions where?Table itselfUndo tablespace
Cleanup mechanismVACUUMUndo overwrite
What consumes space?Dead tuplesUndo blocks
Long query impactPrevents cleanupRequires undo retention
Failure modeTable/index bloatORA-01555
Space reclaimed byVACUUMUndo reuse
Admin tuningautovacuumundo_retention + undo size

Example: 1 Million Updates

PostgreSQL

1 million updates

Creates:

1 million dead tuples

Need:

VACUUM

otherwise:

Table size grows
Indexes grow
Performance drops


Oracle

1 million updates

Creates:

Undo records

Need:

Sufficient Undo Tablespace

otherwise:

ORA-01555

for long consistent reads.


DBA Perspective

PostgreSQL Monitoring

Check dead tuples:

SELECT relname,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables;

Check vacuum activity:

SELECT *
FROM pg_stat_progress_vacuum;


Oracle Monitoring

Check undo retention:

SHOW PARAMETER undo_retention;

Check undo statistics:

SELECT *
FROM v$undostat;


Interview Answer

VACUUM in PostgreSQL removes obsolete row versions created by MVCC after they are no longer needed by any transaction. Its goal is to prevent table and index bloat and reclaim space for reuse.

Undo Retention in Oracle controls how long old row images stored in Undo segments are preserved so Oracle can reconstruct historical versions for consistent reads and flashback operations.

In one sentence:

PostgreSQL keeps old versions in the table and later removes them using VACUUM, whereas Oracle keeps old versions in Undo and preserves them for a configured retention period before reusing the space.

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

PostgreSQL Process Architecture Diagram and Details

PostgreSQL Process Architecture Diagram                            +------------------+                            |    POSTMASTER    |     ...