Definition
- GIN index – A Generalized Inverted Index that stores every key/value path in a jsonb column, supporting containment (
@>), existence (?), and path operators. - jsonb_path_ops – An operator class that indexes only value paths needed for the containment operator, producing a smaller, faster index that supports
@>but not key-existence (?). - B-tree expression index – An index on an extracted scalar value like
(data->>'status'), supporting equality, ranges, and ordering as a normal index seek. - Operator matching – The index must match the query operator exactly; a mismatch means the index is ignored and the planner falls back to a scan.
- Write cost – GIN indexes are larger and slower to update than B-tree indexes, so choose based on the actual query pattern.
National Bank of India stores flexible KYC data in jsonb. Without an index, finding "all active customers" reads every document. A GIN index is like a cross-reference catalogue of every field and value in those documents, so the bank jumps straight to the active ones. If the bank only ever searches by one field, a focused B-tree index on just that field is even leaner – like a single-column phone directory instead of a full encyclopedia index.
Understanding the Concepts
What is a GIN index on jsonb?
A GIN (Generalized Inverted Index) on a jsonb column indexes every key/value path in the document, so containment and existence queries can be answered without scanning.
-- National Bank of India: KYC data stored as jsonb
CREATE TABLE customers (
customer_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(100) NOT NULL,
kyc JSONB NOT NULL
);
-- Sample KYC data:
-- { "status": "active", "pan": "ABCDE1234F",
-- "address": { "city": "Mumbai", "state": "MH" } }
-- Default GIN index: indexes all keys and values
CREATE INDEX ix_kyc_gin ON customers USING GIN (kyc);
This index supports containment, existence, and path operators.
-- Containment: does the document contain this sub-object?
SELECT * FROM customers
WHERE kyc @> '{"status": "active"}';
-- Plan: Bitmap Index Scan on ix_kyc_gin
-- Key existence: does the document have this key?
SELECT * FROM customers
WHERE kyc ? 'pan';
-- Plan: Bitmap Index Scan on ix_kyc_gin
-- Path containment: nested object
SELECT * FROM customers
WHERE kyc @> '{"address": {"city": "Mumbai"}}';
-- Plan: Bitmap Index Scan on ix_kyc_gin
What is jsonb_path_ops and when should you use it?
The jsonb_path_ops operator class indexes only the value paths needed for the containment operator (@>), producing a smaller, faster index. But it does not support key-existence (?).
-- jsonb_path_ops: smaller, faster, but only supports @>
CREATE INDEX ix_kyc_pathops
ON customers USING GIN (kyc jsonb_path_ops);
-- Works: containment query
SELECT * FROM customers
WHERE kyc @> '{"status": "active"}';
-- Plan: Bitmap Index Scan on ix_kyc_pathops
-- Does NOT work: key existence
SELECT * FROM customers
WHERE kyc ? 'pan';
-- Plan: Seq Scan (jsonb_path_ops does not support ? operator)
| Feature | Default GIN | GIN jsonb_path_ops |
|---|---|---|
| Containment @> | Yes | Yes |
| Key existence ? | Yes | No |
| Path operators @?, @@ | Yes (PG 12+) | No |
| Index size | Larger | Smaller (~2-3x) |
| Build time | Slower | Faster |
| Best for | Mixed operator queries | Pure containment queries |
When should you use a B-tree expression index instead?
If you only ever filter on one scalar key, a B-tree expression index is better. It supports equality, ranges, ordering, and is much leaner than a GIN.
-- B-tree expression index on the extracted status value
CREATE INDEX ix_kyc_status
ON customers ((kyc->>'status'));
-- Equality query: uses the expression index
EXPLAIN ANALYZE
SELECT * FROM customers
WHERE kyc->>'status' = 'active';
Index Scan using ix_kyc_status on customers
Index Cond: ((kyc ->> 'status') = 'active')
Rows: 500
Execution Time: 2 ms
-- Range query: also works with B-tree expression index
SELECT * FROM customers
WHERE (kyc->>'risk_score')::int > 80;
-- Needs an expression index on (kyc->>'risk_score')::int
CREATE INDEX ix_kyc_risk
ON customers (((kyc->>'risk_score')::int));
SELECT * FROM customers
WHERE (kyc->>'risk_score')::int > 80
ORDER BY (kyc->>'risk_score')::int DESC;
-- Plan: Index Scan on ix_kyc_risk
Why must the index match the query operator exactly?
A GIN index does not speed up an extracted-text equality, and a B-tree expression index does not help a containment query.
-- GIN index exists on kyc
CREATE INDEX ix_kyc_gin ON customers USING GIN (kyc);
-- This does NOT use the GIN index:
SELECT * FROM customers WHERE kyc->>'status' = 'active';
-- Plan: Seq Scan (GIN does not support ->> equality)
-- B-tree expression index exists on (kyc->>'status')
CREATE INDEX ix_kyc_status ON customers ((kyc->>'status'));
-- This does NOT use the expression index:
SELECT * FROM customers WHERE kyc @> '{"status": "active"}';
-- Plan: Seq Scan (expression index does not support @>)
-- You need the right index for the right operator:
-- @> containment -> GIN index
-- ->> = equality -> B-tree expression index
How do you combine both strategies?
For a hot table with multiple access patterns, create both indexes.
-- GIN for containment queries (compliance searches)
CREATE INDEX ix_kyc_gin ON customers USING GIN (kyc jsonb_path_ops);
-- B-tree for the hot status lookup (dashboard)
CREATE INDEX ix_kyc_status ON customers ((kyc->>'status'));
-- Compliance search: uses GIN
SELECT * FROM customers
WHERE kyc @> '{"address": {"city": "Mumbai"}, "status": "active"}';
-- Dashboard status filter: uses B-tree expression index
SELECT customer_id, name
FROM customers
WHERE kyc->>'status' = 'active'
ORDER BY name;
// com.bank.service.KycSearchService
package com.bank.service;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class KycSearchService {
/**
* Containment query: uses the GIN index.
* The @> operator checks if the document contains the sub-object.
*/
public List<String> findByContainment(
Connection conn, String jsonFilter) throws SQLException {
String sql = """
SELECT customer_id, name
FROM customers
WHERE kyc @> ?::jsonb
""";
List<String> results = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, jsonFilter);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
results.add(rs.getString("customer_id")
+ " | " + rs.getString("name"));
}
}
}
return results;
}
/**
* Extracted-value query: uses the B-tree expression index.
* The ->> operator extracts a text value.
*/
public List<String> findByStatus(
Connection conn, String status) throws SQLException {
String sql = """
SELECT customer_id, name
FROM customers
WHERE kyc->>'status' = ?
ORDER BY name
""";
List<String> results = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, status);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
results.add(rs.getString("customer_id")
+ " | " + rs.getString("name"));
}
}
}
return results;
}
}
What are the trade-offs of GIN indexes?
GIN indexes are powerful but have write-cost implications.
| Feature | GIN Index | B-tree Expression Index |
|---|---|---|
| Query types | Containment, existence, paths | Equality, range, ordering |
| Index size | Large (indexes every path) | Small (one extracted value) |
| Write cost | Higher (updates on every change) | Lower (only if extracted value changes) |
| Build time | Slower | Faster |
| Best for | Flexible, multi-key searches | Single hot-key lookups |
What should a senior engineer know?
- In PostgreSQL 12+, you can also index JSONPath expressions with GIN for
@?and@@operators. - GIN indexes support
pending_pagesfor deferred updates, which can improve write throughput at the cost of slightly slower reads. - For very large jsonb columns, consider normalizing hot fields into regular columns with indexes, and keeping the jsonb for flexible data only.
- Always confirm with
EXPLAIN ANALYZEthat the intended index is chosen, because operator mismatches silently fall back to a scan.
-- PostgreSQL 12+: JSONPath index
CREATE INDEX ix_kyc_jsonpath
ON customers USING GIN (kyc jsonb_path_ops);
-- JSONPath query
SELECT * FROM customers
WHERE kyc @? '$.address.city ? (@ == "Mumbai")';
What are common interview follow-up questions?
- What is the difference between the default GIN operator class and jsonb_path_ops?
- When is a B-tree expression index better than a GIN index for jsonb?
- How does PostgreSQL's visibility map affect GIN index scans?
- What is the write-cost implication of having both GIN and B-tree indexes on the same jsonb column?
Visual Overview
┌─────────────────────────────────────────────────┐
│ Indexing JSON Fields: GIN vs B-tree │
├─────────────────────────────────────────────────┤
│ │
│ GIN Index (jsonb_path_ops): │
│ ┌──────────────────────────────────┐ │
│ │ Indexes every value path │ │
│ │ Supports: @> containment │ │
│ │ Query: kyc @> '{"status":"active"}'│ │
│ │ Size: large │ │
│ └──────────────────────────────────┘ │
│ │
│ B-tree Expression Index: │
│ ┌──────────────────────────────────┐ │
│ │ Indexes one extracted value │ │
│ │ Supports: = , > , < , ORDER BY │ │
│ │ Query: kyc->>'status' = 'active' │ │
│ │ Size: small │ │
│ └──────────────────────────────────┘ │
│ │
│ Index must match operator: │
│ @> containment ──► GIN index │
│ ->> = equality ──► B-tree expression │
│ Mismatch ──► Seq Scan! │
│ │
│ Decision: │
│ Multi-key flexible search ──► GIN │
│ Single hot-key lookup ──► B-tree expression│
│ Both patterns ──► both indexes │
└─────────────────────────────────────────────────┘
Key Takeaway
To index JSON efficiently in PostgreSQL, use a GIN index on the jsonb column, and when the access pattern is containment, add the jsonb_path_ops operator class, which indexes only the value paths needed for the containment operator and produces a smaller, faster index. Query with the @> containment operator, asking whether the document contains a given sub-object, and the planner uses the GIN index instead of scanning. When the access pattern is always a single scalar key, prefer a B-tree expression index on the extracted value, because that supports equality, ranges, and ordering as a normal index seek.
In the National Bank of India compliance screen that filters customers by KYC status over millions of rows, the unindexed query scanned every document. Rajesh added a GIN index with jsonb_path_ops so the active-customer filter using containment became an index scan, and for the hot status key he also created a B-tree expression index on the extracted status so a plain equality filter is a direct seek. The key discipline is that the index must match the query operator exactly – a GIN on the whole document does not accelerate an extracted-text equality, and a B-tree expression index does not help a containment query – and he always confirms the chosen plan with EXPLAIN ANALYZE.
Pankaj also reminds the team that GIN indexes cost more on writes than B-tree indexes, so the choice depends on the actual query pattern. For tables with heavy writes and only one hot key, a B-tree expression index is the right choice. For flexible multi-key searches across the document, GIN is the right choice. And for tables with both patterns, both indexes can coexist.
Remember one sentence: GIN with jsonb_path_ops for containment queries, and a B-tree expression index for a single hot scalar key.
---