Vacuum at the Page Level
6 hours ago
- #PostgreSQL VACUUM
- #Dead Tuple Cleanup
- #Line Pointer Lifecycle
- VACUUM operates in three phases: heap scan with pruning (phase 1), index cleanup (phase 2), and heap cleanup (phase 3), reclaiming dead tuple space and managing line pointer states.
- Line pointers transition through states (LP_NORMAL, LP_DEAD, LP_UNUSED) depending on indexes and HOT updates; pruning reclaims tuple storage, while index cleanup allows LP_DEAD slots to become LP_UNUSED.
- The free space map (FSM) tracks reclaimable space after VACUUM, enabling new INSERTs to reuse space instead of extending the table file, while the visibility map supports index-only scans and efficient VACUUM passes.
- VACUUM FREEZE marks tuples as frozen to prevent XID wraparound, updating the visibility map's all_frozen bit and advancing the table's freeze horizon.
- Regular VACUUM is lightweight and can truncate trailing empty pages, but VACUUM FULL is required to compact interior free space, albeit with locking and performance trade-offs.