VACUUM Command

PostgreSQL uses a multi-version concurrency control (MVCC) model: when a row is updated or deleted, the old version remains on disk as a "dead tuple" until it is no longer visible to any open transaction. VACUUM reclaims that space, making pages available for reuse. Without regular vacuuming, tables grow ("bloat") and performance degrades.

VACUUM variants

-- Standard VACUUM: marks dead tuples as free, does NOT return space to OS
VACUUM orders;

-- VACUUM ANALYZE: reclaim space and update planner statistics in one pass
VACUUM (ANALYZE) orders;

-- VACUUM VERBOSE: show detailed progress for each table
VACUUM (VERBOSE, ANALYZE) orders;

-- VACUUM FULL: rewrites the entire table, returning space to OS (takes exclusive lock!)
VACUUM FULL orders;

The important distinction: standard VACUUM marks dead-tuple space as reusable within PostgreSQL but does not shrink the file on disk. VACUUM FULL compacts the table to its minimum size but requires an exclusive lock that blocks all reads and writes for the duration—avoid it during peak hours.

Viewing dead tuples and bloat

SELECT
  relname                   AS table_name,
  n_live_tup                AS live_rows,
  n_dead_tup                AS dead_rows,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

A large n_dead_tup relative to n_live_tup indicates table bloat. If last_autovacuum is null or stale on a busy table, autovacuum may need tuning.

Autovacuum

PostgreSQL's autovacuum daemon monitors all tables and automatically runs VACUUM and ANALYZE when the number of dead tuples exceeds a threshold (default: 20% of row count + 50 rows). Autovacuum is enabled by default and handles most workloads without manual intervention.

You can tune autovacuum per table for high-write tables:

-- Run autovacuum more aggressively on a high-churn table
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- 1% dead tuples triggers vacuum
  autovacuum_analyze_scale_factor = 0.005 -- 0.5% changes triggers analyze
);

Transaction ID wraparound

PostgreSQL transaction IDs are 32-bit integers and eventually wrap around. VACUUM must "freeze" old tuple versions before wraparound occurs to prevent data corruption. Monitor how far tables are from wraparound:

SELECT
  relname,
  age(relfrozenxid) AS xid_age,
  2147483648 - age(relfrozenxid) AS xids_remaining
FROM pg_class
WHERE relkind = 'r'
ORDER BY xid_age DESC
LIMIT 20;

When xid_age approaches ~2 billion, PostgreSQL will force aggressive vacuuming. Tables with very high xid_age should be vacuumed promptly.

Practical tips

• Prefer VACUUM ANALYZE over running them separately—it updates statistics in the same pass.
• Avoid VACUUM FULL on production tables during peak hours; use it only when you need to reclaim disk space and can schedule a maintenance window.
• Long-running transactions prevent VACUUM from reclaiming dead tuples—check pg_stat_activity for idle-in-transaction sessions.
• The visibility map must be current for index-only scans to avoid heap fetches. Standard VACUUM updates the visibility map; VACUUM FULL also updates it.