What VACUUM actually does in PostgreSQL

Almost every backend dev thinks VACUUM is "database cleanup."

It isn't.

VACUUM is part of how Postgres works. Without it, the database stops. Literally.

And it's the reason your 2 GB table is taking up 18 GB on disk.

First things first: MVCC

Postgres doesn't update rows in place.

When you UPDATE, it:

Marks the old row as dead (a "dead tuple").
Inserts a new row with the updated values.

DELETE is similar: the row doesn't disappear, it's just marked dead.

Why? Because concurrent transactions might still be seeing the previous version. MVCC (Multi-Version Concurrency Control) has to keep both around until nobody needs the old one anymore.

| id | name  | xmin | xmax |
|----|-------|------|------|
| 1  | Vini  | 100  | 200  |  ← dead (replaced)
| 1  | Vini2 | 200  | 0    |  ← live

Without someone cleaning up dead rows, they stay there. Forever.

That someone is VACUUM.

Dead tuples: the root of the problem

Every UPDATE, every DELETE — produces dead tuples.

A write-heavy table without VACUUM keeping up:

Table: orders
- 10M live rows
- 40M accumulated dead tuples
- On-disk size: 6x what it should be

This is called bloat.

And then the drama begins:

Seq Scan reads everything, live and dead (then filters).
Indexes have dead entries too — index bloat.
The Postgres cache fills up with pages holding little useful data.
A query that should be fast turns slow for no apparent reason.

The query didn't change. The plan didn't change. The table rotted.

What VACUUM actually does

Three things, mainly:

1. Marks dead-tuple space as reusable

It doesn't return space to the OS. It marks it as "you can write over this."

Next INSERT/UPDATE uses that space. The table stops growing.

2. Updates the visibility map

A map that says "this page only has rows visible to everyone."

Enables index-only scans — Postgres reads the index without touching the heap. Free performance.

3. Updates statistics (when it's VACUUM ANALYZE)

Distribution histogram, n_distinct, correlation. The stuff the planner uses to decide Seq vs Index.

Stale stats = wrong plan = slow query. We saw this in the previous article.

Autovacuum: the silent hero

You rarely run VACUUM manually. Autovacuum runs by itself.

Approximate defaults:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2

Translation: it runs VACUUM on a table when it has more than 50 + 0.2 * total_rows dead tuples.

On a 100M-row table:

50 + 0.2 * 100_000_000 = 20_000_050 dead tuples

Twenty million dead rows before autovacuum lifts a finger. Too late.

Golden rule:

On big, hot tables, lower the scale_factor.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

When autovacuum can't keep up

Symptoms:

bloat only grows, even with autovacuum on
queries slower and slower
pg_stat_user_tables.n_dead_tup through the roof

Common causes:

1. Very high write workload
   ↓ autovacuum runs but can't finish in time

2. Long-running transactions
   ↓ while transaction X is alive, dead tuples can't be cleaned
     (Postgres doesn't know if X might still want the old version)

3. Orphaned replication slots
   ↓ an inactive slot holds back the xmin for the whole database

4. Big table with too few autovacuum workers
   ↓ default autovacuum_max_workers is 3 — not enough

Cause #2 is the champion. Somebody opened BEGIN in a console, went to lunch, and the database has been frozen from the inside for 3 hours.

Quick diagnosis:

SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_start;

Found someone with xact_start from 2 hours ago? Kill it.

How to know if a table is bloated

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

dead_ratio above 0.2 is already a warning. Above 1.0 (more dead than live) is a fire.

For a byte-level estimate, there's the pgstattuple query:

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('orders');

Gives you dead_tuple_percent, free_percent, tuple_count. Slow to run, but accurate.

Freezing and transaction wraparound

This is where it gets serious.

Every transaction in Postgres has a 32-bit ID — XID. When it hits the limit (~2 billion), it wraps around.

If nothing is done, old transactions become "from the future" and rows turn invisible. Catastrophe.

Postgres's solution: mark old tuples as "frozen" — visible to all transactions, present and future.

This is also VACUUM's job. Specifically, the autovacuum in emergency mode.

Signs you're close to wraparound:

SELECT
  datname,
  age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

Above 200 million, autovacuum enters to prevent wraparound mode — can't be canceled, will hold the table locked if it must.

Above 2 billion, the database refuses to accept new transactions.

You wake up at 3 a.m.

VACUUM FULL: the nuclear option

Normal VACUUM doesn't return space to the OS. VACUUM FULL does — it rebuilds the entire table from scratch.

VACUUM FULL orders;

Problems:

locks the whole table with ACCESS EXCLUSIVE
no one reads, no one writes
takes time proportional to table size

In production, on a big table, that's unacceptable.

The civilized alternative: pg_repack.

pg_repack -t orders mydb

Same job as VACUUM FULL — rewrites the table compacted — but without an exclusive lock. Keeps a copy, syncs via triggers, swaps at the end.

Rule: never run VACUUM FULL in prod on a hot table. Use pg_repack.

VACUUM vs ANALYZE vs VACUUM ANALYZE

Classic confusion:

VACUUM           → cleans dead tuples, updates visibility map
ANALYZE          → updates statistics (doesn't touch dead tuples)
VACUUM ANALYZE   → both
VACUUM FULL      → rewrites the table (returns space to OS)

Autovacuum runs the two separately (autovacuum and autoanalyze), with their own thresholds.

You almost never need to run it manually. When you do, it's because:

you loaded a million rows and want fresh stats right now
you're debugging a plan and want to rule out "stale stats" as a hypothesis

ANALYZE orders;

How bloat kills performance silently

Real scenario:

Week 1:  query runs in 30ms
Week 4:  60ms
Week 8:  150ms
Week 12: 800ms — somebody opens a ticket

Nothing changed in the query. Nothing changed in the schema.

The table accumulated bloat. Each Seq Scan now reads 5x more pages. Each Index Scan visits dead pages. The cache holds less useful data.

In EXPLAIN (ANALYZE, BUFFERS):

Buffers: shared hit=200 read=18000

Almost everything from disk. Used to be almost everything from cache.

Without honest VACUUM, the database rots from the inside out.

Minimum checklist

[ ] Autovacuum enabled (default, but confirm)
[ ] Big/hot tables with reduced scale_factor
[ ] Monitor pg_stat_user_tables.n_dead_tup
[ ] Monitor age(datfrozenxid) — alert at 500M
[ ] No forgotten long-running transactions
[ ] Replication slots active (or removed)
[ ] pg_repack available for tables that need it

Seven lines. Each one has already saved someone a bad night.

The big shift

VACUUM is not optional maintenance.

It's part of the MVCC contract. Postgres only works because someone removes the garbage it creates by design.

Ignoring VACUUM is ignoring how the database works.

And the database, silently, will collect:

slow queries for no clear reason
full disk with no extra data
emergency autovacuum holding production
wraparound at 3 a.m.

Conclusion

VACUUM is Postgres's immune system.

You don't need to understand every detail — but you need to know it exists, that it runs, that sometimes it can't keep up, and that when it can't, you're the one who has to help.

Set aggressive autovacuum on the right tables. Monitor n_dead_tup. Kill zombie transactions. Keep pg_repack handy.

The difference between the team that sleeps well and the team that puts out fires is, more often than it seems, someone who understood what VACUUM does.

It's not cleanup.

It's how Postgres breathes.