Skip to content

Which PostgreSQL index type should I use?

Last updated

View as Markdown

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.

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 (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, 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 (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 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 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.

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?

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.

  • 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 a hypopg hypothetical 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.