PostgreSQL Indexing: A Practical Guide
An index is the single highest-leverage tool for fixing a slow query, and also one of the easiest things to misuse. Here's a practical look at when and how to index a PostgreSQL table.
What an Index Actually Does
Without an index, PostgreSQL scans every row in a table to find matches — a sequential scan. An index builds a separate, sorted data structure (by default a B-tree) that lets the database jump straight to matching rows instead of checking all of them.
Creating a Basic Index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
This helps any query that filters or joins on customer_id:
SELECT * FROM orders WHERE customer_id = 42;
Checking Whether It's Actually Used
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
Look for Index Scan in the output. If you see Seq Scan instead, either the index isn't being used, or — for small tables — PostgreSQL has correctly decided a full scan is cheaper than the overhead of using the index.
Composite Indexes and Column Order
An index on (customer_id, status) helps queries filtering on both columns, or just customer_id alone, but not a query filtering on status alone — column order matters:
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
-- Uses the index:
SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid';
SELECT * FROM orders WHERE customer_id = 42;
-- Does NOT use this index efficiently:
SELECT * FROM orders WHERE status = 'paid';
Partial Indexes
If most queries only care about a subset of rows, a partial index keeps the index smaller and faster:
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
When Indexes Hurt
- Write-heavy tables. Every insert, update, or delete has to update every index on that table, so excessive indexing slows writes.
- Low-cardinality columns. Indexing a boolean or a column with only a few distinct values rarely helps — the database still has to read a large share of the table.
- Unused indexes. Check
pg_stat_user_indexesperiodically; indexes nobody queries against are pure overhead.
Summary
Indexes trade write performance and storage for read performance, so they're not something to add reflexively to every column. Start from your actual slow queries, confirm with EXPLAIN ANALYZE that an index would help, and revisit periodically as query patterns change.
