Index Types (B-tree, GIN, GiST, BRIN)

The Indexes Overview page introduced CREATE INDEX using PostgreSQL's default index type. That default, a B-tree, is the right choice for most columns, but it is far from the only option. PostgreSQL ships with several specialized index types, each built around a different kind of data and a different kind of query, and choosing the right one can turn an unusably slow query into an instant one.

The index types at a glance

Type	Best for	Notes
B-tree	Equality and range queries on scalar types (numbers, text, dates)	The default; used automatically if you don't specify USING
GIN	JSONB containment, array containment, full-text search	Generalized Inverted Index — indexes multiple values per row entry
GiST	Geometric data, range types, nearest-neighbor searches	Generalized Search Tree — supports many operator classes, extensible
BRIN	Very large tables with naturally correlated/sequential data (timestamps, IDs)	Block Range Index — much smaller than a B-tree, less precise

B-tree — the default

A B-tree keeps values in sorted order, which makes it efficient for exactly the comparisons that come up constantly: equals, less-than, greater-than, BETWEEN, and ORDER BY on the indexed column. Unless you specify otherwise, CREATE INDEX builds a B-tree.

B-tree is implicit

SQL

-- USING btree is the default; both lines are equivalent
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_created_at_explicit ON orders USING btree (created_at);

GIN — Generalized Inverted Index

A GIN index is built for columns where each row's value is really a collection of things — the keys inside a JSONB document, the elements of an array, or the lexemes produced by full-text search. Instead of indexing one value per row like a B-tree, GIN indexes every individual element and remembers which rows contain it, which is exactly what containment queries (@> on JSONB or arrays) and text search need.

Indexing JSONB and full-text search columns

SQL

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));

GiST — Generalized Search Tree

GiST is less a single index structure and more a framework PostgreSQL extensions plug into — it supports geometric types, range types, and "nearest neighbor" style queries (find the 5 closest points to this one) that a B-tree has no concept of. It is the index type behind the EXCLUDE USING GIST reservation-overlap example from the Constraints page, and behind PostGIS spatial queries.

Indexing a range type for overlap queries

SQL

CREATE INDEX idx_reservations_during ON reservations USING GIST (during);

BRIN — Block Range Index

BRIN takes a completely different approach: instead of indexing individual rows, it summarizes ranges of physically adjacent table blocks — recording, say, the minimum and maximum value seen in each range. That makes it dramatically smaller than a B-tree, at the cost of precision — it can only narrow a search down to "somewhere in this block range," not pinpoint a row directly. It pays off specifically on very large tables where the indexed column's values correlate with physical row order, the way an append-only log's created_at timestamp naturally does.

A BRIN index on a naturally-ordered timestamp column

SQL

CREATE INDEX idx_events_created_at_brin ON events USING BRIN (created_at);

This breadth is a genuine PostgreSQL strength

Many databases offer little beyond a B-tree and maybe a hash index. PostgreSQL's range of purpose-built index types — GIN for semi-structured and text data, GiST for geometric and range data, BRIN for huge, naturally ordered tables — is one of its most distinctive strengths, and picking the right one for the shape of your data and your queries can matter far more than any other single tuning decision.

B-tree: the default, good for equality and range queries on ordinary scalar columns.
GIN: containment and search over JSONB, arrays, and full-text lexemes.
GiST: geometric data, range types, exclusion constraints, nearest-neighbor search.
BRIN: tiny footprint on huge tables where the column correlates with physical row order.