Pages and tuples · PostgreSQL

Everything PostgreSQL reads or writes is an 8 KiB page. Understanding its layout is what makes the behaviour of MVCC, vacuum and bloat predictable rather than mysterious, and all three are ahead in this course.

The layout

flowchart LR
    subgraph P["One 8 KiB heap page"]
      H["Page header\n24 bytes"] --> L["Line pointers\ngrow forward -->"]
      L --> F["Free space\nin the middle"]
      F --> T["<-- Tuples\ngrow backward"]
    end

The two ends grow toward each other and the free space is what remains between them. pageinspect shows exactly that:

Read-only / Safethe header of a real page— lower is where the line pointers end; upper is where the tuples begin. Captured live.

$ psql -U postgres -c 'SELECT lower, upper, special, pagesize, lsn FROM page_header(get_raw_page('narrow', 0))'

 lower | upper | special | pagesize |    lsn
-------+-------+---------+----------+-----------
 928 |   960 |    8192 |     8192 | 0/32A1F98
(1 row)

lower = 928 and upper = 960 means 32 bytes of free space remain between the line-pointer array and the tuples: this page is essentially full. lsn is the write-ahead log position of the last change to this page, which is the link between a page and the log record that describes it.

Read-only / Safethe line pointers and their tuples— lp_off descends from 8160: tuples are packed from the end of the page backwards.

$ psql -U postgres -c 'SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('narrow', 0)) WHERE lp <= 5'

 lp | lp_off | lp_len | t_xmin | t_xmax | t_ctid
----+--------+--------+--------+--------+--------
1 |   8160 |     32 |    754 |      0 | (0,1)
2 |   8128 |     32 |    754 |      0 | (0,2)
3 |   8096 |     32 |    754 |      0 | (0,3)
4 |   8064 |     32 |    754 |      0 | (0,4)
5 |   8032 |     32 |    754 |      0 | (0,5)
(5 rows)

Each tuple is 32 bytes for a table of two integers — 24 bytes of tuple header plus the data, rounded to an alignment boundary. All five carry t_xmin = 754, the transaction that inserted them, and t_xmax = 0, meaning no transaction has deleted or superseded them.

Read-only / Safehow many rows fit in one page— 226 two-integer rows in 8 KiB. Row width is the dominant factor in scan cost.

$ psql -U postgres -c 'SELECT count(*) AS tuples_on_page_0 FROM heap_page_items(get_raw_page('narrow',0)) WHERE lp_len > 0'

 tuples_on_page_0
------------------
            226
(1 row)

That number is the bridge between schema design and I/O cost. Reading a million rows of this table costs about 4,425 page reads. Double the row width and it costs twice as much, for exactly the same rows.

What an UPDATE actually does

This is the observation the rest of the course is built on. A table with one row, before and after a single UPDATE:

Read-only / Safeone row, before the update— Captured live. One line pointer, one tuple, no successor.

$ psql -U postgres -c 'SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('mvcc_demo',0))'

 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 |    777 |      0 | (0,1)
(1 row)

Read-only / Safethe same page after UPDATE ... SET v = 'changed'— Two tuples now. The original is stamped and points forward.

$ psql -U postgres -c 'SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('mvcc_demo',0))'

 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 |    777 |    778 | (0,2)
2 |    778 |      0 | (0,2)
(2 rows)

Read the change carefully, because every subsequent part of this course depends on it.

The original tuple was not modified in place. Its data is untouched. What changed is its header: t_xmax is now 778, the transaction that superseded it, and its t_ctid now points at (0,2) rather than at itself.

A second tuple appeared at line pointer 2, with t_xmin = 778. That is the new version.

The page now holds two tuples where the table holds one row. The old version remains until vacuum determines that no transaction can still need to see it.

This is why UPDATE-heavy tables grow and why DELETE frees nothing

An UPDATE is, at the page level, an insert plus a stamp on the predecessor. A table updated a million times holds a million dead tuples until vacuum removes them, and the table has grown accordingly.

A DELETE is only the stamp: it sets t_xmax and leaves the tuple in place. Deleting every row in a table frees no disk space at all until vacuum runs, and even then the space is returned to the table for reuse rather than to the filesystem. Part VIII is devoted to that distinction.

The practical consequence for schema design is that a wide, frequently updated table costs more than the same updates on a narrow one, because each update writes a whole new tuple. Splitting a hot, narrow set of columns away from a wide, static set is a real optimisation for exactly this reason.

The tuple header

24 bytes on every tuple, whatever the row contains. The fields an operator reads:

Field	Meaning
`t_xmin`	Transaction that inserted this version
`t_xmax`	Transaction that deleted or superseded it; 0 if live
`t_ctid`	Location of this tuple, or of its successor after an update
`t_infomask`	Flags: committed, aborted, frozen, HOT-updated, and more

t_ctid doing double duty is worth noting: for a current tuple it points at itself, and for a superseded one it points at the next version. Following the chain is how a query that started before an update finds the version it should see.

These are visible without pageinspect as system columns:

SELECT ctid, xmin, xmax, * FROM mvcc_demo;

ctid is a physical address — page number and line pointer — and it changes whenever the row moves. It is emphatically not a stable row identifier, and using it as one is a mistake that survives testing and fails after the first VACUUM FULL.

HOT updates, and why fillfactor exists

The update above created a new tuple on the same page, which raises a question: what happens to the indexes? Every index entry points at a physical location, and the row has moved.

Ordinarily an update must insert a new entry into every index on the table, even for indexes on columns that did not change. That is expensive, and it is why adding an index makes updates slower as well as making reads faster.

Heap-Only Tuple updates avoid it. When an update changes no indexed column and the new version fits on the same page, PostgreSQL links the new tuple to the old one within the page and leaves the indexes alone. Index entries continue to point at the original line pointer, and a lookup follows the in-page chain to reach the current version.

Both conditions matter. If any indexed column changed, the index must be updated, so no HOT. If the page is full, the new tuple goes elsewhere and the indexes must point at it, so no HOT.

The second condition is what fillfactor addresses. It reserves a percentage of each page at insert time, deliberately leaving room for future versions:

ALTER TABLE hot_table SET (fillfactor = 85);
-- applies to pages written from now on; existing pages are unchanged

The trade is direct. A lower fillfactor means fewer rows per page, so scans read more pages, and it means more room for HOT updates, so updates touch fewer indexes and generate less WAL. It is worth setting on a narrow, heavily updated table whose updates do not touch indexed columns, and it is worth leaving alone on an append-mostly table where it would simply waste space.

pg_stat_user_tables reports whether it is working:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd,0), 1) AS hot_pct
  FROM pg_stat_user_tables WHERE n_tup_upd > 0 ORDER BY n_tup_upd DESC;

A low HOT percentage on a heavily updated table is a signal worth following: either the updates touch an indexed column, or the pages have no room.

Production discipline

Read an UPDATE as an insert plus a stamp. Nothing is overwritten, which is why update-heavy tables grow.
Expect DELETE to free nothing until vacuum, and then only for reuse within the table.
Treat row width as an I/O multiplier. 226 narrow rows per page against a handful of wide ones is the difference between a cheap scan and an expensive one.
Never use ctid as a row identifier. It is a physical address that changes whenever the row moves.
Check n_tup_hot_upd against n_tup_upd on hot tables. A low ratio means every update is touching every index.
Consider fillfactor only for narrow, heavily updated tables whose updates avoid indexed columns; elsewhere it wastes space.

Cross-course references

Linux for Production Sysadmins — Part XLI (Disk performance) covers measuring the read cost that row width multiplies.
Ceph & Distributed Storage — Part II (Storage performance) covers what an 8 KiB random read costs on a distributed platform, which is the unit this lesson describes.
Observability for Production Sysadmins — Part LIX (Database observability) covers exporting the HOT-update ratio, which is a leading indicator of update cost.

The layout

What an UPDATE actually does

The tuple header

Production discipline

Cross-course references

Quiz