VACUUM at the Page Level

In HOT Updates in Postgres we covered page pruning clean up HOT chains, an elegant shortcut where PostgreSQL reclaims dead tuple space during ordinary reads. All that without waiting for any background process. But pruning is exactly that: a shortcut. It only works within a single page, and only for HOT-updated tuples. For everything else (cold updates that touch indexed columns, plain DELETEs, index entry cleanup, free space map registration, visibility map maintenance) we need VACUUM. This article won't repeat what VACUUM does operationally. The DELETEs are difficult article covers autovacuum tuning, worker allocation, and the operational side of dead tuple cleanup. Here we are going to watch VACUUM work byte by byte. We'll snapshot a page before and after each phase, tracking exactly what changes in the page header, line pointers, tuple headers, free space map, and visibility map. Same tools as always: pageinspect, pg_visibility, and pg_freespacemap. Setup

We need a table with enough rows to make the before-and-after comparison meaningful, plus indexes to demonstrate the full VACUUM cycle. CREATE EXTENSION IF NOT EXISTS pageinspect; CREATE EXTENSION IF NOT EXISTS pg_visibility; CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

CREATE TABLE vacuum_demo ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, category text NOT NULL, payload text );

INSERT INTO vacuum_demo (category, payload) SELECT 'cat_' || (i % 5), repeat('x', 100) FROM generate_series(1, 50) AS i; Fifty rows with a 100-byte payload each. The primary key gives us an index, which matters: VACUUM's behavior changes when indexes are involved. Run VACUUM once upfront so we start from a clean baseline: VACUUM vacuum_demo;Snapshot before any deletes

Record the baseline state of page 0.

Their t_xmax is now 747, the transaction ID of the DELETE statement. But everything else is unchanged. lp_flags is still 1 (LP_NORMAL). lp_off and lp_len are the same. The tuples are still physically sitting on the page, consuming space. The page header hasn't changed either: SELECT lower, upper, special, pagesize FROM page_header(get_raw_page('vacuum_demo', 0)); lower | upper | special | pagesize -------+-------+---------+---------- 224 | 1392 | 8192 | 8192 (1 row) pd_lower and pd_upper are identical to before the DELETE. PostgreSQL marked the rows as dead (by stamping t_xmax) but did not reclaim a single byte. The dead tuples are bloat, and they will stay that way until VACUUM arrives. How VACUUM processes a table

Before we run VACUUM and watch the page change, one thing about how it runs will explain everything the snapshots are about to show. VACUUM does its work in three passes, and the split between them is the whole reason a deleted tuple's storage disappears at one moment and its line pointer disappears at another. Keep an eye on that gap: it is what the rest of this article makes visible. Phase 1: Heap scan - prune, freeze, collect dead TIDs

VACUUM scans every heap page sequentially, and this first pass does far more than look. For each page, it runs page pruning (the same heap_page_prune_and_freeze machinery that fires opportunistically during ordinary reads). Pruning is where the bytes actually come back: it removes the storage of dead tuples, defragments the page, and advances pd_upper. It also opportunistically freezes tuples that are old enough.

Prior to PostgreSQL 17, VACUUM stored dead TIDs in a flat array allocated from maintenance_work_mem. If the array filled up, VACUUM had to pause, perform index and heap cleanup for the collected batch, then resume scanning. Since PostgreSQL 17, VACUUM uses a radix tree-based TID store that is far more memory-efficient, making maintenance_work_mem much less likely to be a bottleneck.

But here is the subtlety that the rest of this article hinges on. Pruning cannot simply mark a deleted tuple's line pointer LP_UNUSED, because indexes still point at it by TID. So for a table with indexes, a dead tuple's line pointer is set to LP_DEAD: its storage is gone, but the 4-byte slot stays put, holding the TID's place until the index entries are removed. Those LP_DEAD TIDs are what VACUUM collects into its dead-TID store for the next phase. Phase 2: Index cleanup

With the list of dead TIDs in hand, VACUUM scans each index on the table. For every index, it walks through all index entries and removes any that point to a dead TID. This is the expensive part. VACUUM must read every index page, even if only handful of entries need removal. This is also why index bloat happens. If VACUUM cannot finish this phase, because a long-running transaction is holding back the visibility horizon or the table has many indexes and the dead TID list exceeds memory, index entries pointing to dead tuples accumulate. We covered the index bloat implications in detail in VACUUM Is a Lie. Phase 3: Heap cleanup - freeing the line pointers

After the indexes are clean, no index entry references those dead TIDs anymore, so the reserved slots can finally be released. VACUUM revisits each page that had dead tuples and does what it couldn't do in phase 1:

Flips each collected LP_DEAD line pointer to LP_UNUSED (0), reclaiming the slot Sets the visibility map bit if all remaining tuples are visible to everyone

A table with no indexes skips this split entirely: with no index entries to worry about, phase 1's prune sets dead line pointers straight to LP_UNUSED in a single heap pass, and there is no phase 3. The two-pass dance, LP_DEAD now and LP_UNUSED later, exists precisely because of the indexes.

Notice what is not on that list. The tuple data was already removed and the page already defragmented back in phase 1's prune; that is where pd_upper moved. Phase 3 reclaims the line-pointer slots, not the tuple bytes.

The distinction is invisible if you only look before and after plain VACUUM, so let's make it visible. Let's run VACUUM in two steps to catch the intermediate state. VACUUM (INDEX_CLEANUP OFF) performs phase 1's prune but skips index cleanup, and therefore skips the second heap pass too; it has no choice but to leave the dead line pointers as LP_DEAD.

After the prune: LP_DEAD, and space already back

Page header after pruning: SELECT lower, upper, special, pagesize FROM page_header(get_raw_page('vacuum_demo', 0)); lower | upper | special | pagesize -------+-------+---------+---------- 224 | 3568 | 8192 | 8192 (1 row) pd_lower is still 224; the line pointer array hasn't shrunk. But pd_upper jumped from 1392 to 3568.