Vacuum and Autovacuum

Every UPDATE and DELETE in PostgreSQL leaves the old row version in place. Vacuum reclaims those dead versions, updates the visibility map, and advances the frozen transaction horizon. It is not optional maintenance — it is part of how the storage engine works.

What vacuum does

1. Reclaims dead tuples so their space can be reused by new rows. Ordinary VACUUM does not return space to the filesystem; it makes it reusable within the table.
2. Updates the visibility map, which is what makes index-only scans possible.
3. Freezes old transaction IDs, preventing wraparound.
4. Updates statistics when run as ANALYZE.

Tuning autovacuum

The defaults are scaled for small tables. autovacuum_vacuum_scale_factor = 0.2 means a vacuum starts after 20% of the table is dead — on a 500-million-row table that is 100 million dead rows before anything happens.

Set per-table thresholds for large or high-churn tables:

ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor  = 0.01,   -- 1% instead of 20%
    autovacuum_vacuum_threshold     = 5000,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay    = 0       -- do not throttle this table
);

Globally, the throttle is usually the binding constraint. autovacuum_vacuum_cost_limit defaults to 200, which on modern storage is far slower than the hardware allows:

autovacuum_max_workers = 6
autovacuum_vacuum_cost_limit = 2000
autovacuum_naptime = 15s
log_autovacuum_min_duration = 0

Table Bloat

Bloat is space occupied by dead tuples and empty pages. It makes scans read more pages, inflates indexes, and increases backup size.

Three things prevent vacuum from removing dead rows, and all three are transaction-horizon problems:

• Long-running transactions. Vacuum cannot remove a row version that any open snapshot might still need. One session left idle in transaction blocks cleanup across the entire database.
• Abandoned replication slots. An inactive slot holds back the horizon (and retains WAL).
• Long queries on a hot standby with hot_standby_feedback = on, which deliberately holds the primary's horizon back to keep the query valid.

-- What is holding the horizon back, oldest first.
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 10;
 
SELECT slot_name, active, restart_lsn FROM pg_replication_slots WHERE NOT active;
 
-- Dead tuple counts per table.
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC;

Removing existing bloat

Transaction ID wraparound

Transaction IDs are 32-bit and wrap. PostgreSQL freezes old rows to keep them visible; if freezing falls too far behind, the server starts warning, then refuses new transactions to protect data.

-- Age of the oldest unfrozen transaction per table.
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(relfrozenxid) DESC LIMIT 10;

Alert on this well before autovacuum_freeze_max_age (200 million by default). Reaching the wraparound limit is one of the few PostgreSQL failures that stops writes entirely, and recovering requires a vacuum that can take many hours.