Heap Pages, Line Pointers, Tuple Headers, Free Space, and Visibility Map

Learning outcomes

ServiceHub has an update-heavy work-order table. A developer notices that two rows with neighboring IDs are not necessarily adjacent on disk, a DBA sees a visibility-map change after one small update, and an application prototype tries to persist ctid as a row identifier. These are three versions of the same mistake: treating PostgreSQL's physical heap layout as if it were the relational model.

1. From a relation to forks, blocks, line pointers, and tuples

A PostgreSQL table is a relation. The relation's ordinary row storage lives in its main fork. PostgreSQL can maintain additional forks: a Free Space Map (FSM) recording approximate reusable space, a Visibility Map (VM) recording page-level all-visible/all-frozen information, and an initialization fork for unlogged relations. Not every fork exists for every relation at every moment.

The main fork is divided into fixed-size blocks, usually 8 KiB in standard builds. Do not hard-code 8192 in tooling: ask the server. A heap data page contains a page header, an array of item identifiers (often called line pointers), free space, heap tuple data, and no special index-style area at the end. A ctid identifies a tuple version as (block number, item/offset number).

2. Create the diagnostic tools and a disposable heap

Connect to servicehub_lab as your local PostgreSQL superuser only for extension installation/raw-page inspection. Create application objects as the ServiceHub owner. Do not grant pageinspect execution to the application role merely to make the lab convenient.

SELECT version();SELECT name, default_version, installed_versionFROM pg_available_extensionsWHERE name IN ('pageinspect', 'pg_visibility', 'pg_freespacemap')ORDER BY name;CREATE EXTENSION IF NOT EXISTS pageinspect;CREATE EXTENSION IF NOT EXISTS pg_visibility;CREATE EXTENSION IF NOT EXISTS pg_freespacemap;SHOW block_size;

DROP TABLE IF EXISTS app.ch08_heap_probe;CREATE TABLE app.ch08_heap_probe (    work_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    work_code text NOT NULL UNIQUE,    status text NOT NULL,    note text NOT NULL,    touched_at timestamptz NOT NULL DEFAULT clock_timestamp()) WITH (fillfactor = 80);INSERT INTO app.ch08_heap_probe(work_code, status, note)SELECT 'WO-PAGE-' || lpad(g::text, 4, '0'),       CASE WHEN g % 3 = 0 THEN 'closed' ELSE 'open' END,       repeat('diagnostic-', 8)FROM generate_series(1, 200) AS g;ANALYZE app.ch08_heap_probe;

Identity and unique indexes exist, so the table has heap storage plus indexes. The selected fillfactor leaves room during initial page packing, but it is not a promise about exact page counts.

3. Observe fork sizes before touching raw bytes

SELECT pg_size_pretty(pg_relation_size('app.ch08_heap_probe'::regclass, 'main')) AS main,       pg_size_pretty(pg_relation_size('app.ch08_heap_probe'::regclass, 'fsm'))  AS fsm,       pg_size_pretty(pg_relation_size('app.ch08_heap_probe'::regclass, 'vm'))   AS vm,       pg_size_pretty(pg_table_size('app.ch08_heap_probe'::regclass))           AS table_with_toast_forks,       pg_size_pretty(pg_indexes_size('app.ch08_heap_probe'::regclass))         AS indexes,       pg_size_pretty(pg_total_relation_size('app.ch08_heap_probe'::regclass))  AS total;

Exact byte counts are local observations. A zero-byte FSM/VM at one point does not mean PostgreSQL has no such concept; those forks are created and extended as needed. pg_table_size includes the table's auxiliary forks and TOAST relation when present, whereas pg_relation_size(...,'main') isolates one fork.

4. Free-space map evidence is approximate on purpose

The FSM is a search aid: PostgreSQL needs a fast way to find pages likely to have enough room for an incoming tuple version. The supplied pg_freespacemap extension exposes the amount recorded for each page. PostgreSQL documents that these values are rounded and are not kept perfectly current, so they are appropriate for understanding page-selection behavior, not byte-perfect accounting.

SELECT blkno, availFROM pg_freespace('app.ch08_heap_probe'::regclass)ORDER BY blknoLIMIT 12;

Compare the FSM value with page_header() only as two different observations. The page header reflects one copied page at one moment; the FSM is approximate metadata optimized for locating candidate pages. A mismatch within this model is not corruption.

5. Read page 0 with pageinspect

get_raw_page() returns a time-consistent copy of one database page as bytea; page_header() decodes generic header fields. This does not bypass MVCC into a supported application API—it is a debugging tool.

SELECT *FROM page_header(get_raw_page('app.ch08_heap_probe', 'main', 0));

lsn      | checksum | flags | lower | upper | special | pagesize | version | prune_xid---------+----------+-------+-------+-------+---------+----------+---------+----------<dynamic> | <...>    | <...> | <...> | <...> | <...>   | <block>  | <...>   | <...>

lower advances with the line-pointer array; upper marks the beginning of tuple data/free-space boundary. The difference between these offsets is related to currently available page space, but treating raw offsets as a long-term monitoring API would be brittle.

6. Line pointers and tuple headers are not the same as SELECT rows

SELECT lp, lp_off, lp_flags, lp_len,       t_xmin::text, t_xmax::text, t_ctid,       t_infomask, t_infomask2FROM heap_page_items(get_raw_page('app.ch08_heap_probe', 0))ORDER BY lp;

heap_page_items() reports every line pointer and tuple header found in the copied page, including tuple versions that might not be visible to an ordinary MVCC snapshot. Contrast that with a normal query:

SELECT work_id, work_code, status, ctid, xmin::text, xmax::textFROM app.ch08_heap_probeORDER BY work_idLIMIT 8;

Chapter 07 already established that xmin/xmax require transaction-state interpretation. Page inspection adds physical location and flags, not a replacement visibility engine. If you need to know what the current SQL snapshot sees, ask SQL.

7. Visibility map: a page-level optimization fact, not row security

The visibility map tracks whether a heap page is known to contain only tuples visible to all current and future transactions, and whether all tuples are frozen. VACUUM maintains it. The all-visible bit lets an index-only scan sometimes avoid a heap visit because indexes do not themselves carry MVCC visibility information.

SELECT *FROM pg_visibility_map_summary('app.ch08_heap_probe'::regclass);VACUUM (ANALYZE) app.ch08_heap_probe;SELECT *FROM pg_visibility_map_summary('app.ch08_heap_probe'::regclass);

The exact counts depend on the table's pages and concurrent activity. After a vacuum on this quiet disposable table, more pages are commonly marked all-visible. Modify one row and inspect again:

UPDATE app.ch08_heap_probeSET note = note || ' changed'WHERE work_id = 1;SELECT *FROM pg_visibility_map_summary('app.ch08_heap_probe'::regclass);

The bit is conservative metadata: a write to a page can clear its all-visible status. It is not an authorization flag and does not mean every query can read every row.

8. Wrong approach: CTID as a primary key

SELECT work_id, ctidFROM app.ch08_heap_probeWHERE work_id = 2;UPDATE app.ch08_heap_probeSET note = note || ' v2'WHERE work_id = 2RETURNING work_id, ctid;

The returned ctid identifies the new tuple version. It can differ even for a logically unchanged key. HOT chains, VACUUM pruning, VACUUM FULL, CLUSTER, table rewrites, and other operations make physical identity unsuitable for durable references.

9. Reproducible verification and cleanup

SELECT relname, relpages, reltuplesFROM pg_classWHERE oid = 'app.ch08_heap_probe'::regclass;SELECT count(*) AS visible_rows,       min(work_id) AS min_id,       max(work_id) AS max_idFROM app.ch08_heap_probe;SELECT *FROM pg_visibility_map_summary('app.ch08_heap_probe'::regclass);

relpages/reltuples are catalog statistics, not an exact live row inventory. Keep the distinction between estimates, logical query results, and raw-page evidence explicit.

DROP TABLE IF EXISTS app.ch08_heap_probe;-- Leave pageinspect/pg_visibility installed if this database is reused by later labs.-- If the whole database is disposable, dropping that database cleans them too.

10. Production judgment and bridge

Use page internals to explain a symptom, validate a hypothesis, or learn the engine—not as an application schema. Prefer supported statistics and size functions for routine monitoring, and restrict raw-page tools tightly. Lesson 2 now follows the other consequence of fixed-size heap pages: what PostgreSQL does when one attribute is too large to remain comfortably inline.

Authoritative references

• PostgreSQL 18 — Database Physical Storage
• PostgreSQL 18 — Database Page Layout
• PostgreSQL 18 — Free Space Map
• PostgreSQL 18 — pg_freespacemap
• PostgreSQL 18 — Visibility Map
• PostgreSQL 18 — pageinspect
• PostgreSQL 18 — pg_visibility
• PostgreSQL 18 — Database Object Size Functions