VACUUM & ANALYZE

PostgreSQL's MVCC model means that UPDATE and DELETE operations create dead rows — old versions that are no longer visible to any transaction. Without cleanup, these dead tuples accumulate, waste disk space, and eventually cause a catastrophic event called transaction ID wraparound. VACUUM is the process that reclaims this space.

Why VACUUM Exists

When you update a row:

UPDATE employees SET salary = 90000 WHERE id = 1;

PostgreSQL doesn't overwrite the old row. It:

1. Marks the old row as dead (sets xmax to the current transaction ID)
2. Inserts a new row version with the updated salary

The old row is now a "dead tuple" — invisible to future queries but still occupying disk space. VACUUM removes these dead tuples.

Checking Dead Tuples

-- Monitor dead tuples per table
SELECT
    schemaname,
    tablename,
    n_live_tup    AS live_rows,
    n_dead_tup    AS dead_rows,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

A sessions table with 46% dead rows is a bloat problem — VACUUM should run more often.

VACUUM

-- Vacuum a specific table (marks dead tuples as reusable — doesn't shrink the file)
VACUUM employees;

-- Vacuum with verbose output (shows what it's doing)
VACUUM VERBOSE employees;

-- Vacuum all tables in the current database
VACUUM;

VACUUM (without FULL) reclaims space for reuse by PostgreSQL but does NOT return space to the operating system. The table file size stays the same. This is fine — PostgreSQL reuses the freed space for future inserts.

What VACUUM Does

1. Scans the table for dead tuples
2. Marks their space as available for reuse
3. Updates the visibility map (so future Index Only Scans know which pages are all-live)
4. Advances the oldest transaction ID (preventing wraparound)

VACUUM FULL

-- VACUUM FULL: rewrites the entire table, shrinks file, returns space to OS
VACUUM FULL employees;

VACUUM FULL requires an exclusive lock — the table is completely inaccessible while it runs. Do NOT run VACUUM FULL on busy production tables unless absolutely necessary.

ANALYZE — Updating Statistics

ANALYZE collects statistics about table contents — row counts, value distributions, most common values. PostgreSQL's query planner uses these statistics to estimate costs:

-- Analyze a specific table
ANALYZE employees;

-- Analyze a specific column
ANALYZE employees (salary);

-- Analyze all tables in the database
ANALYZE;

Combined VACUUM ANALYZE

The most common maintenance command — do both at once:

VACUUM ANALYZE employees;
-- Removes dead tuples AND updates statistics in one pass

Autovacuum

PostgreSQL's autovacuum daemon runs VACUUM and ANALYZE automatically in the background. It wakes up periodically and checks if any tables need attention.

Default Autovacuum Thresholds

-- View autovacuum settings
SHOW autovacuum_vacuum_threshold;        -- 50 (minimum dead rows before autovacuum runs)
SHOW autovacuum_vacuum_scale_factor;     -- 0.2 (20% of table rows)
SHOW autovacuum_analyze_threshold;       -- 50
SHOW autovacuum_analyze_scale_factor;    -- 0.1 (10%)

Autovacuum triggers VACUUM when: dead_tuples > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × table_size)

For a table with 100,000 rows:

• VACUUM triggers when: dead_rows > 50 + (0.2 × 100,000) = 20,050
• ANALYZE triggers when: changed_rows > 50 + (0.1 × 100,000) = 10,050

High-Churn Tables Need Tuning

For tables with very high update/delete rates (like sessions or queues), the defaults may be too conservative:

-- Tune autovacuum per table
ALTER TABLE sessions SET (
    autovacuum_vacuum_threshold = 100,       -- lower minimum (quicker response)
    autovacuum_vacuum_scale_factor = 0.01,   -- 1% instead of 20% (more frequent)
    autovacuum_analyze_scale_factor = 0.005  -- 0.5%
);

-- Very aggressive for a hot queue table
ALTER TABLE job_queue SET (
    autovacuum_vacuum_scale_factor = 0.0,
    autovacuum_vacuum_threshold = 500,       -- vacuum every 500 dead rows
    autovacuum_vacuum_cost_delay = 2         -- less sleep between work bursts (ms)
);

Monitoring Autovacuum

-- Check if autovacuum is currently running
SELECT pid, state, query, wait_event
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

-- Check autovacuum history per table
SELECT
    relname,
    n_dead_tup,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables
WHERE last_autovacuum IS NOT NULL
ORDER BY last_autovacuum DESC;

Transaction ID Wraparound — The Critical Risk

PostgreSQL uses 32-bit transaction IDs. After ~2.1 billion transactions, they wrap around. If wraparound happens, PostgreSQL goes into emergency shutdown to prevent data corruption — this is catastrophic.

VACUUM prevents this by advancing the "oldest transaction" horizon. Monitor it:

-- Distance from wraparound for each database (in transactions)
SELECT
    datname,
    age(datfrozenxid) AS txid_age,
    2147483647 - age(datfrozenxid) AS txids_remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

  datname  | txid_age | txids_remaining
-----------+----------+-----------------
 myapp     | 1523400  | 2145960247

PostgreSQL automatically sets databases into single-user warning mode at 10M transactions from wraparound and shuts down at 1M. If you see warning messages about transaction ID age, VACUUM immediately:

-- Emergency vacuum to prevent wraparound
VACUUM FREEZE tablename;
-- FREEZE sets all old transaction IDs to a special "frozen" value that's never considered old

REINDEX

Indexes can become bloated or corrupted. REINDEX rebuilds them:

-- Rebuild a specific index
REINDEX INDEX orders_customer_id_idx;

-- Rebuild all indexes on a table
REINDEX TABLE orders;

-- Rebuild all indexes in the database (slow!)
REINDEX DATABASE myapp;

-- Non-blocking rebuild (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
-- Creates a new index while the old one is still used, then swaps atomically

Quick Maintenance Reference

-- Daily maintenance routine (can be automated with pg_cron)
VACUUM ANALYZE;    -- cleans + updates stats for all tables

-- Check bloat
SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
       n_dead_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- Check table sizes
SELECT tablename, pg_size_pretty(pg_relation_size(tablename::regclass)) AS table_size
FROM pg_stat_user_tables ORDER BY pg_relation_size(tablename::regclass) DESC;

-- Force immediate vacuum on a bloated table
VACUUM VERBOSE ANALYZE sessions;