SQL VACUUM

VACUUM is a maintenance command that scans heap tables, removes dead tuples left by UPDATE and DELETE operations, and marks the freed pages as available for reuse. In PostgreSQL it can also update visibility maps, freeze transaction IDs to prevent wraparound, and optionally gather statistics (ANALYZE) so the query planner stays accurate. A standard VACUUM runs in the background with minimal locks, while VACUUM FULL rewrites the entire table, fully compacting it but requiring an exclusive lock. AUTOVACUUM generally handles routine cleanup, yet manual VACUUM remains essential after bulk loads, mass deletes, disabling autovacuum, or before creating critical backups. SQLite supports a different but related VACUUM that rebuilds the entire database file to defragment and shrink it.

• • FULL (keyword) - Perform a full table rewrite, reclaiming maximum space but taking an exclusive lock.
• • FREEZE (keyword) - Aggressively freeze tuples to avoid transaction ID wraparound.
• • VERBOSE (keyword) - Output detailed progress messages.
• • ANALYZE (keyword) - Run ANALYZE on the table after vacuuming.
• • table_name (identifier) - Optional table (and column list) to target; omit for all tables.
• - INDEX_CLEANUP { ON - OFF }
• - PARALLEL integer - Number of parallel workers.
• - SKIP_LOCKED - Skip tables that cannot be locked immediately.

• Dead rows are removed, page space is marked reusable, visibility maps are updated, and (if ANALYZE) planner statistics are refreshed
• VACUUM returns an OK message and, with VERBOSE, a per-table summary of pages scanned, tuples removed, and CPU time

• After bulk DELETE or TRUNCATE operations to immediately free disk space.
• Before major version upgrades or pg_dump to minimize backup size.
• Prevent transaction ID wraparound on very busy systems via FREEZE.
• Improve query performance by updating statistics when autovacuum is disabled or lagging.
• In SQLite, shrink the .db file after massive data churn.

• Assuming autovacuum is always enough - heavy workloads may still need manual runs.
• Using VACUUM FULL on large production tables during peak hours, causing long locks.
• Forgetting to ANALYZE after disabling autovacuum, leading to poor planner choices.
• Confusing PostgreSQL VACUUM with SQLite VACUUM; the options differ.