Types of PostgreSQL Indexes

PostgreSQL supports several index implementations, each suited to different data and query patterns.

B-tree (the Default)

CREATE INDEX idx_name ON employees (name);
-- equivalent to:
CREATE INDEX idx_name ON employees USING BTREE (name);

Handles equality (=) and range queries (<, >, BETWEEN) efficiently, plus sorting. It's the right choice for the vast majority of columns — dates, numbers, text, and more.

Hash Index

CREATE INDEX idx_email_hash ON employees USING HASH (email);

Optimized only for equality checks (=) — it can't help with range queries or sorting. B-tree indexes are so efficient at equality checks too that Hash indexes are rarely worth the trade-off in practice.

GIN (Generalized Inverted Index)

Built for columns holding multiple values per row — arrays, JSONB, and full-text search vectors.

CREATE INDEX idx_tags ON articles USING GIN (tags);
CREATE INDEX idx_attributes ON products USING GIN (attributes);       -- JSONB
CREATE INDEX idx_search ON articles USING GIN (to_tsvector('english', body)); -- full-text

GiST (Generalized Search Tree)

Built for data where "closest match" or "overlap" matters more than exact equality — geometric shapes, ranges, and nearest-neighbor searches.

CREATE INDEX idx_location ON stores USING GIST (location);
CREATE INDEX idx_valid_period ON bookings USING GIST (valid_range);

BRIN (Block Range Index)

Extremely small and cheap, designed for huge tables where the data is naturally sorted on disk — like a created_at timestamp on an append-only log table.

CREATE INDEX idx_created_at ON events USING BRIN (created_at);

A BRIN index only stores the min/max value per block of disk pages, so it's tiny compared to a B-tree — but it only helps when the column's values correlate with physical row order.

Choosing the Right Index

Data / Query Pattern	Index Type
General columns, equality + range	B-tree (default)
Only ever equality-checked	Hash (rarely needed over B-tree)
Arrays, JSONB, full-text search	GIN
Geometric data, ranges, "nearest to"	GiST
Huge, naturally-ordered append-only tables	BRIN

Common Errors & Troubleshooting

• Creating a GIN index on a plain scalar column — GIN is for multi-valued data (arrays, JSONB, full-text) — putting it on a regular integer/text column wastes space and gives no benefit a B-tree wouldn't already provide.
• Expecting a Hash index to speed up sorting — Hash indexes only support equality lookups; they can't be used for ORDER BY or range queries at all.
• BRIN index giving poor performance — this happens when the indexed column ISN'T naturally correlated with physical row order (e.g. indexing a randomly-assigned ID with BRIN); BRIN only pays off on genuinely sequential/append-only data like timestamps in a log table.

Key Takeaways

• B-tree is the default and correct choice for the vast majority of indexed columns.
• GIN is what makes array containment, JSONB queries, and full-text search fast.
• BRIN trades some query speed for a dramatically smaller index, and only works well when data is physically sorted on disk already.