GoKawiilIn 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. First the page header:
SELECT lower, upper, special, pagesize FROM page_header(get_raw_page( 'vacuum_demo' , 0 ));
... continue reading