study › DDIA › Chapter 3 · Data Models and Query Languages

The Document Wins the Whole Read and Loses the Field

Fetching whole profiles, a JSONB document reads ~20× fewer disk pages than the equivalent join — its fields are co-located. Fetching just one field, the same document reads ~12× more, because it must load the entire record. Same data; the winner flips with the access pattern.

Concept

Store a profile as one JSON document and its fields sit together on disk; split it across normalized tables and the pieces scatter, so reassembling one profile means several index lookups. When you need the whole entity, that locality is a big win — the document reads ~20× fewer disk pages than the equivalent join. But locality cuts both ways: when you need just one field, the document forces you to read the entire record, and the normalized narrow column wins by ~12×. You will measure both directions on the same data.

Provenance

Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 3 — Data Models and Query Languages, §"Data locality for reads and writes":

If your application often needs to access the entire document … this storage locality has a performance advantage … The locality advantage applies only if you need large parts of the document at the same time.

The book states both halves of the trade. Here you measure each: the whole-entity win, and the single-field loss.

Prerequisites

The surprise is about where related data physically sits. These help; each line notes where it shows up.

Storage locality — a document is one contiguous value on disk; normalized rows for one entity live in different tables and pages. Fetching related data is one read vs. several.
Index lookups and the join — assembling a normalized entity means a lookup per related row (here, a user's positions), each possibly a different page — random I/O that adds up.
EXPLAIN (ANALYZE, BUFFERS) — Buffers: shared hit/read counts the 8 KB pages a query touched. Fewer buffers = less I/O; it's the metric here.
TOAST — Postgres stores large values (a big JSONB) out-of-line; reading the document pulls those extra pages, which is why one field from a big doc is expensive.

Where to learn the prerequisites Locality & the trade (#1): DDIA §"Data locality for reads and writes"; it names Spanner's interleaved tables and Bigtable's wide-column model as relational ways to get the same locality. Reading buffers (#3): the PostgreSQL manual, EXPLAIN and the BUFFERS option. TOAST (#4): the PostgreSQL manual, "TOAST" — how oversized column values are stored and fetched. If only one is new, make it #1 — locality is the whole story, both ways.

Environment these numbers came from Reproducible plan shapes (buffer counts vary a little with cache state). Captured on macOS 26.5.2 (Darwin 25.5.0), arm64, with Docker 29.6.2 running the official postgres:16 image (PostgreSQL 16.14). 200,000 users, 5 positions each (1,000,000 rows), stored normalized and as JSONB documents.

Mental model

The same profile data, two layouts:

Normalized — users_n and positions_n (5 positions per user, indexed on user_id). Fetching a full profile joins the two tables.
Document — users_doc(id, profile jsonb), the whole profile (user + positions) co-located in one JSONB value.

We run two workloads and read Buffers:

A — fetch whole profiles (user + all positions) for a batch of users.
B — fetch one field (just the name) for a batch of users.

The whole exercise is one script, code/data_locality.sql.

Setup

docker run -d --name ddia-ch3 -e POSTGRES_PASSWORD=study -e POSTGRES_DB=study postgres:16
sleep 3
docker cp data_locality.sql ddia-ch3:/tmp/
docker exec ddia-ch3 psql -U postgres -d study -f /tmp/data_locality.sql

Do not read the output yet — make each prediction first.

Step 1 · fetch whole profiles (the locality win)

Predict. Fetch 2,000 full profiles — each user plus all their positions. Which layout reads fewer pages, and by roughly how much?

Which layout reads fewer pages for whole profiles?

--- normalized: join users to positions --- Gather (actual rows=10000 loops=1) Buffers: shared hit=7929 read=360 written=284 -> Nested Loop ... -> Index Scan using positions_n_user_id_idx on positions_n p Index Cond: (user_id = u.id) Buffers: shared hit=7923 read=327 written=260 --- document: read the co-located JSONB --- Index Scan using users_doc_pkey on users_doc Buffers: shared hit=3 read=405 written=404

The join reads ~8,289 buffers — a nested loop doing 2,000 separate index lookups into positions, each landing on a different page. The document reads ~408 buffers: the whole profile is one value, fetched in one place. ~20× fewer pages for the same profiles.

Step 2 · fetch one field (the locality loss)

Predict. Now fetch just the name of 20,000 users — nothing else. The document has locality on its side… doesn't it?

Which layout wins when you need only one field?

--- normalized: read one narrow column --- Index Scan using users_n_pkey on users_n Buffers: shared hit=36 read=306 written=304 --- document: must read the whole (large) JSONB to pull one field --- Index Scan using users_doc_pkey on users_doc Buffers: shared hit=408 read=3649 written=3620

Now it flips. The normalized query reads ~342 buffers — the users_n table is narrow, so its pages are dense with names. The document reads ~4,057 buffers: to extract one field it must load each entire profile JSONB (positions, descriptions, and all, much of it TOASTed out-of-line). ~12× more pages to get one small field.

What you should see

Whole profiles (Step 1): document ~408 buffers vs join ~8,289 — document ~20× fewer (locality win).
One field (Step 2): normalized ~342 buffers vs document ~4,057 — normalized ~12× fewer (locality loss).
Same data both times; the winner flips with the access pattern.

Why

A document is stored as one contiguous value, so everything about a profile — the user and all their positions — sits together. Fetching the whole thing is a single lookup that lands on essentially one place on disk. The normalized layout scatters the same profile: the user is in users_n, the five positions are in positions_n, and reassembling it means a lookup per position, each potentially a different page. Across 2,000 profiles that's thousands of small random reads — the 8,289 buffers — versus the document's 408. This is exactly the locality advantage the book describes, and why some relational systems (Spanner's interleaved tables, Bigtable-style wide columns) offer the same co-location without giving up the relational model.

But locality is co-location of the whole record, and that is a liability when you want a sliver of it. To read one field the database must load the value that field lives in — and a full profile JSONB is large enough to be stored out-of-line (TOAST), so pulling "just the name" drags in the positions and their descriptions anyway: 4,057 buffers for 20,000 names. The normalized users_n table has no such baggage; its rows are narrow, so a page holds many names and the read is dense: 342 buffers. Same data, opposite winners — decided entirely by whether you need most of the record or a small part of it.

The boundary — match the layout to the dominant read The book's rule is the verdict here: document locality "applies only if you need large parts of the document at the same time." Rendering a whole profile page? The document wins. Listing every user's name, or aggregating one attribute across everyone? The narrow normalized column wins, and the big document is dead weight — which is also why the book warns to keep documents small and avoid loading a large document to touch a little of it. There is no universally better layout, only one that fits your hottest query.

Go deeper

Extract a field into a column. Add a generated column name text to users_doc ((profile->>'name') STORED) and index it; re-run Step 2. Predict whether you can recover the narrow-read speed while keeping the document.
Update locality. The book notes documents are usually rewritten whole on update. Append one position to a user in each layout and compare the write cost — one small row insert vs rewriting the entire JSONB.
Scale the batch. Fetch whole profiles for 200 users instead of 2,000, then 20,000. Predict whether the ~20× locality advantage holds, shrinks, or grows with batch size.

Sources: DDIA 2e, Ch. 3, §"Data locality for reads and writes" · PostgreSQL manual, EXPLAIN (BUFFERS) and "TOAST."