VACUUM, autovacuum and table bloat

What it is

Under multi-version concurrency control, an update writes a new row version and marks the old one dead once no transaction can see it. VACUUM reclaims dead versions for reuse, updates the visibility map, and freezes old transaction ids to prevent wraparound; ANALYZE refreshes planner statistics. The autovacuum daemon runs both based on thresholds per table.

Why it matters

Without vacuuming, tables and indexes grow (bloat), sequential scans slow down, statistics go stale and plans degrade; in the extreme, the server refuses writes to protect against transaction-id wraparound.

How to apply

• Keep autovacuum enabled; never disable it as a "performance fix".
• Lower autovacuum_vacuum_scale_factor for large, frequently updated tables so that vacuum runs before bloat accumulates.
• Watch n_dead_tup and last-vacuum timestamps in pg_stat_user_tables; alert on tables that fall behind.
• Avoid long-running transactions and idle-in-transaction sessions; they prevent dead rows from being reclaimed.
• Use VACUUM (VERBOSE) or pg_stat_progress_vacuum to inspect, and REINDEX or pg_repack for already-bloated indexes.

Pitfalls

VACUUM FULL rewrites the table and takes an exclusive lock; schedule it deliberately. Very large deletes are better done in batches. Statistics targets may need raising for skewed columns.