The right PostgreSQL JSONB index depends entirely on how you query the column. A GIN index helps containment and key-exists searches, does nothing for doc->>'status' = 'paid', and makes every write more expensive. Pick by query shape, not by data type, and most JSONB performance problems disappear.
The example table
CREATE TABLE orders (
id bigint PRIMARY KEY,
created_at timestamptz NOT NULL,
doc jsonb NOT NULL
);
-- a typical document
-- {"customer_id": 4182, "status": "paid", "tags": ["b2b", "eu"],
-- "shipping": {"country": "LV", "method": "courier"}}
A quick reminder of why this is jsonb and not json: jsonb is stored in a decomposed binary format, does not keep whitespace, key order or duplicate keys (the last value wins), and is the only one of the two with containment operators and GIN support. The documentation recommends jsonb for most applications (JSON types).
GIN: jsonb_ops versus jsonb_path_ops
-- default operator class: jsonb_ops
CREATE INDEX CONCURRENTLY orders_doc_gin ON orders USING GIN (doc);
-- smaller, more selective, no key-exists operators
CREATE INDEX CONCURRENTLY orders_doc_gin_path ON orders USING GIN (doc jsonb_path_ops);
Both are GIN operator classes, and the difference matters more than most teams realise (GIN built-in operator classes):
jsonb_opsis the default. It supports containment@>, the jsonpath operators@?and@@, and the key-exists operators?,?|and?&. It creates separate index items for every key and every value.jsonb_path_opssupports only@>,@?and@@. Each item is a hash of a value together with the keys leading to it. The documentation says such an index is usually much smaller and its searches typically perform better, especially when the query mentions keys that appear in almost every document.
The catch with jsonb_path_ops: it cannot answer "does key X exist", and it produces no entries for structures without values, such as {"a": {}}. Searching for those needs a full index scan, which the docs call quite slow. If your application never uses ? and friends, jsonb_path_ops is usually the better default.
Which queries can use which index
-- can use either GIN index
SELECT id FROM orders WHERE doc @> '{"status": "paid", "shipping": {"country": "LV"}}';
SELECT id FROM orders WHERE doc @? '$.tags[*] ? (@ == "b2b")';
-- key exists: only jsonb_ops
SELECT id FROM orders WHERE doc ? 'refund_id';
-- cannot use a GIN index on doc at all
SELECT id FROM orders WHERE doc->>'status' = 'paid';
The last query is the one we see most often in slow-query logs. ->> returns text, and the comparison is not one of the GIN operators, so a GIN index on doc is simply ignored. Rewriting it as containment, doc @> '{"status": "paid"}', makes the GIN index usable, but only if the value is stored as the same JSON type: "4182" as a string does not match 4182 as a number.
For jsonpath, GIN only helps with clauses of the form accessor_chain == constant. Range conditions inside jsonpath, such as @.amount > 100, are not served by the index. Always confirm with EXPLAIN ANALYZE rather than assuming.
Expression indexes: usually the real answer
When most queries filter, join or sort on one or two well-known fields, index those fields directly:
-- B-tree on one extracted field: equality, ranges, ORDER BY
CREATE INDEX CONCURRENTLY orders_customer_idx
ON orders (((doc->>'customer_id')::bigint));
SELECT id FROM orders
WHERE (doc->>'customer_id')::bigint = 4182
ORDER BY created_at DESC LIMIT 20;
-- GIN on one sub-document instead of the whole thing
CREATE INDEX CONCURRENTLY orders_tags_gin ON orders USING GIN ((doc->'tags'));
SELECT id FROM orders WHERE doc->'tags' ? 'b2b';
- A B-tree expression index supports equality, ranges and
ORDER BY, which GIN never does. - The query must use exactly the indexed expression, including the cast.
doc->>'customer_id' = '4182'will not use an index built on(doc->>'customer_id')::bigint. - A GIN index on a sub-document like
doc->'tags'is, in the documentation's words, likely to be smaller and faster to search than one on the whole document. - An expression index also gives the planner statistics for that expression. If you only need better estimates and not an index,
CREATE STATISTICSon an expression gives similar statistics without index maintenance (CREATE STATISTICS).
What GIN costs on writes
The documentation is blunt: updating a GIN index tends to be slow, because one row can produce many index entries, one per extracted key or value. To soften this, GIN has a fast update mode, on by default: new entries go to an unsorted pending list first and are merged in bulk later (CREATE INDEX storage parameters).
- The list is flushed by vacuum or autoanalyze, by
gin_clean_pending_list(), or when it grows pastgin_pending_list_limit, 4MB by default (client settings). - Searches must scan the pending list as well, so a big list slows reads. And the insert that pushes it over the limit pays for the whole cleanup, which shows up as random slow writes.
- If stable latency matters more than insert speed, turn
fastupdateoff. Turning it off does not flush what is already queued; vacuum or call the function afterwards.
-- pending list size per GIN index (default 4MB, set globally or per index)
ALTER INDEX orders_doc_gin SET (gin_pending_list_limit = 16384); -- kB
-- predictable read latency instead of faster inserts
ALTER INDEX orders_doc_gin SET (fastupdate = off);
SELECT gin_clean_pending_list('orders_doc_gin'); -- flush what is already queued
There is a second, quieter cost. A HOT update, which avoids new index entries, is possible only when no indexed column changes (HOT updates). With a GIN index on the whole doc, every update that touches the document is a non-HOT update and writes to every index on the table, not only the GIN one. An expression index on one field has the same effect, because the indexed column is doc.
JSONB indexes on large tables
A JSONB index tested on 50,000 rows tells you very little about 500 million. The things that change at scale:
- Index size. In our experience a
jsonb_opsindex on rich documents can approach the size of the table itself. Measure both classes on a production-sized copy withpg_relation_sizebefore choosing; the difference is often large enough to decide by itself. - Build time and locks. A plain
CREATE INDEXblocks writes until it is done; useCONCURRENTLY, and check for anINVALIDindex if it fails. GIN build time is very sensitive tomaintenance_work_mem, per the docs, so raise it for the building session. - Pending list and autovacuum. On a table taking thousands of inserts per second, the pending list fills constantly. If autovacuum on that table is slow, reads degrade and write latency spikes. Per-table autovacuum settings and a deliberately chosen
gin_pending_list_limitmatter more here than on any B-tree. - Write amplification. Losing HOT updates on a frequently updated JSONB column means more index writes, more WAL, more replication traffic and more vacuum work. In our experience this is the hidden reason many "JSONB is slow" complaints are really "this table writes too much WAL".
- TOAST. Rows wider than about 2 kB are compressed or moved out of line. Large documents mean every
doc->>'x'on a non-indexed path has to detoast the value first, which is CPU and I/O you pay per row on sequential scans. - Index sprawl. JSONB invites "one more expression index" for each new filter. Every one of them costs writes; review them periodically and drop the ones nobody uses, as described in our guide to finding unused PostgreSQL indexes.
Key takeaways
- GIN serves
@>,@?,@@and (withjsonb_ops)?,?|,?&; it does nothing for->>comparisons. - Prefer
jsonb_path_opswhen you never test for key existence: smaller and usually faster. - For equality, ranges and sorting on a known field, use a B-tree expression index, or promote the field to a column.
- GIN writes are expensive; watch the pending list, autovacuum and the loss of HOT updates.
- On large tables, measure index size and build time on production-sized data and build with
CONCURRENTLY.