B-tree, GIN, BRIN and Hash: A Postgres Index Type Cheat-Sheet

Nearly every slow query I am asked to look at has an index on the column. It is just the wrong kind of index for the operator the query actually uses.

PostgreSQL ships five general-purpose index access methods, and the planner will happily ignore the one you built if it cannot answer the operator in your WHERE clause. A B-tree cannot serve a JSONB containment query. A hash index cannot serve a range. A GIN index will not help an ORDER BY. The choice is not about the column’s data type in isolation; it is about the column, the operator and the shape of the query together. This is the one-page reference I wish were pinned next to every migration. It targets PostgreSQL 17, but the substance holds from 14 onward.

B-tree: equality, range and sort order

B-tree is the default and the right answer far more often than the interesting alternatives. It serves =, <, <=, >, >=, BETWEEN, IN, IS NULL and prefix LIKE 'foo%', and it is the only method that can satisfy an ORDER BY or feed a merge join without a separate sort. It is also the only one of the five that enforces uniqueness. If you are indexing a scalar column that you filter on equality or range and occasionally sort by, stop reading and use B-tree. Since PostgreSQL 13 it also de-duplicates repeated keys automatically, which shrinks indexes on low-cardinality columns that people used to route to other methods out of misplaced cleverness.

-- Equality, range and ordering all served by one B-tree.
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Multicolumn: leftmost-prefix rule applies. This serves
-- (customer_id) and (customer_id, created_at), not (created_at) alone.
CREATE INDEX idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

-- Uniqueness is a B-tree feature no other method offers.
CREATE UNIQUE INDEX uq_users_email ON users (lower(email));

Hash: equality only, and nothing else

Hash indexes serve exactly one operator, =. No ranges, no ordering, no prefix matching, no uniqueness constraint. For years the standard advice was to avoid them, because before PostgreSQL 10 they were not written to the write-ahead log and so did not survive a crash or replicate to standbys. That caveat is gone: since version 10 hash indexes are WAL-logged and crash-safe. Where they earn their place is equality lookups on wide or long values, because a hash index stores a 32-bit hash of the key rather than the key itself, so it can be smaller than a B-tree over the same large column. That is the whole case for them. If you ever need a range, an ordering or a unique constraint on that column later, you will be rebuilding, so reach for hash only when you are confident the access pattern is equality forever.

-- Equality-only lookup on a long token. Smaller than a B-tree here.
CREATE INDEX idx_sessions_token_hash
    ON sessions USING hash (session_token);

Equality-only stores like this show up constantly behind authentication and deduplication logic; the same shape underpins idempotency keys used to make POST safe to retry, where you only ever probe for an exact key.

GIN: arrays, JSONB and full-text

GIN, the Generalised Inverted Index, is for columns that hold multiple values inside one row: arrays, jsonb, tsvector for full-text search, and trigram matching via the pg_trgm extension. It indexes each element and points back to the rows containing it, which is exactly what you want for containment (@>), overlap (&&), key-existence (?) and full-text match (@@). The operator class matters here. For jsonb the default jsonb_ops indexes every key and value and supports the full set of JSONB operators; jsonb_path_ops is smaller and faster but supports only containment-style queries. GIN’s cost is write amplification: inserting one row touches many index entries, so it is slower to maintain than a B-tree. The fastupdate mechanism buffers those writes into a pending list, trading query-time work for insert-time speed.

-- JSONB containment: default opclass supports the full operator set.
CREATE INDEX idx_events_payload ON events USING gin (payload);

-- Smaller, containment-only: choose when @> is all you query.
CREATE INDEX idx_events_payload_path
    ON events USING gin (payload jsonb_path_ops);

-- Trigram search for LIKE '%substring%' (needs pg_trgm).
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm
    ON products USING gin (name gin_trgm_ops);

GiST: geometry, ranges and nearest-neighbour

GiST is the extensible one, and the method you want for anything with a notion of overlap or distance: geometric types, range and multirange types, IP addresses, and full-text where you favour update speed over query speed. Two things only GiST does well. First, it backs exclusion constraints, so you can forbid overlapping bookings on a tstzrange column declaratively rather than in application code. Second, it answers nearest-neighbour ordering, ORDER BY column <-> point, returning the closest rows without scanning the table. If you are storing time ranges, spatial data, or anything you will query with && overlap, GiST is the reach.

-- Forbid overlapping reservations for the same room.
ALTER TABLE reservations ADD CONSTRAINT no_overlap
    EXCLUDE USING gist (room_id WITH =, during WITH &&);

-- Nearest-neighbour: GiST answers ORDER BY distance directly.
CREATE INDEX idx_sites_location ON sites USING gist (location);

BRIN: enormous, naturally-ordered tables

BRIN, Block Range INdex, is the specialist that wins on tables too big for a B-tree to be economical, provided the column is physically correlated with row order. It stores only the minimum and maximum value for each block range of the table, so a range scan can skip whole swathes of pages whose summary cannot match. For an append-only table of a billion timestamped events, a BRIN index on the timestamp is a tiny fraction of the size of the equivalent B-tree and still lets a date-range query skip most of the heap. The catch is the correlation: if the column is not roughly sorted on disk, the summaries overlap, nothing can be skipped, and the index is useless. The default pages_per_range is 128; lower it for tighter summaries on smaller tables. minmax_multi operator classes tolerate some disorder, and a bloom variant handles equality on uncorrelated columns.

-- Huge append-only table, timestamp correlated with disk order.
CREATE INDEX idx_metrics_ts_brin
    ON metrics USING brin (recorded_at) WITH (pages_per_range = 64);

-- Tolerate mild disorder with the multi-minmax opclass.
CREATE INDEX idx_metrics_ts_multi
    ON metrics USING brin (recorded_at timestamptz_minmax_multi_ops);

How to actually decide

Read the operator, not the column. Equality or range on a scalar, or any sort, is B-tree. Equality-only on a large value where size matters is hash. A multi-valued column queried by containment or match is GIN, with the operator class chosen by how narrow your queries are. Overlap, distance or exclusion constraints are GiST. A vast, naturally-ordered table where a B-tree would be too big is BRIN. Then confirm with EXPLAIN (ANALYZE, BUFFERS) that the planner is using the index and reading fewer buffers, because an index the optimiser declines is pure write-amplification cost for no read benefit. Keep the index definitions in version control alongside the schema, run them through the same pre-commit and CI checks as the rest of your migrations, and measure the effect the way you measure any other change; an index is a deployment, and it belongs in the same change and performance instrumentation as everything else you ship.

The index that never gets used is not free. It slows every write, inflates every backup, and lies in the migration history looking like diligence. Choose it for the operator, or do not build it.