PostgreSQL VACUUM and autovacuum — Diagnosing and Tuning Table Bloat

What Is VACUUM?

PostgreSQL uses MVCC (Multi-Version Concurrency Control). Even after an UPDATE or DELETE, existing rows are not removed immediately; they are left as "dead versions" (dead tuples). Other transactions may still need to see those versions. VACUUM is the work that reclaims dead tuples nobody references anymore so their space can be reused.

What VACUUM does:

• Marks space occupied by dead tuples as reusable
• Updates the Visibility Map → more efficient Index-Only Scans
• Transaction ID (XID) freezing → prevents wraparound failures
• Updates statistics (when combined with ANALYZE) → better planner accuracy

What Is Table Bloat?

If dead tuples are not cleaned up in time, or if they are cleaned up but the space is not filled again immediately, tables and indexes grow much larger than the live data. That is bloat.

Effects of bloat:

• More pages to read on Seq Scan → slower queries
• Worse cache (shared_buffers) efficiency
• Index bloat → higher index scan cost

1. Diagnosing Dead Tuples

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

If dead_pct exceeds 10–20% and last_autovacuum is a long time ago, autovacuum is falling behind.

View together with physical table size:

SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

The pgstattuple extension is more accurate for estimating bloat.

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('public.orders');
-- dead_tuple_percent, free_percent check

2. Manual VACUUM

--
VACUUM (VERBOSE, ANALYZE) public.orders;

-- DB
VACUUM (ANALYZE);

In VERBOSE output you can check removed N row versions, there were N dead row versions, and similar lines.

3. How autovacuum Works

autovacuum runs automatically per table when this condition is met:

 = autovacuum_vacuum_threshold
 + autovacuum_vacuum_scale_factor *

Defaults are threshold=50, scale_factor=0.2. So a 10-million-row table needs about 2 million dead tuples before autovacuum starts. This is the classic problem of running too late on large tables.

Global defaults in postgresql.conf:

autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s

#
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1 # 0.2 → 0.1

# I/O
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 2ms

4. Per-Table Tuning for Large Tables

Keep global values conservative, and set aggressive storage parameters only on large, frequently updated tables.

ALTER TABLE public.orders SET (
 autovacuum_vacuum_scale_factor = 0.02, -- 2% run
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_cost_limit = 4000,
  autovacuum_analyze_scale_factor = 0.01
);

5. Monitoring In-Progress VACUUM

SELECT
  p.pid, t.relname, p.phase,
  p.heap_blks_scanned, p.heap_blks_total,
  round(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total, 0), 1) AS pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_user_tables t ON t.relid = p.relid;

Check whether a long-running autovacuum is blocking other work:

SELECT pid, state, wait_event_type, query, now() - xact_start AS duration
FROM pg_stat_activity
WHERE query ILIKE '%autovacuum%' OR query ILIKE '%vacuum%';

6. Preventing XID Wraparound

Transaction IDs wrap around at about 2.1 billion. If freezing does not happen in time, wraparound can force the database into read-only mode or halt it — a critical failure.

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

When xid_age approaches autovacuum_freeze_max_age (default 200 million), a forced anti-wraparound autovacuum starts. This work must not be stopped, and if regular VACUUM is running normally you will almost never hit it.

7. Removing Bloat — VACUUM FULL and Alternatives

-- file ACCESS EXCLUSIVE
VACUUM FULL public.orders;

If you need online operation with no downtime, use the pg_repack extension. It builds a new table in the background and swaps it in, so only a brief lock is taken.

pg_repack -d appdb -t orders --no-order

Summary

VACUUM is essential maintenance that must not be turned off. Lowering scale_factor on large tables so it runs more often, and regularly monitoring dead-tuple ratios and XID age, is the most reliable way to prevent both bloat and wraparound.