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're not sure, it's B-tree.

B-tree — the default, and usually right​

CREATE INDEX with no type specified builds a B-tree, and it's 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're 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 can't 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 can't 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 isn't 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's 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's 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 isn't physically ordered, it's much less effective.

Hash — pure equality only​

Hash indexes support only =. They were historically avoided because they weren't 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's the rarest of the six, but it's 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's 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 can't 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.