PostgreSQL Indexes Explained: What They Are, When to Add Them, and When They Hurt

If slow queries are the disease that brings clients to me, indexes are the medicine most often prescribed — and most often prescribed wrong. I've audited databases with zero indexes on their biggest tables, and databases with forty indexes on a single table, half of them identical, all of them taxing every write. Both extremes are expensive. This is the guide I wish every developer would read before typing CREATE INDEX — how indexes actually work, which types matter in PostgreSQL, and the rules I use in my database optimization work.

What an index actually is (the 30-second version)

An index is a sorted lookup structure kept beside your table — the book-index analogy is genuinely accurate. Without one, finding rows that match a condition means reading the entire table (a sequential scan). With one, the database walks a balanced tree directly to the matching rows. The trade: the index takes disk space, and every insert, update and delete must maintain it. Indexes buy read speed with write cost and storage. Everything else is detail about when that trade is worth it.

The index types that actually matter

B-tree (the default). Handles equality and ranges on sortable data — the workhorse for WHERE user_id = 42, dates, prices, statuses. Probably 90% of the indexes you'll ever create are B-trees, and that's fine.

Composite B-tree (multiple columns). An index on (status, created_at) serves queries filtering by status, or by status and date, or ordering by date within a status — thanks to the leftmost-prefix rule: the columns work left to right, and an index starting with a column your query doesn't filter on is an index your query mostly can't use. Column order is the most common place I see indexes fail: right columns, wrong order, no benefit.

Partial indexes. Index only the rows you actually query: WHERE status = 'pending' on an orders table where 97% of rows are old and completed. Tiny, fast, and brutally effective for "work queue" patterns.

GIN indexes. For searching inside a value — JSONB documents, arrays, full-text search. If you're querying WHERE metadata @> '{"plan": "pro"}' without a GIN index, the database is reading the whole table to peek inside every document.

BRIN indexes. For enormous, naturally ordered append-only tables (logs, events, time series). A BRIN index is minuscule and can turn a hopeless scan of a billion-row table into something workable — niche, but when it fits, it's magic.

The rules I actually use

Index what you filter, join and order by — based on real queries, not imagination. I pull the top slow/frequent queries from pg_stat_statements and let them nominate the indexes. Indexing a column "because it might be searched someday" is how tables end up wearing forty of them.

Foreign keys are not auto-indexed in PostgreSQL. This surprises people coming from other databases. Every orders.user_id that you join or filter on needs its index created deliberately — missing foreign-key indexes are the single most common finding in my audits, and the cause of the classic symptom: fast for one user, dead when you delete or update a parent row with children to check.

Selectivity decides. An index on a boolean-ish column with two values (95% one value) rarely helps reads of the dominant value — the database still has to visit most of the table. A composite or partial index usually expresses the real question better.

Every index is a write tax — audit the unused ones. pg_stat_user_indexes tells you which indexes are never scanned. Dead indexes still get maintained on every write and still occupy cache. Removing them is free performance, but verify on production-like traffic first: the index used once a month by the finance report is not dead, it's sleeping.

Measure with EXPLAIN ANALYZE, before and after. An index you didn't verify is a rumor. The plan should show the index being used, row counts collapsing, and time dropping — the same discipline I apply in finding and fixing slow queries.

A worked miniature

Real pattern from a real audit (details changed): an orders list page filtered by status and ordered by created_at, on a table of ~2 million rows. Plan: sequential scan, sort, 1,900ms. The table already had an index — on (created_at) alone, which the planner rightly ignored for this shape. One composite index on (status, created_at): the query became an index scan returning rows already in order. 11ms. No code changed, no server upgraded — the index just finally matched the question being asked.

That's the whole lesson, really. Indexes aren't decoration or insurance; they're answers shaped to specific questions. Shape them to the questions your application actually asks, prove each one earns its keep, and delete the ones that don't.

If you'd rather have this done with the measurements handled for you, that's exactly what my database architecture & optimization service is for — send me your slowest page and I'll tell you what's behind it.