Indexing

Without an index, every query is a sequential scan — Postgres reads every page of a table, checking every row against your WHERE clause. An index is a separate, ordered structure that lets Postgres jump straight to matching rows instead. Postgres ships with six index types, each suited to a different kind of query.

CREATE INDEX idx_accounts_balance ON accounts (balance);

B-Tree — the default

CREATE INDEX with no type specified builds a B-Tree, and it's the right choice for the large majority of columns: equality, ranges, sorting, and IS NULL all use it.

CREATE INDEX idx_accounts_balance ON accounts (balance);

SELECT * FROM accounts WHERE balance > 100;      -- range, uses the index
SELECT * FROM accounts ORDER BY balance LIMIT 10; -- sort, uses the index

Hash — equality only

A Hash index supports only = comparisons, never ranges or sorting. It's rarely worth choosing over a B-Tree in practice — B-Tree equality lookups are already fast, and Hash indexes were unlogged (not crash-safe) before Postgres 10. Reach for it only when you're certain the column is queried exclusively with = and the index would be large enough for the smaller per-entry size to matter.

GIN — composite values

Generalized Inverted Index. Built for columns that hold multiple values per row — arrays, jsonb, full-text search vectors — where you need to ask "does this row contain X?" A GIN index maps each individual element to the rows containing it:

CREATE INDEX idx_tags_gin ON posts USING GIN (tags);        -- array
CREATE INDEX idx_data_gin ON events USING GIN (payload);    -- jsonb

SELECT * FROM posts WHERE tags @> ARRAY['postgres'];
SELECT * FROM events WHERE payload @> '{"type": "signup"}';

See JSON & Arrays for the operators GIN accelerates on those types.

GiST — geometric and "nearest" queries

Generalized Search Tree. Supports queries with no strict ordering — geometric containment/overlap, nearest-neighbor (ORDER BY point <-> target), and range-type exclusion constraints (e.g. "no two bookings for the same room can overlap").

BRIN — huge, naturally-ordered tables

Block Range Index. Instead of indexing every row, BRIN stores the min/max value per range of pages (128 pages by default). It's tiny — often a few hundred KB even for a billion-row table — and works well when a column correlates with physical insertion order, like a timestamp on an append-only log table:

CREATE INDEX idx_events_ts_brin ON events USING BRIN (created_at);

SP-GiST — space-partitioned data

Space-Partitioned GiST. For data with a natural, non-balanced tree structure — IP address ranges, phone number prefixes, quadtree-style geometric data. Niche, but the right tool when your data actually has that shape.

Making an index more useful

• Partial index — index only the rows you actually query: CREATE INDEX idx_active ON users (email) WHERE active = true; — smaller and faster than indexing every row when most queries filter on active.
• Covering index — add extra columns with INCLUDE so an index-only scan can satisfy a query without touching the heap at all: CREATE INDEX idx_covering ON accounts (id) INCLUDE (balance);
• Expression index — index the result of an expression, not the raw column: CREATE INDEX idx_lower_email ON users (lower(email)); lets a case-insensitive lookup (WHERE lower(email) = 'x@example.com') use an index at all.

Having the right index doesn't guarantee Postgres will use it — that decision belongs to the planner, covered next in Query Planner.