Database: GIN indexes & JSONB
This course follows one app's catalogue search, which filtered jsonb title columns with ILIKE while GIN indexes on those columns went unused. It shows how an operator class decides which operators a GIN index can serve. The default jsonb_ops has no support for LIKE or ILIKE, while pg_trgm's gin_trgm_ops indexes three-character pieces of text. Later lessons cover expression indexes that must match the query character for character and the measured size tradeoff between jsonb_ops and jsonb_path_ops. It ends with the fix: two indexes replaced, two unused ones dropped.
What you'll learn
- ✓How a GIN operator class decides which operators an index can serve
- ✓What the default jsonb_ops indexes and which six operators it registers
- ✓Indexing ILIKE searches on extracted jsonb text with pg_trgm and gin_trgm_ops
- ✓Writing expression indexes that match the query's expression character for character
- ✓Comparing jsonb_ops and jsonb_path_ops on index size and supported operators
- ✓Removing GIN indexes on columns that no query filters on
A real GIN index, and a query that never touched it
Catalog::SearchQuery is the class behind this app's catalogue search. Until 2 September 2026, it filtered courses and lessons like this:
Read the full lesson free →Curriculum
7 lessonsA real GIN index, and a query that never touched it
A GIN index on the jsonb title column went unused by the ILIKE search but served a containment query.
jsonb_ops: what the default opclass indexes
The default jsonb_ops operator class registers six operators, including @> and key existence, and neither LIKE nor ILIKE.
pg_trgm and gin_trgm_ops: indexing substrings, not documents
pg_trgm's gin_trgm_ops indexes three-character trigrams, letting ILIKE searches use an index once patterns reach three characters.
Expression indexes need exact syntax, not just the right opclass
An expression index is used only when the query writes the same expression, so each locale needed its own index.
jsonb_ops vs jsonb_path_ops: a real, measured tradeoff
Measured on 50,000 rows, jsonb_path_ops was 56.7% smaller than jsonb_ops but cannot serve key existence queries.
The fix: one migration, checked before and after
The fix dropped two GIN indexes no query used and replaced two with trigram indexes, with tests unchanged.
Putting it together: choosing the right jsonb GIN strategy
A decision guide matches each way of querying a jsonb column to jsonb_path_ops, jsonb_ops, gin_trgm_ops or no index.