The cleanup problem
Because MVCC leaves dead tuples behind on every update and delete, tables would grow without bound. VACUUM is the process that finds dead tuples no transaction can see anymore and marks their space reusable.
What vacuum does
- It scans pages and reclaims dead tuple space for future inserts in the same table.
- It updates the visibility map so future queries can skip all visible pages.
- It freezes very old tuples to prevent transaction id wraparound.
A plain VACUUM does not return disk to the operating system. VACUUM FULL rewrites the whole table to shrink it but takes a heavy exclusive lock, so it is rarely used on busy systems.
Autovacuum
You should not run vacuum by hand on a healthy system. The autovacuum daemon watches each table and triggers a vacuum once enough rows have changed, based on a threshold plus a scale factor of the table size.
- Busy tables can be tuned with a lower scale factor so cleanup runs more often.
- A separate autovacuum pass also runs ANALYZE to refresh planner statistics.
Key idea
Vacuum reclaims dead tuple space and prevents wraparound, and autovacuum runs it automatically once a table changes enough, so tuning thresholds matters more than manual cleanup.