You Can Index Inside a Document

Filtering 2 million JSONB documents on a nested field scans the whole table (~98 ms, ~34,000 pages). Add a GIN index and the same query becomes an index lookup touching ~600 pages in ~3.6 ms — ~27× faster. The catch: the index serves @> containment, not the ->> equality you might reach for.

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.

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.

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.

Go deeper

• 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.
• 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."
• 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 ?).

Sources: DDIA 2e, Ch. 3, §"Convergence of document and relational databases" and §"Query languages for documents" · PostgreSQL manual, "jsonb Indexing" and "GIN Indexes."