Inspect Storage Internals with pageinspect and Connect Physical Evidence to SQL Behavior

Learning outcomes

This final lab treats pageinspect as a microscope. ServiceHub will insert three rows, update one non-indexed attribute, delete another row, VACUUM the page, and repeatedly compare three views of reality: ordinary SQL, tuple system columns, and raw-page metadata. The goal is not to memorize flag bits; it is to learn how to form and verify a storage hypothesis without turning internals into an application dependency.

1. Prepare a quiet, disposable page story

Run extension installation/raw-page functions as a local superuser. Make sure no long transaction from Chapter 07 is still holding a snapshot in this database.

CREATE EXTENSION IF NOT EXISTS pageinspect;CREATE EXTENSION IF NOT EXISTS pg_visibility;DROP TABLE IF EXISTS app.ch08_page_story;CREATE TABLE app.ch08_page_story (    id integer PRIMARY KEY,    status text NOT NULL,    note text NOT NULL) WITH (fillfactor = 70);INSERT INTO app.ch08_page_story(id, status, note) VALUES(1, 'open',   repeat('a', 80)),(2, 'open',   repeat('b', 80)),(3, 'closed', repeat('c', 80));

Three small rows leave abundant room on a standard heap page, making a non-indexed update to note a strong HOT candidate. Still, we will prove the result from flags/statistics rather than merely assume it.

2. Snapshot A: SQL rows and raw page after INSERT

SELECT id, status, ctid, xmin::text, xmax::textFROM app.ch08_page_storyORDER BY id;

SELECT h.lp, h.lp_flags, h.lp_len,       h.t_xmin::text, h.t_xmax::text, h.t_ctid,       f.raw_flags, f.combined_flagsFROM heap_page_items(get_raw_page('app.ch08_page_story', 0)) AS hLEFT JOIN LATERAL heap_tuple_infomask_flags(h.t_infomask, h.t_infomask2) AS f  ON h.t_infomask IS NOT NULL OR h.t_infomask2 IS NOT NULLORDER BY h.lp;

At this moment, SQL should show three visible rows. Raw-page output has one or more active line pointers corresponding to tuple versions. Exact xmin, offsets and flag arrays are dynamic. The important invariant is that raw storage can contain more lifecycle information than a SELECT result exposes.

3. Make an eligible non-indexed update and look for HOT evidence

UPDATE app.ch08_page_storySET note = note || '-updated'WHERE id = 2RETURNING id, ctid, xmin::text, xmax::text;SELECT pg_stat_clear_snapshot();SELECT n_tup_upd, n_tup_hot_updFROM pg_stat_user_tablesWHERE relid = 'app.ch08_page_story'::regclass;

SELECT h.lp, h.lp_flags, h.t_xmin::text, h.t_xmax::text, h.t_ctid,       f.raw_flags, f.combined_flagsFROM heap_page_items(get_raw_page('app.ch08_page_story', 0)) AS hLEFT JOIN LATERAL heap_tuple_infomask_flags(h.t_infomask, h.t_infomask2) AS f  ON h.t_infomask IS NOT NULL OR h.t_infomask2 IS NOT NULLORDER BY h.lp;

On this deliberately roomy page, the update is designed to be HOT-eligible. The cumulative HOT counter can lag briefly, so do not treat an immediately unchanged counter as proof that HOT failed. The raw-page evidence is more direct for this disposable diagnostic: in decoded flags you can commonly observe a root version marked HOT-updated and a successor marked heap-only, with the root's t_ctid pointing along the chain. Exact flags remain implementation details. Do not make application code parse them; use them to connect physical evidence to the supported cumulative HOT statistics.

4. DELETE changes visibility before VACUUM reclaims anything

DELETE FROM app.ch08_page_storyWHERE id = 3;SELECT id, status, ctid, xmin::text, xmax::textFROM app.ch08_page_storyORDER BY id;SELECT lp, lp_flags, t_xmin::text, t_xmax::text, t_ctidFROM heap_page_items(get_raw_page('app.ch08_page_story', 0))ORDER BY lp;

Ordinary SQL now returns two rows. The raw page can still contain tuple bytes/header state for the deleted version because DELETE does not immediately compact the page. Its xmax participates in the retirement record; Chapter 07's warning still applies—do not treat raw xmax as a universal human-readable deletion audit field.

5. Visibility map before/after VACUUM

SELECT *FROM pg_visibility_map_summary('app.ch08_page_story'::regclass);VACUUM (VERBOSE, ANALYZE) app.ch08_page_story;SELECT *FROM pg_visibility_map_summary('app.ch08_page_story'::regclass);SELECT id, status, ctid, xmin::text, xmax::textFROM app.ch08_page_storyORDER BY id;

After VACUUM on a quiet disposable relation, the page can become all-visible and dead storage can be pruned/reused. The logical result remains rows 1 and 2. VM state is an optimization/maintenance fact; SQL correctness does not mean applications need to know the bit.

6. Snapshot B: line pointers after pruning/reuse

SELECT h.lp, h.lp_off, h.lp_flags, h.lp_len,       h.t_xmin::text, h.t_xmax::text, h.t_ctid,       f.raw_flags, f.combined_flagsFROM heap_page_items(get_raw_page('app.ch08_page_story', 0)) AS hLEFT JOIN LATERAL heap_tuple_infomask_flags(h.t_infomask, h.t_infomask2) AS f  ON h.t_infomask IS NOT NULL OR h.t_infomask2 IS NOT NULLORDER BY h.lp;

You may see unused or redirect line-pointer states depending on pruning and HOT-chain state. Exact physical shape can vary with timing/version. The safe conclusion is comparative: VACUUM/pruning can change physical page organization after old versions become globally unnecessary, without changing the surviving logical rows.

7. A write can clear all-visible knowledge again

UPDATE app.ch08_page_storySET status = 'in_progress'WHERE id = 1;SELECT *FROM pg_visibility_map_summary('app.ch08_page_story'::regclass);

This reconnects page lifecycle to Lesson 1. A visibility map is conservative and maintained as data changes. Do not treat a temporary mismatch observed by sophisticated diagnostics as proof of corruption without following PostgreSQL's documented integrity-check procedures and considering concurrency/crash timing.

8. Compare page-header free-space boundaries across the lifecycle

The page header's lower and upper offsets provide another physical perspective. Inserts extend the line-pointer array and consume tuple space; pruning can recover tuple space; the precise offsets depend on alignment and tuple representation. Capture them at checkpoints rather than deriving application limits from them.

SELECT lower, upper, special, pagesize, prune_xidFROM page_header(get_raw_page('app.ch08_page_story', 0));

The free region is conceptually between lower and upper, but the FSM stores rounded/approximate free-space knowledge for allocation decisions. These structures serve related purposes but are not required to match byte-for-byte at all times.

9. Physical evidence must preserve logical invariants

A useful low-level investigation always returns to business-visible checks. After each physical observation, verify that the surviving ServiceHub rows satisfy their declared key and state rules. The most impressive raw-page dump is worthless if the diagnostic story contradicts ordinary SQL without an explanation of snapshot/timing.

SELECT count(*) AS visible_rows,       count(*) FILTER (WHERE id = 1 AND status = 'in_progress') AS row1_ok,       count(*) FILTER (WHERE id = 2) AS row2_present,       count(*) FILTER (WHERE id = 3) AS row3_absentFROM app.ch08_page_story;SELECT id, count(*)FROM app.ch08_page_storyGROUP BY idHAVING count(*) > 1;

The final query should return no duplicate IDs because the primary key is the logical integrity contract. Page locations and line-pointer histories are subordinate evidence.

10. Wrong approach: make a monitoring agent parse raw pages continuously

A production agent that scrapes every heap page would require elevated privileges, consume I/O/CPU, depend on internal formats and duplicate higher-level statistics. The safer monitoring hierarchy is:

1. business invariants and ordinary SQL results;
2. supported catalog/statistics/size/progress views;
3. supplied diagnostic extensions such as pgstattuple/pg_visibility;
4. pageinspect only when a specific low-level hypothesis justifies it.

11. Final cleanup

DROP TABLE IF EXISTS app.ch08_page_story;

12. Chapter summary and bridge to Chapter 09

Chapter 08 connected SQL writes to physical storage without crossing the application/diagnostic boundary. Heap pages contain line pointers and tuple versions; TOAST keeps oversized attributes manageable; HOT can avoid ordinary index churn for eligible same-page updates; VACUUM turns dead versions into reusable space; rewrites are different from cleanup; and raw pages are evidence, not schema. Chapter 09 builds directly on this lifecycle with autovacuum, freezing, transaction-ID wraparound protection, visibility maintenance, and vacuum tuning.

Authoritative references

• PostgreSQL 18 — pageinspect
• PostgreSQL 18 — Heap-Only Tuples
• PostgreSQL 18 — Visibility Map
• PostgreSQL 18 — pg_visibility
• PostgreSQL 18 — Routine Vacuuming
• PostgreSQL 18 — System Columns