Postgres Index Types Beyond B-Tree: GIN, GiST, BRIN, and Hash

Ninety percent of Postgres indexes should be B-Tree, and that's fine. But the other four index types exist because B-Tree is the wrong tool for whole classes of query — full-text search, array containment, geometry, and append-only time-series. Knowing which is which turns a sequential scan into an index scan.

1. B-Tree — the default, and usually right

Balanced tree, ordered. Handles =, <, >, BETWEEN, ORDER BY, and prefix LIKE 'abc%'. If your predicate is an equality or a range on a scalar column, stop here.

2. GIN — for "values inside a value"

Generalised Inverted iNdex. Built for columns where one row contains many searchable elements: jsonb, arrays, and full-text tsvector. GIN indexes the elements, not the row, so containment queries become index lookups.

CREATE INDEX idx_doc_tags ON docs USING GIN (tags);
SELECT * FROM docs WHERE tags @> '{kafka}';   -- array contains

CREATE INDEX idx_doc_body ON docs USING GIN (to_tsvector('english', body));
SELECT * FROM docs WHERE to_tsvector('english', body) @@ plainto_tsquery('idempotency');

GIN is slower to build and write than B-Tree (every element is indexed) but unbeatable for read-heavy containment. Use jsonb_path_ops if you only need @> — smaller and faster.

3. GiST — for overlap and nearest-neighbour

Generalised Search Tree. The framework behind geometric and range queries: "what overlaps this box", "what's nearest this point". PostGIS is built on it, and so is tsrange overlap.

CREATE INDEX idx_room_period ON bookings USING GiST (period);
SELECT * FROM bookings WHERE period && tsrange('2026-05-01','2026-05-07');  -- overlaps

4. BRIN — tiny indexes for huge, ordered tables

Block Range INdex. Instead of indexing every row, it stores the min/max value per block range. On a table where the column correlates with physical order — append-only logs ordered by created_at — a BRIN index is thousands of times smaller than B-Tree and still prunes most of the table.

CREATE INDEX idx_events_time ON events USING BRIN (created_at);
-- a multi-GB table gets a few-hundred-KB index

The catch: it only helps when physical order matches the column. Random inserts kill BRIN's selectivity.

5. Hash — narrow but real

Equality only, no ranges. Since Postgres 10 hash indexes are WAL-logged and crash-safe. They can be marginally smaller than B-Tree for pure = lookups on large keys, but the win is small enough that most people just use B-Tree. Reach for it only when you've measured.

Rules of thumb

• Scalar equality/range → B-Tree.
• JSONB, arrays, full-text → GIN (jsonb_path_ops if you only do containment).
• Geometry, ranges, nearest-neighbour → GiST.
• Append-only, naturally-ordered, billions of rows → BRIN.
• Always confirm with EXPLAIN ANALYZE that the planner actually picks the index — an unused index is pure write overhead.