Choosing the Right PostgreSQL Index Type

The B-tree handles most queries, but Postgres ships five other index types, and each exists because a B-tree can't do a specific job well. Trying to full-text search, query JSONB, or index geometric data with a B-tree either fails outright or performs badly. Knowing which index matches which problem is what separates "I added an index and it didn't help" from queries that actually get fast.

This is part 10 of the PostgreSQL Masterclass, following how indexes work.

GIN: for values that contain many items

GIN stands for Generalized Inverted Index, and the word "inverted" is the clue. A B-tree indexes one value per row. A GIN index indexes the many items inside a single value, which is exactly what you need for arrays, JSONB, and full-text search.

Take a jsonb column or a full-text document. One row contains many keys or many words, and you want to find rows that contain a particular one. GIN builds a map from each contained item back to the rows that hold it:

-- fast containment queries on a jsonb column
CREATE INDEX events_data_idx ON events USING gin (data);
SELECT * FROM events WHERE data @> '{"type": "signup"}';

-- fast full-text search
CREATE INDEX articles_search_idx ON articles USING gin (to_tsvector('english', body));
SELECT * FROM articles WHERE to_tsvector('english', body) @@ plainto_tsquery('postgres');

GIN is the workhorse for "does this collection contain X." It's slower to build and update than a B-tree, because one row produces many index entries, so it fits data you search far more than you write.

GiST: for ranges, geometry, and nearest-neighbor

GiST (Generalized Search Tree) is a framework for indexing data where the question is "overlaps," "contains," or "is near," rather than "equals." Its common uses are geometric and spatial data (PostGIS is built on GiST), range types, and nearest-neighbor searches.

The exclusion constraint from earlier, the one that prevents double-booked rooms, is powered by a GiST index on a range type:

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE reservations (
  room_id BIGINT NOT NULL,
  during  tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

If you index a tstzrange, a geometry, or need "find the 5 closest points," GiST is the type. For plain equality it's worse than a B-tree, so use it only for these overlap and proximity questions.

BRIN: tiny indexes for naturally ordered data

BRIN (Block Range INdex) is the specialist that saves enormous space on one specific shape of data: large tables where the column's values line up with physical row order. The classic case is an append-only table with a created_at timestamp. Rows are inserted in time order, so rows physically near each other have similar timestamps.

BRIN exploits that. Instead of indexing every row, it stores the min and max value for each block range of the table. To find rows in a date window, Postgres skips any block range whose min-max doesn't overlap. A BRIN index is often thousands of times smaller than the equivalent B-tree:

CREATE INDEX events_created_brin ON events USING brin (created_at);

The catch is the correlation requirement. BRIN only works when the column's order roughly matches the table's physical order. On a randomly distributed column it's useless. For time-series and log tables it's close to free.

Hash: equality only, and rarely worth it

A Hash index supports exactly one operation: equality (=). It can't do ranges or sorting. Since a B-tree already handles equality well and does everything else too, Hash indexes are rarely the right pick. They became crash-safe and replicated in Postgres 10, so they're usable, but the situations where a Hash beats a B-tree are narrow. Reach for a B-tree by default and don't think about Hash unless you're specifically tuning a huge equality-only workload and have measured a win.

SP-GiST: the niche one

SP-GiST (Space-Partitioned GiST) handles non-balanced structures like quadtrees and radix trees, useful for certain spatial data and things like IP-range or phone-prefix lookups. You'll know when you need it, and most applications never do. It's on the list for completeness.

A decision table

Here's the whole module in one place:

The takeaway is simple. Default to a B-tree, and switch types only when the data shape demands it: GIN for "contains," GiST for "overlaps or near," BRIN for "big and naturally ordered." Match the index to the question and slow queries turn fast; mismatch it and the index sits there doing nothing.

Next, we go back to the B-tree and get more out of it with composite and covering indexes, where column order and included columns change everything.