Files, pages and row versions

From table to file

A table looks like rows and columns, but on disk it is a plain file. PostgreSQL can tell you which:

SELECT pg_relation_filepath('customers') AS file, pg_relation_size('customers') AS bytes;

       file        | bytes
-------------------+-------
 base/16741/327683 |  8192

The path is relative to the server’s data directory: database number 16741, file number 327683. Yours will differ. The file is 8,192 bytes: exactly one page, because all eight customers fit on it. When a table passes 1 GB, PostgreSQL starts a second file, 327683.1, and so on. Each table also gets two small companion files, a free space map that remembers which pages have room, and a visibility map, which Heap access and the visibility map covers.

Inside a page

Every page is 8 kB and has the same shape:

The line pointers grow forward from the front, the rows grow backward from the end, and the page is full when they meet. A row’s address, its ctid, is (page, line pointer): (0,3) means page 0, third pointer. Because indexes store ctids, a row can move within a page by updating its pointer, without touching the indexes.

Rows are versions

The pageinspect extension shows the raw contents of a page. Make a small table with three rows:

CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE TABLE notes (id int PRIMARY KEY, body text);
INSERT INTO notes VALUES (1, 'buy milk'), (2, 'call Ben'), (3, 'fix the leaky tap');

SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('notes', 0));

 lp | lp_off | lp_len | t_xmin | t_xmax | t_ctid
----+--------+--------+--------+--------+--------
  1 |   8152 |     37 |   3106 |      0 | (0,1)
  2 |   8112 |     37 |   3106 |      0 | (0,2)
  3 |   8064 |     46 |   3106 |      0 | (0,3)

lp_off counts down from 8,192: the first row sits at the very end of the page. t_xmin is the transaction that inserted each row (yours will be different numbers), and t_xmax is 0: nothing has replaced them.

 lp | lp_off | lp_len | t_xmin | t_xmax | t_ctid
----+--------+--------+--------+--------+--------
  1 |   8152 |     37 |   3106 |   3107 | (0,4)
  2 |   8112 |     37 |   3106 |      0 | (0,2)
  3 |   8064 |     46 |   3106 |      0 | (0,3)
  4 |   8016 |     41 |   3107 |      0 | (0,4)

A DELETE works the same way with nothing to replace it: it only sets t_xmax. Deleting note 2 leaves its row on the page with t_xmax set, and the table stays 8,192 bytes. Old and deleted versions stay until vacuum decides nobody can see them any more and marks their space as free for new rows.

Check yourself

1. What is a ctid, and why does it change when a row is updated?

   Show answer

   It is the row version’s physical address: page number and line pointer. An update writes a new version in a new slot, so the logical row’s current version has a new ctid. Use primary keys, not ctids, to identify rows.

   I could explain this without looking

2. Why does a page fill up from both ends?

   Show answer

   Line pointers are added from the front and row data from the back, so the free space in the middle shrinks from both sides. The page is full when they meet.

   I could explain this without looking

3. After UPDATE notes SET body = ... WHERE id = 1, why is the old row version still on the page?

   Show answer

   PostgreSQL never overwrites a row in place. It writes a new version and marks the old one with the updating transaction in xmax, so transactions that should still see the old value can. Vacuum removes it later.

   I could explain this without looking