What a data JSONB column costs

A data JSONB column is the schema you write when you do not yet know the schema. It is a real answer to a real problem — the shape genuinely varies, or genuinely is not yours to fix — and it is also the default output of every model that has ever been asked to design a table. This lesson is about telling those two cases apart, and the way to tell them apart is to read what the column costs.

The dataset here holds the same two thousand form submissions twice. submissions.data is a JSONB document. answers is the same responses as ordinary rows with an ordinary index. Nothing is rigged: the document version is what an agent writes, and the normalised version is what it would write if you asked it twice.

It is not the text you gave it

jsonb is parsed, not stored. What comes back is normalised — whitespace gone, duplicate keys collapsed to the last one, and object keys reordered by length and then alphabetically rather than by the order you wrote them.

SELECT '{"b": 1, "a": 2, "aa": 3}'::jsonb AS normalised

That matters more than it looks. A round-trip through a jsonb column is not an identity function, so anything that compares documents as text — a checksum, a diff, an idempotency key — is comparing something the database reserves the right to rewrite.

Two arrows, and the difference is the whole API

-> returns jsonb and ->> returns text. Reaching into a document is a chain of -> with one ->> at the end, and getting that wrong is the single most common JSONB bug: data -> 'status' = 'open' compares a JSON string against a text literal and is not the comparison you wanted.

SELECT id, data -> 'status' AS as_jsonb, data ->> 'status' AS as_text FROM submissions WHERE id IN (1, 5) ORDER BY id

The array inside is a table nobody declared

Each submission holds an answers array. To filter on what is in it you have to turn it into rows, and jsonb_array_elements in FROM is how — one row of output per element, per row of the table. A function in FROM is implicitly LATERAL, so it can name the row to its left without the word being written.

EXPLAIN ANALYZE SELECT count(*) FROM submissions s, jsonb_array_elements(s.data -> 'answers') AS answer WHERE answer ->> 'q' = 'plan' AND answer ->> 'a' = 'pro'

Two thousand loops, six thousand rows produced, five and a half thousand of them thrown away by a filter that could not be applied any earlier — because there was no index to apply it with. Now the same question against the normalised table.

EXPLAIN ANALYZE SELECT count(*) FROM answers WHERE question = 'plan' AND answer = 'pro'

Both queries return 500. One of them read every page of the table to get there and the other read eight, and at two thousand rows neither is slow — which is precisely why this ships. The document version is not wrong until the table is large, and then it is wrong everywhere at once.

The planner is guessing, and it says so

Look at the estimate on the Nested Loop: sixty-six thousand rows, against an actual of five hundred. That is not a bad statistic, it is the absence of one. ANALYZE builds a histogram per column, and the inside of a document is not a column — so a set-returning function gets a flat guess of a hundred rows per call whether the array holds two elements or two thousand.

An estimate that wrong is not a cosmetic problem. It is how a hash join becomes a nested loop over a million rows, and there is no ANALYZE you can run to fix it.

You can index a path, one path at a time

An expression index over a JSONB path is legal and works: CREATE INDEX ON submissions ((data ->> 'status')) gives the planner something to use for exactly that predicate. The catch is in the words exactly that. It does not help data ->> 'form', and it does nothing at all for the array. A document column queried on six paths wants six indexes, which is six write-path costs and six things to keep — and avoiding precisely that is what the document was supposed to buy you.

When it is still the right answer

• The shape is genuinely not yours: a webhook payload, a third-party response, an audit record you must keep verbatim.
• You write it and read it whole, and never filter on anything inside it.
• It is a sparse tail on an otherwise normal row — the fifteen optional fields that would otherwise be fifteen nullable columns.
• You have measured, and the table is small enough that a sequential scan is the honest plan anyway.