PostgreSQL Indexing: A Practical Guide

A query that ran fine with 1,000 rows in dev takes eight seconds in production with 2 million rows, and the fix is usually one line: an index that was never added because nobody noticed the table grow. PostgreSQL indexing isn't complicated once you know what to look for — the hard part is knowing which queries actually need one and which index type fits the job.

Why Missing Indexes Are So Easy to Miss

Postgres will happily run a full table scan on a query with no index — it just gets slower as the table grows, linearly at first and then painfully once the table stops fitting in memory. Nothing errors. Nothing warns you. The query just quietly gets worse every month until someone notices the dashboard is slow, and by then it's not obvious which of a dozen queries is the culprit.

Finding the Queries That Actually Need an Index

Don't guess — measure. EXPLAIN ANALYZE shows you exactly what Postgres is doing:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 4821
AND status = 'pending';

Look for Seq Scan in the output — that means a full table scan, and on a large table it's almost always your problem. Compare the actual time value against what you'd expect; a "Seq Scan" taking hundreds of milliseconds on a filtered query is a strong signal an index is missing. pg_stat_statements (a built-in extension) is even better for finding candidates at scale — it tracks every query's total and average execution time across your whole database, so you can sort by total time and find your worst offenders instead of guessing which query to check.

Adding the Right Index for the Query

The simplest case: a B-tree index on the column(s) you filter or join on.

CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);

Column order in a composite index matters — put the column used in equality filters first (customer_id = 4821) and range/less-selective filters after. An index on (customer_id, status) speeds up queries filtering on customer_id alone or on both columns together, but does nothing for a query filtering on status alone.

For different query patterns, different index types apply:

B-tree (the default) — equality and range queries, the vast majority of cases.
GIN — full-text search, JSONB containment queries (@>), array columns.
GiST — geometric data, range types, nearest-neighbor searches.
Partial indexes — index only the rows you actually query, e.g. CREATE INDEX ON orders (customer_id) WHERE status = 'pending' when 95% of queries only care about pending orders. Smaller index, faster writes, faster lookups for that specific case.

The Cost Side: Indexes Aren't Free

Every index speeds up reads but slows down writes — Postgres has to update every index on a table for every INSERT, UPDATE, or DELETE. A table with eight indexes on it pays that cost eight times per write. This is why blindly indexing every column is the wrong instinct: index the columns your slow queries actually filter or join on, verified with EXPLAIN ANALYZE, not every column that seems like it might someday be useful.

Indexes also take disk space, sometimes more than the table itself for a wide composite index on a large table — worth checking with \di+ in psql before assuming an index is free.

Common Mistakes Teams Make

Indexing everything defensively. More indexes means slower writes and more disk usage — index based on measured query patterns, not guesses.
Wrong column order in composite indexes. (status, customer_id) and (customer_id, status) are not interchangeable — the leftmost column needs to match your most selective, most common filter.
Forgetting to index foreign keys. Postgres does NOT automatically index foreign key columns, unlike some other databases — a join on an unindexed foreign key is a common and easily missed slow-query source.
Never checking if an index is actually used. pg_stat_user_indexes shows scan counts per index — an index with zero scans months after creation is dead weight, just slowing down writes for no benefit.

Best Practices

Run EXPLAIN ANALYZE on your genuinely slow queries before adding anything — never index speculatively. Use CREATE INDEX CONCURRENTLY in production to avoid locking the table during index creation, since a regular CREATE INDEX takes a lock that blocks writes for the duration. And periodically audit unused indexes with pg_stat_user_indexes — a database that's accumulated indexes over two years of feature work usually has several nobody needs anymore.

Frequently Asked Questions

How do I know if an index is actually being used? Query pg_stat_user_indexes and check idx_scan — zero or near-zero scans over a meaningful time window means the index isn't earning its write-performance cost.

Does adding an index lock my table? A plain CREATE INDEX takes a lock that blocks writes for its duration, which can matter on a large table. CREATE INDEX CONCURRENTLY avoids this, at the cost of taking longer to build and needing a bit more care around failure handling.

Should I index every foreign key? Usually yes, if you actually join or filter on it — Postgres won't auto-index foreign keys the way some databases do, and an unindexed foreign key join is one of the most common accidental slow-query sources.

Key Takeaways

PostgreSQL indexing comes down to measuring before acting: use EXPLAIN ANALYZE and pg_stat_statements to find your genuinely slow queries, match the index type to the actual query pattern, and remember every index has a real write-performance cost — so index deliberately based on evidence, not defensively based on guesswork, and periodically check pg_stat_user_indexes to remove ones that never got used.