Lesson 7 · Internals & data structures

TOAST

How a big JSONB value fits in an 8 KB page — and why SELECT * can cost you.

Your win: explain how Postgres stores oversized column values, and draw the practical lesson for your wide jsonb and array columns.

The problem: a tuple should fit in a page

A page is 8 KB (Lesson 5), and Postgres prefers a tuple to fit within one page. But columns like a big jsonb document or a long text[] can be far larger. The answer is TOAST — The Oversized-Attribute Storage Technique.¹

How it works

When a row's variable-length values would make it too big, Postgres transparently compresses them and/or moves them out-of-line into a separate TOAST table, leaving just a small pointer in the main tuple. It applies to variable-length types (text, jsonb, bytea, arrays) — never to fixed types like int. It's invisible to your SQL: you query the column normally.

main tuple (in the 8 KB page) TOAST table (side) ┌──────────────────────────┐ ┌───────────────────────┐ │ email_id subject ... │ │ chunk 1 of content │ │ content → [TOAST pointer] │ ────────▶ │ chunk 2 of content │ └──────────────────────────┘ │ chunk 3 … │ └───────────────────────┘

The practical consequence Reading a row does not fetch its TOASTed columns unless you select them. So SELECT * — or selecting a large content jsonb when you only need subject — pulls extra data from the TOAST table and costs I/O you didn't need. Select only the columns you use.

Anchor emails.content (jsonb), conversation.latest_message / optional_config (jsonb), and email_recipients (text[]) are all TOAST-eligible. When they're large, they live out-of-line — so a list query that returns only subject/status is much cheaper than one that drags content along. This is a real, free win in list endpoints.

PostgreSQL — TOAST

The reference on out-of-line storage, compression, and storage strategies (PLAIN/EXTENDED/EXTERNAL/MAIN).

→ postgresql.org/docs — TOAST

Check yourself (from memory)

Q1. TOAST stores large column values…

Compressed and/or moved to a separate TOAST table, with a pointer left in the main tuple. Transparent to SQL.

Q2. TOAST applies mainly to…

Variable-length types — text, jsonb, bytea, arrays. Fixed types like int aren't TOASTed.

Q3. A practical consequence of TOAST is…

TOASTed columns are only fetched when selected — so pulling a big content you don't need wastes I/O. Select what you use.

What is TOAST, and why does it affect SELECT *?

recall, then click to reveal

TOAST (The Oversized-Attribute Storage Technique) stores large variable-length values (big text, jsonb, bytea, arrays) that won't fit in an 8 KB page: it compresses them and/or moves them out-of-line into a separate TOAST table, leaving a pointer in the main tuple — transparently. Consequence: a row read does NOT fetch TOASTed columns unless you select them, so SELECT * (or selecting a big content jsonb when you only need subject) pulls extra data and costs I/O. Select only the columns you need.

Want the per-column storage strategies (EXTENDED vs EXTERNAL), or how compression interacts with JSONB search? Ask me.

1. PostgreSQL — TOAST.