Lesson 13 · Indexes

Index trade-offs

Indexes aren't free — the cost, and why a present index sometimes isn't used.

Your win: explain the write cost of indexes, decide when not to add one, and diagnose why an existing index gets ignored — a top interview question.

Indexes cost writes and storage

Every index must be kept in sync: an INSERT, and an UPDATE of an indexed column, must update every affected index too — more work per write, more WAL (Lesson 8), more disk.¹ (The HOT optimisation from Lesson 9 skips index updates when no indexed column changed — one reason our soft-delete deleted_at updates stay cheap.)

Don't index everything Each index speeds some reads but slows all writes to that table. Skip indexes on: tiny tables (a seq scan is already fast), low-selectivity columns (a boolean returns ~half the rows — an index doesn't help), and rarely-queried columns on write-heavy tables.

Why a present index isn't used

Interviewers love this. An index exists but EXPLAIN shows a seq scan — common reasons:²

Reason	Fix
Query returns most of the table → seq scan is cheaper	nothing — the planner is right
Stale statistics → planner misestimates	`ANALYZE` (autovacuum usually)
A function wraps the column: `WHERE lower(email) = …`	an expression index (Lesson 12)
Type mismatch (e.g. `text` col vs `int` literal)	cast correctly / fix the param type
`WHERE` lacks the composite index's leading column	reorder the index or the query

Anchor Your migrations build indexes CONCURRENTLY IF NOT EXISTS (notificationmgmt/1026,1028) — CONCURRENTLY builds without locking writes on a big live table (it's slower and can't run in a transaction, but it doesn't block production). And notice the team indexes deliberately — resource_path, FKs, real search columns — not every column. That restraint is the lesson.

Use The Index, Luke! — "Why isn't the database using my index?" + CREATE INDEX

Winand's diagnosis of ignored indexes; the docs on CONCURRENTLY and index maintenance cost.

→ use-the-index-luke.com
→ postgresql.org/docs — CREATE INDEX

Check yourself (from memory)

Q1. Every extra index makes ___ slower.

Indexes must be maintained on write. They speed some reads but cost every write + storage. Don't over-index.

Q2. An index is a poor choice on a column that is…

Low selectivity (e.g. a boolean) returns most rows, so the planner skips the index. Index selective columns.

Q3. A present index may be ignored if…

WHERE lower(email) = … can't use a plain email index — you'd need an expression index. (Also: low selectivity, stale stats, type mismatch, wrong composite prefix.)

Why not index everything, and name three reasons a present index isn't used?

recall, then click to reveal

Every index is maintained on INSERT and on UPDATE of its columns — extra write latency, more WAL, more storage. So skip tiny tables (seq scan is fine), low-selectivity columns (a boolean returns ~half the table), and rarely-queried columns on write-heavy tables. A present index is IGNORED when: the query isn't selective enough (planner prefers seq scan), stats are stale (ANALYZE), a function wraps the column (needs an expression index), types don't match, or the WHERE omits the composite index's leading column. Build big ones CONCURRENTLY to avoid locking writes.

🎓 That's Part 3 — indexes You can pick an index type, use composite/partial/covering/expression, and reason about cost and non-use. Part 4 makes it measurable: the query planner and reading EXPLAIN — where you'll see an index get used or ignored.

Ready for Part 4 (query performance), or a mixed quiz across Lessons 10–13 first? Ask me.

1. PostgreSQL — CREATE INDEX.

2. Use The Index, Luke!