JSONB is one of PostgreSQL’s most popular features — it lets you store flexible, schema-less documents in a relational database. But the moment your JSONB table grows past a few thousand rows, unindexed queries start to crawl. The good news: PostgreSQL gives you several indexing strategies that keep JSONB fast without giving up flexibility.

In this tutorial, you’ll build a small events table, load sample JSONB data, and walk through the indexing techniques that matter: the general-purpose GIN index, targeted expression indexes, and how to verify each one with EXPLAIN. You can run every step in psql against PostgreSQL 14 or newer.

Step 1: Create the table and load sample data

Start with a table that stores application events. Each row has an immutable id, a timestamp, and a payload JSONB column holding arbitrary event attributes:

CREATE TABLE events (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created timestamptz NOT NULL DEFAULT now(),
  payload jsonb NOT NULL
);

INSERT INTO events (payload)
SELECT jsonb_build_object(
  'event_type', (ARRAY['page_view','signup','purchase','login'])[1 + (random()*4)::int],
  'user_id',    (random()*100000)::int,
  'amount',     round((random()*200)::numeric, 2),
  'meta',       jsonb_build_object('device', (ARRAY['web','ios','android'])[1 + (random()*3)::int])
)
FROM generate_series(1, 100000);

This inserts 100,000 rows with random event types, user IDs, amounts, and device metadata. It’s enough data to make index choices visible.

Step 2: Find the queries that need indexing

Before adding any index, look at how your application actually filters JSONB. The two most common patterns are:

• Existence / containment checks — “does the payload contain this key or value?”
• Range or equality lookups on a specific key — “all purchases above $50” or “events for user 12345”

Run the containment query first and check the plan:

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

You’ll see a sequential scan across all 100,000 rows. That’s your baseline — and your signal that a GIN index is needed.

Step 3: Add a GIN index for containment queries

For @>, ?, ?|, and ?& operators, a GIN (Generalized Inverted Index) is the right tool. One index covers every key in your documents:

CREATE INDEX events_payload_gin ON events USING gin (payload);

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

The plan should now show a Bitmap Index Scan on events_payload_gin followed by a Bitmap Heap Scan — typically a 100x or better speedup on this workload. GIN indexes are also what powers full-text search over tsvector, so the same mental model applies there.

Step 4: Add a targeted expression index for hot keys

GIN handles containment well, but it won’t help with range scans or sorting on a specific key, like payload->>'amount' comparisons. For those, create a plain B-tree index on the extracted value using an expression:

CREATE INDEX events_payload_amount_idx
  ON events ((payload ->> 'amount'));

EXPLAIN ANALYZE
SELECT payload ->> 'user_id' AS user_id, payload ->> 'amount' AS amount
FROM events
WHERE (payload ->> 'amount')::numeric > 150
ORDER BY payload ->> 'amount' DESC;

Now the planner can use the B-tree for both the filter and the sort. If you consistently query one or two keys — a user_id, a status field, a tenant_id — a handful of expression indexes will outperform a single wide GIN index on those paths.

Step 5: Know when a GIN index is the wrong choice

Indexes are not free. Every GIN index slows down INSERT and UPDATE because each new document must be tokenized and inserted into the inverted list. On write-heavy tables, consider:

• Only index what you query. If you never filter on meta.device, don’t pay for it in the GIN index.
• Use jsonb_path_ops for containment-only workloads — it’s smaller and faster than the default GIN operator class, at the cost of not supporting the ? key-existence operators.
• Extract hot keys to real columns when they become stable schema. Regular columns get better statistics, better compression, and simpler indexes.

-- Smaller, faster GIN for pure containment queries:
CREATE INDEX events_payload_path_ops
  ON events USING gin (payload jsonb_path_ops);

Wrap up

You now have a repeatable recipe: baseline with EXPLAIN, add a GIN index for containment, add expression B-tree indexes for hot keys, and re-check the plan after each change. For a deeper look at indexing strategy and benchmark trade-offs, see SitePoint’s PostgreSQL JSONB Performance Guide.

Next time your JSONB queries slow down, resist the urge to move the data out of PostgreSQL — a well-chosen index usually fixes it in minutes.