# 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.

```sql
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:

```sql
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 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?

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 →](https://app.dbgorilla.com/signup)

## 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.

## Related

- [How to find missing indexes in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-find-missing-indexes-in-postgresql/)
- [How to add a PostgreSQL index without downtime](https://www.dbgorilla.com/learn/postgres/how-to-add-a-postgres-index-without-downtime/)
- [Why AI-generated SQL is slow](https://www.dbgorilla.com/learn/postgres/why-ai-generated-sql-is-slow/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)