Indexing Beyond B-Trees

postgres1-4 and postgres1-6 both reached for a GIN index without fully explaining what one actually is. This chapter closes that gap, and covers the rest of Postgres's own richer index ecosystem — genuinely broader than MySQL's mostly B-tree-plus-hash approach.

B-Tree — The Default, Shared With MySQL

Both engines default to a B-tree index for ordinary equality and range queries — this is shared ground, not new material. Everything below is what Postgres adds beyond it.

GIN — Generalized Inverted Index

GIN stores a mapping from each individual component of a value — a JSONB key, an array element, a tsvector lexeme — to the list of rows containing it. This is a structurally different approach from a B-tree, which is built around ordering a single value per row; GIN is built for "does this composite value contain X" queries instead, which is exactly why it was used, unexplained until now, for postgres1-4's own JSONB containment queries and postgres1-6's own full-text search matching.

The trade-off: GIN indexes are genuinely more expensive to update than B-tree indexes — writes cost more, since a single row update can touch many entries in the index (one per component). GIN is a real cost, not a free upgrade.

GiST — Generalized Search Tree

GiST supports a broader, extensible class of queries than GIN — nearest-neighbor search, geometric and spatial queries (previewing postgres1-10's own PostGIS coverage), and, notably, it's the exact index structure powering postgres1-3's own EXCLUDE USING gist range-overlap constraint.

In general, GIN tends to be faster for lookups once built but slower to build and update; GiST is more flexible across a wider variety of query types but can be slower for simple lookups than a specialized GIN index — a genuine, real trade-off in both directions, not a case of one being strictly worse.

BRIN — Block Range Index

BRIN (Block Range Index) is designed specifically for very large tables where a column's values have a natural physical correlation with insertion order — a timestamp column on an append-only log or events table is the classic case, since rows are naturally inserted in roughly chronological order. Rather than indexing every individual row the way B-tree, GIN, and GiST all do, BRIN stores only summary information — typically the min/max values — for each physical block range of the table. The result is a dramatically smaller index, much cheaper to maintain, that still enables efficient range queries when the correlation assumption genuinely holds.

Hash Indexes

Hash indexes exist in both engines, but Postgres's own history here is worth being honest about: Postgres hash indexes weren't crash-safe or WAL-logged before Postgres 10, a real, documented limitation. They're safe to use now, but remain a genuinely narrow tool — useful only for pure equality lookups (=), never for range queries — and in practice, B-tree is usually still preferred, since it handles equality well and supports range queries too. Hash indexes remain a narrow, rarely-the-best-choice option even today.

Choosing the Right Index

Hands-On Exercises