Postgres jsonb: when documents beat columns, and when they don't
JSONB vs JSON storage, GIN index, jsonb_path_ops, ->>vs->, expression indexes, ACID inside documents
Jsonb feels free. You throw the shape-shifting payload into a column, query it later, ship the feature, move on. Most teams pay for that feeling around month 18. The cost shows up in three places at once - write amplification where every partial update rewrites the entire document, GIN indexes nowhere near as cheap as they look on paper, and schema drift you can't query against because the shape only exists in the heads of whoever last touched the writer. The decision isn't "columns vs jsonb". It's "what does the access pattern look like, and which trade-off do you want to pay?"
The free-feeling column that isn't
The pitch is irresistible. Variable shape, no migrations, the planner handles it. A startup with five product directions a quarter, an event-ingest table where every source has different fields, an admin metadata column that grows a new field every release - all reach for jsonb and feel like they got away with something. Two years later, the same three failure modes show up on every one of them.
First, write amplification on partial updates. Postgres has no concept of updating a field inside a jsonb document. jsonb_set of one nested key is the same code path as overwriting the whole thing - row read, new version materialised in memory, brand-new tuple written. On a 5 KB document where you changed one boolean, you wrote 5 KB of new tuple, marked the old one dead, handed 5 KB of bloat to autovacuum. Multiply by a million updates a day and you're back in the disk-graph nightmare Evergreen #4 covered, except now the cause is your data model.
Second, query plan disasters when nobody indexed for the access pattern. WHERE doc @> '{"status":"active"}' against ten million rows without a GIN index is a sequential scan that parses every jsonb document on every page. The query took 4 ms on staging and takes 14 seconds in production. The team's first instinct is to "add an index" - but jsonb indexing has three flavours for three different operator families, and the wrong one is one the planner won't use.
Third, schema drift you can't query against. Six months in, half the rows have status, the other half have state, values are sometimes strings, sometimes booleans, sometimes the string "true". A check constraint would have caught it on day one. The jsonb column silently accepted everything. When analytics asks "how many active users", the honest answer is "we don't know, the field is spelled three ways". This isn't a Postgres problem - it's the absence of one. Jsonb doesn't push back on you, and that's exactly the failure.
What jsonb actually stores
jsonb isn't the JSON text. It's a binary tree representation of the parsed document, in a Postgres-specific format. On insert, Postgres parses the JSON, validates it, writes the tree. On read it walks the tree directly - no re-parsing. That's why jsonb is faster to query than json (the other type, which keeps the original text and re-parses on every access) and why storage is slightly higher. You pay parse time once at write, get cheap reads forever. For anything you'll query, jsonb is the right type.
Then there's TOAST. Postgres rows live in 8 KB pages, and anything bigger gets pushed out-of-line into the TOAST table for that relation, compressed, reassembled on read. A 5 MB jsonb document doesn't live in your events table - it lives in pg_toast_<oid>, with the main row holding a pointer. SELECT doc FROM events WHERE id = 42 follows the pointer, reads the chunks, decompresses, hands you back the document. A lot of I/O for one row.
TOAST is what makes the atomic-write story so brutal. A partial update can't update part of the document - the document is a single immutable value stored as TOAST chunks. Postgres reads the whole thing into memory, applies your change to the new version, writes a fresh set of chunks, updates the main-row pointer, marks the old tuple dead. A 200-byte field flipped inside a 5 MB document means 5 MB of new TOAST writes plus WAL traffic plus future vacuum work. The statement looks like one line and behaves like a full-document rewrite, every time.
One subtle detail: inserting a document and reading it back doesn't give you exactly what you sent. Keys get reordered, whitespace disappears, duplicate keys are silently de-duplicated keeping the last, numeric values are normalised. If your app round-trips jsonb expecting byte-equivalence, it won't get it.
Indexing jsonb: jsonb_ops vs jsonb_path_ops
GIN is the workhorse for jsonb. It's a generalised inverted index - builds a posting list for each indexable item and lets the planner intersect those lists at query time. The two flavours differ on what counts as "an indexable item".
jsonb_ops is the default. It indexes keys and values separately - every key path and every leaf value gets its own posting list. A wide set of operators work against it: containment @>, key existence ?, key-in-array ?| and ?&, plus JSON path operators on newer Postgres. The cost is index size - on documents with many distinct keys and values, the index can grow larger than the table itself.
jsonb_path_ops is the slimmer cousin. It hashes the entire path from root to each leaf value into a single token and indexes only the hashes. The index is typically half the size. The trade-off is that key-existence operators stop working - it answers @> containment but not "does this document have a top-level key called status". For most production workloads the only operator that matters is @>, which is exactly what jsonb_path_ops is optimised for.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events
WHERE doc @> '{"action":"login","tenant":"acme"}';
Seq Scan on events (cost=0.00..184320.00 rows=120 width=8)
(actual time=0.041..3924.882 rows=118 loops=1)
Filter: (doc @> '{"action": "login", "tenant": "acme"}'::jsonb)
Rows Removed by Filter: 9999882
Buffers: shared hit=21 read=141204
Execution Time: 3924.940 msSame query after a GIN index with jsonb_path_ops:
CREATE INDEX ix_events_doc ON events USING gin (doc jsonb_path_ops);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events
WHERE doc @> '{"action":"login","tenant":"acme"}';
Bitmap Heap Scan on events (cost=24.50..425.18 rows=120 width=8)
(actual time=0.612..1.118 rows=118 loops=1)
Recheck Cond: (doc @> '{"action": "login", "tenant": "acme"}'::jsonb)
-> Bitmap Index Scan on ix_events_doc (cost=0.00..24.47 rows=120 width=0)
Heap Blocks: exact=104
Buffers: shared hit=18 read=86
Execution Time: 1.184 msRead your plans before deciding. Evergreen #6 covered EXPLAIN ANALYZE - that's the tool that tells you whether the index you built is the index the planner actually picked. Building a jsonb_ops index for a query that only uses @>, then watching it sit untouched on disk eating write throughput, is a real-life mistake.
GIN writes are not free. Every insert and update touches every posting list the new document hits. On a wide document with dozens of fields, a single insert touches dozens of lists. The fastupdate option batches these into a pending list merged later, smoothing per-row cost but trading it for occasional vacuum spikes. For very write-heavy tables, dropping the index during a bulk load and rebuilding after is often faster.
The third indexing path - the one most teams reach for too late - is the expression index. If you query one field a lot (WHERE doc->>'status' = 'active'), GIN over the whole document is overkill. A targeted btree on the expression is smaller, faster, and lets the planner use it for sorts and range queries:
CREATE INDEX ix_events_status ON events ((doc->>'status'));Now the planner uses it like any other column, and the GIN can be dropped if no other query needs it. Most production jsonb workloads end up with one expression index on the single hot field and either no GIN or a small jsonb_path_ops for the rare containment queries.
The decision framework
The question isn't "columns or jsonb". It's "what does the access pattern look like". Walk through it honestly.
Jsonb wins when:
Shape varies per row and you genuinely don't know it. Event payloads from heterogeneous sources, per-tenant custom fields, plug-in metadata - every row has a different set of keys, and forcing a schema means a sparse table with hundreds of mostly-null columns.
You read documents wholesale. One SELECT returns the whole thing and the application picks it apart. No WHERE doc->>'field' = ... predicate on twenty different fields.
Writes are mostly full-document replacements. You're rewriting the document or appending rows, not doing partial updates. No write amplification because every write was going to be the full document anyway.
Index size on a few hot fields is fine. You need GIN on one or two fields, not twenty. The index stays bounded.
Columns win when:
Shape is stable and you know it. A user has an email, a created_at, a tenant_id. These fields don't disappear or rename themselves. Make them columns.
You query by field with predicates and joins.WHERE created_at > $1 AND status = $2 AND tenant_id = $3 against a jsonb document with three expression indexes is the same query you'd write against three columns, and slower at every step.
Writes are field-level partial updates. Updating one field should write the row, not 5 MB of TOAST chunks. Columns get this right for free.
You need foreign keys, check constraints, or column-level statistics. None of these work on jsonb fields. The planner has no histogram for doc->>'status', so its row estimates for that predicate are guesses. Statistics come from columns.
The hybrid is the most common production answer. A few stable columns - id, tenant_id, created_at, kind, status - plus a metadata jsonb for the long tail. The hot path queries against columns, the cold path digs into metadata when needed. You keep the planner's statistics, the option to add constraints, and the flexibility for fields that don't have a stable shape. Almost every production table that lasts three years ends up looking like this.
Migration patterns
You'll often want to extract a column from a jsonb document once the field is stable enough to deserve one. The clean pattern is a generated column - Postgres maintains it from the underlying jsonb, and you index it like any other column:
ALTER TABLE events
ADD COLUMN status TEXT
GENERATED ALWAYS AS (doc->>'status') STORED;
CREATE INDEX ix_events_status ON events (status);Stored generated columns cost disk space but zero CPU on read. Virtual ones (Postgres 17+) cost no disk but recompute on access. For a hot filter field, stored is the right answer. Going the other way - collapsing a wide table into a metadata jsonb column - is rarer and usually a sign the shape was never stable. Add the column, backfill via jsonb_build_object, drop the source columns. The hard part is updating every writer and reader: do it behind a feature flag, in stages, with the jsonb column dual-written first.
Common mistakes
A few patterns that come up over and over when teams hit the wall:
Using jsonb as a substitute for schema design. "We'll figure out the shape later" means "we'll have three spellings of the same field by Q3". Sketch the shape on paper before the column hits the migration.
No GIN index when the workload runs containment queries. Sequential scans over millions of documents in the hot path. Read the plan, see the seq scan, build the right index.
jsonb_ops everywhere when jsonb_path_ops would be half the size. If the only operator touching the column is @>, jsonb_path_ops is the answer - smaller index, faster writes, same query speed.
Treating jsonb as searchable text. LIKE '%foo%' can't use any GIN you've built. For full-text search, use a tsvector column.
Reading large jsonb documents to extract one field. SELECT doc->>'status' FROM events on 5 MB documents detoasts every row. An expression index or stored generated column avoids the detoast entirely.
Forgetting that jsonb doesn't enforce types. The same key holds a string in one row, a number in another, a null in a third. Check constraints with jsonb_typeof are the only thing standing between you and a downstream parsing bug.
Partial updates on huge documents. Every jsonb_set rewrites the whole thing. 5 MB document, 50-byte field, you're paying 100,000x. Split the document or move the volatile field into a column.
Autovacuum was storage. Explain analyze was the planner. Isolation was concurrency. Jsonb is data model. Four sides of one box - and most production correctness and performance problems live in exactly one of them. The Postgres-fundamentals arc closes here, but the box is the thing to remember. Every regression you'll chase for the next year sits on one of these faces, and you now know which face to start with.