Architecture

PostgreSQL JSONB Indexing: GIN, jsonb_path_ops and Expression Indexes

A GIN index on a jsonb column looks like the obvious fix for slow JSON queries, and it is often the wrong one. Here is which index each query shape can actually use, what GIN costs on writes, and how JSONB indexing behaves on tables with hundreds of millions of rows.

· 13 min read · AG Data Team

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):

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';
If a field is in every query, it probably wants to be a column. In our experience, the cleanest fix for a hot JSONB key such as a customer or tenant ID is to promote it to a real column with a normal index and keep the rest of the document as JSONB.

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).

-- 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:

Key takeaways

PostgreSQL JSONB GIN index Performance
Work with us

JSONB queries getting slower every month?

We review schemas, query plans and index design for PostgreSQL databases where JSONB columns hold hundreds of millions of documents, and fix them without downtime.

Get in touch More from the blog