PostgreSQL Performance & Query Optimization · Lesson

GIN vs Expression Indexes on JSONB

Choose between jsonb_path_ops GIN indexes and targeted expression indexes for your query shapes.

Two Ways to Index JSONB

When you store data in a jsonb column, an unindexed query forces PostgreSQL to read and parse every row. There are two very different tools to fix this:

GIN index — a general inverted index over the whole document, great for flexible containment and key/value lookups.
Expression (B-tree) index — a targeted index on one extracted scalar, great for a specific known query shape.

This lesson is about choosing the right one for your query patterns.

The Sample Table

Imagine an events table where each row carries a flexible JSON payload. We will index its data column.

Notice the payload mixes a few common keys (type, user_id) with arbitrary extras.

CREATE TABLE events (
  id     bigserial PRIMARY KEY,
  data   jsonb NOT NULL
);

INSERT INTO events (data) VALUES
  ('{"type": "login",  "user_id": 42, "ip": "10.0.0.1"}'),
  ('{"type": "logout", "user_id": 42}'),
  ('{"type": "login",  "user_id": 99, "mfa": true}');

All lessons in this course

← Back to PostgreSQL Performance & Query Optimization