Records on Disk

This is a conceptual representation: the general shape every row-store uses, drawn with round numbers. Real engines move the parts around and size the header differently, but every one has these five regions in some form. The record is (42, 'Alice', 'alice@example.com') from users (id INT, name TEXT, email TEXT).

Read it top to bottom as the engine would. The header gives the length — 77 bytes — so the next record is findable without parsing this one. The NULL bitmap is one byte for three columns; all bits clear. id is fixed-size, so it sits right after the bitmap at byte 33. name and email are variable, so the fixed part holds two offsets, 41 and 53, and only after them do the bytes appear, each with its own length word and padded to a multiple of four.

Set email to NULL and three things happen, none of them in the data area: bit 3 of the bitmap flips to 1, the email offset becomes meaningless (the engine never reads it), and the record ends after name — 53 bytes instead of 77. NULL did not write a sentinel value; it removed a region. This is why NULL is cheap to store and why a column that is "usually NULL" costs almost nothing — and why a nullable column cannot be found by arithmetic: the bitmap must be consulted first, every time.

Change the name to Alice Margaret Fairweather and the name region grows from 12 to 32 bytes. The email offset moves from 53 to 73. Nothing before the name moved: the header, bitmap, id and both offset slots are exactly where they were, because everything up to the first variable-size column has a fixed shape. That fixed prefix is what makes the layout fast: the engine can reach any fixed column, and the offset of any variable column, without looking at a single data byte.

The lesson for schema design follows directly. Put fixed-size, frequently-read columns first; group nullable columns; and remember that an UPDATE which lengthens a string produces a *different-sized* record, which is the whole reason Slotted Pages exist.

The header is where the engine stores facts *about* the row that it needs before, or instead of, reading the row. The record length and column count are the minimum. Flags follow: has NULLs (so the bitmap exists), has variable-width columns, is this a deleted or moved record. Then whatever the concurrency design needs: in an MVCC engine, the ids of the transactions that created and deleted this version, so visibility can be decided from the header alone (A Transaction, Inside the Engine).

This is why a "row header" of 24 bytes is normal and why a table of tiny rows — (int, int) — is mostly header: 8 bytes of data, 24 of header, 4 of slot. Narrow tables are proportionally the most expensive to store, which is a real argument for arrays or JSONB in the few cases where thousands of tiny related values are always read together.

A PostgreSQL row version is a heap tuple: a 23-byte HeapTupleHeaderData, padded to the next 8-byte boundary, an optional null bitmap t_bits, an optional object id, then the data. The data starts at t_hoff, which is the header's way of saying "here is where the columns begin, whatever came before". There are no per-column offsets: PostgreSQL walks the columns in order, adding fixed sizes and reading varlena length words, and caches the running offsets for the fixed prefix of the row (attcacheoff) so the common case is arithmetic.

t_xmin is the transaction that inserted this version; t_xmax is the transaction that deleted or updated it (0 while it is live). Together with the commit log and the reader's snapshot they decide whether a reader may see this tuple — MVCC is stored in the header of every row, which is why PostgreSQL has no undo log and why an UPDATE writes a whole new tuple. t_ctid is the tuple's own address (page, slot); after an update it points forward to the new version, so an index entry that still names the old location can follow the chain. t_infomask caches facts such as "xmin is known committed" (hint bits) so the commit log is not consulted twice; t_infomask2 holds the column count and the HOT flags.

InnoDB stores rows inside the leaf pages of the table's clustered index, ordered by primary key, in the COMPACT / DYNAMIC row formats. The layout runs *backwards* from the header: first a list of the lengths of the variable-length columns, in reverse column order, one or two bytes each; then the NULL flags, one bit per nullable column; then a 5-byte record header. The header's most important field is next_record, an offset to the next row in key order — rows on an InnoDB page form a singly linked list, and the page directory only points at every 4th–8th of them.

After the header come three hidden columns. DB_ROW_ID (6 bytes) exists only when the table has no usable primary key. DB_TRX_ID (6 bytes) is the id of the last transaction that changed the row — InnoDB's counterpart of t_xmax, but there is no t_xmin because old versions do not live in the table. DB_ROLL_PTR (7 bytes) points into the undo log, where the previous version of the row was copied before the change. A reader whose snapshot is older than DB_TRX_ID follows the roll pointer backwards until it finds a version it may see. Same MVCC problem as PostgreSQL, opposite placement: the current version in the table, the history elsewhere.

Because the lengths list is in front, InnoDB reaches variable-size column k by summing the first k lengths — no separate offset table, at the cost of a small loop. Long values (past what fits with the row in a 16 KB page) go to overflow pages, with a 20-byte pointer left in the row in DYNAMIC format.

Both engines start from the same conceptual record and diverge on one design choice: where old versions live. PostgreSQL keeps them in the table, so its header needs both xmin and xmax, and its ctid must be able to forward. InnoDB keeps them in the undo log, so its header needs one transaction id and a pointer backwards. Everything downstream — VACUUM versus purge, table bloat versus undo growth, heap-only tuples versus in-place updates — follows from that one choice. Physical Layouts Compared: Heap + Secondary Index vs Clustered Index draws the consequences side by side.