# DDIA Ch. 3: the document wins the whole read and loses the field ## 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. 1. **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. 2. **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. 3. **`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. 4. **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 them - **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 Reproducible plan shapes (buffer counts vary a little with cache state): - macOS 26.5.2 (Darwin 25.5.0), arm64 - 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. ## Steps ### 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? **Observe.** ``` --- 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? **Observe.** ``` --- 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 1. **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. 2. **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. 3. **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.