Indexing JSONB with GIN
Build GIN indexes on JSONB documents and use jsonb_path_ops for fast containment queries.
Indexing JSONB with GIN is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Why GIN for JSONB
JSONB documents have many "items" (key/value pairs and array elements). GIN (Generalised Inverted Index) is built for "rows where document contains X" queries.
Default GIN Index
The default operator class supports @>, ?, ?|, ?&:
CREATE INDEX events_data_gin ON events USING GIN (data);
-- These now use the index:
SELECT * FROM events WHERE data @> '{"type":"login"}';
SELECT * FROM events WHERE data ? 'error';jsonb_path_ops: Smaller and Faster
Half the size and faster for containment-only queries, but ONLY supports @>:
CREATE INDEX events_data_gin ON events USING GIN (data jsonb_path_ops);
-- Supports @>
-- Does NOT support ? ?| ?&
SELECT * FROM events WHERE data @> '{"type":"login"}';Index Just One Path
If you only query one key, an expression B-tree index on the extracted value is even faster:
CREATE INDEX events_user_id_idx
ON events (((data->>'user_id')::BIGINT));
SELECT * FROM events WHERE (data->>'user_id')::BIGINT = 42;Indexing Inside Arrays
Use a GIN index on the array path:
CREATE INDEX events_tags_gin
ON events USING GIN ((data->'tags'));
SELECT * FROM events WHERE data->'tags' @> '["admin"]'::JSONB;Combining JSONB Index with Other Filters
Composite predicates can use the GIN index for the JSONB part and another index for non-JSONB:
EXPLAIN ANALYZE
SELECT * FROM events
WHERE data @> '{"type":"login"}'
AND ts >= NOW() - INTERVAL '7 days';
-- BitmapAnd: GIN index on data, B-tree on tsGIN Write Performance
GIN updates are heavier than B-tree. For very write-heavy tables, the fastupdate option batches GIN updates in a pending list, flushed by VACUUM.
CREATE INDEX events_data_gin ON events USING GIN (data) WITH (fastupdate = on);
-- Flush manually if needed:
SELECT gin_clean_pending_list('events_data_gin');Index Size
JSONB GIN indexes can be large. For huge tables, consider:
- Indexing only specific paths (expression index)
- Switching to jsonb_path_ops for containment-only
- Splitting hot fields into real columns
Combining with Trigram
For fuzzy text search inside JSONB, extract to a TEXT expression and add a pg_trgm GIN index:
CREATE INDEX events_message_trgm
ON events USING GIN ((data->>'message') gin_trgm_ops);When Indexing Doesn't Help
If your filter touches every row (very low selectivity), the planner may pick a sequential scan even with the index. EXPLAIN ANALYZE to confirm.
Maintaining JSONB Indexes
GIN indexes bloat like any other index. Use REINDEX CONCURRENTLY periodically:
REINDEX INDEX CONCURRENTLY events_data_gin;Recap
GIN turns JSONB filters into millisecond lookups.
- Default GIN: @>, ?, ?|, ?&
- jsonb_path_ops: smaller, containment-only
- Expression B-tree on extracted scalar: fastest for one key
Quick Check
You only ever query data @> ... on a JSONB column. Which index gives the smallest size with full feature support?
Frequently asked questions
Is the “Indexing JSONB with GIN” lesson free?
Yes — the full text of “Indexing JSONB with GIN” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Indexing JSONB with GIN”?
Build GIN indexes on JSONB documents and use jsonb_path_ops for fast containment queries. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Indexing JSONB with GIN” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.