Indexing and Querying JSONB

By Michael Samuel Naeem · March 19, 2026 · 5 min read

Event sourcing lite: one events table, payload JSONB, ship it. Six months later, WHERE payload->>'user_id' = '123' scans 40 million rows. JSONB is flexible until it isn't — without the right index and operator, you're doing full table scans on opaque blobs.

JSONB operators you'll use

SELECT * FROM events
WHERE payload @> '{"type": "purchase", "status": "completed"}';

SELECT * FROM events
WHERE payload->>'user_id' = '550e8400-e29b-41d4-a716-446655440000';

@> containment uses GIN efficiently. ->> equality needs expression index unless wrapped in generated column.

GIN indexes for containment

CREATE INDEX events_payload_gin ON events USING GIN (payload jsonb_path_ops);
-- Supports: payload @> '{"type": "click"}'

Broader operator support:

CREATE INDEX events_payload_ops ON events USING GIN (payload jsonb_ops);
-- Supports: @>, ?, ?&, ?|, @?

jsonb_path_ops index is ~30% smaller for @>-only workloads — prefer it when you don't need ? key existence.

Expression indexes for scalar lookups

CREATE INDEX events_user_id_idx ON events ((payload->>'user_id'))
WHERE payload ? 'user_id';

Partial WHERE excludes rows missing key — smaller index for sparse fields.

Generated column (Postgres 12+):

ALTER TABLE events ADD COLUMN user_id UUID
  GENERATED ALWAYS AS ((payload->>'user_id')::uuid) STORED;

CREATE INDEX events_user_id_btree ON events (user_id);

Queryable like normal column; stays synced on insert/update.

JSONPath queries (Postgres 12+)

SELECT * FROM events
WHERE jsonb_path_exists(payload, '$.items[*].price ? (@.double() > 100)');

SELECT jsonb_path_query_first(payload, '$.metadata.source') FROM events;

Complex paths may not use GIN — test EXPLAIN. @? and @@ operators can use GIN with jsonb_ops in some cases.

Schema design patterns

Envelope + typed core:

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_id UUID NOT NULL,
  total_cents INT NOT NULL,
  metadata JSONB DEFAULT '{}'
);
CREATE INDEX ON orders (customer_id);
CREATE INDEX ON orders USING GIN (metadata jsonb_path_ops);

Category column for partition pruning:

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  category TEXT NOT NULL,  -- 'click', 'purchase'
  payload JSONB NOT NULL
);
CREATE INDEX ON events (category, id DESC);
-- Filter category first, then JSONB

Don't store searchable fields only inside JSONB if every query extracts them — promote to columns.

Update and bloat considerations

JSONB updates rewrite entire JSON value if any key changes — TOAST compression helps but hot keys on wide documents cause bloat.

Split frequently updated keys to columns; keep stable blob in JSONB.

jsonb_strip_nulls on insert reduces size if API sends null-heavy payloads.

Anti-patterns

• No index on filter path used in every query
• @> with dynamic key order — {"a":1,"b":2} ≠ {"b":2,"a":1} for containment; normalize JSON key order on write or use consistent construction
• Storing large base64 blobs in JSONB — use bytea or object storage
• SELECT * on wide JSONB — extract needed keys

Migration from JSONB-only to hybrid schema

When JSONB queries dominate slow-query logs, promote hot keys incrementally without a big-bang rewrite:

1. Add nullable generated column for the hottest key
2. Backfill in batches during low traffic
3. Create B-tree index on generated column
4. Update application to filter on column first, JSONB second
5. After one release, make column NOT NULL if business rules require it

This pattern shipped a search feature from 800ms p95 to 40ms on a 30M-row events table — same data model externally, different physical layout internally. Track promotion candidates by logging slow-query keys from pg_stat_statements normalized on JSON path.

jsonb_strip_nulls and storage

Strip null keys on ingest if upstream API sends sparse objects — smaller JSONB storage, faster comparisons. For write-heavy JSONB, monitor TOAST behavior; consider extracting only search fields to columns while keeping full blob for archival.

Operational notes

For API responses returning full JSONB documents, separate read replica for analytics queries on JSONB containment — OLTP write path stays isolated from analyst exploration queries.

Version JSON schema documents alongside API — when payload shape changes, index and query paths update in same PR reducing drift between documented and indexed fields.

JSONB GIN index

CREATE INDEX idx_data_gin ON documents USING GIN (data jsonb_path_ops);
SELECT * FROM documents WHERE data @> '{"status": "active"}';

jsonb_path_ops smaller index than default jsonb_ops — use when queries are containment-only.

Common production mistakes

Teams get jsonb indexing queries wrong in predictable ways:

• Skipping failure-mode rehearsal — run a game day or fault injection exercise before peak traffic, not after the first outage.
• Missing correlation context — every error path should carry request, trace, or tenant identifiers so incidents are debuggable.
• Optimizing for demo, not steady state — load tests, cache warm-up, and cold-start paths matter more than local dev latency.
• Undocumented trade-offs — if you chose speed over strict correctness (or vice versa), write that down for the next engineer.

Postgres work on jsonb indexing queries causes outages when migrations run without lock_timeout, connection pools are sized for app servers not PgBouncer modes, and EXPLAIN plans from staging are assumed to match production statistics.

Debugging and triage workflow

When jsonb indexing queries misbehaves in production, work top-down instead of guessing:

1. Confirm scope — one tenant, region, or deployment stage? Narrow blast radius before deep diving.
2. Check recent changes — deploys, flag flips, config pushes, and schema migrations in the last 24 hours.
3. Compare golden signals — latency, error rate, saturation, and traffic for the affected surface vs. baseline.
4. Reproduce minimally — smallest input or scenario that triggers the failure; capture traces/logs with correlation IDs.
5. Fix forward or rollback — if rollback is faster than root-cause during incident, rollback first, postmortem second.
6. Add a guard — alert, integration test, or circuit breaker so the same class of failure is caught earlier next time.

Document the timeline during triage. Future you (and on-call) will need timestamps, not just conclusions.

Resources

• PostgreSQL JSON types documentation
• PostgreSQL JSON functions
• JSONB indexing docs
• PostgreSQL jsonpath
• Crunchy Data JSONB tips