10 Postgres JSONB Indexing Patterns With Query Plans

Practical GIN and functional index recipes that turn slow JSONB lookups into sub-millisecond answers — without rewriting your schema.

Modexa

Follow

6 min read

·

1 day ago

100

2

Listen

Share

Press enter or click to view image in full size

Ten proven Postgres JSONB indexing patterns with example queries and EXPLAIN plans. Learn when to use GIN, jsonpath, expression indexes, and partials.

You added a JSONB column to move faster. Then traffic grew and suddenly that “temporary” flexibility turned into 900ms queries. Let’s be real — JSONB can fly, but only if you pair it with the right index and the right operator. Below are ten patterns I’ve used in production, each with a tiny dataset, an index, the query, and a trimmed EXPLAIN plan so you can feel what “fast” looks like.

Sample table used throughout

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT now(),
  attrs JSONB NOT NULL
);

-- Example rows
INSERT INTO events(attrs) VALUES
  ('{"user":{"id":42,"plan":"pro"},"type":"click","meta":{"ref":"ad-7"}}'),
  ('{"user":{"id":7,"plan":"free"},"type":"signup","meta":{"ref":"organic"}}');

1) GIN on the Whole Document (Default Recipe)

When