Postgres ships six built-in index access methods, but only four earn a permanent place in most schemas: B-tree for sorted equality and range lookups, GIN for containment and full-text search, GiST for overlaps and nearest-neighbour queries, and BRIN for huge, physically-ordered tables. Pick the wrong one and Postgres won't error out — it will just quietly ignore the index, which is the worse failure to debug.
Why "just add an index" is the wrong instinct
A few years back I got paged at 02:10 for a payments reconciliation job that had gone from four minutes to fifty. The offending query was ordinary:
SELECT id, occurred_at
FROM events
WHERE payload @> '{"kind": "refund"}'::jsonb
AND occurred_at >= now() - interval '1 day';
There was an index on events.payload. Someone had created it months earlier, it was valid, it sat on exactly the right column, and the planner ignored it completely. It was a B-tree.
A B-tree on a jsonb column is perfectly legal. It indexes whole documents in sort order and can answer payload = '...' or payload < '...'. What it cannot do, at any price, is answer @>. The containment operator simply doesn't appear in the B-tree operator family for jsonb. The index existed; the operator class had never heard of the operator; the planner did the only correct thing available to it and scanned 400 million rows.
That's the whole thesis of this article. You don't choose a Postgres index type by looking at the column name. You choose it by answering two questions: what is the physical shape of the data in that column, and which operators show up in the WHERE clause. Everything else falls out of the page layout of the access method.
Postgres ships six built-in index access methods: B-tree, hash, GiST, SP-GiST, GIN and BRIN. B-tree is what you get when CREATE INDEX omits USING. Four of them matter day to day. I'll be honest about the other two as we go.
The contract every access method signs
Postgres deliberately splits two concepts that people conflate.
The access method (pg_am) is the on-disk structure and the search algorithm: how pages are laid out, how a scan descends, how splits happen. The operator class (pg_opclass) is the adapter that teaches that structure about a specific data type: which operators it can answer, and which support functions implement comparison, union, hashing and so on.
You can see the installed access methods directly:
\dA
Name | Description
--------------------+------------------------------------------
brin | block range index (BRIN) access method
btree | b-tree index access method
gin | GIN index access method
gist | GiST index access method
hash | hash index access method
spgist | SP-GiST index access method
Whether a GIN index can serve ?| on jsonb isn't folklore you have to guess at — the catalog will just tell you:
SELECT am.amname, opc.opcname, op.oprname
FROM pg_opclass opc
JOIN pg_am am ON am.oid = opc.opcmethod
JOIN pg_amop amop ON amop.amopfamily = opc.opcfamily
JOIN pg_operator op ON op.oid = amop.amopopr
WHERE opc.opcname IN ('jsonb_ops', 'jsonb_path_ops')
ORDER BY 1, 2, 3;
Run that and you get the exact operator list each opclass supports. \dAc lists the operator classes, \dAo the operators per family. When a colleague insists an index "should be working," this query settles it in ten seconds — it's settled a lot of arguments for me.
It also explains a second, more common failure than the jsonb one above: a perfectly good B-tree on customer_email doing nothing for WHERE lower(customer_email) = 'a@b.com'. The index has an opclass entry for text =, but the query's actual operand is the return value of a function call, not the column, and no operator class matches that unless you index the expression itself (CREATE INDEX ... (lower(customer_email))). The default operator class for a type gets chosen implicitly, and sometimes the default — or the literal expression the index was built on — doesn't match what the query actually evaluates. A surprising number of incidents live in exactly that gap.
B-tree: the sorted default, and what sorting buys you
A B-tree is a balanced multi-level tree of sorted keys. Internal pages hold separator keys and downlinks; leaf pages hold the actual keys plus heap TIDs, and leaf pages are linked to their siblings. That's the entire mechanism, and every capability of a B-tree traces back to it.
Because keys are sorted, the tree can answer <, <=, =, >=, >, and by extension BETWEEN and IN, plus IS NULL and IS NOT NULL. Because leaves are linked, a range scan reads one leaf and walks sideways rather than re-descending. Because the order is total, ORDER BY on the indexed columns in a matching direction can be satisfied by the index scan alone, with no Sort node above it — which is what makes keyset pagination fast. MIN/MAX on an indexed column gets the same benefit: it collapses to a single descent to the first or last leaf entry instead of a scan.
CREATE INDEX CONCURRENTLY orders_customer_placed_idx
ON orders (customer_id, placed_at DESC);
That index serves WHERE customer_id = $1 ORDER BY placed_at DESC LIMIT 50 with a plain Index Scan and no sort. It also serves WHERE customer_id = $1 alone. It serves WHERE placed_at > $1 alone very poorly: with a multicolumn B-tree, constraints on the leading columns are what let the scan skip ahead; a constraint on a later column only restricts which entries get returned, and the scan still has to walk most of the index. That leading-column rule falls straight out of how the keys are ordered — order columns by what you actually filter on, leading with whichever appears in the most queries as an equality predicate.
Pattern matching is the other place the sorted structure shows its edges. LIKE 'ACME-%' is a range scan in disguise, so a B-tree can serve it, but only if the collation orders strings the way the pattern comparison does. In practice that means the C locale, or an index built with text_pattern_ops / varchar_pattern_ops:
CREATE INDEX CONCURRENTLY orders_reference_prefix_idx
ON orders (reference varchar_pattern_ops);
In any other locale, plain text comparison sorts case- and accent-aware, which doesn't match byte-prefix search, so the pattern opclass has to be explicit.
Two more B-tree features worth knowing. INCLUDE columns (PG 11+) let you carry non-key payload in the leaf pages so a query can be answered index-only without widening the search key or affecting uniqueness. And index-only scans depend on the visibility map showing the heap pages as all-visible; if vacuum has fallen behind, the executor has to visit the heap anyway and your "index-only" plan quietly turns into an ordinary index scan with heap fetches. I've watched a dashboard query triple in latency purely because autovacuum was starved on that table.
PG 13 added deduplication, which stores a repeated key once with a posting list of TIDs. On a column like orders.status this can shrink an index dramatically compared with pre-13 behaviour. It makes low-cardinality B-trees cheaper to store. It doesn't make them useful.
Where B-tree quietly fails
Low cardinality. An index on orders.status with four distinct values across 300 million rows will be built, will be maintained on every write, and will almost never get chosen for status = 'shipped' because that predicate matches 60% of the table. A sequential scan beats a bitmap heap scan that touches most pages anyway. The index exists purely as a write tax.
Wide keys. B-tree entries are limited to roughly a third of a page. Index a long text value and you get:
ERROR: index row size 3512 exceeds btree version 4 maximum 2704 for index "documents_body_idx"
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
I've seen this fire in production at 3am when one customer pasted an unusually long address. If you need to index long text, you want a hash of it, a prefix expression, or a different access method entirely.
Containment and search semantics. Arrays, jsonb containment, full-text matching and unanchored LIKE '%acme%' are not range queries. No amount of sorting helps, because the thing you're searching for is inside the value rather than being the value. LIKE '%foo%' with a wildcard on both ends can never use a B-tree — there's no prefix to seek to, since the match could start anywhere in the string.
That's exactly the gap the other three access methods fill.
GIN: one index entry per element
GIN is an inverted index. Instead of one entry per row, it stores one entry per key contained within a composite value: each lexeme of a tsvector, each element of an array, each key/value pair of a jsonb document, each trigram of a string. Each entry points at a posting list of heap TIDs, or, once that list gets large, a posting tree.
Everything about GIN follows from that. Searching for payload @> '{"kind":"refund"}' becomes a lookup of one or two entries followed by a walk of their TID lists, which is why containment is fast. Multi-key searches (?&, ?|, a tsquery with several terms) become set intersections or unions of posting lists.
The cost lands on writes. Inserting one row with a 200-lexeme tsvector means up to 200 index entries, each landing in a different part of the index. To make that survivable, GIN has fastupdate (on by default): new entries go into an unsorted pending list and get flushed in bulk later.
This is where GIN earns its reputation for unpredictability. Reads have to scan the pending list in addition to the main structure, so search latency creeps up as the list grows. And the flush is paid by whichever unlucky backend triggers it. That's the origin of the classic support ticket: "inserts into events take 1.5ms, except roughly one in every few thousand takes 200ms." Nothing is broken. One session ate the pending list flush. Tune gin_pending_list_limit (default 4MB) down if you want the pain spread more evenly, or turn fastupdate off on tables where predictable latency matters more than throughput.
When to use GIN index in Postgres
jsonb containment
The default opclass jsonb_ops supports @>, <@, ?, ?&, ?| and the jsonpath operators. jsonb_path_ops indexes hashed key/value paths instead, which makes it meaningfully smaller and typically faster, but it only supports containment-style operators (@>, @@, @?). The tradeoff is explicit: if your application only ever does containment, use jsonb_path_ops and take the size win. If anyone anywhere runs payload ? 'refund_id', you need jsonb_ops.
CREATE INDEX CONCURRENTLY events_payload_gin
ON events USING gin (payload jsonb_path_ops);
Arrays
array_ops is the default and handles @>, <@ and &&:
CREATE INDEX CONCURRENTLY orders_tags_gin
ON orders USING gin (tags);
Substring search
gin_trgm_ops from pg_trgm is the answer to LIKE '%substring%'. It decomposes the string into trigrams and indexes each one, so an unanchored pattern becomes a search for the trigrams it contains.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY orders_billing_email_trgm
ON orders USING gin (billing_email gin_trgm_ops);
Bitmap Heap Scan on orders (actual time=7.94..9.61 rows=212 loops=1)
Recheck Cond: (billing_email ~~ '%acme-holdings%'::text)
Rows Removed by Index Recheck: 4
Heap Blocks: exact=209
Buffers: shared hit=241 read=12
-> Bitmap Index Scan on orders_billing_email_trgm (actual time=7.61..7.61 rows=216 loops=1)
Index Cond: (billing_email ~~ '%acme-holdings%'::text)
Execution Time: 9.83 ms
Before the index, that query was a 6.4 second sequential scan. Note the Recheck Cond and the four rows removed: trigram matching is approximate, so the heap tuple always gets rechecked against the real pattern.
Full text search
Store the vector in a generated column so it can never drift from the source text:
ALTER TABLE events
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (to_tsvector('english', coalesce(summary, ''))) STORED;
CREATE INDEX CONCURRENTLY events_search_gin
ON events USING gin (search_vec);
Here's the failure mode from the opening story, after the GIN index was added but with work_mem left at 4MB:
Bitmap Heap Scan on events (actual time=41.2..1180.4 rows=812443 loops=1)
Recheck Cond: (payload @> '{"kind": "refund"}'::jsonb)
Rows Removed by Index Recheck: 2914088
Heap Blocks: exact=18422 lossy=241096
Buffers: shared hit=1204 read=259318
-> Bitmap Index Scan on events_payload_gin (actual time=38.9..38.9 rows=812443 loops=1)
Index Cond: (payload @> '{"kind": "refund"}'::jsonb)
Execution Time: 1301.8 ms
lossy=241096 is the tell. The TID bitmap outgrew work_mem, so it degraded to page granularity, and every tuple on those 241k pages had to be rechecked. Nearly three million rows got thrown away at recheck time. Raising work_mem for that job cut execution to 240ms without touching the index. If you only learn to read one number in EXPLAIN (ANALYZE, BUFFERS), make it that one.
GiST index in Postgres: a framework rather than an index
GiST gets misfiled as "the geometry one" more often than not. Think of it instead as an extensible framework than a single algorithm. Structurally it's a balanced tree of pages where each internal node holds a predicate that's true for every entry beneath it. That predicate is a lossy summary: a bounding box, a range union, a signature. A search descends into any subtree whose predicate might match, and because the summary is approximate, matching tuples get rechecked at the heap.
The semantics live in operator-class support functions. consistent decides whether a query could match a subtree predicate, union computes the summary of a set of entries, penalty scores how much inserting an entry would bloat a node, and picksplit decides how to divide an overflowing page. Swap those four functions and you have an index for a completely different problem — which is why GiST underpins geometry, ranges, ltree, pg_trgm, and exclusion constraints, all under one access method.
The lossiness costs you something, but it buys generality in return, and it shows up honestly in plans as Recheck Cond — the same signal you see in trigram GIN scans, except here it's the tree's summary being approximate rather than the operator being fuzzy.
Overlap, exclusion, nearest neighbour
The use case I reach for most is preventing double bookings. Range overlap (&&) is a GiST operator, and exclusion constraints are implemented with an index, so the two compose:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
id bigserial PRIMARY KEY,
room_id int NOT NULL,
guest_id bigint NOT NULL,
stay tstzrange NOT NULL,
EXCLUDE USING gist (room_id WITH =, stay WITH &&)
);
btree_gist is what lets a plain-equality column (room_id) sit in the same constraint as a range overlap. Without it, GiST has no operator class for int equality and the constraint won't build. This one DDL statement replaces an entire category of application-level locking that never quite works under concurrency.
The same index shape serves scheduling queries:
CREATE INDEX CONCURRENTLY bookings_stay_gist
ON bookings USING gist (stay);
SELECT id FROM bookings
WHERE stay && tstzrange('2026-08-10', '2026-08-14');
GiST also supports nearest-neighbour ordering, so ORDER BY location <-> point(...) LIMIT 10 gets answered by descending the tree in distance order rather than sorting the whole table — but only in the presence of that LIMIT; without it there's nothing to bound the descent, and the planner won't use the index for ordering alone. For PostGIS work, the GiST opclasses for geometry and geography are the default and you rarely think about it, which is exactly how it should be.
SP-GiST is the non-balanced sibling: partitioned search trees such as quadtrees, k-d trees and radix trees, for data that divides cleanly into non-overlapping partitions of unequal size. Text prefixes and inet addresses are its natural home. I've deployed it perhaps twice in a decade, both times for IP range lookups, and both times I measured it against GiST first — in most systems, the overlap and containment cases it could serve are already covered by GiST or GIN.
BRIN index in Postgres: the index that barely exists
BRIN stores no per-row entries at all. It divides the table into ranges of physically adjacent blocks (pages_per_range, default 128) and stores one summary tuple per range. For the minmax opclass that summary is the minimum and maximum value in those blocks.
A scan reads the summaries, discards ranges whose min/max can't contain the sought value, and returns every tuple in the surviving ranges as a candidate. BRIN scans are always lossy; the recheck is mandatory, and there's no way around it given that no per-row information is stored at all.
Which means BRIN's entire value rests on one property: does physical row order track the indexed value? On an append-only ingest_blobs table where rows arrive in received_at order, yes, beautifully. On a blob_uuid column, no — a BRIN index there will return essentially the whole table on every lookup while looking innocent, and tiny, in pg_indexes.
The go/no-go test is a catalogue query, not intuition:
SELECT attname, correlation, n_distinct
FROM pg_stats
WHERE schemaname = 'public'
AND tablename = 'ingest_blobs'
AND attname IN ('received_at', 'tenant_id', 'blob_uuid');
correlation runs from -1 to +1 and reports how closely physical ordering matches logical ordering. Near ±1 and BRIN is a candidate. On a real cluster I checked last month: received_at at 0.998, tenant_id at 0.11, blob_uuid at -0.0003. Only one of those three gets an index.
CREATE INDEX CONCURRENTLY ingest_blobs_received_at_brin
ON ingest_blobs USING brin (received_at) WITH (pages_per_range = 64, autosummarize = on);
Bitmap Heap Scan on ingest_blobs (actual time=2.1..96.4 rows=41902 loops=1)
Recheck Cond: (received_at >= '2026-07-28'::timestamptz)
Rows Removed by Index Recheck: 8194
Heap Blocks: lossy=768
-> Bitmap Index Scan on ingest_blobs_received_at_brin (actual time=1.9..1.9 rows=7680 loops=1)
The size difference is the point. That BRIN index is 96kB against a 4.1GB B-tree on the same column and the same 2.3 billion rows. On time-series tables where you need coarse range pruning and nothing else, that ratio is hard to argue with.
Two operational details. New heap pages aren't summarised automatically unless autosummarize is on; otherwise you wait for VACUUM or call brin_summarize_new_values() yourself. After a large bulk load with autosummarize off, the newest and most-queried data sits in unsummarised ranges, which are always treated as candidates. And PG 14 added minmax_multi, which keeps several intervals per range so one stray backdated row doesn't blow the min/max wide open — genuinely useful on columns with occasional stragglers, like a processed_at field where most rows are current but a few are reprocessed months later — plus bloom opclasses for equality on uncorrelated data.
Postgres indexing strategy: a decision table
| Data shape | Operators in WHERE | Access method | Main gotcha |
|---|---|---|---|
| Scalar, high cardinality | =, <, >, BETWEEN, IN, ORDER BY | B-tree | Leading-column rule on multicolumn indexes |
| Text, prefix match | LIKE 'abc%' | B-tree + text_pattern_ops | Needs C locale or the pattern opclass |
| Text, substring / fuzzy | LIKE '%abc%', % similarity | GIN + gin_trgm_ops | Index size; always rechecks |
| jsonb, containment only | @>, @? | GIN + jsonb_path_ops | Won't serve ?, ?&, ?| |
| jsonb, key existence too | @>, ?, ?&, ?| | GIN + jsonb_ops | Larger index, slower writes |
| Arrays | @>, <@, && | GIN + array_ops | Write amplification per element |
| Full text | @@ on tsvector | GIN | Pending-list flush latency spikes |
| Ranges, geometry | &&, @>, <-> | GiST | Lossy, recheck cost; KNN needs LIMIT |
| No-overlap invariant | EXCLUDE ... WITH && | GiST + btree_gist | Constraint blocks writes on conflict |
| Huge, physically ordered | >=, <=, BETWEEN | BRIN | Dies if correlation drops |
| Non-overlapping partitions (prefixes, inet) | prefix, containment | SP-GiST | Measure against GiST first |
| Equality, nothing else | = | hash | Usually not the answer |
Three questions, in this order: what operator, what cardinality, what physical correlation. Answer all three and you've already chosen the index.
On hash indexes: since PG 10 they're WAL-logged, so crash-safe and replicable, and the old "never use them" advice is out of date. That said, I've never once needed a hash index in production. B-tree serves equality perfectly well and also serves ranges, ordering and uniqueness. The narrow case where hash wins on size involves very wide keys under pure equality, and I've always found a cheaper answer.
Measuring instead of guessing
Start with what's dead:
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
idx_scan counts scans since statistics were last reset, so check stats_reset before you drop anything, and be careful about indexes that only serve a quarterly report or enforce a constraint.
When two access methods are both plausible, build both and compare. CREATE INDEX takes an ACCESS EXCLUSIVE lock on the table, blocking reads and writes for the entire build, which on a large table amounts to an outage. CREATE INDEX CONCURRENTLY is the production default: two table scans, waits on concurrent transactions, slower, and it can leave an INVALID index behind if it fails, which you then drop and retry. Build both candidates concurrently, compare pg_relation_size, run the real queries with EXPLAIN (ANALYZE, BUFFERS), drop the loser.
When you read the plan, four things carry most of the signal. Rows Removed by Filter means the index isn't selective enough for the predicate, or got you to roughly the right area without the actual condition being part of the index scan itself. Heap Blocks: exact=N lossy=M means the bitmap outgrew work_mem. Recheck Cond tells you the index is lossy by nature (GIN trigram, GiST, BRIN) and that the heap visit is unavoidable. And Buffers: shared read tells you whether you're actually saving I/O or just moving it around.
The mistakes I keep finding
Redundant indexes. A table with (customer_id) and (customer_id, placed_at) almost never needs the first one; the multicolumn index serves the leading-prefix case. I regularly find three or four such pairs per schema, each paying write cost on every insert.
GIN on a hot-write table with default settings. Fine in staging, then p99 insert latency develops a spiky tail in production as pending-list flushes land on random backends.
BRIN on an uncorrelated column. Someone reads that BRIN is tiny and cheap, puts one on blob_uuid, and every query returns every block range. The index is genuinely 80kB. It's also worthless, and worse, it looks like a working index in \d.
GiST where B-tree belonged. A timestamptz column indexed with GiST via btree_gist because the table already had btree_gist installed, or a range constraint that only ever needed plain equality. Five times the size, slower on range scans, lossy where B-tree is exact.
Indexing the column instead of the expression. A B-tree on customer_email doing nothing for WHERE lower(customer_email) = $1, because the operator class matches the column's raw type, not the output of a function applied to it. The fix is an expression index, not a bigger B-tree.
Unused indexes still charging rent. Every index must be updated on INSERT and on any non-HOT UPDATE. HOT updates only happen when no indexed column changes and there's free space on the same heap page, so a wide index set directly reduces how often you get the cheap update path.
Which brings it back to vacuum. Bloated tables destroy BRIN correlation because new tuples land in reclaimed space scattered through the heap rather than at the end. Bloat inflates every index. Falling behind on vacuum breaks index-only scans through a stale visibility map. You can pick perfect access methods and still lose all of it to an autovacuum configuration that hasn't been touched since the cluster was provisioned.
Pick the access method by operator and data shape. Verify with pg_stats and EXPLAIN. Then go and check your autovacuum settings, because that's where the next 2am page is coming from.