PostgreSQL JSONB Indexing: GIN, Expression & Partial Index Strategies

2026-04-18

PostgreSQL JSONB columns store flexible, schema-less data inside a relational database. Without proper indexing, every query against JSONB fields triggers a sequential scan. GIN indexes, expression indexes on specific keys, and partial indexes each solve different JSONB query patterns — choosing the right strategy depends on how you query the data.

The Problem

JSONB is one of PostgreSQL's most powerful features. It lets you store semi-structured data — user preferences, API payloads, feature flags, event metadata — without defining every column up front. The problem is that developers treat JSONB as a black box: they throw data in, query it with operators like ->, ->>, and @>, and assume PostgreSQL will figure out how to make it fast.

It will not. Without an index, every JSONB query scans the entire table. A query like WHERE metadata->>'status' = 'active' on a 5-million-row events table reads every single row, extracts the status key from every JSONB document, and compares it. This is catastrophically slow — and it gets worse as the table grows.

The confusion deepens because JSONB supports multiple index types, each with different operator support and trade-offs:

GIN indexes with the default jsonb_ops operator class support @>, ?, ?|, ?& operators — but not ->> equality checks
GIN indexes with jsonb_path_ops support only @> containment — smaller and faster, but less flexible
Expression indexes (B-tree on a specific key extraction) support =, <, >, BETWEEN on a single extracted value
Partial indexes combine any of the above with a WHERE clause to index only a subset of rows

The most common mistake is creating a GIN index and expecting it to accelerate ->> equality queries. A GIN index on a JSONB column does not help WHERE data->>'email' = 'user@example.com' — that query needs an expression index. The second most common mistake is creating a full GIN index when you only query one or two keys, wasting disk space and write performance on indexing the entire document.

GIN index maintenance cost is real. Every INSERT or UPDATE that touches the JSONB column must update the GIN index, which decomposes the entire document into indexed entries. On write-heavy tables with large JSONB documents, this overhead can cut insert throughput by 30-50%.

How to Detect It

Start by identifying which JSONB queries are performing sequential scans:

-- Find tables with JSONB columns that lack indexes
SELECT
    t.schemaname,
    t.relname AS table_name,
    a.attname AS column_name,
    pg_size_pretty(pg_relation_size(t.relid)) AS table_size,
    t.seq_scan,
    t.seq_tup_read
FROM pg_stat_user_tables t
JOIN pg_attribute a ON a.attrelid = t.relid
JOIN pg_type ty ON ty.oid = a.atttypid
WHERE ty.typname = 'jsonb'
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND t.seq_scan > 100
ORDER BY t.seq_tup_read DESC;

Check whether existing GIN indexes are actually being used by the query planner:

-- Find GIN indexes on JSONB columns and their usage
SELECT
    schemaname,
    indexrelname AS index_name,
    relname AS table_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%gin%'
   OR indexrelname LIKE '%jsonb%'
ORDER BY idx_scan ASC;

Confirm the problem with EXPLAIN on your actual JSONB queries:

-- This query will NOT use a default GIN index
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE metadata->>'status' = 'active';

-- This query WILL use a default GIN index
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE metadata @> '{"status": "active"}';

If you see Seq Scan on the first query despite having a GIN index, the index does not support the ->> operator. You need either an expression index or to rewrite the query using the @> containment operator.

How myDBA.dev Shows It

myDBA.dev's Index Advisor identifies JSONB indexing problems from two angles. The query analysis surfaces slow queries that filter on JSONB columns — sorting by total execution time, you will quickly spot queries like SELECT * FROM events WHERE metadata->>'type' = 'purchase' consuming seconds of cumulative time across thousands of calls. The performance grade drops as these queries degrade, providing an early warning that indexing is needed.

Index Advisor showing JSONB-related index recommendations with usage trends and optimization opportunities

The Queries page provides the complementary view — click any slow JSONB query to see its EXPLAIN plan, confirming whether the planner chose a sequential scan or a GIN index scan. The plan output shows buffer counts, making the I/O cost of missing indexes immediately visible. A sequential scan reading 45,000 buffers versus a GIN index scan reading 12 buffers tells you exactly how much performance you are leaving on the table.

Queries page showing JSONB query performance with execution plans and buffer statistics

How to Fix It

The right index strategy depends on your query patterns. Here are the three main approaches:

GIN Index for Containment Queries (`@>`)

If your queries use the @> containment operator, a GIN index is the right choice:

-- Default operator class: supports @>, ?, ?|, ?&
CREATE INDEX CONCURRENTLY idx_events_metadata_gin
    ON events USING gin (metadata);

-- jsonb_path_ops: supports only @>, but 2-3x smaller and faster
CREATE INDEX CONCURRENTLY idx_events_metadata_pathops
    ON events USING gin (metadata jsonb_path_ops);

Use jsonb_path_ops when you only need containment queries. It creates a significantly smaller index by hashing the full path to each value rather than indexing every key and value separately. On a table with 10 million rows containing complex JSONB documents, the size difference can be 3-4x.

Rewrite ->> equality queries to use @> to take advantage of GIN indexes:

-- Before: does NOT use GIN index
SELECT * FROM events WHERE metadata->>'status' = 'active';

-- After: DOES use GIN index
SELECT * FROM events WHERE metadata @> '{"status": "active"}';

Expression Index for Specific Key Lookups

When you repeatedly query a single key, an expression index is more efficient than GIN:

-- B-tree index on a specific extracted key
CREATE INDEX CONCURRENTLY idx_events_status
    ON events ((metadata->>'status'));

-- Now this query uses the index
SELECT * FROM events WHERE metadata->>'status' = 'active';

Expression indexes are smaller than GIN indexes (they index only one value per row, not the entire document), support range queries (<, >, BETWEEN), and support ORDER BY. They are the right choice when you query specific, known keys.

For JSONB arrays, you can index individual elements or use GIN on the array:

-- GIN index for array containment queries
CREATE INDEX CONCURRENTLY idx_events_tags_gin
    ON events USING gin ((metadata->'tags'));

-- Query: find events tagged with "important"
SELECT * FROM events
WHERE metadata->'tags' @> '"important"';

Partial Index for Selective Conditions

When only a fraction of rows match a condition, a partial index avoids indexing the entire table:

-- Index only active events (if 90% are archived)
CREATE INDEX CONCURRENTLY idx_events_active_metadata
    ON events USING gin (metadata jsonb_path_ops)
    WHERE metadata->>'status' = 'active';

This index is dramatically smaller than a full GIN index and faster to maintain, since archived rows are excluded entirely. Combine partial indexes with expression indexes for highly targeted optimization:

-- Expression index on user_id, only for purchase events
CREATE INDEX CONCURRENTLY idx_events_purchase_user
    ON events ((metadata->>'user_id'))
    WHERE metadata->>'type' = 'purchase';

After creating any index, verify with EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE metadata @> '{"status": "active", "type": "purchase"}';

Look for Bitmap Index Scan or Index Scan instead of Seq Scan, and confirm that buffer reads dropped dramatically.

How to Prevent It

Establish a JSONB indexing strategy before writing queries. When you add a JSONB column to a table, document which keys will be queried and how — this drives the index type selection:

| Query Pattern | Index Type | Example | |--------------|-----------|---------| | @> containment | GIN (jsonb_path_ops) | WHERE data @> '{"k": "v"}' | | ->> equality on known key | Expression (B-tree) | WHERE data->>'status' = 'x' | | Key existence (?) | GIN (default jsonb_ops) | WHERE data ? 'email' | | Range on extracted value | Expression (B-tree) | WHERE (data->>'score')::int > 90 | | Array containment | GIN on sub-path | WHERE data->'tags' @> '"x"' |

Monitor GIN index size relative to table size. If a GIN index is approaching or exceeding the table size, you are likely indexing too much. Switch to expression indexes on the keys you actually query, or use jsonb_path_ops to reduce GIN overhead.

Watch for write throughput degradation after adding GIN indexes. Track insert latency in myDBA.dev's query performance trends — if INSERT statements slow down significantly after a GIN index is created, consider whether a targeted expression index or partial index would serve the same queries with less write overhead.

Review JSONB query patterns quarterly. As application features evolve, the keys you query change. An index on metadata->>'status' may become irrelevant when the application switches to a state key. Unused JSONB indexes waste space and slow writes for zero benefit.

FAQ

Should I use JSON or JSONB in PostgreSQL?

Use JSONB in nearly all cases. JSONB stores data in a decomposed binary format that supports indexing, containment operators, and efficient key lookups. JSON stores the raw text and must be reparsed on every access. The only reason to use JSON is if you need to preserve exact formatting, key order, or duplicate keys — which is rare. JSONB is slightly slower on insert (due to parsing) but dramatically faster on read and query operations.

Does a GIN index help with the ->> operator?

No. The default GIN index (jsonb_ops) supports @>, ?, ?|, and ?& operators. It does not accelerate ->> equality comparisons. To index ->> queries, create an expression index: CREATE INDEX ON table ((column->>'key')). Alternatively, rewrite the query to use @> containment, which the GIN index does support.

How much space does a GIN index on JSONB use?

GIN indexes on JSONB are typically 1-3x the size of the table itself, depending on document complexity and the number of keys. Using jsonb_path_ops instead of the default operator class reduces index size by 2-3x because it hashes full paths rather than indexing individual keys and values. For large tables with complex documents, the difference between a 2GB and a 600MB index is significant for both storage and maintenance.

Can I index nested JSONB keys?

Yes. Expression indexes support nested path extraction: CREATE INDEX ON table ((column->'address'->>'city')). GIN indexes automatically index all nested keys and values in the document. For deeply nested structures, expression indexes on the specific paths you query are more efficient than a full GIN index.