Skip to main content

Which PostgreSQL index type should I use?

Quick answer: Use B-tree for almost everything, equality, ranges, sorting, and prefix matches. Reach for GIN on 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 (classically append-mostly time-series); and Hash only for pure equality (crash-safe since PostgreSQL 10). If you are not sure, it is B-tree.

B-tree, the default, and usually right​

CREATE INDEX with no type specified builds a B-tree, and it is the answer for the vast majority of 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).

If you are indexing a normal scalar column, an id, a foreign key, a timestamp, a status, you want a B-tree. Start here and only move on when B-tree genuinely cannot express the query.

GIN, many values per row​

GIN (Generalized Inverted Index) is for columns where each row contains multiple searchable values:

  • jsonb containment (@>) and key/path lookups,
  • arrays (does this array contain X?),
  • full-text search over tsvector (@@),
  • with the pg_trgm extension, unanchored LIKE '%substring%', which a B-tree cannot do.
CREATE INDEX ON documents USING gin (body_tsv);
CREATE INDEX ON products USING gin (attributes);   -- jsonb

For trigram search you must 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 GIN-only: GiST supports it too via gist_trgm_ops. GIN is faster to search but slower to build and update; GiST is the better fit for a column that changes constantly.

GiST, geometry, ranges, and nearest-neighbour​

GiST (Generalized Search Tree) is the framework for more exotic operators:

  • geometric and spatial data (it is the backbone of PostGIS),
  • range types and exclusion constraints,
  • nearest-neighbour (KNN) searches.ORDER BY location <-> point LIMIT 10.

BRIN, huge, naturally-ordered tables​

BRIN (Block Range Index) stores a small summary (like min/max) per range of blocks rather than per row. It is a great fit when the column correlates with the table's physical order, the canonical case is an append-only created_at on a massive time-series table. A BRIN index can be a tiny fraction of the size of the equivalent B-tree, trading precision for size. On data that is not physically ordered. It is much less effective.

Hash, pure equality only​

Hash indexes support only =. They were historically avoided because they were not WAL-logged (not crash-safe, not replicated), but since PostgreSQL 10 hash indexes are WAL-logged and safe to use. Even so, a B-tree handles equality about as well and covers ranges and sorting, so Hash stays a niche choice.

SP-GiST, space-partitioned data​

SP-GiST covers non-balanced, space-partitioned structures, quadtrees, k-d trees, and radix trees (tries). In practice you reach for it on point data, non-overlapping ranges, and text-prefix work where a trie beats a B-tree. It is the rarest of the six, but it is the right answer when your data partitions naturally rather than sorting naturally.

The quick decision table​

Your queryIndex type
Equality, ranges, sorting, foo% prefixB-tree (default)
jsonb, arrays, full-text, %substring% (with pg_trgm)GIN
Geometric/spatial, ranges, nearest-neighbourGiST
Very large table, column correlated with physical orderBRIN
Points, non-overlapping ranges, text prefixes (trie-shaped data)SP-GiST
Pure equality, nicheHash

Why does aI-generated code pick the wrong index?​

Your AI coding agent (Claude Code, Cursor) usually reaches for a plain B-tree because that is the default in its training data, which is right most of the time and quietly wrong for the cases that need GIN or GiST. It cannot see that a column is jsonb queried with @>, or that a LIKE '%term%' search needs pg_trgm with gin_trgm_ops (or gist_trgm_ops), because the right choice depends on the query shape and the data type in production, not just the column definition it reads.

How do I choose confidently?​

  • Default to B-tree; justify anything else by the specific operator you query with.
  • Match the index to the query: containment/full-text → GIN, spatial/KNN → GiST, physically-ordered huge table → BRIN.
  • Validate with EXPLAIN (or a hypopg hypothetical index) that the planner actually uses what you built.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the real column types, the queries hitting them, and the plans, so it can recommend GIN for that jsonb @> filter or BRIN for a physically-ordered timestamp instead of defaulting to B-tree everywhere. It surfaces and explains; it does not create indexes for you. Get started free →

FAQ​

What is the default index type? B-tree.CREATE INDEX builds one, and it covers equality, ranges, ORDER BY, IN, BETWEEN, and anchored prefix LIKE 'foo%'.

When should I use GIN? For multiple values per row: jsonb, arrays, full-text tsvector, and (with pg_trgm) unanchored LIKE '%substring%'.

What is BRIN good for? Very large tables where the column correlates with physical order (append-only created_at); tiny index, less precise.

Are hash indexes safe now? Yes. WAL-logged and crash-safe since PostgreSQL 10. They only do equality, though, so B-tree is usually the better default.