Index JSONB Attributes for Containment Queries
Add a GIN index on JSONB attributes, query with containment operators, and confirm the planner uses the index instead of scanning.
Task
Make attribute lookups on a JSONB column fast without indexing every possible key in every document.
When to use this
Events store flexible attributes in a jsonb column. Filtering by attributes->>'plan' takes four seconds because every query reads and parses the whole table.
Prerequisites
- PostgreSQL 13 or newer
- A jsonb column with a stable set of filtered attributes
- EXPLAIN (ANALYZE, BUFFERS) access for verification
Steps
1. Create a GIN index with the smaller jsonb_path_ops operator class for containment queries.
CREATE INDEX events_attrs_gin
ON events USING gin (attributes jsonb_path_ops);
Expected output: The index is created and is typically several times smaller than the default jsonb_ops index.
2. Rewrite multi-key filters to use containment instead of extracting each key.
SELECT id, occurred_at
FROM events
WHERE attributes @> '{"plan": "pro", "region": "eu"}'::jsonb;
Expected output: Only rows whose attributes contain both pairs are returned, and the GIN index can serve the predicate.
3. For frequent single-key equality, add a btree expression index on the extracted value.
CREATE INDEX events_plan_idx ON events ((attributes ->> 'plan'));
Expected output: Equality filters on plan use a compact btree index, and ordering by the expression becomes possible.
4. Confirm the plan uses a bitmap index scan rather than a sequential scan.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE attributes @> '{"plan": "pro"}'::jsonb;
Expected output: Bitmap Index Scan on events_attrs_gin with a Recheck Cond, and far fewer buffers than a Seq Scan.
Troubleshooting
Problem
The GIN index is never chosen
Solution
The query probably uses ->> or ->, which containment does not cover. Use @> or create an expression index for the extracted key.
Problem
Index size grows faster than the table
Solution
Switch to jsonb_path_ops if you only need containment, and avoid indexing documents with large, high-cardinality arrays.
Problem
Writes slowed down after adding the index
Solution
GIN maintenance is expensive. Check the pending list, tune gin_pending_list_limit, and keep the indexed document small.
Limitations
- jsonb_path_ops supports only @>, ?, ?| and ?&; other operators require jsonb_ops.
- GIN indexes do not provide ordering, so ORDER BY on an attribute needs a btree expression index.
- Indexing every attribute increases write cost; index only the keys used in filters.
JSONB is flexible, but not free to query
A jsonb column lets different rows carry different attributes, which is useful for events and settings. The cost appears at query time: attributes ->> 'plan' = 'pro' has to extract and compare a value for every row, and no ordinary index can help. On a few million rows that is a sequential scan and a parse of every document, which is why the endpoint that was fine at launch becomes the slowest one in the system.
Containment is the indexable shape
@> asks whether the left document contains the right one, and a GIN index can answer that without reading every row. The jsonb_path_ops operator class indexes hashes of paths rather than full keys, which makes it smaller and faster for containment at the cost of dropping some operators. Use it when filters are {key: value} pairs.
CREATE INDEX events_attrs_gin ON events USING gin (attributes jsonb_path_ops);
SELECT id FROM events WHERE attributes @> '{"plan": "pro"}'::jsonb;Not every filter is containment
If a query only checks one key for equality, a btree expression index is often better: it is smaller, supports ordering, and can be combined with range predicates. Queries that compare values, such as attributes ->> 'score' > '80', need an expression index with the right cast, or a generated column with a proper type. Choose the index per query shape instead of adding one generic GIN and hoping.
Watch the write cost
GIN indexes buffer updates in a pending list and merge them later. A bulk import of millions of documents can leave a large pending list, making reads slower until it is flushed. Monitor index size and write latency after the change, and consider excluding very large payloads from the indexed column, for example by indexing only a small attributes object rather than the entire event.
Verify and iterate
Run the real query shape under EXPLAIN (ANALYZE, BUFFERS) before and after. A bitmap index scan with a recheck is expected for containment; a sequential scan means the predicate shape or the operator class is wrong. Keep the query text and the plan in the same review, because an index that no query can use is pure write overhead.