Postgres Index Types: B-tree, GIN, GiST, BRIN, Hash — and When Each Wins

CREATE INDEX defaults to a B-tree, and B-trees are the right answer most of the time. But Postgres ships several index access methods, each built for different data and operators — and the wrong one simply won't be used. (Database indexes covers the basics; this is the deeper map.)

The key idea: indexes serve operators

An index helps a query only if it supports the operator in the WHERE (or ORDER BY, or join). A B-tree supports =, <, >, BETWEEN, IN, sorting and prefix LIKE 'abc%' (with the right collation or opclass). It does not help @> (contains) on JSONB, @@ full-text matching, or && (overlaps) on ranges. Those need other types.

You can list which operator classes an index type supports:

SELECT am.amname, opc.opcname, typ.typname
FROM pg_opclass opc
JOIN pg_am am ON am.oid = opc.opcmethod
JOIN pg_type typ ON typ.oid = opc.opcintype
WHERE am.amname = 'gin';

B-tree (default)

A balanced tree of sorted keys. Supports equality, ranges, sorting, IS NULL, and multi-column indexes (leftmost-prefix rule: an index on (tenant_id, created_at) serves WHERE tenant_id = ? and WHERE tenant_id = ? ORDER BY created_at, but not WHERE created_at > ? alone efficiently — though Postgres 18's skip scan can now use such an index for some queries on later columns when the leading column has few distinct values).

CREATE INDEX orders_tenant_created ON orders (tenant_id, created_at DESC);

Use for: IDs, foreign keys, timestamps, status columns (with care), anything you sort or range over.

GIN (Generalized Inverted Index)

Maps each element inside a value to the rows containing it — like a book's index. Ideal when a column holds many items:

• JSONB: @>, ?, ?|, ?& (with jsonb_ops; jsonb_path_ops is smaller and faster but supports only @> and path queries).
• Arrays: @>, &&.
• Full-text search: tsvector @@ tsquery.
• Trigram similarity (pg_trgm): LIKE '%abc%', ILIKE, % similarity.

CREATE INDEX products_attrs ON products USING gin (attrs jsonb_path_ops);
CREATE INDEX docs_search ON docs USING gin (to_tsvector('english', body));
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);

Trade-offs: fast lookups, slower writes (one row → many index entries). GIN uses a "pending list" (fastupdate) to batch insertions, which can make some reads slower until it's merged; gin_pending_list_limit tunes it. (Postgres JSONB, full-text search)

GiST (Generalized Search Tree)

A framework for overlapping, multidimensional or "nearness" data, organised by bounding regions:

• Geometry and PostGIS: within, intersects, nearest neighbour (<-> for ORDER BY distance LIMIT k).
• Ranges: && overlaps, @> contains — the basis of exclusion constraints (no two bookings for the same room overlapping).
• Full-text (smaller than GIN, slower to query, faster to update).
• Trigram with KNN ordering.

CREATE EXTENSION btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

That constraint is something no B-tree unique index can express.

SP-GiST

Space-partitioned trees (quadtrees, k-d trees, radix tries) for data with natural non-overlapping partitions: IP addresses (inet), points, text prefixes. Niche but excellent when it fits.

BRIN (Block Range Index)

Stores only a summary per range of table pages (by default min/max per 128 pages). Tiny — kilobytes for a table of hundreds of gigabytes — and effective only when the column's values correlate with physical row order: append-only time-series where created_at increases with insertion.

CREATE INDEX events_created_brin ON events USING brin (created_at);

Check correlation first:

SELECT attname, correlation FROM pg_stats WHERE tablename = 'events';

Near ±1 → BRIN can work well. Near 0 → it will be scanned and discarded. Updates and deletes that scatter rows erode it. Pairs well with table partitioning.

Hash

Equality-only (=), sometimes smaller than a B-tree for long keys such as URLs or tokens. Crash-safe and replicated since Postgres 10. Rarely worth choosing over B-tree, which also does equality — but useful for very long values queried only by equality.

Partial, expression and covering indexes

These work with most types and are often the bigger win:

Partial — index only the rows you query:

CREATE INDEX jobs_pending ON jobs (run_at) WHERE status = 'pending';

Small, hot, and perfect for queues and soft-deleted tables (WHERE deleted_at IS NULL). (Postgres SKIP LOCKED queue, soft deletes)

Expression — index a computed value; the query must use the same expression:

CREATE INDEX users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);

Covering (INCLUDE) — carry extra columns so the query is satisfied by an index-only scan:

CREATE INDEX orders_customer ON orders (customer_id) INCLUDE (status, total_cents);

Index-only scans also depend on the visibility map being current — i.e. on VACUUM. (Postgres VACUUM)

Choosing, in one table

Data / query	Index
=, ranges, sorting, joins	B-tree
JSONB containment, arrays, full-text, %substring%	GIN (+ pg_trgm for substrings)
Geometry, ranges, overlaps, nearest-neighbour, exclusion constraints	GiST
IPs, points, prefix trees	SP-GiST
Huge append-only tables with time-ordered columns	BRIN
Long keys, equality only	Hash (or B-tree)
Vector similarity	HNSW / IVFFlat via pgvector (pgvector tutorial)

Verify, don't assume

EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

Look for Index Scan, Index Only Scan or Bitmap Index Scan on your index, and compare buffers read before and after. (Reading EXPLAIN ANALYZE) Then check for indexes that never get used — every index slows writes and costs space:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 20;

Build indexes on live tables with CREATE INDEX CONCURRENTLY to avoid blocking writes. (Migrations on large tables)

The summary

• Indexes serve specific operators; match the type to the query.
• B-tree for most things; GIN for "contains" over many elements; GiST for overlap and nearness; BRIN for huge, physically ordered data.
• Partial, expression and covering indexes are often the biggest wins.
• Confirm with EXPLAIN (ANALYZE, BUFFERS) and drop unused indexes.

---

EasySpawn servers include PostgreSQL with extensions like pg_trgm and pgvector available, and Claude Code can test index changes against a copy of your data before they reach production. See how it works or join the waitlist.

Related: Database Indexes · Reading Postgres EXPLAIN ANALYZE · Postgres JSONB · Postgres Full-Text Search