π ±οΈ B-tree default
Equality, range, sorting, uniqueness β the workhorse. Use unless you have a concrete reason not to. Supports multicolumn, INCLUDE, and UNIQUE.
An index is a bet: you pay storage and slower writes on every row, wagering that reads will be so much faster it's worth it. Win the bet by matching the index shape to the query shape β the right type, the right columns in the right order, and no more indexes than you can justify. This is how to choose.
The default index β and 95% of what you'll create β is a B-tree: a balanced, sorted tree. Walking from the root to a leaf takes a handful of page reads regardless of table size, and each leaf entry points at the row's physical location (a TID) in the heap.
Two consequences fall straight out of "sorted tree":
=, IN), ranges (<, BETWEEN), and ordered reads (ORDER BY, prefix LIKE 'abc%') β anything that maps to "jump to a point and walk in order".Postgres ships six access methods. Reach for B-tree by default; the others solve specific shapes B-tree can't.
Equality, range, sorting, uniqueness β the workhorse. Use unless you have a concrete reason not to. Supports multicolumn, INCLUDE, and UNIQUE.
Only =. Crash-safe & replicated since PG 10. Can be smaller than B-tree for very large keys, but no ranges or ordering β so it's rarely worth choosing over a B-tree. A niche tool.
Extensible tree for overlapping data: geometric & geographic (PostGIS), range types, full-text, and nearest-neighbour (ORDER BY location <-> point). Also backs EXCLUDE constraints.
Space-partitioned trees (quadtrees, radix tries) for non-balanced, naturally clustered data β IP ranges, phone numbers, text sharing long prefixes.
Inverted index for composite values you search inside: jsonb, arrays, full-text tsvector, and β via pg_trgm β substring LIKE '%term%'. Fast lookups, slower/heavier writes.
Tiny "block-range" index storing min/max per block range. Ideal for append-only, physically-sorted data (time-series, sequential IDs) where a full B-tree would be enormous. Cheap to maintain, lossy but effective.
A multicolumn index is not "an index on each column" β it's one sorted structure ordered by the columns left to right, like a phone book sorted by (last name, first name). That ordering dictates which queries it can serve.
An index on (a, b, c) can be used for predicates on a leading prefix: a; a, b; a, b, c. It cannot efficiently serve b alone or c alone β the same way a phone book sorted by last name is useless if you only know the first name.
| Query predicate | Index (a, b, c)? |
|---|---|
WHERE a = ? | Yes leading prefix |
WHERE a = ? AND b = ? | Yes uses a and b |
WHERE a = ? AND c = ? | Partly a to seek, c filtered afterward |
WHERE b = ? (alone) | No b isn't a leading column |
Put columns used with = first, and the column used for a range or ORDER BY last. Equality columns narrow the index to a single contiguous slice; a range column can only be the last one you scan usefully.
-- Query: WHERE customer_id = ? AND status = ? ORDER BY created_at DESC
-- β range/sort column in the middle wastes the index
CREATE INDEX ON orders (customer_id, created_at, status);
-- β equality columns first, sort column last (in sort direction)
CREATE INDEX ON orders (customer_id, status, created_at DESC);
(β¦, created_at DESC) lets Postgres read the first 20 entries and stop for ORDER BY created_at DESC LIMIT 20 β no separate sort node. See the worked 4.2 s β 0.3 ms example in the performance guide for exactly this in a plan.Normally an index finds the row then fetches it from the heap. If the index already contains every column the query needs, Postgres skips the heap entirely β an Index Only Scan, often several times faster. Add non-filtered payload columns with INCLUDE so they ride along in the leaf without becoming part of the sort key.
-- Query: SELECT total FROM orders WHERE customer_id = ? AND status = ?
CREATE INDEX idx_orders_cust_status
ON orders (customer_id, status) -- key columns (the WHERE)
INCLUDE (total); -- payload, answered without the heap
A partial index indexes only the rows matching a WHERE clause. Smaller index, cheaper writes, and it turns a useless low-cardinality column into a sharp tool β because you index only the interesting slice.
-- 99% of rows are done; you only ever query the pending ones:
CREATE INDEX idx_jobs_pending
ON jobs (created_at)
WHERE status = 'pending';
-- Enforce uniqueness on a subset (one active email per user):
CREATE UNIQUE INDEX ON users (email)
WHERE deleted_at IS NULL;
WHERE status = 'pending' matches the first index above; WHERE status = $1 with a parameter may not, because the value isn't known at plan time. Keep the index predicate matching a literal your queries actually use.When your filter wraps a column in a function, a plain index on the column can't be used. Index the expression instead so the computed value is what's stored and searched.
-- Case-insensitive lookup β index the lowercased value:
CREATE INDEX ON users (lower(email));
-- now this uses the index:
SELECT * FROM users WHERE lower(email) = 'a@b.com';
-- Extract a JSON field, or index a derived value:
CREATE INDEX ON events ((payload ->> 'user_id'));
lower(email) in the index won't help email = 'A@B.com'. And every write recomputes the expression to update the index β cheap for lower(), not free for heavy functions.A plain CREATE INDEX takes a lock that blocks writes to the table for the whole build β fine on a 10k-row table, an outage on a 500M-row one. In production, build concurrently.
-- Build without blocking writes (two passes, slower, worth it):
CREATE INDEX CONCURRENTLY idx_orders_cust
ON orders (customer_id);
-- Rebuild a bloated index online (PG 12+):
REINDEX INDEX CONCURRENTLY idx_orders_cust;
CONCURRENTLY: it can't run inside a transaction block, it does two table scans so it's slower, and if it fails it leaves an INVALID index behind that you must DROP and recreate. Check for leftovers: SELECT * FROM pg_index WHERE NOT indisvalid;Indexes rot: workloads change, someone adds a broad index that shadows a narrow one, and unused indexes quietly tax every write. Two questions to ask regularly β what's unused? and what's redundant?
-- Unused indexes: zero scans since stats were last reset
SELECT relname, indexrelname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
Redundant indexes: an index on (a) is largely covered by (a, b) for equality-prefix lookups, so the narrow one is often droppable β but the narrower index is smaller and faster for pure a lookups, so weigh it, don't reflexively delete. Duplicate indexes (same columns, same order) are always safe to remove.
| Anti-pattern | Why it hurts | Instead |
|---|---|---|
| Index every column "just in case" | Each index slows every write and bloats disk/cache | Index for observed queries; measure with pg_stat_statements |
| Duplicate / redundant indexes | Pure overhead, no new plans | Consolidate; drop the shadowed one |
| Single-column index on low cardinality | Planner ignores it; prefers Seq Scan | Composite, or a partial index on the hot slice |
| Unindexed foreign keys | Slow joins & cascade deletes; lock contention | Index the referencing column yourself (not automatic) |
| Function on the column in the filter | Non-SARGable β the plain index can't be used | Expression index, or rewrite the predicate |
INSERT/UPDATE/DELETE (an UPDATE that changes an indexed column can't use the cheap HOT path β see HOT updates). The best index is the one that turns a slow query fast and that you can point at a query in production. If you can't name the query, don't create the index.An orders table serves three real queries. The goal isn't "an index for each" β it's the fewest indexes that make all three fast.
-- Q1: a customer's recent orders in a state
WHERE customer_id = ? AND status = ? ORDER BY created_at DESC;
-- Q2: the pending-orders worker poll (tiny hot slice of a huge table)
WHERE status = 'pending' ORDER BY created_at;
-- Q3: case-insensitive lookup by email
WHERE lower(email) = ?;
Equality columns first (customer_id, status), sort column last in its direction. This one index satisfies the filter and the ordering.
CREATE INDEX CONCURRENTLY idx_orders_cust_status_created
ON orders (customer_id, status, created_at DESC);
status = 'pending' is a fraction of a percent of rows. A partial index stays small and hot no matter how big the table grows β far better than a full index on status.
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';
CREATE INDEX CONCURRENTLY idx_orders_lower_email
ON orders (lower(email));
| Tempting index | Why we skipped it |
|---|---|
(customer_id) | Redundant β the Q1 composite already serves customer_id = ? as its leading prefix. |
(status) | Low cardinality; the partial index handles the only status we query hot. |
(created_at) alone | No query filters by created_at without also fixing customer_id or status. |
CONCURRENTLY, and there's nothing for an audit to flag as unused. That's the whole strategy: index deliberately, then verify the planner uses what you built with EXPLAIN ANALYZE.| Question | Short answer |
|---|---|
| Default index type? | B-tree β equality, range, sorting, uniqueness. |
| Substring / JSON / array / full-text? | GIN (with pg_trgm for LIKE '%x%'). |
| Geo or nearest-neighbour? | GiST. Billion-row time-series? BRIN. |
| Multicolumn column order? | Equality columns first, range/sort column last. |
Which queries can use (a,b,c)? | Any leading prefix: a, a+b, a+b+c β not b or c alone. |
| Skip the heap fetch? | Covering index with INCLUDE β Index Only Scan (needs VACUUM). |
| Low-cardinality hot slice? | Partial index with a WHERE clause. |
| Function in the filter? | Expression index on the exact expression. |
| Build in production? | CREATE INDEX CONCURRENTLY β no write blocking. |
| Find dead weight? | pg_stat_user_indexes where idx_scan = 0. |
Six questions. Pick an answer for each, then check your score.
Verify it worked β read EXPLAIN ANALYZE and confirm the planner picks your index.
Keep indexes healthy β index-only scans, index bloat & REINDEX.
The app side β pooling, N+1, and the write cost of indexes.
The hub β LLMs, agents, data & infra.