Heap storage, TOAST, and column alignment

A team trims a table from 40 columns to 35. Storage drops by 18%. They did not delete any data — they reordered the remaining columns to eliminate alignment padding. The database never changed its rules. The team finally read them.

The heap: how Postgres stores rows

Postgres stores table rows in fixed-size 8KB pages on disk. The layout matters for storage and query performance:

• Each page has a header (24 bytes) and an item pointer array.
• Each row (tuple) starts with a 23-byte tuple header containing transaction visibility information (xmin, xmax, ctid — covered in the MVCC lesson).
• If any column is nullable, a null bitmap follows (1 bit per column, rounded to a byte).
• Then the column data in declaration order, each padded to its alignment boundary.

The total row size affects how many rows fit on one page, which affects how many pages must be read for a sequential scan — one of the most direct connections between schema design and query cost.

Column alignment padding

Alignment padding is inserted between columns to ensure each value starts at its alignment boundary. A column layout that alternates between wide and narrow types wastes bytes:

-- Bad order: [SMALLINT(2), BIGINT(8), SMALLINT(2)]
-- Layout: 2 bytes + 6 bytes padding + 8 bytes + 2 bytes + 6 bytes padding = 24 bytes

-- Good order: [BIGINT(8), SMALLINT(2), SMALLINT(2)]
-- Layout: 8 bytes + 2 bytes + 2 bytes + 4 bytes padding = 16 bytes (struct alignment at end)

On a wide table (40 columns of mixed types), poor column ordering can waste 10-20% of storage. ORM-generated schemas rarely optimise for this; it is a real production lever on tables above ~100M rows.

Rule of thumb: declare columns from widest alignment to narrowest (8-byte types first, then 4-byte, then 2-byte, then 1-byte). Variable-length types (TEXT, JSONB) go last — they have internal length headers and their alignment cost is fixed regardless of position relative to fixed-width columns.

TOAST: handling wide values

Postgres pages are 8KB. Rows that exceed approximately 2KB trigger TOAST (The Oversized-Attribute Storage Technique). What happens:

1. The wide value is compressed (using LZ4 or pglz by default).
2. If still over the threshold, it is sliced into ~2KB chunks and stored in a separate TOAST table alongside the main table.
3. The main row holds a pointer (18 bytes) referencing the TOAST chunks.

TOAST is transparent — a SELECT returns the full value as if it were inline — but has performance implications:

• Reading a TOASTed column requires an extra disk fetch from the TOAST table (not in the main page).
• Updating a TOASTed value rewrites the side-table chunks plus the pointer in the main row.
• Listing rows without reading TOASTed columns is cheap — the pointer is inline, the chunks are not fetched unless you SELECT that column.

Implication for schema design: if a wide TEXT/JSONB/BYTEA column is rarely read (it is attached metadata that you only display on a detail page), excluding it from queries that list rows is important. SELECT * FROM events LIMIT 1000 fetches the TOASTed payload for every event — a SELECT id, event_type, created_at FROM events LIMIT 1000 does not.

Type selection: the production defaults

Integer types. Pick the narrowest range that exceeds your domain by 100x. A user_id that will never exceed 10 million can be INTEGER (4 bytes, saves 4 bytes per index entry). A payment ID that could reach billions should be BIGINT. Never SMALLINT for anything that could grow.

String types. TEXT is the production default. VARCHAR(n) adds a length-check constraint but uses the same storage. CHAR(n) pads to length (trailing spaces cause subtle bugs) — almost always wrong.

Money. NUMERIC(p,s) (exact arithmetic) or BIGINT storing cents. Never REAL or DOUBLE PRECISION — IEEE 754 floats accumulate rounding errors on financial arithmetic. A REAL money column drifts cents after months of transactions.

Timestamps. TIMESTAMPTZ (with time zone) is the production default — stores UTC, displays in the session timezone. TIMESTAMP (without time zone) is a footgun when the application spans multiple timezones. DATE for date-only data.

UUID vs BIGSERIAL. BIGSERIAL is 8 bytes, sequential (B-tree-friendly, good index locality), simple. UUID is 16 bytes, globally unique (good for distributed inserts, offline-first clients, merging datasets from multiple sources). UUIDv4 is random — B-tree indexes fragment badly on large tables. UUIDv7 (time-ordered, RFC 9562) fixes the locality issue with a timestamp prefix; it is the modern default when UUID is needed. Pick BIGSERIAL for single-region services; UUIDv7 for multi-region or offline-first.