Lesson 11 · Indexes
Beyond B-tree: GIN, GiST, BRIN, Hash
When the workhorse won't do — the specialised index types.
Your win: match each index type to the data and query it fits, and recognise the GIN indexes your services already run for text search and arrays.
B-tree handles scalars; other data needs other structures
A B-tree indexes a single comparable value per row. But some columns hold many values (an array, a JSONB doc, the words in a text field), and some queries aren't equality or range at all. Postgres ships several index access methods.1
| Type | Best for | Example query |
|---|---|---|
| B-tree | scalar equality & range, ORDER BY | WHERE status = 'SENT' |
| GIN | many values per row: arrays, JSONB, full-text/trigram | WHERE tags @> ARRAY['x'], name ILIKE '%foo%' |
| GiST | ranges, geometry, nearest-neighbour, exclusion | "points within this box", range overlap |
| BRIN | huge, naturally-ordered tables | time-series scan on append-only data |
| Hash | equality only | rarely worth it over B-tree |
GIN in one line
A GIN (Generalized Inverted Index) maps each element (array
item, JSON key, text token) → the rows that contain it — like a book's index of words.
That's what makes "does this array/JSONB/text contain X?" fast.
BRIN in one line
A BRIN (Block Range Index) stores just the min/max value per block
range, so it's tiny — but only helpful when the column's values line up with physical
order (e.g. an ever-increasing
created_at on an append-only table).
Anchor — you already run GIN
Text search in your repo uses GIN with trigrams:
gin (name gin_trgm_ops) on conversation.name
(tom/1064:17) and on user names (tom/1059:40);
array membership uses gin (inferred_location_ids)
(bob/1706:1). No GiST/BRIN in these three services — but
knowing when they'd apply is the interview point.
Read this next
PostgreSQL — Index Types
The authoritative rundown of B-tree/Hash/GiST/SP-GiST/GIN/BRIN and what each supports.
Check yourself (from memory)
Q1. A GIN index is best for columns holding…
GIN indexes each element → containing rows. Arrays, JSONB,
full-text/trigram. Scalars are B-tree's job.
Q2. BRIN indexes suit tables that are…
BRIN stores min/max per block range — tiny, and effective
only when values track physical order (append-only time-series).
Q3. Our trigram text search uses which index?
GIN with
gin_trgm_ops (the pg_trgm
extension) powers substring/fuzzy search on conversation.name etc.
Match each index type to its use: B-tree, GIN, GiST, BRIN, Hash.
recall, then click to reveal
B-TREE (default) — scalar equality & range, ORDER BY (PKs,
resource_path, composites). GIN — many values per row: arrays, JSONB, full-text/trigram
(our
gin_trgm_ops on names, gin on the
inferred_location_ids array). GiST — ranges, geometry, nearest-neighbour,
exclusion constraints. BRIN — huge, naturally-ordered/append-only tables (min/max per
block range; tiny, approximate). HASH — equality only, rarely worth it over B-tree.
Want to know when a JSONB query needs
jsonb_path_ops GIN vs the default, or
how pg_trgm makes ILIKE '%x%' indexable? Ask me.