The idea: a page that fills from both ends
A PostgreSQL table is a file of 8 KB pages (blocks). Every page has the same layout: a 24-byte page header, then an array of 4-byte line pointers that grows forward, then free space, then the tuples, which are added from the end of the page backward. The header field pd_lower is where the line pointers end, and pd_upper is where the tuples start. The free space is simply pd_upper − pd_lower.
The page on this page holds the table items (id int PRIMARY KEY, qty int, note char(964)). Every row is exactly 1000 bytes, so eight of them fill a page: 24 + 8 × (4 + 1000) = 8056, leaving 136 bytes. Demo: INSERT fills the page from both ends adds rows until the ninth one has to go to block 1.
ctid: block and line pointer
A row's physical address is its ctid, written (block, line pointer): (0,3) is line pointer 3 of block 0. Indexes store ctids. The line pointer holds the tuple's byte offset and length, so it is an indirection: the tuple can move inside its page, and only the line pointer changes. No index has to know. You can see ctids yourself with SELECT ctid, * FROM items; and the raw page with the pageinspect extension.
| Header field | What it holds |
|---|---|
pd_lsn | The WAL position of the last change to this page. The page may not be written to disk before WAL up to here is flushed. |
pd_lower | The end of the line pointer array: 24 + 4 × number of line pointers. |
pd_upper | The start of the lowest tuple. |
pd_special | The start of a special area at the very end. Index pages keep sibling links there; heap pages have none, so it is 8192. |
pd_prune_xid | The oldest xid that deleted or updated a tuple on this page: a hint that pruning may find something. |
The tuple header
Each tuple starts with a 23-byte header (padded to 24). The fields that matter here:
xmin: the transaction that created this version.xmax: the transaction that deleted or replaced it (0 if none). Oncexmaxhas committed and no snapshot still needs the old version, the tuple is dead.t_ctid: the ctid of this tuple itself, or of the next version after an UPDATE. Readers follow it like a linked list.t_infomask/t_infomask2: flags, including commit hint bits and the two HOT flags below.
How a reader decides which version it may see is the subject of MVCC and Isolation Levels. Here every statement commits at once and nobody else is connected, so a committed xmax means dead.
UPDATE and HOT chains
An UPDATE never changes a tuple in place. It sets xmax on the old version, points the old version's t_ctid at the new one, and writes a new tuple. Normally the new tuple also needs a new entry in every index of the table, even indexes whose columns did not change.
A heap-only tuple (HOT) update avoids that. If no indexed column changed and the new version fits on the same page, the old version gets the flag HEAP_HOT_UPDATED (shown as hot-upd) and the new one HEAP_ONLY_TUPLE (heap-only). No index entry is added. The index keeps pointing at the first version, the root of the chain, and a reader walks the chain through t_ctid until it finds the version it can see (Demo: UPDATE builds a HOT chain).
Pruning: cleaning one page without VACUUM
When a statement reads a page that has dead tuples and little free space (less than 10% of the page, or less than the fillfactor reserve), it first prunes the page (heap_page_prune_opt). Pruning works one HOT chain at a time and changes the state of line pointers:
| Line pointer state | Meaning | When |
|---|---|---|
LP_NORMAL | Points at a tuple (offset + length). | Every live or not-yet-pruned tuple. |
LP_REDIRECT | No tuple; holds the number of another line pointer. | The root of a HOT chain whose first versions are dead: indexes still point at the root, so it stays and redirects to the first live version. |
LP_DEAD | No tuple; must not be reused yet. | The root of a chain with no live version left (a deleted row, or one whose new version went to another page). An index entry still points here. |
LP_UNUSED | Free; the next tuple on this page may take it. | Dead heap-only tuples (no index points at them), and dead roots after VACUUM. |
Then the page is defragmented (PageRepairFragmentation): the live tuples slide to the end of the page and the holes join the free space. Their line pointers get new offsets, so every ctid and every index entry stays valid. Demo: pruning: redirect and defragment shows a chain of four versions shrinking to a redirect and one live tuple.
Why VACUUM is still needed
Pruning cannot touch indexes. A line pointer that an index entry still points at can only become LP_DEAD: if it became unused, a new row could take the slot and the stale index entry would point at the wrong row. VACUUM first deletes the index entries that point at dead line pointers, and only then marks those line pointers LP_UNUSED (Demo: DELETE, VACUUM, the slot is reused: the next INSERT gets the same ctid (0,4) as the deleted row). Since PostgreSQL 14, VACUUM also cuts unused line pointers off the end of the array, which lowers pd_lower. VACUUM also updates the free space map and the visibility map (see Free Space Map and Visibility Map).
A full page: the update leaves the page
A HOT chain never leaves its page. If the page has no room for the new version, even after pruning, the new version goes to another page chosen by the free space map. That update is not HOT: the old version gets an xmax and a t_ctid into the other page, but no hot-upd flag, and every index gets a second entry for the row. Until VACUUM, an index scan for that id visits both entries and finds the old one dead (Demo: full page: the update leaves the page). The statistic n_tup_hot_upd in pg_stat_user_tables shows how many of a table's updates were HOT.
fillfactor: keep room for HOT
The table option fillfactor (default 100) tells INSERT to stop filling a page at that percentage. With fillfactor = 75, INSERT leaves 8192 × 25% = 2048 bytes free on every page. UPDATE ignores the reserve for the page it is already on, so the reserved space is exactly where HOT versions go (Demo: fillfactor 75 keeps room for HOT). The cost is a bigger table: more pages for the same rows. It pays off for tables with many updates to non-indexed columns.
| fillfactor 100 | fillfactor 75 | |
|---|---|---|
| Rows per page by INSERT (1000-byte rows) | 8 | 6 |
| Room for HOT versions on a freshly loaded page | none (136 bytes) | 2 versions (2144 bytes) |
| Pruning starts below | 819 bytes free (10%) | 2048 bytes free |
See also How PostgreSQL Runs a Query for the whole query path, PostgreSQL Shared Buffers for how pages are cached in memory, TOAST for rows too big for a page, PostgreSQL WAL for the log that pd_lsn points into, and PostgreSQL Index Types for the indexes that hold ctids.
InnoDB stores rows in the primary-key B+ tree instead of a heap: see Inside an InnoDB Index Page.
See also Row store vs column store: the same table laid out row by row and column by column, and the pages each query reads.
What the page leaves out
Real rows have different lengths, so tuples are packed by byte, aligned to 8 bytes (MAXALIGN), with a null bitmap in the header; here every tuple is exactly 1000 bytes. There are no concurrent transactions, so a committed xmax always means dead; really, a version stays until no snapshot can see it (the xmin horizon), and an open transaction can block pruning. Commit hint bits in t_infomask, the page checksum, pd_flags (like PD_ALL_VISIBLE), the index's own "killed" bits for dead entries, row locks in xmax, TOAST, the free space map's own structure and WAL record contents are not drawn. The drawing shows two blocks and at most 10 line pointers per page; PostgreSQL allows 291 on an 8 KB page and adds new blocks to the file when all are full.