How do I index JSONB columns in PostgreSQL so queries are fast? · DpkDB
How do I index JSONB columns in PostgreSQL so queries are fast?
GIN helps containment and key queries; ->> comparisons need expression indexes. Match the index to the operator, and promote hot keys to generated columns.
You moved flexible attributes into a jsonb column: product specs, event payloads, user settings. Queries like these were instant in development and now take seconds:
SELECT*FROM events WHERE payload->>'type'='purchase';
SELECT*FROM products WHERE attrs @>'{"color": "red"}';
You added an index on payload and nothing changed. jsonb can be indexed very well, but which index helps depends on the operator in your query.
Operators decide which index can be used
Query shape
Operator
Index that helps
attrs @> '{"color":"red"}'
containment
GIN (jsonb_ops or jsonb_path_ops)
attrs ? 'discount'
key exists
GIN (jsonb_ops only)
attrs @? '$.tags[*] ? (@ == "sale")'
jsonpath
GIN
payload->>'type' = 'purchase'
extract, then compare
B-tree expression index
(payload->>'amount')::numeric > 100
extract, cast, range
B-tree expression index on the cast
A plain B-tree index on the whole jsonb column only helps when comparing entire documents, which almost nobody does.
After an import or migration, every insert collides with an existing id because the sequence is behind. Reset one or all sequences, and prevent it with identity columns.
-- Default operator class: supports @>, ?, ?|, ?&, @?, @@CREATE INDEX products_attrs_gin ON products USING gin (attrs);
-- Smaller and faster, but only @> (and jsonpath @?, @@)CREATE INDEX products_attrs_path ON products USING gin (attrs jsonb_path_ops);
Write queries that use containment so they can use the index:
-- ✅ uses GINSELECT*FROM products WHERE attrs @>'{"color": "red", "size": "M"}';
-- ❌ can't use GIN: extraction + equalitySELECT*FROM products WHERE attrs->>'color'='red';
jsonb_path_ops indexes are usually much smaller than the default and faster for @>. Choose the default only if you need the key-exists operators (?, ?|, ?&).
Expression indexes for a hot key
If you always filter or sort on one or two keys, index exactly that expression:
CREATE INDEX events_type_idx ON events ((payload->>'type'));
CREATE INDEX events_amount_idx ON events (((payload->>'amount')::numeric));
SELECT*FROM events WHERE payload->>'type'='purchase'; -- uses events_type_idxSELECT*FROM events WHERE (payload->>'amount')::numeric>100; -- uses events_amount_idx
The query must use the same expression (the same operator, cast and parentheses) as the index. payload->'type' = '"purchase"' (the -> operator, which returns jsonb) won't use an index built on ->>.
Expression indexes are small and support ranges and ORDER BY, which GIN can't do.
Promote hot keys to real columns
If a key is on every row and used in most queries, it probably isn't "flexible" data. A generated column gives you a typed, easily indexed column without changing writes:
ALTER TABLE events
ADDCOLUMN event_type text GENERATED ALWAYS AS (payload->>'type') STORED;
CREATE INDEX ON events (event_type);
Adding a STORED generated column rewrites the table, so plan it like any other large migration. Real columns also get proper statistics. The planner estimates payload->>'type' = 'x' poorly (it can't see inside the JSON), which causes bad plans. With an expression index you can at least ANALYZE to get statistics for that expression.
Other jsonb performance traps
Large documents are TOASTed: compressed and stored out of line. Reading one key still has to fetch and decompress the whole document. Keep big blobs out of rows you query often, or split them into another table.
Updates rewrite the whole document, even jsonb_set on one key, which creates a new row version (MVCC) every time. Frequently updated counters inside JSON cause bloat. Keep them in regular columns.
GIN indexes slow down writes. Pending-list settings (fastupdate, gin_pending_list_limit) trade write speed for read speed. Index only what you query.
Checklist
Match the index to the operator: GIN for @>/?/jsonpath, B-tree expression for ->> comparisons.
Rewrite equality filters as @> to use a GIN index; prefer jsonb_path_ops when you only need @>.
Keep query and index expressions identical, including casts.
Promote hot keys to generated columns, and keep huge or frequently updated data out of jsonb.
A founder's guide to choosing PostgreSQL or MySQL: team experience, managed hosting, scaling paths, extensions, licensing, switching cost, and a clear "pick PostgreSQL unless" rule.
timestamptz doesn't store a time zone, and timestamp silently drops the offset you pass in. What each type really stores, where timestamp causes bugs and the one case where it's right.