Maintenance: VACUUM, Bloat and Statistics

Why VACUUM exists

PostgreSQL never overwrites a row in place. An UPDATE writes a new version and marks the old one dead; a DELETE just marks it dead. This (MVCC) lets readers and writers work without blocking each other. VACUUM reclaims the dead rows so the space can be reused.

Without it, tables bloat (grow with dead rows), queries slow down, and in the extreme case transaction ID wraparound forces the database to stop accepting writes.

Autovacuum

Autovacuum runs automatically. Never turn it off. Check it is keeping up:

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

For large, busy tables the defaults wait too long (20% of the table must be dead). Tune per table:

ALTER TABLE page_views SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

ANALYZE and statistics

The query planner chooses plans based on statistics about your data. After a large import or delete, refresh them:

ANALYZE orders;

Bad plans after a data migration are very often stale statistics.

Long transactions block cleanup

VACUUM cannot remove rows that an open transaction might still see. One forgotten BEGIN in a psql session can block cleanup of the whole database for hours.

SELECT pid, now() - xact_start AS age, state, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY age DESC LIMIT 5;

Protect yourself with timeouts:

ALTER ROLE app_rw SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app_rw SET statement_timeout = '30s';

Fixing severe bloat

VACUUM FULL rewrites the table but locks it completely for the duration, so it is rarely acceptable in production. The pg_repack extension rebuilds tables and indexes online. To rebuild a bloated index without downtime: REINDEX INDEX CONCURRENTLY idx_name;.

A weekly health query set

Keep a saved script that shows: the largest tables and indexes, dead-row counts, unused indexes, the longest-running transactions and the cache hit ratio. Ten minutes a week with it prevents most database emergencies.

Check yourself

• Why do updates create dead rows?
• What can a forgotten open transaction cause?
• Why is VACUUM FULL usually avoided in production?