PostgreSQL VACUUM's Page-Level Byte-by-Byte Inspection

This piece walks through exactly what PostgreSQL's VACUUM does at the page level, using pageinspect, pg_visibility, and pg_freespacemap to capture before-and-after snapshots. Unlike HOT pruning, which runs opportunistically during ordinary reads but only reclaims space within a single page for HOT-updated tuples, VACUUM handles everything pruning can't: cold updates touching indexed columns, plain DELETEs, index entry cleanup, and visibility/free-space map maintenance. The article demonstrates concretely that a DELETE only stamps a tuple's t_xmax — not a single byte of page space is reclaimed until VACUUM actually runs.

The core technical insight is VACUUM's three-phase design. Phase 1 scans the heap, prunes dead tuples, advances pd_upper, and opportunistically freezes eligible tuples — but for indexed tables, dead line pointers are set to LP_DEAD rather than LP_UNUSED, because index entries still reference those TIDs. Phase 2 scans every index to remove entries pointing at dead TIDs; this is the most expensive step, and when long-running transactions or memory limits prevent it from completing, index bloat accumulates. Phase 3 finally flips the collected LP_DEAD pointers to LP_UNUSED once no index references remain, freeing the slots and updating the visibility map.

Since PostgreSQL 17, the flat array once used to store dead TIDs has been replaced by a radix-tree-based structure, substantially reducing the risk of maintenance_work_mem becoming a bottleneck. Tables without indexes skip the phase split entirely, since dead pointers can go straight to LP_UNUSED in a single pass. These mechanics matter for engineers tuning autovacuum or debugging index bloat, since they explain precisely why a dead tuple's storage and its line pointer disappear at different moments.