The PostgreSQL JSONB Trap That Cost Me a Weekend

The dashboard froze.

Not a crash. Worse. A slow crawl that made users think something was broken.

12ms became 4,200ms. On the same query. With the same data structure. After a "minor" schema change.

The Setup

We store event metadata as JSONB. Flexible schema, easy querying, PostgreSQL handles the indexing. That's the promise, anyway.

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  type VARCHAR(50),
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_events_metadata ON events USING GIN (metadata);

The query was simple:

SELECT * FROM events
WHERE metadata->>'user_id' = '12345'
ORDER BY created_at DESC
LIMIT 50;

12ms with 2 million rows. Fast enough.

The Change

Product wanted to filter by nested properties. User preferences, specifically.

SELECT * FROM events
WHERE metadata->'preferences'->>'theme' = 'dark'
ORDER BY created_at DESC
LIMIT 50;

4.2 seconds.

I checked the index. Still there. I ran EXPLAIN ANALYZE. Sequential scan on 2 million rows.

The Confession

I assumed GIN indexes on JSONB columns indexed everything. Every path, every nested key, every value.

I was wrong.

GIN indexes on JSONB index the structure—keys and containment. They don't index arbitrary path expressions. The ->> operator extracts text, bypassing the index entirely.

The Fix

-- This uses the index (containment query)
SELECT * FROM events
WHERE metadata @> '{"preferences": {"theme": "dark"}}'
ORDER BY created_at DESC
LIMIT 50;

14ms.

The @> containment operator asks "does this JSONB contain this structure?" That's what GIN indexes are built for.

Why doesn't a GIN index help when using the ->> operator on JSONB?

GIN indexes cover structure and containment, not arbitrary path extraction

Use the @> containment operator or create an expression index for specific paths

Click to reveal answer

The Better Fix

For frequently queried paths, expression indexes are faster:

CREATE INDEX idx_events_pref_theme
ON events ((metadata->'preferences'->>'theme'));

Now the original query works:

SELECT * FROM events
WHERE metadata->'preferences'->>'theme' = 'dark'
ORDER BY created_at DESC
LIMIT 50;

8ms. Faster than containment because it's a B-tree on a scalar value.

The Lesson

JSONB flexibility has costs:

Query Type	Index Used	Time
`->>` extraction	None (seq scan)	4,200ms
`@>` containment	GIN	14ms
Expression index	B-tree	8ms

The index exists. PostgreSQL just can't use it for your query.

Before adding JSONB columns, ask: "What queries will I run?" Then build indexes for those specific access patterns.

When is an expression index faster than a GIN index for JSONB queries?

When querying a specific, frequently-accessed path

Expression indexes create a B-tree on a scalar value, giving O(log n) lookup vs GIN's containment check

Click to reveal answer

Flexible schema. Rigid query planning.

One operator. ->> to @>. 300x faster.

Sometimes the documentation isn't lying. You just didn't read the right section.