PostgreSQL implements Multi-Version Concurrency Control (MVCC), meaning old row versions remain in the heap until cleaned up. VACUUM helps maintain performance by:
- Removing dead tuples, freeing space for new data.
- Updating the visibility map, allowing index-only scans to avoid heap fetches.
- Preventing transaction ID wraparound issues by ensuring visibility metadata remains current.
A standard VACUUM marks dead tuples and updates the visibility map, while VACUUM FULL rewrites the table entirely, reclaiming space but requiring an exclusive lock. VACUUM ANALYZE also updates query planner statistics, improving execution plans.