GIN indexes on JSONB: the module's most important decision

Capsule overview

Up to here you talked about operators and path queries. You know that @> is indexable and that ->> isn't. But "indexable" is only the beginning — the real decision is which kind of GIN to create. PostgreSQL gives you two operator classes for gin(jsonb): jsonb_ops (the default) and jsonb_path_ops. They pick a different trade-off between operator coverage, index size, and query speed.

This is the most important decision in the whole module. It's the difference between 4 seconds and 12 milliseconds in the anchor case (50M rows). It's the difference between an 8 GB index and a 3 GB one. And it's the decision 90% of devs never make consciously — they use the default and move on.

In this capsule you're going to understand how GIN works internally (enough detail to make the decision, not to implement one), you're going to compare the two operator classes with real benchmarks over the same table, you're going to see partial indexes and expression indexes (the two techniques that separate a senior dev from a junior one in JSONB), and you're going to learn to validate with EXPLAIN ANALYZE which index the planner is using — because if you don't validate, you aren't indexing, you're creating files.

By the end you'll be able to defend the jsonb_ops vs jsonb_path_ops decision in an interview with concrete criteria, you'll have the mechanics of partial indexes on JSONB clear, and you'll be ready for the module project (capsule 08) where you apply all of this to 5M rows.

Mental model: how GIN works over JSONB

GIN stands for Generalized Inverted Index. The key word is inverted — it comes from the world of full-text search.

A B-tree indexes each row in a single index entry. GIN indexes each component of a composite value. If your row has {"a": 1, "b": 2, "c": 3}, GIN doesn't store a single entry — it stores multiple entries (one per key/value pair, or in a specific encoding) that point to that row.

B-tree over an integer column:
   rows: [(1, row 5), (2, row 9), (3, row 1), ...]
   query "= 2"  →  looks up "2"  →  goes to row 9

GIN over JSONB:
   tokens of the JSON {"a":1,"b":2}:  ["a", "a/1", "b", "b/2", ...]
   query "@> {a:1}"  →  looks up tokens "a" AND "a/1"  →  intersection  →  row X

The practical consequence is:

1. GIN is more expensive to maintain. Every insert/update has to refresh all the index entries corresponding to that JSONB. What in a B-tree is 1 entry, in GIN can be 50.
2. GIN is bigger. It multiplies the index storage compared to a B-tree by typically 2-5x.
3. GIN is very fast for containment and existence searches. That's what it's designed for. It beats a sequential scan by orders of magnitude.

GIN has a fastupdate mechanism (ON by default) that defers inserts to a "pending list" so it doesn't pay the update cost on every insert. The pending list is processed periodically (vacuum, threshold). This is worth knowing: if your workload is write-heavy and you want more predictable inserts, you can disable fastupdate (WITH (fastupdate = off)), accepting higher latency per insert but gaining consistency in search.

The two operator classes: jsonb_ops vs jsonb_path_ops

PostgreSQL ships with two operator classes for indexing jsonb with GIN:

jsonb_ops (DEFAULT)

CREATE INDEX ON my_table USING gin(my_column);
-- equivalent to:
CREATE INDEX ON my_table USING gin(my_column jsonb_ops);

What it indexes: every key and every value of the JSONB as separate tokens. It supports ALL the search operators: @>, <@, ?, ?|, ?&.

Trade-off: bigger (more tokens), slower than jsonb_path_ops for @>, but it supports key-existence queries (?).

jsonb_path_ops

CREATE INDEX ON my_table USING gin(my_column jsonb_path_ops);

What it indexes: a single hash per complete path from the root to the value. It only supports @>.

Trade-off: more compact (a single hash per path), faster for @> (especially for deep JSONs), but it doesn't speed up ?, ?|, ?&, <@.

Comparison table

Decision matrix

Do your queries use ONLY @> ?
                │
                ├── YES ─→  Use jsonb_path_ops (explicit default)
                │           - Smaller index
                │           - Faster @>
                │           - Cheaper inserts
                │
                └── NO ──→  Do you also use ? / ?| / ?& ?
                            │
                            ├── YES ─→  Use jsonb_ops (implicit default)
                            │           - Accept the extra cost
                            │           - You need those operators
                            │
                            └── NO, I use <@ ──→  Use jsonb_ops
                                                   - jsonb_path_ops doesn't support <@

Empirical rule: if your app filters by containment of an object/sub-object (@>), jsonb_path_ops almost always wins. If it filters by "this key exists" (?), you need jsonb_ops.

When you use each one in practice

jsonb_path_ops:

• An events/logs table filtered by payload @> '{"status": "X"}' or payload @> '{"action": "Y", "country": "Z"}'. The module's anchor case (50M rows) uses jsonb_path_ops.
• Product metadata with queries like metadata @> '{"category": "shoes"}'.
• Polymorphic data where you filter by a discriminator: data @> '{"type": "premium"}'.

jsonb_ops:

• A dynamic schema where you need "all the records that have key X" (data ? 'feature_flag').
• Multi-tenant config where you check which tenants have certain integrations (config ? 'slack_webhook_url').
• Auditing: "records where any field of X was modified" (changes ?| ARRAY['email','phone']).

A concrete benchmark: the 2-3x factor

Let's see it with data. We generate 500k rows with realistic payloads and compare.

-- Setup
DROP TABLE IF EXISTS events;
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

INSERT INTO events (payload)
SELECT jsonb_build_object(
  'action', (ARRAY['view', 'click', 'purchase', 'signup'])[1 + (random() * 3)::int],
  'country', (ARRAY['US', 'MX', 'ES', 'AR', 'BR', 'CO', 'CL', 'PE'])[1 + (random() * 7)::int],
  'amount', round((random() * 500)::numeric, 2),
  'user_id', (random() * 100000)::int,
  'metadata', jsonb_build_object(
    'campaign', 'campaign-' || (random() * 50)::int,
    'referrer', (ARRAY['google', 'direct', 'twitter', 'email', 'facebook'])[1 + (random() * 4)::int],
    'device', (ARRAY['desktop', 'mobile', 'tablet'])[1 + (random() * 2)::int]
  )
)
FROM generate_series(1, 500000);

-- Size before indexing
SELECT pg_size_pretty(pg_total_relation_size('events'));
-- ~165 MB

We create the two indexes (on separate tables to compare fairly, or on the same one with different names):

CREATE INDEX events_payload_default ON events USING gin(payload);
CREATE INDEX events_payload_pathops ON events USING gin(payload jsonb_path_ops);

-- Size of each index
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'events' AND indexname LIKE 'events_payload%';

-- Example output:
--          indexname        |  size
-- -------------------------+--------
--  events_payload_default  | 73 MB
--  events_payload_pathops  | 38 MB

jsonb_path_ops typically takes up ~50% of the space. The difference grows with deeper JSONs.

We compare query speed:

-- Force the use of a specific index (to compare):

-- With jsonb_ops:
SET enable_seqscan = OFF;
DROP INDEX IF EXISTS events_payload_pathops;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';

-- We put the other one back
CREATE INDEX events_payload_pathops ON events USING gin(payload jsonb_path_ops);

-- With jsonb_path_ops:
DROP INDEX IF EXISTS events_payload_default;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';

Illustrative results (they vary by hardware):

jsonb_path_ops is typically 2-3x faster for pure @>. The difference grows with more complex JSONs and more selective queries.

Partial indexes on JSONB: the "secret sauce"

The big win in JSONB performance doesn't come from "indexing everything" — it comes from indexing only what matters. Partial indexes are indexes with a WHERE that filters which rows are included. For JSONB they're especially powerful.

Example: 95% of queries filter by status: active

-- A normal index: indexes all 500k records
CREATE INDEX events_payload_idx ON events USING gin(payload jsonb_path_ops);
-- Size: ~38 MB

-- A partial index: indexes only the ones with status active
CREATE INDEX events_active_idx ON events USING gin(payload jsonb_path_ops)
WHERE payload @> '{"status": "active"}';
-- Size: ~5 MB (it only covers the subset of active rows)

Why it matters:

1. A smaller index = the index cache fits in RAM more easily. More rows indexed in fewer pages. More cache hits.
2. Cheaper maintenance. Inserts and updates to records that do NOT match the partial's WHERE don't update this index.
3. Faster lookups. The index is physically smaller, scanning it is faster.

When to use partial:

• When 80%+ of your queries share a common filter (a status, a country, a tenant_id).
• When only 20% of the rows are "interesting" (active vs deleted, recent vs archived, premium vs free).
• When you want to index only "the expensive part," not everything.

When NOT to use partial:

• When all the queries vary too much in their filters (there's no common filter).
• When the WHERE predicate depends on variables — the partial must be literal.

Stacking partial indexes

You can have multiple partials that cover different logical partitions:

-- Partial for the active ones
CREATE INDEX events_active_idx ON events USING gin(payload jsonb_path_ops)
WHERE payload @> '{"status": "active"}';

-- Partial for purchases
CREATE INDEX events_purchase_idx ON events USING gin(payload jsonb_path_ops)
WHERE payload @> '{"action": "purchase"}';

-- Partial for recent purchases
CREATE INDEX events_recent_purchase_idx ON events USING gin(payload jsonb_path_ops)
WHERE payload @> '{"action": "purchase"}'
  AND created_at > '2026-01-01';

The planner picks the most selective partial automatically. Three small partials that together cover your hot queries can be more efficient than one giant global index.

The anchor case (4s → 12ms) uses a partial

Remember the module's case: 50M rows, the query payload @> '{"action": "purchase", "country": "US"}' took 4 seconds with a default GIN. One of the techniques they applied:

CREATE INDEX events_purchase_us_idx ON events USING gin(payload jsonb_path_ops)
WHERE payload @> '{"action": "purchase"}';

The partial covered only ~5M rows (10% of the total that were purchases) instead of 50M. Combined with jsonb_path_ops and date partitioning (which comes in module 4), they got down to 12ms.

Expression indexes on JSONB

While GIN is for search (operators that return a boolean over the whole JSONB), expression indexes are for access to specific fields. They're normal B-trees, but the index's "field" is an expression over the JSONB.

-- Index on the value of a specific field
CREATE INDEX events_user_id_idx ON events ((payload->>'user_id'));

-- Now WHERE payload->>'user_id' = '42' uses this index
EXPLAIN ANALYZE SELECT * FROM events WHERE payload->>'user_id' = '42';
-- Index Scan using events_user_id_idx

When an expression index beats GIN:

• When you filter by equality on a specific field (->>'field' = '...') and that field is what you always filter on.
• When you JOIN by a field of the JSONB (more common than it seems — an events table where the user_id is in JSONB and joins with the users table).
• When you need ORDER BY payload->>'field' — GIN doesn't speed up ORDER BY.

Casting in expression indexes:

-- If the field is numeric and you're going to compare it as a number:
CREATE INDEX events_amount_idx ON events (((payload->>'amount')::numeric));

-- This query uses it:
SELECT * FROM events WHERE (payload->>'amount')::numeric > 100;

Combination: GIN + expression for different queries:

One table can have:

• A GIN over payload jsonb_path_ops for queries with @>.
• An expression index over ((payload->>'user_id')) for filters by a specific user_id.
• An expression index over ((payload->>'created_at')::timestamptz) for sorting by date.

Each query uses the appropriate index. The planner decides. This is what makes a table with varied queries truly performant.

Trade-offs of expression indexes

Pros:

• Much smaller than GIN (B-trees are compact).
• They support ORDER BY, range queries (<, >, BETWEEN), IS NULL.
• Insert/update is nearly free (a B-tree is cheap).

Cons:

• They only speed up queries over that exact expression. ((payload->>'user_id')) doesn't speed up ((payload->>'name')).
• You have to anticipate which fields you'll filter on and create an index for each one. JSONB with 50 filterable keys → 50 expression indexes (unsustainable).

Practical rule: an expression index for the 2-3 hot fields of the JSONB. GIN for everything else (flexible search).

Validating with EXPLAIN: the only thing that matters

Creating an index doesn't guarantee the planner will use it. The planner has its own criteria: cardinality, statistics, estimated cost. Sometimes a sequential scan comes out "cheaper" than using your index.

You don't assume — you validate.

The basic pattern: EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';

Expected output with the index working:

 Aggregate  (cost=...)
   ->  Bitmap Heap Scan on events  (cost=... rows=12450)
         Recheck Cond: (payload @> '{"action": "purchase", "country": "US"}'::jsonb)
         Heap Blocks: exact=842
         ->  Bitmap Index Scan on events_payload_idx  (cost=...)
               Index Cond: (payload @> '{"action": "purchase", "country": "US"}'::jsonb)
 Planning Time: 0.123 ms
 Execution Time: 35.412 ms

The key words: Bitmap Index Scan on events_payload_idx. That confirms the index is being used.

Output without the index working (Seq Scan):

 Aggregate  (cost=...)
   ->  Seq Scan on events  (cost=... rows=500000)
         Filter: (payload @> '{"action": "purchase", "country": "US"}'::jsonb)
 Planning Time: 0.094 ms
 Execution Time: 1840.182 ms

Seq Scan with a filter = the index isn't being used. Something is wrong.

Why the index might not be used

1. Stale statistics. ANALYZE events to refresh them.
2. Very low cardinality. If your query returns 80% of the table, the planner decides a sequential scan is more efficient. That's correct — change the query (more selective) or accept it.
3. Operator not compatible with the operator class. ? with jsonb_path_ops → doesn't use GIN. Switch to jsonb_ops or use another form.
4. Ambiguous implicit cast. WHERE (payload->>'amount')::numeric > 100 may not use the ((payload->>'amount')::numeric) index if the cast is ambiguous. Validate.
5. The partial index doesn't apply. Your query doesn't include the partial's predicate → the planner doesn't use it. Make sure the query literally includes the predicate.

The trick: force it to diagnose

If you want to know "what would happen with the index," force it:

SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT ...;
SET enable_seqscan = ON;

This temporarily disables Seq Scan. If forcing GIN the query is slow, you know GIN isn't the solution (the cardinality or the data don't lend themselves to it). If forcing GIN it's 100x faster, you know you have to help the planner (statistics, rewrite the query).

Worked example: from the slow baseline to the right index

Suppose you land on a team with this table:

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);
-- 5 million rows, no index on payload

The problematic query:

SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';
-- Takes 3.2 seconds

Step 1: validate the problem

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';

-- Seq Scan on events
--   Filter: ...
--   Rows Removed by Filter: 4,985,000
--   Buffers: shared hit=12500 read=210000
-- Execution Time: 3215.412 ms

Confirmed: Seq Scan, 5M rows read, ~3.2s.

Step 2: default GIN

CREATE INDEX ON events USING gin(payload);

EXPLAIN ANALYZE SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';

-- Bitmap Heap Scan on events
--   Recheck Cond: ...
--   Heap Blocks: exact=12500
--   ->  Bitmap Index Scan on events_payload_idx
-- Execution Time: 180.523 ms

3.2s → 180ms. A ~18x improvement with jsonb_ops. Good, but we can do better.

Step 3: jsonb_path_ops

DROP INDEX events_payload_idx;
CREATE INDEX events_payload_idx ON events USING gin(payload jsonb_path_ops);

EXPLAIN ANALYZE SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';

-- Bitmap Heap Scan on events
--   Recheck Cond: ...
--   Heap Blocks: exact=12500
--   ->  Bitmap Index Scan on events_payload_idx
-- Execution Time: 75.182 ms

180ms → 75ms. Another 2.4x. The index is also smaller (37 MB vs 70 MB).

Step 4: partial index

If the dashboard queries ALWAYS filter country: US (it's the main market), a partial:

CREATE INDEX events_us_idx ON events USING gin(payload jsonb_path_ops)
WHERE payload @> '{"country": "US"}';

EXPLAIN ANALYZE SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';

-- Bitmap Heap Scan on events
--   Recheck Cond: ...
--   ->  Bitmap Index Scan on events_us_idx
-- Execution Time: 25.412 ms

75ms → 25ms. Total: 3,215ms → 25ms = a 128x improvement. And still without touching partitioning (module 4).

Step 5: validate that there's no regression

-- A query that does NOT include 'country': US' should keep using the global index
EXPLAIN ANALYZE SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase", "country": "MX"}';

-- Bitmap Heap Scan
--   ->  Bitmap Index Scan on events_payload_idx  (not events_us_idx)
-- Execution Time: 80.412 ms

The planner chose the global index for queries that don't match the partial. Correct behavior.

Summary table of the exercise

That progression is the flow of the module 8 project. You're going to do it yourself in capsule 08 with your own dataset.

Why does this matter in real work?

1. The jsonb_ops vs jsonb_path_ops decision shows up in code reviews and interviews.

When someone creates a GIN over JSONB without thinking about the operator class, you can step in: "do they filter only with @>? jsonb_path_ops. Do they filter with ? too? jsonb_ops. Why did you leave the default?" That question changes the product's performance at zero cost.

2. Partial indexes are the difference between slow and fast at scale.

A global index over 50M rows weighs GBs and is slow to scan. Three partials over 5M rows each are together 10x smaller and faster. A junior dev rarely knows this technique. Knowing it and applying it is senior-level.

3. EXPLAIN ANALYZE is non-negotiable.

Creating an index without validating that it gets used is an anti-pattern. The planner can ignore it and you never found out. Every new index comes with its EXPLAIN before and after. It's senior discipline.

4. Deciding when NOT to index.

GIN is expensive. If you have a JSONB column that's barely filtered on (write-heavy, queries only by PK), adding GIN penalizes your inserts with no benefit. Knowing how to say "this column shouldn't have a GIN" is as important as knowing how to create the right index.

Traps and common mistakes

Mistake 1 (conceptual): assuming the default is the right thing

Symptom: every table with JSONB in the schema has CREATE INDEX ... USING gin(column) without an explicit operator class.

Why it happens: docs/tutorials rarely explain jsonb_path_ops. The default works and nobody questions it.

How to detect it: audit the schema looking for GIN over JSONB. For each one, ask: "which operators do the real queries over this column filter with?" If the answer is only @>, there's a free 2-3x improvement by switching to jsonb_path_ops.

How to fix it: DROP INDEX + CREATE INDEX ... USING gin(column jsonb_path_ops). Re-validate with EXPLAIN.

Mistake 2 (practical): not running ANALYZE after creating the index

Symptom: the index is created, EXPLAIN keeps showing a Seq Scan or uses wrong estimates (rows=1 when there are thousands).

Why it happens: the planner uses statistics. If they're stale (especially after bulk loads), the decisions are bad.

How to fix it: ANALYZE events; after creates/bulk changes. On large tables, consider VACUUM ANALYZE.

Mistake 3 (conceptual): a partial index without matching the predicate in the query

Symptom: you created CREATE INDEX ... WHERE payload @> '{"country": "US"}' and the query WHERE payload @> '{"country": "US", "action": "purchase"}' doesn't use it (or does, depending on the planner).

Why it happens: the partial's predicate must be implied by the query. PostgreSQL is smart about detecting logical implication in some cases but not all.

How to detect it: EXPLAIN. If the partial isn't used when you expected it to be, validate exactly what predicate the partial has vs what the query has.

How to fix it: make sure the query includes the partial's predicate or adjust the partial to something more general.

Mistake 4 (practical): an expression index without a consistent cast

Symptom: CREATE INDEX ON events ((payload->>'amount')) and WHERE (payload->>'amount')::numeric > 100 doesn't use the index.

Why it happens: the index is over the text (no cast), the query is over the numeric (with a cast). The planner doesn't match them.

How to fix it: index with the explicit cast: CREATE INDEX ON events (((payload->>'amount')::numeric)). Now the query uses it.

Mistake 5 (practical): over-indexing with GIN

Symptom: a table with 8 GIN indexes on different JSONB columns, the inserts are slow, the queries don't take advantage of that many.

Why it happens: someone created a GIN "just in case" over every JSONB column. Every GIN pays on insert/update.

How to detect it: pg_stat_user_indexes shows idx_scan (how many times each index was used). An index with idx_scan = 0 after a significant period = a candidate for deletion.

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

How to fix it: drop the unused indexes. Every GIN you drop speeds up inserts/updates. A clear trade-off: queries waiting vs writes paying.

Mistake 6 (conceptual): thinking GIN replaces B-tree for everything

Symptom: "I have GIN, I don't need an index on created_at."

Why it's wrong: GIN doesn't speed up ORDER BY, range queries (< now() - interval '1 day'), or IS NULL. B-trees are still needed for columns that aren't JSONB.

How to fix it: GIN for JSONB with search. B-trees for scalar columns with filters/ORDER BY/joins. They coexist.

Exercises

Exercise 1: choose the operator class

For each case, decide between jsonb_ops and jsonb_path_ops and justify it:

a) An events table with the queries: WHERE payload @> '{"action": "X"}' (95%) and WHERE payload @> '{"country": "Y"}' (5%).

b) A users table with the queries: WHERE config ? 'feature_enabled' (60%) and WHERE config @> '{"plan": "premium"}' (40%).

c) An audit_logs table with the queries: WHERE changes ?| ARRAY['email', 'phone'] (100%).

d) A products table with the queries: WHERE attributes <@ '{"colors": ["red", "blue"], "sizes": ["S","M","L"]}' (100%).

Exercise 2: compare size and speed

Replicate the 500k-row setup from the example, create the two GIN indexes, and compare the size and query time with EXPLAIN ANALYZE for a query with @>. Report the numbers.

-- Setup (the same as the 500k-row example)
DROP TABLE IF EXISTS events_ex;
CREATE TABLE events_ex (id BIGSERIAL PRIMARY KEY, payload JSONB NOT NULL);

INSERT INTO events_ex (payload)
SELECT jsonb_build_object(
  'action', (ARRAY['view','click','purchase','signup'])[1+(random()*3)::int],
  'country', (ARRAY['US','MX','ES','AR','BR'])[1+(random()*4)::int],
  'amount', round((random()*500)::numeric, 2)
)
FROM generate_series(1, 500000);

-- Index 1: jsonb_ops
CREATE INDEX events_ex_default ON events_ex USING gin(payload);
ANALYZE events_ex;

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events_ex
WHERE payload @> '{"action":"purchase","country":"US"}';
SELECT pg_size_pretty(pg_relation_size('events_ex_default'));

-- Index 2: jsonb_path_ops (drop the other one first to force it)
DROP INDEX events_ex_default;
CREATE INDEX events_ex_pathops ON events_ex USING gin(payload jsonb_path_ops);
ANALYZE events_ex;

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events_ex
WHERE payload @> '{"action":"purchase","country":"US"}';
SELECT pg_size_pretty(pg_relation_size('events_ex_pathops'));

Exercise 3: design partial indexes

Your app has a posts table (10M rows) with a metadata JSONB column. The most frequent queries are:

• 70%: WHERE metadata @> '{"status": "published"}' AND metadata @> '{"language": "es"}'
• 20%: WHERE metadata @> '{"status": "published"}'
• 10%: WHERE metadata @> '{"author_tier": "premium"}'

Design the index strategy (which partials to create, with which operator class). Explain your reasoning.

-- Partial 1: covers the 70% (most selective, two conditions)
CREATE INDEX posts_es_published ON posts USING gin(metadata jsonb_path_ops)
WHERE metadata @> '{"status": "published"}'
  AND metadata @> '{"language": "es"}';

-- Partial 2: covers the remaining 20% of published that is NOT Spanish
-- (or use the global one as a fallback — it depends on the % of rows)
CREATE INDEX posts_published ON posts USING gin(metadata jsonb_path_ops)
WHERE metadata @> '{"status": "published"}';

-- Partial 3: for premium (10%, a different query)
CREATE INDEX posts_premium ON posts USING gin(metadata jsonb_path_ops)
WHERE metadata @> '{"author_tier": "premium"}';

Exercise 4: expression index for a JOIN

You have an events table with a payload JSONB that contains user_id (numeric). You frequently run:

SELECT u.name, e.created_at, e.payload
FROM events e
JOIN users u ON u.id = (e.payload->>'user_id')::bigint
WHERE e.created_at > '2026-01-01';

Which index do you create to speed up this JOIN?

CREATE INDEX events_user_id_idx ON events (((payload->>'user_id')::bigint));

EXPLAIN ANALYZE
SELECT u.name, e.created_at
FROM events e
JOIN users u ON u.id = (e.payload->>'user_id')::bigint
WHERE e.created_at > '2026-01-01';

-- Expected: Index Scan / Index Only Scan using events_user_id_idx
-- If you see a Hash Join without an Index Scan, analyze the statistics or force it with SET enable_seqscan = OFF.

CREATE INDEX events_user_id_recent ON events (((payload->>'user_id')::bigint))
WHERE created_at > '2026-01-01';

Exercise 5: diagnosis

A colleague tells you: "I created the GIN like in the tutorial, but the query is still slow. Look:"

CREATE INDEX events_payload_idx ON events USING gin(payload);

EXPLAIN ANALYZE
SELECT * FROM events WHERE payload->>'status' = 'active';
-- Seq Scan
-- Execution Time: 2,800 ms

What do you tell them?

SELECT * FROM events WHERE payload @> '{"status": "active"}';
-- This does use the GIN. It probably drops to 50-200 ms.

CREATE INDEX events_status_idx ON events ((payload->>'status'));

-- Now the original query uses this index:
SELECT * FROM events WHERE payload->>'status' = 'active';
-- Index Scan using events_status_idx

-- For WHERE payload @> '{"status":"active","tier":"premium"}'
CREATE INDEX events_payload_idx ON events USING gin(payload jsonb_path_ops);
-- Faster than the default and it covers @> with N keys.

Exercise 6: clean up unused indexes

Run pg_stat_user_indexes on your DB and identify indexes with idx_scan = 0 or a very low idx_scan after a period of real use. List the steps to audit them before dropping them.

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND idx_scan < 10  -- used fewer than 10 times
ORDER BY pg_relation_size(indexrelid) DESC;

Summary and next step

In this capsule you learned:

• GIN indexes each component of a composite value (vs a B-tree, which indexes one value per entry). More expensive, bigger, but fast for search.
• The jsonb_ops operator class (default): indexes keys + values as separate tokens. Supports ALL the search operators. Bigger.
• The jsonb_path_ops operator class: indexes hashes of complete paths. Only supports @>. Smaller, faster for @>.
• Decision: if you only use @>, jsonb_path_ops almost always wins. If you use ?/?|/?&/<@, you need jsonb_ops.
• Partial indexes on JSONB are the secret sauce. They index only the rows that match a predicate, they're much smaller and faster for queries that share that predicate.
• Expression indexes are B-trees over an expression of the JSONB. Ideal for JOINs by an extracted field, ORDER BY, range queries on a numeric/temporal field.
• EXPLAIN ANALYZE is non-negotiable. Creating an index without validating that it gets used is building files on disk. Every new index comes with its EXPLAIN.

Before moving on you should be able to:

• Defend the jsonb_ops vs jsonb_path_ops decision in an interview with concrete criteria
• Design partial indexes for a table with asymmetric queries (80/20)
• Create expression indexes with an explicit cast that the planner uses
• Diagnose why a GIN isn't being used (wrong operator, statistics, cardinality)

Next capsule — Complex queries: filters, joins, and aggregations. You already know the operators, path queries, and how to index each case. Capsule 06 puts all of that into real queries: combinations of WHERE, GROUP BY, JOIN, aggregations (jsonb_agg, jsonb_object_agg), and the cases where you need additional expression indexes for JOIN or ORDER BY to be fast. It's the capsule that connects "technique" with "the queries that make your app."

Resources

1. PostgreSQL 16 Documentation — GIN Indexes — the official reference on GIN, fastupdate, maintenance.
2. PostgreSQL 16 Documentation — JSONB Indexing — a dedicated section with jsonb_ops vs jsonb_path_ops and partials.
3. pganalyze — Lukas Fittl — "Understanding GIN Indexes in PostgreSQL" — a deep technical analysis, fastupdate, internals. Recommended reading.
4. pganalyze — "JSONB indexing strategies" — applied patterns with comparative benchmarks.
5. Crunchy Data — "Indexing JSONB" — a practical tutorial with common cases.
6. Bruce Momjian — "Postgres GIN Index Slides" — material from the core team about GIN.
7. dev.to/ohugonnot — Advanced PostgreSQL: JSONB, partial indexes and partitioning — the module's anchor case. We're going to replicate it in capsule 08.
8. PostgreSQL Wiki — Index Maintenance — useful queries for auditing index usage (pg_stat_user_indexes, etc.).

Module 1 — Advanced PostgreSQL for Backend Guide

Next capsule: Complex queries — combining operators, joins, and aggregations over JSONB.