PostgreSQL Indexing Deep Dive: B-Tree, GIN, GiST, and BRIN Explained

1. B-Tree: The General-Purpose Workhorse

B-tree is PostgreSQL's default index type and covers the vast majority of real-world indexing needs: equality and range predicates (=, <, <=, >, >=, BETWEEN), ORDER BY satisfaction without a separate sort, and efficient duplicate handling. It maintains a balanced tree of pages where every leaf is at the same depth, giving O(log n) lookup cost that barely changes as the table grows from a million to a billion rows — this predictability is exactly why it should be your default choice unless the query pattern specifically calls for something else.

Composite (multi-column) B-tree indexes follow leftmost-prefix matching: an index on (a, b, c) can serve queries filtering on a alone, a+b, or a+b+c efficiently, but not b alone or c alone — the leading column must be present in the predicate for the index to be used at all for that lookup. Column order in a composite index should generally put the highest-selectivity or most commonly-filtered-alone column first, unless a specific query pattern's WHERE + ORDER BY combination dictates otherwise.

Partial indexes — `CREATE INDEX ... WHERE condition` — build a B-tree over only the subset of rows matching the condition, which is dramatically smaller and faster to scan than a full index when queries consistently filter on that same condition (e.g. an index on orders WHERE status = 'pending' when 95% of orders are eventually 'completed' and rarely queried by status again). The index is both smaller on disk and cheaper to maintain on writes to rows outside the partial condition, since they never touch it.

The trade-off nobody skips paying is write amplification: every INSERT, UPDATE (that touches an indexed column), and DELETE must also update every B-tree index on that table, and each index update is itself a random-access B-tree operation. A table with eight indexes pays that cost eight times per write, which is why 'index everything defensively' is a real anti-pattern — audit index usage with pg_stat_user_indexes and drop indexes with near-zero idx_scan counts.

2. GIN (Generalized Inverted Index): Arrays and JSONB Containment

GIN indexes flip the usual mapping: instead of one index entry per row, GIN builds one index entry per distinct key/element found inside composite values, each pointing to the list of rows containing it — exactly like a search engine's inverted index mapping words to documents. This makes it the correct index type whenever a single column value contains multiple searchable 'things': array elements, JSONB keys/values, or lexemes in a tsvector for full-text search.

For JSONB, GIN supports the containment operator @> efficiently — `WHERE metadata @> '{"plan": "pro"}'` can use a GIN index to jump straight to matching rows instead of scanning and deserializing every JSONB document. The default GIN opclass (jsonb_ops) indexes every key and value; the alternative jsonb_path_ops indexes hashed path/value combinations instead, producing a smaller, faster index for containment queries specifically, at the cost of not supporting the `?`, `?|`, `?&` key-existence operators.