Storage Layout

Run this first — it spins up an actual Postgres (WASM) and creates a table we'll inspect throughout the lesson. The first run downloads the engine, so give it a second.

-- a real Postgres, right here in the page
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id      int,
owner   text,
balance numeric
);
INSERT INTO accounts
SELECT g, 'user_' || g, g * 100
FROM generate_series(1, 8) AS g;

SELECT * FROM accounts ORDER BY id;

A table is stored in a file, and Postgres reads and writes that file one page at a time. You can even see where it lives and how big it is:

SHOW block_size;                         -- 8192, the page size (BLCKSZ)
SELECT pg_relation_filepath('accounts')  AS file,
     pg_relation_size('accounts')      AS bytes;  -- one 8KB page so far

Inside one page

Pick a row size below and insert rows. Watch the page fill from both directions at once: a small line-pointer array grows downward from just under the header, while the tuples (your rows) are written upward from the bottom. The empty middle is free space — they grow toward each other until they meet, and the next row spills onto a fresh page.

That two-ended design is real. The header records two offsets, pd_lower and pd_upper; the gap between them is the free space. Insert a row and pd_lower moves down by 4 bytes (a new line pointer) while pd_upper moves up by the tuple's size.

The line pointer, and what a ctid really is

Every row's address is its ctid — a pair (block, offset). The block is which 8 KB page; the offset is which line pointer (1-based), not a byte position. Here are the real ctids of our rows:

SELECT ctid, id, owner FROM accounts ORDER BY id;

A line pointer is a tiny 4-byte slot (ItemIdData) that stores where the tuple actually sits on the page, plus a few status bits. That indirection is the whole trick: Postgres can move a tuple within its page during cleanup and only update the pointer — the ctid other parts of the system hold never changes.

The tuple header — and a sneak peek at MVCC

Each tuple carries a 23-byte header before its column data. Two of its fields decide who can see the row: xmin (the transaction that created this version) and xmax (the transaction that deleted/superseded it, or 0). They 're exposed as system columns:

SELECT ctid, xmin, xmax, id, owner FROM accounts ORDER BY id LIMIT 4;

Every row you inserted shares one xmin (the transaction that ran the setup) and has xmax = 0 (still live). Hold onto this — the MVCC lesson is entirely a story about xmin/xmax.

The header also holds t_ctid (a forwarding pointer to a newer version), t_infomask status bits, and a null bitmap so NULL columns cost no data bytes.

An UPDATE doesn't modify a row — it writes a new one

This surprises people. Update one row and watch its ctid change:

UPDATE accounts SET balance = balance + 1 WHERE id = 1;
SELECT ctid, id, balance FROM accounts WHERE id = 1;

The old version is still on the page, now marked dead (its xmax set to your transaction). Postgres wrote a brand-new tuple for the updated row and pointed the old one at it. That's MVCC in miniature — and it's why the next point matters.

Deleting (and updating) leaves bloat

Click a tuple in the explorer above to delete it, and watch the free-space number: it doesn't move. DELETE only marks a tuple dead — its bytes stay put. Same for the old version left behind by every UPDATE.

Column order is not free

Postgres stores columns in the order you declare them, and it aligns many types to their size (an int8 starts on an 8-byte boundary). Declaring columns in a poor order leaves padding holes in every single row. The effect is real and measurable — same six columns, two orderings, ten thousand rows:

DROP TABLE IF EXISTS padded;
DROP TABLE IF EXISTS packed;
-- interleaved small/large columns → padding in every row
CREATE TABLE padded (a smallint, b bigint, c smallint, d bigint, e smallint, f bigint);
-- same columns, large ones grouped first → tighter packing
CREATE TABLE packed (b bigint, d bigint, f bigint, a smallint, c smallint, e smallint);
INSERT INTO padded SELECT 1,1,1,1,1,1 FROM generate_series(1, 10000);
INSERT INTO packed SELECT 1,1,1,1,1,1 FROM generate_series(1, 10000);
SELECT pg_relation_size('padded') AS padded_bytes,
     pg_relation_size('packed') AS packed_bytes;

Why 8 KB, and why fixed?

The block size is a compile-time constant (BLCKSZ, 8 KB by default everywhere). Fixing it means one heap fetch is one predictable I/O, the buffer cache is a tidy array of equal-sized slots, and the WAL and on-disk format never have to reason about variable-length blocks. The trade-off: a single row can never exceed one page.

What about huge values? TOAST

When a value would blow past roughly a quarter of a page (~2 KB), Postgres TOASTs it (The Oversized-Attribute Storage Technique): it first tries to compress the value, and if it's still too big, stores it out-of-line in a hidden companion toast table, leaving only a small pointer on the main page. Your row always fits in 8 KB.

-- a 10KB value can't live inline; Postgres compresses and/or off-lines it
SELECT pg_column_size(repeat('postgres ', 1200)) AS bytes;  -- ~ logical size

Big tables: forks and segments

One table is actually several files (“forks”): the main fork of 8 KB pages you've been inspecting, a free space map (_fsm) that tracks where there's room to insert, and a visibility map (_vm) that lets VACUUM and index-only scans skip all-visible pages. And because some filesystems dislike enormous files, the main fork is split into 1 GB segments on disk.

Your turn

Prove you can read the storage yourself (the accounts table is from the first cell — run it first).

SELECT ctid, owner
FROM accounts
ORDER BY ctid;

SELECT pg_column_size(1::int)    AS int_bytes,
     pg_column_size(1::bigint) AS bigint_bytes;