# DDIA Ch. 3: you can index inside a document

## Concept

"Document data is opaque — you can store JSON, but querying inside it means
scanning every row." That was true once; it isn't now. Relational databases
converged with document databases, and Postgres can index *inside* a JSONB
column. You will filter 2 million documents on a nested field, watch it scan the
whole table (~**98 ms**, ~34,000 pages), add a **GIN** index, and watch the same
query become an index lookup that touches ~**600 pages** in ~**3.6 ms** — ~27×
faster. The catch: the index serves the containment operator `@>`, not the `->>`
equality you might reach for first.

## Provenance

*Designing Data-Intensive Applications*, 2nd ed. (Kleppmann & Riccomini,
2025), Chapter 3 — *Data Models and Query Languages*, §"Convergence of document
and relational databases" (and §"Query languages for documents"):

> Relational databases added support for JSON types and query operators, and the
> ability to index properties inside documents.

The book claims relational and document models converged. Here you exercise that
convergence — a document column, queried and indexed inside — and meet the
operator subtlety it comes with.

## Prerequisites

The surprise is about indexing a semi-structured column. These help; each line
notes where it shows up.

1. **JSONB** — Postgres's binary JSON type; you can query into it with operators
   like `@>` (contains) and `->>` (get field as text).
2. **Sequential scan vs. index scan** — with no usable index, a filter reads every
   row; an index jumps to the matching rows. The plan change is the whole point.
3. **GIN (Generalized Inverted iNdex)** — an inverted index that maps the
   *elements inside* a value (JSON keys/values, array items) to the rows holding
   them — the structure that makes "index inside a document" possible.
4. **Operator classes** — a GIN index supports specific operators. The default
   `jsonb_ops` serves containment `@>` and key-existence `?`, but **not** the
   `->> = ` field-equality expression.

### Where to learn them

- **JSONB indexing (#1, #3, #4):** the PostgreSQL manual, "jsonb Indexing" —
  which operators `jsonb_ops` vs `jsonb_path_ops` support.
- **GIN internals (#3):** the PostgreSQL manual, "GIN Indexes" (inverted index
  over composite values).
- **The convergence (#1):** DDIA §"Convergence of document and relational
  databases."

If only one is new, make it #4 — the operator catch is the part that bites.

## Environment

Reproducible plan shapes (buffer counts and timing 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
- 2,000,000 JSONB documents; ~200 have `status: "archived"`

## Mental model

A `docs(id, data jsonb)` table of 2,000,000 documents, each like:

```
{"status": "active", "priority": 3, "author": "user42", "tags": ["t7","t22"]}
```

About 200 of them have `status: "archived"`. We ask for those with a containment
query — `data @> '{"status":"archived"}'` — three ways: with no index, with a GIN
index on `data`, and finally the same logical filter written as `data->>'status'
= 'archived'`. We read `EXPLAIN (ANALYZE, BUFFERS)`. The whole exercise is one
script, `code/jsonb_gin_index.sql`.

## Setup

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

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

## Steps

### Step 1 — no index (calibration)

**Predict.** `WHERE data @> '{"status":"archived"}'` over 2,000,000 documents,
no index on `data`. How does Postgres run it, and how many pages does it read?

**Observe.**

```
########## querying a nested field WITHOUT an index ##########
 Finalize Aggregate (actual time=94.373..97.886 rows=1 loops=1)
   Buffers: shared hit=14129 read=20354
   ->  Gather ...
         ->  Parallel Seq Scan on docs (actual rows=67 loops=3)
               Filter: (data @> '{"status": "archived"}'::jsonb)
               Rows Removed by Filter: 666600
               Buffers: shared hit=14129 read=20354
```

A **Parallel Seq Scan** — ~**34,483 buffers**, ~**98 ms** — reading and testing
every one of the 2,000,000 documents to find ~200. No index, no shortcut.

### Step 2 — add a GIN index (the surprise)

**Predict.** Create a GIN index on the whole `data` column and run the identical
query. Can an index on a JSON blob actually help? How much?

**Observe.**

```
########## same query WITH a GIN index on the jsonb column ##########
 Aggregate (actual time=3.596..3.596 rows=1 loops=1)
   Buffers: shared hit=475 read=134
   ->  Bitmap Heap Scan on docs (actual rows=200 loops=1)
         Recheck Cond: (data @> '{"status": "archived"}'::jsonb)
         ->  Bitmap Index Scan on docs_gin (actual rows=200 loops=1)
               Index Cond: (data @> '{"status": "archived"}'::jsonb)
               Buffers: shared hit=409
```

**~3.6 ms — about 27× faster** — a **Bitmap Index Scan** on `docs_gin`. The index
maps the key/value `status:archived` straight to the ~200 rows that contain it,
reading ~**600 buffers** total (409 to probe the index) instead of 34,483. You
*can* index inside a document.

### Step 3 — the catch: the operator matters

**Predict.** The same filter can be written `data->>'status' = 'archived'`.
Does the GIN index serve *that* query too?

**Observe.**

```
########## the catch: GIN jsonb_ops does NOT serve ->> equality ##########
 Finalize Aggregate
   ->  Gather
         ->  Parallel Seq Scan on docs
               Filter: ((data ->> 'status'::text) = 'archived'::text)
```

Back to a **Seq Scan.** The default GIN operator class (`jsonb_ops`) indexes
containment (`@>`) and key existence (`?`), but a `->>`-then-`=` expression is
opaque to it. Same logical question, different operator — and the index sits
unused unless you write the query the way the index understands (or build a
separate expression index for `->>`).

## What you should see

- No index: **Parallel Seq Scan**, ~**34,483 buffers**, ~**98 ms**.
- With GIN: **Bitmap Index Scan**, ~**600 buffers**, ~**3.6 ms** — **~27×**
  faster, ~**57×** fewer pages.
- `data->>'status' = 'archived'`: **Seq Scan again** — the GIN index doesn't
  serve `->>` equality.

## Why

Old intuition treats a document column as an opaque blob: you can store it, but
filtering inside means reading every one. A **GIN index** breaks that assumption.
GIN is an *inverted* index over composite values: instead of one key per row, it
extracts the elements inside each JSONB — its keys and values — and, for each such
element, records the list of rows that contain it. Querying `@>
'{"status":"archived"}'` becomes a lookup of that one key/value pair in the index,
returning the ~200 matching rows directly (the 409-buffer Bitmap Index Scan)
rather than scanning 34,483 pages. This is the convergence the book points at:
the relational engine gained the document engine's ability to index inside a
value.

The catch is that "inside a document" is indexed by *element*, through specific
operators. `data @> '{"status":"archived"}'` asks "does this document contain
this key/value?" — the exact question GIN's inverted structure answers. But
`data->>'status' = 'archived'` first *extracts* a scalar and then compares it;
to the planner that's an arbitrary expression over an opaque function result, and
the containment index has nothing to offer it. The fix is to speak the index's
language (`@>`, or a jsonpath `@@`), or to build a different index for the other
form — a B-tree on the expression `(data->>'status')`. The capability is real;
you just have to match the query to the index you built.

**The boundary — indexing inside a document isn't free, and it's operator-shaped.**
A GIN index over a whole JSONB column can be large and slower to update than a
B-tree, because every key and value in every document becomes an index entry
(`jsonb_path_ops` trades some query flexibility for a smaller index). And it only
accelerates the operators it's built for. So the book's convergence is genuine but
partial: you get document-style flexibility *and* indexed queries in one system —
provided you choose the operator class and write the query to match. This is the
same theme as the earlier Ch. 3 exercises: the relational and document models now
overlap, but the seams still show.

### Go deeper

1. **Index the array.** Query `data @> '{"tags":["t7"]}'` and confirm the same
   GIN index serves array-containment too. Predict whether it's as selective as
   the status query.
2. **Fix the `->>` query.** `CREATE INDEX ON docs ((data->>'status'));` then
   re-run the Step 3 query. Predict whether a plain B-tree expression index makes
   `->>` equality fast — the other half of "index inside a document."
3. **`jsonb_path_ops`.** Rebuild the GIN index `USING gin (data jsonb_path_ops)`
   and compare its size to the default. Predict the space saving and which
   queries you lose (key-existence `?`).