Which PostgreSQL index type should I use?
Quick answer: Use B-tree for almost everything (equality, ranges, sorting, and prefix matches). Use GIN for
jsonb, arrays, and full-text; GiST for geometric, range, and nearest-neighbour queries; BRIN when a column correlates with physical row order on a huge table, especially an append-mostly time-series table; and Hash only for pure equality (crash-safe since PostgreSQL 10). If you are not sure, use B-tree.
B-tree, the default, and usually right
Section titled “B-tree, the default, and usually right”CREATE INDEX with no type specified builds a B-tree, which works for most
columns. It handles:
- equality (
=) and range comparisons (<,<=,>,>=,BETWEEN), ORDER BY(the index is sorted, so it can satisfy ordering),IN,IS NULL,- anchored prefix
LIKE 'foo%'(with the right operator class or a C locale).
For a normal scalar column, such as an id, foreign key, timestamp, or status, use a B-tree. Move to another index type only when the query requires it.
GIN, many values per row
Section titled “GIN, many values per row”GIN (Generalized Inverted Index) is for columns where each row contains multiple searchable values:
jsonbcontainment (@>) and key/path lookups,- arrays (does this array contain X?),
- full-text search over
tsvector(@@), - with the
pg_trgmextension, unanchoredLIKE '%substring%', which a B-tree cannot do.
CREATE INDEX ON documents USING gin (body_tsv);CREATE INDEX ON products USING gin (attributes); -- jsonbFor trigram search, name the operator class. A bare
USING gin (col) on a text column has no default GIN opclass and errors out:
CREATE EXTENSION IF NOT EXISTS pg_trgm;CREATE INDEX ON customers USING gin (email gin_trgm_ops); -- LIKE '%acme%'Trigram indexing is not limited to GIN: GiST supports it through
gist_trgm_ops. GIN searches faster but takes longer to build and update.
GiST is a better fit for a column that changes constantly.
GiST, geometry, ranges, and nearest-neighbour
Section titled “GiST, geometry, ranges, and nearest-neighbour”GiST (Generalized Search Tree) supports more specialized operators:
- geometric and spatial data (it is the backbone of PostGIS),
- range types and exclusion constraints,
- nearest-neighbour (KNN) searches, such as
ORDER BY location <-> point LIMIT 10.
BRIN, huge, naturally-ordered tables
Section titled “BRIN, huge, naturally-ordered tables”BRIN (Block Range Index) stores a small summary, such as a min/max value,
for each range of blocks rather than for each row. It fits when the column
correlates with the table’s physical order, such as an append-only created_at
column on a massive time-series table. A BRIN index can be much smaller than
the equivalent B-tree, but it provides less precise filtering. It is much less
effective when the data is not physically ordered.
Hash, pure equality only
Section titled “Hash, pure equality only”Hash indexes support only =. They were historically avoided because they
were not WAL-logged and were therefore not crash-safe or replicated. Since
PostgreSQL 10, hash indexes are WAL-logged and safe to use. A B-tree also
handles equality and supports ranges and sorting, so Hash indexes remain a
niche choice.
SP-GiST, space-partitioned data
Section titled “SP-GiST, space-partitioned data”SP-GiST covers non-balanced, space-partitioned structures such as quadtrees, k-d trees, and radix trees (tries). Use it for point data, non-overlapping ranges, and text-prefix work where a trie fits the data better than a B-tree. It is appropriate when the data partitions naturally rather than sorting naturally.
The quick decision table
Section titled “The quick decision table”| Your query | Index type |
|---|---|
Equality, ranges, sorting, foo% prefix | B-tree (default) |
jsonb, arrays, full-text, %substring% (with pg_trgm) | GIN |
| Geometric/spatial, ranges, nearest-neighbour | GiST |
| Very large table, column correlated with physical order | BRIN |
| Points, non-overlapping ranges, text prefixes (trie-shaped data) | SP-GiST |
| Pure equality, niche | Hash |
Why does AI-generated code pick the wrong index?
Section titled “Why does AI-generated code pick the wrong index?”A coding agent often suggests a plain B-tree because it is PostgreSQL’s default.
That works for most columns but fails for queries that need GIN or GiST. The
agent cannot determine from a column definition alone that a jsonb column is
queried with @>, or that a LIKE '%term%' search needs pg_trgm with
gin_trgm_ops (or gist_trgm_ops). The choice depends on the query shape and
the production data.
How do I choose confidently?
Section titled “How do I choose confidently?”- Default to B-tree; justify another type by the operator used in the query.
- Match the index to the query: containment/full-text → GIN, spatial/KNN → GiST, physically-ordered huge table → BRIN.
- Validate with
EXPLAIN(or ahypopghypothetical index) that the planner uses the index you built.
What is the default index type in PostgreSQL?
B-tree. CREATE INDEX without specifying a type builds a B-tree, and it handles the vast majority of needs: equality, range comparisons, ORDER BY, IN, BETWEEN, and anchored prefix LIKE matches like foo%.
When should I use a GIN index?
Use GIN for columns that hold multiple values per row: jsonb (containment queries), arrays, and full-text search over tsvector. With the pg_trgm extension a GIN index also accelerates unanchored LIKE %substring% searches.
What is a BRIN index good for?
BRIN suits very large tables where the column correlates with physical row order, classically an append-only created_at on a huge time-series table. It stores min/max summaries per block range, so it is tiny compared with a B-tree, at the cost of precision.
Are hash indexes safe to use in PostgreSQL now?
Yes. Since PostgreSQL 10 hash indexes are WAL-logged, so they are crash-safe and replicated. They only support equality, and a B-tree usually serves equality just as well, so they remain a niche choice.