VACUUM, ANALYZE and Autovacuum

Key features

• VACUUM: reclaims dead tuple space for reuse within the table, does not shrink the file, requires no exclusive lock
• VACUUM FULL: rewrites the table into a new, compact file, does shrink it and returns space to the OS, requires an ACCESS EXCLUSIVE lock
• ANALYZE: collects statistics (row count estimate, most-common values, histograms) the query planner uses — completely separate concern from dead tuples
• VACUUM ANALYZE (or just letting autovacuum handle both) runs both operations together in a single table scan
• Stale statistics from a missing ANALYZE can cause bad query plans even on a table with zero bloat — the two problems are independent

Command

VACUUM accounts;               -- reclaim dead tuple space for reuse
ANALYZE accounts;              -- refresh planner statistics
VACUUM ANALYZE accounts;       -- both, in one pass
VACUUM FULL accounts;          -- reclaim AND shrink -- rare, blocking

Key features

• Autovacuum runs as one or more background worker processes, launched by the autovacuum launcher process on a periodic check (autovacuum_naptime, default 1 minute)
• autovacuum_max_workers caps how many tables can be vacuumed concurrently — a large database with many tables needing attention at once can exhaust this pool
• Autovacuum decides per-table, using each table's own accumulated dead-tuple/change count against a threshold, not a global fixed schedule
• Without autovacuum, dead tuples accumulate indefinitely on any actively-written table, and transaction ID wraparound protection is the ultimate, database-halting consequence of statistics/vacuum falling far enough behind
• Autovacuum is enabled by default and disabling it globally is strongly discouraged — it is considered essential infrastructure, not an optional tuning feature

Command

SHOW autovacuum;              -- on (the default)
SHOW autovacuum_max_workers;  -- 3 (the default)
SHOW autovacuum_naptime;      -- 1min (the default check interval)

-- see autovacuum activity per table
SELECT relname, last_autovacuum, last_autoanalyze
  FROM pg_stat_user_tables;

Key features

• The recommended strategy: moderately-frequent routine VACUUM (usually via autovacuum) rather than infrequent VACUUM FULL runs
• VACUUM FULL is appropriate for recovery — a table that has already bloated significantly, often because autovacuum was disabled, misconfigured, or blocked by a long-running transaction for an extended period
• Needing VACUUM FULL repeatedly on the same table is a signal to investigate why routine vacuuming is not keeping up, not a sign to schedule VACUUM FULL as routine maintenance itself
• pg_stat_user_tables' n_dead_tup relative to n_live_tup, tracked over time, is the practical signal for whether routine vacuuming is keeping pace
• A table with a stable, low dead-tuple ratio needs no VACUUM FULL at all, regardless of how much total write activity it has seen historically

Command

-- the routine, steady-state signal to check:
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / GREATEST(n_live_tup, 1), 1) AS dead_pct
  FROM pg_stat_user_tables
 ORDER BY dead_pct DESC
 LIMIT 10;

Key features

• Table bloat: dead tuples not yet reclaimed by VACUUM, inflating the table's physical size beyond its live row count
• Index bloat: a parallel problem on indexes — dead index entries pointing at now-dead row versions also need reclaiming
• Bloat degrades performance even for queries that never touch the dead rows directly, since scans and cache usage both pay for the extra physical size
• Bloat is a moving balance (write activity creates it, VACUUM reclaims it), not a one-time condition — the dead-tuple ratio over time is the practical signal to watch
• High bloat despite regular autovacuum activity usually points to a specific cause: a blocking long-running transaction, undersized thresholds for a hot table, or a workload creating dead tuples faster than autovacuum can keep up

Command

-- the practical bloat signal: dead tuples relative to live ones
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1), 1) AS dead_pct
  FROM pg_stat_user_tables
 ORDER BY n_dead_tup DESC
 LIMIT 10;

Key features

• VACUUM cannot remove a dead tuple if any open transaction's snapshot could still need it — this check is conservative and database-wide, not scoped to tables the long transaction has touched
• pg_stat_activity's backend_xmin column shows the oldest row version a given session's snapshot might still need
• age(backend_xmin) growing large for any session is the direct, queryable signal of this problem
• An idle-in-transaction session (one that opened a transaction and then stopped doing anything) is the most common real-world cause — often an application bug, a forgotten interactive psql session, or a connection pool holding a transaction open
• The fix is always the same: commit, roll back, or terminate the offending session — there is no way to "vacuum around" an open transaction's snapshot

Command

-- find the session(s) holding back the vacuum horizon
SELECT pid, state, age(backend_xmin) AS xmin_age, now() - xact_start AS duration
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
 ORDER BY xmin_age DESC
 LIMIT 10;

Key features

• ANALYZE stores its findings in pg_statistic (and the more readable pg_stats view) — estimated row count, most-common values, histogram boundaries, correlation
• The planner uses these stored statistics, not a live read of the data, to estimate how many rows a WHERE clause will match and choose a plan accordingly
• A large bulk INSERT, a big DELETE, or a substantial shift in a column's value distribution can all leave existing statistics badly stale until the next ANALYZE
• autovacuum triggers ANALYZE automatically based on how many rows have changed since the last one (the analyze threshold/scale factor), similar to how it triggers VACUUM
• Stale statistics are a common, underappreciated cause of "this query used to be fast" reports — nothing about the query or schema changed, only the gap between reality and what the planner believes

Command

SELECT attname, n_distinct, most_common_vals, last_analyze
  FROM pg_stats JOIN pg_stat_user_tables USING (relname)
 WHERE tablename = 'orders' AND attname = 'status';

ANALYZE orders;  -- refresh the statistics explicitly

Key features

• Vacuum threshold formula: autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples (defaults: 50 + 0.1 × row count)
• Analyze threshold formula: the same shape, with its own separate parameters (autovacuum_analyze_threshold, default 50; autovacuum_analyze_scale_factor, default 0.1)
• A table is also always vacuumed once its relfrozenxid age exceeds autovacuum_freeze_max_age, regardless of the dead-tuple threshold — the wraparound-prevention trigger
• Both threshold and scale factor can be overridden per table via storage parameters, which is the standard way to tune a specific hot table more aggressively
• A very large table with the default 0.1 scale factor can accumulate a large ABSOLUTE number of dead tuples before triggering — often the reason to lower the scale factor for specific large, high-churn tables

Command

SHOW autovacuum_vacuum_threshold;      -- 50
SHOW autovacuum_vacuum_scale_factor;   -- 0.1

-- tune a specific hot, large table more aggressively
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000
);