Index types

Postgres ships with several index access methods. Picking the right one for your access pattern can be the difference between a 1ms and a 1s query.

B-tree — the default

CREATE INDEX idx_users_email ON users (email);

• Balanced tree. Supports =, <, <=, >, >=, BETWEEN, IN, IS NULL.
• Used for range queries, equality, ordering (an index on created_at DESC lets ORDER BY created_at DESC LIMIT 10 skip the sort).
• Multi-column: (a, b, c) works for queries on a, (a, b), (a, b, c) — not b alone (left-prefix rule).

When in doubt, B-tree.

Hash — equality-only

CREATE INDEX idx_session_token ON sessions USING HASH (token);

• Equality only. No range queries, no ordering.
• Smaller than B-tree for the same data — fewer disk reads.
• WAL-logged and crash-safe since PG 10. Before that, basically a footgun.
• Rarely worth it. B-tree on the same column is usually fine, and supports more.

GIN — Generalized Inverted iNdex

-- Array containment
CREATE INDEX idx_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['python'];

-- JSONB containment / path queries
CREATE INDEX idx_metadata ON events USING GIN (metadata);
SELECT * FROM events WHERE metadata @> '{"user_id": 42}';
SELECT * FROM events WHERE metadata -> 'tags' ? 'urgent';

-- Full-text search
CREATE INDEX idx_search ON articles USING GIN (to_tsvector('english', body));
SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('foo & bar');

-- Trigram for ILIKE
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);  -- needs pg_trgm
SELECT * FROM users WHERE name ILIKE '%alice%';

GIN indexes one entry per value-inside-document. Slow to update (every insert touches multiple index entries), fast to search.

GiST — Generalized Search Tree

-- Geometry / geography (PostGIS)
CREATE INDEX idx_location ON places USING GIST (geom);

-- Range types
CREATE INDEX idx_period ON bookings USING GIST (period);  -- tstzrange
SELECT * FROM bookings WHERE period && tstzrange('2026-01-01', '2026-02-01');

-- Exclusion constraints — "no two bookings overlap for the same room"
ALTER TABLE bookings ADD EXCLUDE USING GIST (
    room_id WITH =,
    period WITH &&
);

Use for spatial, range, and “overlapping” predicates.

BRIN — Block Range INdex

CREATE INDEX idx_events_time ON events USING BRIN (created_at);

• Stores a min/max summary per range of disk blocks.
• Tiny — kilobytes for a multi-GB table.
• Only useful when data is physically clustered by the indexed column (append-only logs, time-series).
• Useless for randomly-ordered data — the min/max ranges become indistinguishable.

The classic BRIN win: events table appended-only by created_at. A BRIN index over created_at is ~1000× smaller than a B-tree and queries like “events from 2026-01-15” only touch a few block ranges.

Partial indexes

Index a subset of rows.

-- Only active users
CREATE INDEX idx_active_email ON users (email) WHERE active = true;

-- Only unprocessed jobs
CREATE INDEX idx_pending_jobs ON jobs (created_at) WHERE status = 'pending';

Wins:

• Smaller — fewer index pages, more fits in cache.
• Faster updates — most rows don’t touch the index at all.

The query optimizer must be able to prove the WHERE matches the index condition — exact match required. WHERE active = TRUE matches; WHERE active != FALSE does not.

Covering / INCLUDE indexes

CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (name, created_at);

The included columns aren’t part of the index key — they ride along on each index leaf. Lets the query be served by an index-only scan without touching the heap.

SELECT email, name, created_at FROM users WHERE email = 'a@b.com';
-- Plan: Index Only Scan using idx_users_email_covering

Trade-off: bigger index, slower writes.

Expression indexes

-- Index on lower(email) for case-insensitive lookup
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

-- Index on a JSON path
CREATE INDEX idx_metadata_user_id ON events ((metadata->>'user_id'));
SELECT * FROM events WHERE metadata->>'user_id' = '42';

The query must use the exact same expression for the index to be usable.

Cheat sheet

Index cost

Every index slows writes (INSERT, UPDATE on indexed columns, DELETE). Rule of thumb: 5–10% per index. Don’t reflexively index every column — index only what your queries actually filter, join, or order by.

Interview angle

• Q: “What index types does Postgres support and when do you use each?”
• Q: “Difference between GIN and GiST?” — GIN: fast read, slow update, exact-set semantics; GiST: tree-based, supports nearest-neighbor, geometric.
• Follow-up: “How do you index for WHERE LOWER(email) = ??” — expression index on LOWER(email).
• Follow-up: “When would BRIN beat B-tree?” — large append-only tables clustered on the indexed column (logs, time series).

See 02_indexes.md for index basics, 10_explain_analyze.md for verifying the planner uses your index.