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.