Indexing JSONB With GIN in PostgreSQL and When It Helps

If a JSONB column has a GIN index on it, searches that look inside the documents can go from scanning the table to touching a handful of pages. That is the pitch, and it is true for a certain shape of query. This article builds the indexes on a table of 200,000 documents, shows what each one supports and costs in size, build time, insert time and query time, and ends with the cases where I would not index a JSONB column with GIN at all.

What GIN stores

A GIN (generalized inverted) index stores, for each distinct element extracted from a value, the list of rows that contain it. For JSONB, the elements are the keys and values of the document. A query for "documents containing brand: Acme" looks up the element and reads its row list, instead of reading every row.

PostgreSQL provides two operator classes for JSONB:

jsonb_path_ops stores one entry per value, hashed together with the keys above it. That makes it smaller, and faster for containment, which matches whole paths. But it cannot answer a question about a key alone, because there is no entry for "key exists". If you need ?, you need the default class.

Creating them

create index products_attrs_gin  on products using gin (attrs);
create index products_attrs_path on products using gin (attrs jsonb_path_ops);

On the 200,000-row table, the results were:

     indexrelid      | pg_size_pretty 
---------------------+----------------
 products_pkey       | 4632 kB
 products_attrs_gin  | 24 MB
 products_attrs_path | 18 MB

The table itself was 59 MB, so the default GIN index was 40 percent of it. In a separate copy I timed the builds: 1.5 seconds for the default class, 0.65 seconds for jsonb_path_ops. A third option is to index only the part you search, with an expression: create index on g1 using gin ((attrs -> 'tags')) took 52 ms to build and was 544 kB, because it indexes one small array and not the whole document. Where you know which sub-document gets searched, an expression index is a tenth of the cost.

What it buys

The lookup by SKU, using containment:

explain (analyze, costs off) select id from products where attrs @> '{"sku":"SKU-123456"}';

Without an index: a parallel sequential scan, Rows Removed by Filter: 66666 per worker, 17.2 ms. With jsonb_path_ops: a bitmap index scan, one heap page, 0.033 ms. That is five hundred times faster, and it is the case GIN exists for: a selective search for a value among many documents.

Existence checks work the same way with the default class. attrs ? 'clearance' (a key that no row has) took 0.025 ms with the default GIN index. When I dropped it and kept only jsonb_path_ops, the planner fell back to a 17 ms sequential scan, since the index cannot answer ?.

Combined conditions are fine. attrs @> '{"brand":"Acme","specs":{"color":"red"}}' and (attrs->>'price')::numeric > 499 used the index for the containment part, found 10,000 candidates, and filtered the price on the heap, 15 rows left, in 10.6 ms. The index does the narrowing it is good at and the rest of the condition is a filter.

What it does not help

GIN answers "does this document contain this" and "does this key exist". It does not answer questions that need ordering or ranges. Two jsonpath queries on the same table:

select count(*) from g1 where attrs @@ '$.specs.weight_g > 990';
select count(*) from g1 where attrs @? '$.specs.weight_g ? (@ > 990)';

Both used a parallel sequential scan at about 21 ms, even though both operators are in the list GIN supports. The index can use jsonpath only for the parts that are equality checks on a path; a greater-than has no entry to look up. For range queries, use an expression btree on the extracted number, as in create index on products (((attrs #>> '{specs,weight_g}')::int)).

Also not helped: a query for a value most of the table has. attrs @> '{"in_stock": true}' matches 171,429 of the 200,000 rows, and the planner chose a sequential scan (14.8 ms), correctly. Even when a bitmap scan is chosen, a low-selectivity query pays for the heap: attrs @> '{"tags":["gift"]}' matched a third of the table and took 21.9 ms with jsonb_path_ops, against 62.6 ms through the small (attrs -> 'tags') expression index. The index finds the matching rows quickly, then the executor still has to read 6,667 heap pages. A usual rule of thumb is that GIN pays off when a query returns well under a few percent of the table.

An arrow expression is not an indexed expression. attrs ->> 'sku' = 'SKU-123456' ran a 19 ms scan with a GIN index on the column, as mentioned in querying JSONB with the arrow operators. Either rewrite the filter as attrs @> '{"sku": "..."}' or build a btree on the expression.

The write cost

An index is a cost on every write. I loaded the same 200,000 rows into three tables:

no index                      0.50 s
GIN, jsonb_path_ops           2.49 s
GIN, default (jsonb_ops)      3.45 s

The default class indexes every key and every value as its own entry, so it does more work per row than the path class. For a table loaded once and read often that is fine. For a table with a steady insert stream, the extra time is on every insert, and GIN softens it with a pending list: new entries are appended to a small unsorted list and merged into the main index in bulk, by vacuum or when the list reaches gin_pending_list_limit (4 MB by default). Searches have to scan the pending list too, so a very busy table can see query latency wobble when the list is long and then drops after a merge. You can turn this off per index with fastupdate = off if you prefer steady inserts to fast ones.

Updates are worse than inserts. A change to anything in the document is a change to the indexed column, so the update cannot be a HOT update, and every index on the table gets new entries. In my measurement, 3,000 updates of one key were HOT in 1,261 cases without the GIN index and in none with it; see updating one key inside a JSONB column. If a table is write-heavy, this is the most important line in this article.

Choosing, in practice

Start with the queries you actually run, and list the operators.

1. Equality on one or two known fields. A btree on the expression or on a generated column. Smaller, supports ordering and ranges, cheap to write: create index on products ((attrs ->> 'sku')) was 6.2 MB against 18 MB for the path GIN index, and answered the lookup in 0.06 ms.

2. Containment of fragments, with unpredictable keys. jsonb_path_ops. This is the "search by any attribute" case, such as a filter UI where users pick arbitrary attribute combinations.

3. **Key existence tests (?, ?|, ?&) or a mix with containment.** The default jsonb_ops.

4. A single array or sub-object that gets searched. A GIN expression index on that part only: (attrs -> 'tags'), a few hundred kilobytes.

5. Ranges, sorting, joins. None of the above helps. Promote the field to a column, as in the hub guide's generated column section.

Check the plan before and after with explain (analyze, buffers), and look at Rows Removed by Index Recheck and Heap Blocks to see how much of the heap the index is making you read. And check that the index is used at all: pg_stat_user_indexes.idx_scan tells you after a day of traffic. An index with idx_scan = 0 is cost with no benefit.

For a quick look at what a JSONB value is made of before you design a search, the JSON formatter shows a document's structure, and querying arrays inside JSONB covers the array operators GIN supports.

When a GIN index does not pay for itself

The table is small. Below a few thousand rows a sequential scan is as fast as an index lookup, and the planner knows it. I would not index a table of 5,000 documents.

You filter on the same three fields every time. Three btree indexes on expressions are less than one GIN index in size and write cost, and they support sorting.

The table is write-heavy. Every insert pays 5 to 7 times more with GIN than without, in my test, and every update loses HOT. Index a read replica's copy, move the searchable fields out, or accept the cost with eyes open.

Queries return a large share of the table. The index finds rows quickly and the executor still reads most of the table. Look at the percentage of rows returned, not the count.

You need range or full-text search over values inside. A range wants a btree, text search wants tsvector with its own GIN index, built from the extracted text, and trigram matching wants pg_trgm. JSONB's GIN is for structure, not for text relevance.

The documents are huge. The index grows with the number of distinct keys and values. For documents with thousands of keys the index can exceed the table. Index the expression you need.

The operator classes and their supported operators are documented in the manual, under JSONB indexing and GIN, including the pending list and gin_pending_list_limit discussed above.