Beyond B-Tree: GIN, GiST, BRIN, and Hash Indexes

The one thing to understand first

B-tree assumes a total ordering of scalar keys. Many workloads do not fit that model — full-text search, JSON containment, geometric overlap, or simply tables too large to index conventionally. PostgreSQL's extensible access-method framework provides specialised index types for each.

The index type must match the operator in your WHERE clause, not the column's data type. "Containment" wants GIN, "overlap" wants GiST, "this huge table is already sorted on disk" wants BRIN — pick the structure that speaks your query's operator and the rest is detail.

GIN — Generalized Inverted Index

src/backend/access/gin/. A GIN index maps each component of a value to the list of rows containing it — an inverted index. It powers:

• Full-text search over tsvector (each lexeme → rows).
• jsonb containment (@>, key existence).
• Array membership (&&, @>).

GIN is fast to search but slower to update; it buffers updates via the "pending list" (controlled by fastupdate and gin_pending_list_limit) to amortise insert cost.

CREATE INDEX idx_doc_fts ON documents USING gin (to_tsvector('english', body));
CREATE INDEX idx_meta ON events USING gin (metadata jsonb_path_ops);

GiST — Generalized Search Tree

src/backend/access/gist/. GiST is a balanced tree where each node holds a predicate that is true for everything below it. It is a framework: operator classes define how to split and search. Used for:

• Geometry / PostGIS — bounding-box overlap for spatial queries.
• Range types — overlap and containment (&&, @>) and exclusion constraints.
• Nearest-neighbour — ordered ORDER BY geom <-> point KNN search.

-- Prevent overlapping bookings with a GiST exclusion constraint
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

BRIN — Block Range Index

src/backend/access/brin/. BRIN stores only a summary (e.g. min/max) per range of physical blocks. It is tiny — often kilobytes for a multi-terabyte table — and wins when data is naturally ordered on disk, such as an append-only time-series table where created_at increases with physical position.

CREATE INDEX idx_events_brin ON events USING brin (created_at)
  WITH (pages_per_range = 128);
-- A range query prunes block ranges whose min/max can't match.

BRIN trades precision for size: it tells you which block ranges might contain matches; the executor rechecks. On correlated data this is enormously efficient; on randomly ordered data it is useless.

Hash indexes

src/backend/access/hash/. Hash indexes support only equality (=) but can be smaller and faster than B-tree for that single case on large keys. Since PostgreSQL 10 they are crash-safe and WAL-logged. In practice B-tree is usually preferred because it also serves ranges and ordering, but hash can win for pure equality on long text keys.

Layer 3 — Watch it happen on your own database

-- Dramatic size difference: BRIN vs B-tree on an ordered column
CREATE INDEX o_btree ON events (created_at);
CREATE INDEX o_brin  ON events USING brin (created_at);
SELECT relname, pg_size_pretty(pg_relation_size(oid))
FROM   pg_class WHERE relname IN ('o_btree','o_brin');

-- Confirm the access method actually gets used
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events WHERE created_at >= now() - interval '1 day';

On a large, append-ordered table the BRIN index is often a few hundred kilobytes against many megabytes (or gigabytes) for the B-tree — proof of the precision-for-size trade. The EXPLAIN tells you whether the planner chose a Bitmap Heap Scan driven by your specialised index, or fell back to a sequential scan because the operator did not match the index type.

Layer 4 — The levers this hands you

• GIN write cost: tune fastupdate and gin_pending_list_limit to balance search freshness against insert throughput.
• BRIN granularity: pages_per_range controls summary resolution; smaller ranges prune better but enlarge the index.
• jsonb operator class: jsonb_path_ops is smaller and faster for containment than the default jsonb_ops when you do not need key-existence queries.
• Exclusion constraints ride on GiST — the cleanest way to forbid overlapping ranges (bookings, schedules).

Layer 5 — What an Oracle DBA should expect vs what they get

This is where PostgreSQL's extensibility pulls clearly ahead of a stock Oracle DBA's toolkit:

• GIN ≈ Oracle Text, but native. Full-text and "inverted index" needs that Oracle meets with Oracle Text / domain indexes are core, built-in GIN here — no separate text index type to license or manage.
• GiST is a generic framework. Oracle spatial uses dedicated R-tree-style domain indexes; GiST is one extensible tree that serves geometry, ranges, KNN, and exclusion constraints through operator classes.
• BRIN ≈ Exadata storage indexes / zone maps, but as an ordinary, persistent index you create with SQL — ideal for time-series where Oracle DBAs would reach for partition pruning alone.
• Hash indexes are equality-only and rarely needed. Unlike Oracle's hash clusters, a Postgres hash index just serves =; B-tree usually wins because it also covers ranges and ordering.

Key takeaway

PostgreSQL's access-method framework gives you GIN (inverted, for full-text/jsonb/arrays), GiST (predicate tree, for geometry/ranges/KNN/exclusion), BRIN (tiny block-range summaries for physically-ordered data), and hash (equality-only). Choose by the operator your query uses, not the column type, and tune the type-specific knobs (fastupdate, pages_per_range, operator class). Matching index to data shape beats almost any query rewrite.