A page that had loaded in under 200ms for a year started taking nine seconds. Nothing had been deployed. No configuration had changed. The only thing that was different was that the events table had crossed about four million rows.
The query filtered on a column with no index. For a year that meant scanning a small table, which is fast enough that nobody notices. Then the table got big and the same query became a full scan of four million rows on every page load.
This is the single most common performance failure in backend work, and it has the most predictable shape: fine, fine, fine, then suddenly not fine. Understanding indexes properly is the highest-return database knowledge there is.
What an Index Actually Is
The book analogy gets used constantly and it is genuinely accurate, so let me use it precisely.
A table with no index is a book with no index at the back. To find every mention of "idempotency" you read every page. That is a sequential scan, and its cost grows linearly with the size of the book.
An index is the sorted list at the back: terms in alphabetical order, each with page numbers. You binary-search to the term and jump straight to the pages. The cost grows logarithmically — doubling the book adds roughly one extra step, not double the work.
That difference is why the failure is so sudden. At 10,000 rows a scan costs almost nothing. At 4 million it costs everything. The query did not change; the constant it was hiding behind did.
Under the hood it is a B-tree: a balanced structure holding the indexed values in sorted order, each pointing at the row's location. Sorted order is what makes range queries and ORDER BY fast too, not just equality lookups.
Read the Plan, Do Not Guess
Before adding anything, find out what the database is actually doing. Guessing at indexes is how you end up with twelve of them and slow writes.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE tenant_id = 'a1b2' AND created_at > now() - interval '7 days';
What you are looking for:
Seq Scan on a large table — reading everything. On a big table in a frequent query, that is your answer.
Index Scan — using an index, then fetching matching rows. Normal and good.
Index Only Scan — answered entirely from the index without touching the table. The fastest case, and something you can deliberately engineer.
Estimated versus actual rows — if the planner expected 50 rows and got 500,000, its statistics are stale and it may have chosen badly. ANALYZE the table.
Use ANALYZE (which runs the query) rather than plain EXPLAIN (which only estimates). The difference between the estimate and reality is often the whole story.
Composite Indexes and the Left-to-Right Rule
This is the part that trips up most people, and it explains why an index you added did not get used.
An index on (tenant_id, created_at, status) is a single sorted structure ordered by those columns in that order. It works like a phone book sorted by surname, then first name.
So it can serve:
WHERE tenant_id = ?— yesWHERE tenant_id = ? AND created_at > ?— yesWHERE tenant_id = ? AND created_at > ? AND status = ?— yes
But not:
WHERE created_at > ?alone — you cannot find everyone born in June by scanning a phone book sorted by surnameWHERE status = ?alone — same reason
The ordering rule I use: equality columns first, then the range column, then anything you only want to select. A range condition stops the index being useful for columns after it, so created_at goes near the end.
-- Query: WHERE tenant_id = ? AND status = ? AND created_at > ? ORDER BY created_at DESC
CREATE INDEX idx_events_tenant_status_time
ON events (tenant_id, status, created_at DESC);
Matching the index's sort direction to your ORDER BY lets the database skip sorting entirely, which on a large result set is a bigger win than the lookup.
Why Your Index Is Being Ignored
You added the index and the plan still shows a sequential scan. The usual causes, in the order I check them:
A function on the column. WHERE lower(email) = 'x' cannot use an index on email, because the index stores the original values. Either index the expression — CREATE INDEX ON users (lower(email)) — or stop transforming the column in the predicate. Same problem with WHERE date(created_at) = '2026-09-13'; use a range on the raw column instead.
A leading wildcard. LIKE '%term' cannot use a B-tree. LIKE 'term%' can. For genuine substring search you want a trigram index or full-text search.
Type mismatch. Comparing a bigint column to a string literal forces a cast and drops the index.
Low selectivity. If the condition matches 40% of the table, a scan really is cheaper than an index lookup plus that many row fetches. The planner is right and the index is the wrong fix.
Stale statistics. After a large bulk load, run ANALYZE.
Indexes You Should Use More
Partial indexes. If you constantly query only pending jobs, index only those rows. Dramatically smaller, faster to maintain, and it does not carry the 99% you never search.
CREATE INDEX idx_jobs_pending ON jobs (run_after)
WHERE status = 'pending';
Covering indexes. Add the columns you select so the query never touches the table at all — that is the Index Only Scan above.
CREATE INDEX idx_orders_lookup ON orders (tenant_id, created_at DESC)
INCLUDE (status, total_cents);
Unique indexes for correctness, not just speed. A unique constraint is the only reliable way to prevent duplicates under concurrency — application-level checks lose that race.
The Cost Nobody Budgets For
Indexes are not free, and the failure mode from too many is quieter than from too few.
Every index must be updated on every insert, update and delete touching its columns. A table with twelve indexes does thirteen writes per insert. They also consume storage and memory, competing with your data for cache.
So audit periodically. Postgres tracks how often each index is read:
SELECT relname AS table, indexrelname AS index,
idx_scan AS times_used, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 50
ORDER BY pg_relation_size(indexrelid) DESC;
An index that has been read zero times since the last stats reset is pure cost. Drop it.
One operational note: on a large live table, always use CREATE INDEX CONCURRENTLY. The plain form takes a lock that blocks writes for the duration, which on a big table means an outage you scheduled yourself.
The Habit Worth Building
Every time you write a query with a WHERE, JOIN or ORDER BY, ask which index serves it. Not later — while you are writing it.
And test with realistic data volumes. Our nine-second page was fine in staging forever, because staging had eight thousand rows. The bug was written a year before it appeared, and it would have been visible in ten seconds with a realistic table and one EXPLAIN.
