The Standard Tool
In the "RDBMS Internals" topic, we learned about the B-Tree. It is the default index type for a reason: it is incredibly efficient for equality (id = 5) and range (age > 21) queries on simple data types like integers and strings. A B-Tree works by maintaining a sorted, balanced tree structure where every lookup is O(log n) — fast and predictable.
The Limitation
But what if your data isn't a simple number or string that can be sorted on a single axis?
- How do you index a JSON document to find "all users where
attributes->>'color'is'blue'"? - How do you index a geographic map to find "all coffee shops within 2 miles of this point"?
- How do you index a text array to find "all posts containing the tag
'database'"?
A standard B-Tree fails here because it relies on a strict, one-dimensional sort order. You can answer "is A greater than B?" for integers, but "is North greater than West?" is a meaningless question for coordinates. The B-Tree has no way to express spatial proximity, document containment, or multi-element membership. We need Advanced Indexing structures designed specifically for complex, multi-dimensional, and container-style data.
The Landscape
PostgreSQL provides four index types beyond B-Tree that cover the vast majority of advanced use cases:
| Index Type | Best For | Key Operator |
|---|---|---|
| GIN | Arrays, JSONB, Full-Text Search | @>, @@, && |
| GiST | Geometry, Ranges, Nearest-Neighbor | &&, <->, @> |
| BRIN | Huge, naturally-ordered append-only tables | <, >, = |
| Hash | Exact equality only, no range support | = |
This lesson covers GIN and GiST in depth, then explores two structural optimizations — Covering Indexes and Partial Indexes — that apply to any index type. We close with the often-ignored cost of over-indexing.