← All tutorials
PostgreSQL Indexing

PostgreSQL indexing strategies

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 strategy in one line. Index for the queries you actually run, cover the equality predicates first, add a range/sort column last, and delete anything nothing scans. An index the planner never uses is pure write tax.

Foundations  How a B-tree index works

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.

B-tree index structure A root node points to leaf pages of sorted keys, each leaf entry pointing at a row in the heap table. A lookup descends root to leaf in a few page reads. Rootkey ranges β†’ children Leaf page (keys 1–5000)sorted keys + TID pointers β†’ Leaf page (keys 5001–…)sorted keys + TID pointers β†’ πŸ—ƒοΈ Heap β€” the actual table rows (unordered)

Two consequences fall straight out of "sorted tree":

  • A B-tree is fast for equality (=, IN), ranges (<, BETWEEN), and ordered reads (ORDER BY, prefix LIKE 'abc%') β€” anything that maps to "jump to a point and walk in order".
  • After the index finds matching entries it usually still has to visit the heap to fetch the row (an Index Scan). Avoiding that heap trip is what covering indexes below are for.
Cardinality decides everything. An index earns its keep when a query selects a small fraction of the table. Indexing a boolean or a status with three values rarely helps β€” the planner will (correctly) prefer a Seq Scan. Index high-cardinality columns, or make a low-cardinality one useful with a partial index (Β§5).

The catalog  The six index types & when to use each

Postgres ships six access methods. Reach for B-tree by default; the others solve specific shapes B-tree can't.

πŸ…±οΈ B-tree default

Equality, range, sorting, uniqueness β€” the workhorse. Use unless you have a concrete reason not to. Supports multicolumn, INCLUDE, and UNIQUE.

# Hash equality only

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.

🌐 GiST geometry / ranges / KNN

Extensible tree for overlapping data: geometric & geographic (PostGIS), range types, full-text, and nearest-neighbour (ORDER BY location <-> point). Also backs EXCLUDE constraints.

🌲 SP-GiST partitioned data

Space-partitioned trees (quadtrees, radix tries) for non-balanced, naturally clustered data β€” IP ranges, phone numbers, text sharing long prefixes.

πŸ”Ž GIN "many values per row"

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.

🧱 BRIN huge, ordered tables

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.

Quick decision: scalar equality/range β†’ B-tree. Substring / JSON / arrays / full-text β†’ GIN. Geo or nearest-neighbour β†’ GiST. Billion-row time-series filtered by time β†’ BRIN. Everything else β†’ probably B-tree.

The core skill  Multicolumn indexes & column order

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.

The leftmost-prefix rule

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 predicateIndex (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

The ordering rule: equality first, then range/sort

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);
Why the sort direction matters: an index on (…, 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.

Skip the heap  Covering indexes & index-only scans

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
Index-only scans depend on VACUUM. Postgres can skip the heap only for pages the visibility map marks all-visible β€” which VACUUM maintains. On a heavily-updated table that autovacuum can't keep up with, your "index-only" scan quietly degrades to heap fetches. The two topics are linked: see VACUUM & autovacuum.

Index less  Partial indexes

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;
The catch: the planner uses a partial index only when it can prove your query's predicate implies the index's. A query filtering 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.

Index a computation  Expression indexes

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'));
The query must use the exact same expression as the index. 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.

In production  Building indexes safely

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;
Trade-offs of 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;

Prune  Auditing your indexes

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;
Before you drop: check that stats have accumulated over a full workload cycle (a weekly report, month-end job, etc.), and check replicas separately β€” an index unused on the primary may serve read traffic on a standby. Never judge from a five-minute window.

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.

Avoid  Indexing anti-patterns

Anti-patternWhy it hurtsInstead
Index every column "just in case"Each index slows every write and bloats disk/cacheIndex for observed queries; measure with pg_stat_statements
Duplicate / redundant indexesPure overhead, no new plansConsolidate; drop the shadowed one
Single-column index on low cardinalityPlanner ignores it; prefers Seq ScanComposite, or a partial index on the hot slice
Unindexed foreign keysSlow joins & cascade deletes; lock contentionIndex the referencing column yourself (not automatic)
Function on the column in the filterNon-SARGable β€” the plain index can't be usedExpression index, or rewrite the predicate
Writes pay for reads. Every index must be updated on 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.

End to end  Worked example: designing a minimal index set

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) = ?;

Step 1 Β· One composite index for Q1

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);

Step 2 Β· A tiny partial index for Q2

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';

Step 3 Β· An expression index for Q3

CREATE INDEX CONCURRENTLY idx_orders_lower_email
  ON orders (lower(email));

Step 4 Β· What we did not create

Tempting indexWhy 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) aloneNo query filters by created_at without also fixing customer_id or status.
Result: three queries, three purpose-built indexes, zero redundant ones. Each traces to a real query, each was built 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.

Cheat sheet

QuestionShort 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.

Knowledge check

Six questions. Pick an answer for each, then check your score.

1. You have an index on (customer_id, status, created_at). Which query cannot use it efficiently?

2. For a query WHERE a = ? AND b = ? ORDER BY c, the best column order is…

3. What enables an Index Only Scan (no heap fetch)?

4. Which index type suits substring search WHERE name LIKE '%smith%'?

5. Why build a large index with CREATE INDEX CONCURRENTLY?

6. An index shows idx_scan = 0 in pg_stat_user_indexes after a month. It is…

Next steps

🐘 Query performance

Verify it worked β€” read EXPLAIN ANALYZE and confirm the planner picks your index.

🧹 VACUUM & autovacuum

Keep indexes healthy β€” index-only scans, index bloat & REINDEX.

🧡 Concurrency design

The app side β€” pooling, N+1, and the write cost of indexes.

πŸ““ All tutorials

The hub β€” LLMs, agents, data & infra.