Index JSONB Columns for Efficient Querying

Why This Matters

JSONB columns are powerful for storing flexible, schema-less data, but queries on JSONB can be slow without proper indexing. GIN indexes make JSONB queries fast, especially when using the jsonb_path_ops operator class.

Index JSONB columns when:

• Querying nested paths: data->>'status' or data @> '{"status": "active"}'
• Filtering on JSON values: Finding rows where JSON contains specific keys/values
• Using containment operators: @> and ? operators
• Large JSONB columns: Tables with millions of rows containing JSON data

The Problem

Querying JSONB without indexes causes sequential scans:

-- No index, full table scan
SELECT * FROM products 
WHERE metadata @> '{"category": "electronics"}';

-- Or using path operator
SELECT * FROM products 
WHERE metadata->>'status' = 'active';

-- EXPLAIN shows:
-- Seq Scan on products (cost=0.00..25000.00 rows=1000)
--   Filter: (metadata @> '{"category": "electronics"}'::jsonb)
--   Rows Removed by Filter: 999000

The Solution

Create a GIN index on the JSONB column:

-- Standard GIN index (supports all operators)
CREATE INDEX products_metadata_idx 
ON products USING gin (metadata);

-- Or use jsonb_path_ops for containment queries (smaller, faster)
CREATE INDEX products_metadata_idx 
ON products USING gin (metadata jsonb_path_ops);

-- Now queries use index
SELECT * FROM products 
WHERE metadata @> '{"category": "electronics"}';

-- EXPLAIN shows:
-- Bitmap Index Scan on products_metadata_idx
--   Index Cond: (metadata @> '{"category": "electronics"}'::jsonb)

Index Types

• Default GIN: Supports all JSONB operators (@>, ?, ?&, ?|, @?, @@)
• jsonb_path_ops: Only supports containment (@>), but is 3-4x smaller and faster

Query Patterns

Different operators work with different index types:

-- Containment (@>) - works with both index types
SELECT * FROM products 
WHERE metadata @> '{"status": "active"}';

-- Key existence (?) - only works with default GIN
SELECT * FROM products 
WHERE metadata ? 'category';

-- Path queries - use expression index
CREATE INDEX products_status_idx 
ON products ((metadata->>'status'));

SELECT * FROM products 
WHERE metadata->>'status' = 'active';

Common Mistakes

• Using jsonb_path_ops for key existence: jsonb_path_ops only supports @> - use default GIN if you need ? operator.
• Not indexing frequently queried paths: If you always query metadata->>'status', create an expression index on that path.
• Indexing entire JSONB when only querying one key: Expression indexes on specific paths are smaller and faster than full GIN indexes.
• Forgetting GIN indexes slow writes: GIN indexes are slower to update than B-tree indexes - only index JSONB columns you actually query.