Postgres Index Types: BRIN, GIN, GiST & More
Postgres reaches for a B-tree by default, and for good reason: it handles equality and range lookups on scalar columns and powers most primary keys. But the planner can only choose an index that exists, and several postgres index types solve problems a B-tree handles poorly or not at all. Knowing when to use which index separates a two-millisecond lookup from a sequential scan over millions of rows. What follows tours the main alternatives — BRIN, GIN, and GiST — and where Hash and SP-GiST fit.
BRIN: tiny indexes for big, ordered tables
A BRIN index (Block Range INdex) stores only the minimum and maximum values for each range of disk blocks, not a pointer to every row. That makes it tiny — often kilobytes where a B-tree would need gigabytes — but it only helps when a column's physical order on disk correlates with its values. The classic fit is a large, append-only table keyed by a timestamp or sequential id: logs, events, sensor readings, ledger rows. A query for last Tuesday's events can skip every block range whose maximum timestamp is earlier. On a randomly ordered column BRIN is nearly useless. Because the payoff depends on how well-ordered the table actually is, reading an EXPLAIN plan first is worth the time.
GIN: indexing what's inside a value
Where a B-tree indexes a whole value, a GIN index (Generalized Inverted Index) indexes the elements inside one. That is what full-text search needs, since each document holds many lexemes, and what arrays and jsonb need, since one row holds many keys or members. A GIN index over a tsvector answers "which rows contain this word" directly; one over a jsonb column accelerates containment queries using the @> operator. Updates are heavier than a B-tree's, so GIN rewards read-heavy columns over write-hot ones. For arrays, jsonb, hstore, and text search it is usually the right default.
GiST, SP-GiST, and Hash: the specialists
A GiST index (Generalized Search Tree) is a framework, not a single structure, built for data where "overlaps" or "is near" matters more than equality. Range types, geometric and spatial data, and exclusion constraints all lean on it — for instance, forbidding two bookings whose time ranges overlap. SP-GiST handles similar non-balanced cases such as quadtrees and IP-prefix trees by partitioning space. Hash indexes do exactly one thing: equality. Since becoming crash-safe and WAL-logged in Postgres 10 they can edge out a B-tree on equality lookups of large values, though they offer nothing for ranges or sorting.
Picking — and verifying — the right index
The honest answer to when to use which index starts with the query, not the column. Read the plan: a sequential scan over a filtered timestamp range hints at BRIN; a slow @> or full-text predicate points to GIN; overlap and nearest-neighbor operators call for GiST. Then build the candidate and confirm the planner uses it, because an index the optimizer ignores is just write overhead and extra dead-tuple bloat for autovacuum to clean up. Tools that read an EXPLAIN plan and propose matching index DDL shorten that loop. Querk returns a diagnosis with candidate index DDL from a pasted query or plan, so you can analyze a slow query or plan in the browser. The same diagnosis is reachable from an editor through its MCP server.