VACUUM at the Page Level | boringSQL

Guide<br>Chapter 7 of 7 in PostgreSQL Storage Internals

Table of Contents

Setup

Snapshot before any deletes

Create dead tuples

How VACUUM processes a table

Phase 1: Heap scan - prune, freeze, collect dead TIDs

Phase 2: Index cleanup

Phase 3: Heap cleanup - freeing the line pointers

After the prune: LP_DEAD, and space already back

After the full VACUUM: LP_UNUSED

The line pointer lifecycle, precisely

Free space map

Visibility map

Freezing

VACUUM FULL vs regular VACUUM

Putting it all together

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;<br>CREATE EXTENSION IF NOT EXISTS pg_visibility;<br>CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

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

INSERT INTO vacuum_demo (category, payload)<br>SELECT<br>'cat_' || (i % 5),<br>repeat('x', 100)<br>FROM generate_series(1, 50) AS i;<br>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<br>FROM page_header(get_raw_page('vacuum_demo', 0)); lower | upper | special | pagesize<br>-------+-------+---------+----------<br>224 | 1392 | 8192 | 8192<br>(1 row)<br>pd_lower is at 224: that's the 24-byte page header plus 50 line pointers at 4 bytes each (24 + 200 = 224). pd_upper is at 1392, so our tuples occupy bytes 1392 through 8191. Free space is 1392 - 224 = 1168 bytes. Not much room left; those 100-byte payloads add up.

Now the line pointers and tuple headers:

SELECT lp, lp_flags, lp_off, lp_len, t_xmin, t_xmax, t_ctid<br>FROM heap_page_items(get_raw_page('vacuum_demo', 0))<br>LIMIT 10; lp | lp_flags | lp_off | lp_len | t_xmin | t_xmax | t_ctid<br>----+----------+--------+--------+--------+--------+--------<br>1 | 1 | 8056 | 135 | 746 | 0 | (0,1)<br>2 | 1 | 7920 | 135 | 746 | 0 | (0,2)<br>3 | 1 | 7784 | 135 | 746 | 0 | (0,3)<br>4 | 1 | 7648 | 135 | 746 | 0 | (0,4)<br>5 | 1 | 7512 | 135 | 746 | 0 | (0,5)<br>6 | 1 | 7376 | 135 | 746 | 0 | (0,6)<br>7 | 1 | 7240 | 135 | 746 | 0 | (0,7)<br>8 | 1 | 7104 | 135 | 746 | 0 | (0,8)<br>9 | 1 | 6968 | 135 | 746 | 0 | (0,9)<br>10 | 1 | 6832 | 135 | 746 | 0 | (0,10)<br>(10 rows)<br>lp_len is 135, the tuple's actual byte length, but each tuple occupies MAXALIGN'd slot of 136 bytes on the page; notice the lp_off values step down by 136. That aligned stride is what the free-space arithmetic below uses.

Every line pointer is LP_NORMAL (lp_flags = 1). Every tuple has t_xmax = 0: nobody has touched these rows since they were inserted. Every t_ctid points to itself. This is a perfectly clean page.

Create dead tuples

Now make some rows dead:

DELETE FROM vacuum_demo WHERE id % 3 = 0;DELETE 16<br>That deletes roughly every third row: IDs 3, 6, 9, 12, and so on. Sixteen rows are now dead. Look at the page before VACUUM runs:

SELECT lp, lp_flags, lp_off, lp_len, t_xmin, t_xmax, t_ctid<br>FROM heap_page_items(get_raw_page('vacuum_demo', 0))<br>LIMIT 10; lp | lp_flags | lp_off | lp_len | t_xmin | t_xmax | t_ctid<br>----+----------+--------+--------+--------+--------+--------<br>1 | 1 | 8056 | 135 | 746 | 0 | (0,1)<br>2 | 1 | 7920 | 135 | 746 | 0 | (0,2)<br>3 | 1 | 7784 | 135 | 746 | 747 | (0,3)<br>4 | 1 | 7648 | 135 | 746 | 0 | (0,4)<br>5 | 1 | 7512 | 135 | 746 | 0 | (0,5)<br>6 | 1 | 7376 | 135 | 746 | 747 | (0,6)<br>7 | 1 | 7240 | 135 | 746 | 0 | (0,7)<br>8 | 1 | 7104 | 135 | 746 | 0 | (0,8)<br>9 | 1 | 6968 | 135 | 746 | 747 | (0,9)<br>10 | 1 | 6832 | 135 | 746 | 0 | (0,10)<br>(10 rows)<br>Look at rows 3, 6, and 9. 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...