One size does not fit all
Postgres ships several index access methods, each tuned for a different kind of query. Picking the wrong one wastes space or never gets used by the planner.
The main families
- Btree is the default. It handles equality and range queries on sortable values and powers ORDER BY and most where clauses.
- GIN, the Generalized Inverted Index, maps each contained element to the rows holding it. It is ideal for arrays, jsonb keys, and full text search.
- GiST, the Generalized Search Tree, is a balanced framework for overlap and nearest neighbor queries on geometry, ranges, and text similarity.
- BRIN, the Block Range Index, stores only the min and max value per block range. It is tiny and shines on huge tables physically ordered by the indexed column, such as append only time series.
Trade offs
- Btree is fast and general but grows with every distinct value.
- GIN is excellent for searching inside composite values but slower to update.
- GiST trades some lookup speed for flexible operators like distance.
- BRIN is the smallest index by far but only helps when data is well correlated with physical order.
Key idea
Match the index family to the query: Btree for ordered scalars, GIN for membership inside composite values, GiST for overlap and nearest neighbor, and BRIN for tiny summaries over naturally ordered tables.