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

Every 8 KB page opens with the same fixed 24-byte header (PageHeaderData). Most of it is bookkeeping, but four fields run the whole show — the two that mark where free space begins and ends, pd_lower and pd_upper:

Now pick a row size below and insert rows. Watch the page fill from both directions at once: a line-pointer array grows downward from just under the header (pushing pd_lower down 4 bytes per row), while the tuples (your rows) are written upward from the bottom (pulling pd_upper up by each tuple's size). The gap between them is the free space.

Because a page is a fixed 8 KB, a table's file only ever grows in whole 8 KB steps. Insert enough rows to spill onto new pages and watch the file size jump one block at a time:

DROP TABLE IF EXISTS blocks;
CREATE TABLE blocks (id int);
SELECT pg_relation_size('blocks') AS empty;          -- 0

INSERT INTO blocks SELECT generate_series(1, 200);
SELECT pg_relation_size('blocks') AS one_page;       -- 8192

INSERT INTO blocks SELECT generate_series(1, 5000);
SELECT pg_relation_size('blocks') AS several_pages;  -- a multiple of 8192

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;

Each line pointer is a 4-byte slot (ItemIdData) — just 32 bits, packed into three fields:

• lp_off (15 bits) — the byte offset within the page where the tuple actually sits.
• lp_len (15 bits) — the tuple's length in bytes.
• lp_flags (2 bits) — the slot's state, which is where the cleverness lives.

Those 2 flag bits give a slot one of four states:

• LP_UNUSED — empty and reusable.
• LP_NORMAL — points at a live tuple (the usual case).
• LP_REDIRECT — points at another line pointer, not a tuple. This is how HOT update chains work (next section): an index still points at slot 3, but slot 3 forwards to the current version's slot.
• LP_DEAD — the tuple is gone; the slot's space can be reclaimed.

The tuple header — and a sneak peek at MVCC

Before its actual column data, every tuple carries a 23-byte header (HeapTupleHeaderData). It's small, but every field earns its place:

The two fields that matter most decide who can see the row: t_xmin (the transaction that created this version) and t_xmax (the transaction that deleted or superseded it, or 0 if it's still live). They're exposed as system columns — select them like any other:

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 rest of the header does real work too:

• t_ctid normally points at the tuple itself, but after an UPDATE it becomes a forwarding pointer to the newer version — the update chain you'll see next.
• infomask / infomask2 are packed status bits. They cache expensive facts so Postgres doesn't recompute them: is xmin known-committed? does this row have any NULLs? was it HOT-updated?
• t_hoff says where the header ends and column data begins, because between them sits an optional null bitmap — one bit per column. If a column is NULL, its bit is 0 and it occupies zero data bytes (the value simply isn't stored). That's why a table full of mostly-NULL wide columns can be far smaller than it looks.

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;

Postgres didn't edit the row in place. It wrote a brand-new tuple for the new version (with a fresh xmin), stamped the old version's t_xmax with your transaction id, and set the old version's t_ctid to point forward at the new one. The old bytes are still sitting on the page — now a dead version. Every UPDATE is really an insert-plus-tombstone; that's the heart of MVCC, and the reason the next section (bloat) exists.

Deleting (and updating) leaves bloat

DELETE doesn't erase anything either — it just stamps the tuple's t_xmax so no future snapshot can see it. The bytes stay exactly where they were. Prove it: build a table, delete 90 % of it, and watch the file size not budge:

DROP TABLE IF EXISTS churn;
CREATE TABLE churn (id int, v text);
INSERT INTO churn SELECT g, repeat('x', 80) FROM generate_series(1, 1000) g;
SELECT pg_relation_size('churn') AS before_delete;   -- ~122 KB

DELETE FROM churn WHERE id <= 900;                    -- remove 90% of rows
SELECT pg_relation_size('churn') AS after_delete,     -- unchanged!
     count(*)                  AS live_rows FROM churn;

Same file size, a tenth of the rows. Those 900 dead tuples still occupy their pages. The same thing happens on the write side: every UPDATE leaves its old version behind, so an update-heavy table accumulates dead versions fast.

Column order is not free

You can declare the same columns in any order and get the same data — but not the same row size. The reason is alignment, and it's worth understanding byte-for-byte, because on a wide, high-row-count table a careless order silently wastes gigabytes of disk and cache.

Why alignment exists

A CPU reads memory fastest when a value sits on a boundary that's a multiple of its size: an 8-byte bigint read from an address divisible by 8 is a single aligned fetch; read it from a ragged offset and the hardware has to do extra work (and on some platforms it faults outright). So Postgres stores every value on its natural boundary. Each type carries an alignment requirement — it's the typalign column in pg_type:

• bool, "char" → 1 byte (fits at any offset)
• smallint (int2) → 2
• int, float4 → 4
• bigint, timestamptz, float8 → 8

Column values are laid out in declaration order, right after the 24-byte tuple header. When the next value's type needs a boundary the current offset doesn't sit on, Postgres inserts padding — dead filler bytes that hold nothing — to reach it. That padding is the entire cost of a bad column order.

Watch the padding form

Below are six columns in a careless, interleaved order. Follow the byte strip: a 1-byte bool immediately followed by an 8-byte bigint forces 7 padding bytes to reach the next 8-byte boundary (the cyan gridlines). Reorder the columns with the arrows — or hit pack optimally — and watch the padding, and the row size, collapse to nothing.

Putting the widest-alignment columns first packs every value tight against the previous one, so no padding is ever needed — the same six columns, the same data, in a 40 % smaller row.

It's real — measure it

pg_column_size returns a row's exact on-disk byte count. The interleaved order is 64 bytes/row; the packed order is 48 — a 16-byte difference that is pure padding:

DROP TABLE IF EXISTS bad_order;
DROP TABLE IF EXISTS good_order;

-- interleaved 1,8,2,8,1,4-byte types → padding after every small column
CREATE TABLE bad_order (
is_read    boolean, id bigint, priority smallint,
created_at timestamptz, kind boolean, user_id int
);
-- widest → narrowest: not a single padding byte
CREATE TABLE good_order (
id       bigint, created_at timestamptz, user_id int,
priority smallint, is_read boolean, kind boolean
);

INSERT INTO bad_order  VALUES (true, 1, 2, now(), false, 3);
INSERT INTO good_order VALUES (1, now(), 3, 2, true, false);

SELECT pg_column_size(b) AS bytes FROM bad_order  b    -- 64
UNION ALL
SELECT pg_column_size(g) AS bytes FROM good_order g;   -- 48

Sixteen bytes per row sounds trivial. Multiply it out — fill both tables with 50,000 identical rows and compare the actual files on disk:

INSERT INTO bad_order
SELECT true, g, 1, now(), false, g FROM generate_series(1, 50000) g;
INSERT INTO good_order
SELECT g, now(), g, 1, true, false FROM generate_series(1, 50000) g;

SELECT pg_relation_size('bad_order')  AS bad_bytes,    -- ~3.4 MB
     pg_relation_size('good_order') AS good_bytes;   -- ~2.6 MB

The badly-ordered table is roughly a third larger — for byte-identical data.

Why 8 KB, and why fixed?

The block size is a compile-time constant (BLCKSZ, 8 KB by default everywhere). Two separate choices are baked in here — the size, and the fact that it's fixed.

Why fixed at all? Uniform blocks make everything downstream simple: the buffer cache is a tidy array of equal-sized slots (any page fits any slot), one heap fetch is one predictable I/O, and the WAL never has to describe variable-length blocks. The cost is that a single row can't exceed one page — which is exactly the problem TOAST (next) solves.

Why 8 KB specifically? It's a tuned middle between two opposing pressures:

• Bigger pages (16/32 KB) amortize I/O nicely for sequential scans and make indexes shallower — but they punish random access. To read one 100-byte row you must still fetch the whole block, so a 32 KB page reads 4× the data for the same row, and every page write to the WAL (full-page images) gets bigger.
• Smaller pages (4/2 KB) cut that read amplification, but each page pays the fixed 24-byte header and per-page overhead more often, and indexes grow more levels (more pages to hold the same keys), so lookups touch more blocks.

8 KB has been the sweet spot for OLTP-style mixed workloads for decades. And it quietly sets other limits: the ~2 KB TOAST threshold below is "about a quarter of a page."

What about huge values? TOAST

A row can't exceed a page — so what happens when you store a 10 KB blob of text? TOAST (The Oversized-Attribute Storage Technique). When a row would blow past the ~2 KB TOAST threshold (about a quarter of a page), Postgres works oversized columns in two steps: first compress the value; if it's still too big, move it out-of-line into a hidden companion toast table, leaving only an 18-byte pointer on the main page.

Watch both halves happen. Store one very compressible value and one incompressible one, then ask how many bytes each actually takes on disk:

DROP TABLE IF EXISTS docs;
CREATE TABLE docs (id int, body text);

-- 10,000 chars of repetitive text → compresses to almost nothing
INSERT INTO docs VALUES (1, repeat('the quick brown fox ', 500));

-- ~9,600 chars of random hex → can't compress, goes out-of-line as-is
INSERT INTO docs VALUES (2, (SELECT string_agg(md5(g::text), '')
                           FROM generate_series(1, 300) g));

SELECT id,
     length(body)          AS chars,        -- both ~10,000
     pg_column_size(body)  AS on_disk_bytes -- 147  vs  ~9,600
FROM docs ORDER BY id;

Same logical length, two orders of magnitude apart on disk — the first compressed roughly 68×, the second couldn't compress at all. And the big values don't live in the table's own file; they're in its toast table:

SELECT relname                  AS table_name,
     reltoastrelid::regclass  AS toast_table,   -- the hidden companion
     pg_relation_size('docs')       AS main_bytes,   -- tiny: just pointers
     pg_total_relation_size('docs') AS total_bytes   -- includes the toast data
FROM pg_class WHERE relname = 'docs';

Big tables: forks and segments

One table isn't one file — it's a small family of them, called forks, all sharing a numeric base name (the relfilenode):

• The main fork — the 8 KB pages you've been inspecting all lesson.
• The free space map (_fsm) — tracks how much room each page has, so an INSERT can jump straight to a page with space instead of scanning.
• The visibility map (_vm) — one bit per page marking it all-visible (every tuple visible to everyone). It's what lets VACUUM skip clean pages and lets index-only scans answer without touching the heap.

You can see where the main file lives on disk:

SELECT pg_relation_filepath('accounts') AS main_file,   -- base/<db>/<relfilenode>
     pg_relation_size('accounts', 'main') AS main_bytes,
     pg_relation_size('accounts', 'fsm')  AS fsm_bytes,   -- built lazily
     pg_relation_size('accounts', 'vm')   AS vm_bytes;    -- built lazily

The _fsm and _vm forks start empty and are built as the table grows and gets vacuumed — on a tiny table they're 0.

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;