VACUUM vs VACUUM FULL in PostgreSQL

A common misconception is:

"VACUUM removes dead tuples, so table size should shrink."

Wrong.

Regular VACUUM and VACUUM FULL have very different objectives.

---

Understanding the Problem

Suppose a table has grown to:

orders = 100 GB

Live data:

30 GB

Dead tuples:

70 GB

After many UPDATEs and DELETEs.

---

Regular VACUUM

VACUUM orders;

What it does

1. Finds dead tuples.
2. Marks their space as reusable.
3. Updates visibility information.
4. Helps index-only scans.
5. Advances freeze information.

What it DOES NOT do

It does not return space to the operating system.

---

Example

Before:

Table Size = 100 GB

Page 1  Live rows

Page 2  Dead rows

Page 3  Live rows

Page 4  Dead rows

After VACUUM:

Page 1  Live rows

Page 2  Free space

Page 3  Live rows

Page 4  Free space

File size remains:

100 GB

But PostgreSQL can reuse that free space for future inserts/updates.

---

Analogy

Think of a hotel.

Before VACUUM:

100 rooms

70 empty

30 occupied

VACUUM:

Marks empty rooms available

Hotel building still has:

100 rooms

The building doesn't shrink.

---

VACUUM FULL

VACUUM FULL orders;

What it does

Instead of reusing space inside the file:

Creates a new compact copy

Copies only live rows

Drops old file

---

Example

Before:

100 GB table

30 GB live

70 GB dead

After:

30 GB table

Space is returned to:

Operating System

immediately.

---

Analogy

Using the hotel example:

Before:

100 rooms

30 occupied

70 empty

VACUUM FULL:

Build a new 30-room hotel

Move guests

Demolish old building

Now building size becomes:

30 rooms

---

Internal Architecture

Regular VACUUM

+------------------+

| Existing Table   |

+------------------+

         |

         +--> mark dead space reusable

No table rewrite.

---

VACUUM FULL

Old Table

     |

     v

Create New Table

     |

Copy Live Rows

     |

Swap Files

     |

Delete Old Table

Complete rewrite.

---

Locking Behavior

VACUUM

VACUUM table_name;

Lock:

SHARE UPDATE EXCLUSIVE

Practical impact:

✅ SELECT works

✅ INSERT works

✅ UPDATE works

✅ DELETE works

Usually invisible to users.

---

VACUUM FULL

VACUUM FULL table_name;

Lock:

ACCESS EXCLUSIVE

Impact:

❌ SELECT blocked

❌ INSERT blocked

❌ UPDATE blocked

❌ DELETE blocked

Everything waits.

---

Example:

500 GB table

VACUUM FULL might run:

Several hours

During which the table is unavailable.

---

Extra Disk Requirement

VACUUM

Needs very little additional disk.

---

VACUUM FULL

Needs enough space to hold:

Old table

+

New table

Example:

Current size = 500 GB

May require:

~500 GB extra

temporarily.

---

Index Impact

VACUUM

Indexes stay as-is.

Some index bloat may remain.

---

VACUUM FULL

Indexes are rebuilt.

Example:

Table bloat removed

Index bloat removed

Result:

Faster scans

Smaller indexes

---

Performance Comparison

Feature	VACUUM	VACUUM FULL
Removes dead tuples	Yes	Yes
Reclaims internal space	Yes	Yes
Returns disk to OS	No	Yes
Rewrites table	No	Yes
Rebuilds indexes	No	Yes
Requires exclusive lock	No	Yes
AutoVacuum uses it	Yes	No
Suitable for production use	Yes	Rarely

---

Check Whether VACUUM Is Enough

Query:

SELECT

    relname,

    n_live_tup,

    n_dead_tup

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC;

If:

dead tuples present

but table size reasonable

Use:

VACUUM;

---

When VACUUM FULL Makes Sense

Case 1: Massive Delete

DELETE FROM orders

WHERE order_date < '2020-01-01';

Deleted:

90% of rows

Table remains:

1 TB

Live data:

100 GB

VACUUM won't shrink it.

VACUUM FULL can reduce:

1 TB -> 100 GB

---

Case 2: One-Time Data Purge

Archive and remove old data.

Then run:

VACUUM FULL;

---

Case 3: After Bulk Migration Cleanup

Drop old rows

Keep small dataset

Reclaim storage.

---

Why Production DBAs Prefer pg_repack

Instead of:

VACUUM FULL

many DBAs use:

pg_repack

Benefits:

✅ Removes bloat

✅ Rebuilds indexes

✅ Minimal locking

✅ Works online

Very common on:

Large OLTP systems

Financial databases

E-commerce databases

---

Interview Answer

VACUUM removes dead tuples and makes the space reusable inside the table file, but it does not reduce the file size on disk. It runs without blocking normal application activity and is what Autovacuum uses.

VACUUM FULL rewrites the entire table and indexes into a new compact copy, returning unused space to the operating system. However, it requires an ACCESS EXCLUSIVE lock, blocks all access to the table, and needs additional temporary disk space.

One-line summary:

VACUUM reuses free space inside the table; VACUUM FULL shrinks the table by rebuilding it.

What is Table Bloat in PostgreSQL Database ?

 

PostgreSQL Table Bloat Explained in Detail

Table bloat is one of the most important PostgreSQL performance issues.

Because PostgreSQL uses MVCC, every UPDATE creates a new row version instead of modifying the row in place.

How Bloat Happens

Assume a table:

CREATE TABLE employees(

   id INT,

   name TEXT

);

Initial data:

id   name

1    John

---

First Update

UPDATE employees

SET name='David'

WHERE id=1;

Internally:

Tuple 1: John    -> dead

Tuple 2: David   -> active

Physical storage:

Page 1

John   (dead)

David  (live)

---

More Updates

UPDATE employees SET name='Mike' WHERE id=1;

UPDATE employees SET name='Tom' WHERE id=1;

UPDATE employees SET name='James' WHERE id=1;

Now:

John    dead

David   dead

Mike    dead

Tom     dead

James   live

Although only one row exists logically, five versions exist physically.

80% dead tuples

20% live tuples

This is table bloat.

---

Why Bloat Is Bad

1. More Disk Usage

Logical data:

1 GB

Physical table:

20 GB

A common production issue.

---

2. More I/O

Suppose query:

SELECT *

FROM orders

WHERE order_id=100;

PostgreSQL scans pages.

Without bloat:

1000 pages

With bloat:

5000 pages

More disk reads.

---

3. Index Bloat

Every UPDATE creates new index entries.

Example:

Table Size = 50 GB

Indexes = 100 GB

Sometimes indexes become larger than the table.

---

4. Vacuum Takes Longer

More pages:

More work for VACUUM

which further increases maintenance cost.

---

Typical Causes

Long Running Transactions

Check:

SELECT pid,

       usename,

       now()-xact_start AS txn_age

FROM pg_stat_activity

WHERE xact_start IS NOT NULL

ORDER BY txn_age DESC;

Example:

3 hours transaction

Autovacuum cannot remove versions visible to that transaction.

---

Idle In Transaction Sessions

BEGIN;

-- user goes to lunch

COMMIT;

Very common application problem.

Check:

SELECT *

FROM pg_stat_activity

WHERE state='idle in transaction';

---

Heavy UPDATE Workloads

Examples:

Banking systems

Order systems

E-commerce inventory

Stock exchange data

---

Autovacuum Not Keeping Up

Autovacuum running slower than updates.

Result:

Dead tuples accumulate faster than cleanup.

---

Find Dead Tuples

Most useful first query:

SELECT

    schemaname,

    relname,

    n_live_tup,

    n_dead_tup,

    ROUND(

        100.0 * n_dead_tup /

        GREATEST(n_live_tup + n_dead_tup,1),

        2

    ) AS dead_pct

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC;

Example:

table         live       dead      dead_pct

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

orders        1M         500K      33%

payments      2M         50K       2%

The orders table needs attention.

---

Find Largest Tables

SELECT

    relname,

    pg_size_pretty(pg_total_relation_size(relid))

FROM pg_catalog.pg_statio_user_tables

ORDER BY pg_total_relation_size(relid) DESC;

Example:

orders           500 GB

transactions     300 GB

Large tables with heavy updates are prime suspects.

---

Estimate Bloat Quickly

Top Suspects

SELECT

    relname,

    n_live_tup,

    n_dead_tup,

    pg_size_pretty(pg_total_relation_size(relid))

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC

LIMIT 20;

---

Exact Table Bloat Analysis

Install extension:

CREATE EXTENSION pgstattuple;

Check table:

SELECT *

FROM pgstattuple('orders');

Example output:

table_len         100 GB

tuple_len         40 GB

dead_tuple_len    45 GB

free_space        10 GB

Meaning:

45% table occupied by dead tuples

This is highly bloated.

---

Check Index Bloat

Install:

CREATE EXTENSION pgstattuple;

Check index:

SELECT *

FROM pgstatindex('idx_orders_order_id');

Example:

avg_leaf_density = 42%

Healthy:

>70%

Bloated:

<50%

---

Solution 1: Improve Autovacuum

For heavily updated tables:

ALTER TABLE orders

SET (

    autovacuum_vacuum_scale_factor = 0.01,

    autovacuum_analyze_scale_factor = 0.01

);

Instead of default:

20%

vacuum starts near:

1%

changes.

---

Solution 2: Manual VACUUM

VACUUM VERBOSE orders;

Benefits:

✅ Cleans dead tuples

✅ Updates visibility map

✅ Improves future index-only scans

Limitation:

❌ Does NOT shrink physical table size.

---

Solution 3: VACUUM FULL

VACUUM FULL orders;

What it does:

Rewrites complete table

Removes all dead space

Compacts table

Example:

Before: 100 GB

After : 35 GB

---

Downside

Exclusive lock.

Reads blocked

Writes blocked

until completion.

Not usually used on busy production systems.

---

Solution 4: pg_repack (Preferred)

Install:

pg_repack

Very popular production tool.

pg_repack -d mydb -t orders

Benefits:

✅ Rebuilds table

✅ Removes bloat

✅ Minimal locking

✅ Online

Example:

500 GB → 150 GB

without major downtime.

Most production DBAs prefer pg_repack over VACUUM FULL.

---

Find Tables Nearing Wraparound

Frequently correlated with poor vacuuming.

SELECT

    relname,

    age(relfrozenxid)

FROM pg_class

WHERE relkind='r'

ORDER BY age(relfrozenxid) DESC;

Large ages indicate vacuum is struggling.

---

Real Production Bloat Checklist

Run these in order:

Step 1

SELECT

    relname,

    n_live_tup,

    n_dead_tup

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC;

Step 2

SELECT *

FROM pg_stat_activity

WHERE state='idle in transaction';

Step 3

SELECT *

FROM pg_stat_progress_vacuum;

Step 4

SELECT *

FROM pgstattuple('large_table');

Step 5

If bloat > 30-40%:

Tune autovacuum

or

Run pg_repack

---

Interview Answer

PostgreSQL table bloat occurs because MVCC keeps old row versions after UPDATE or DELETE. When VACUUM cannot remove these dead tuples fast enough, tables and indexes grow larger than the actual live data. Bloat increases storage, I/O, query latency, and maintenance cost. It can be identified using pg_stat_user_tables, pgstattuple, and index statistics, and is typically resolved through proper autovacuum tuning, manual vacuuming, or online reorganization tools such as pg_repack.

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.