Database Indexing and Performance
Making queries fast on purpose: indexes, query plans and the N+1 hunt.
Before this: Relational Data Modeling
Objective
Diagnose slow queries with query plans instead of guesses, add indexes that match real query patterns, eliminate N+1 access patterns, and pay attention to the write-side cost indexes charge.
Vocabulary
The words to use when directing your agent.
- index
- A lookup structure the database maintains so rows can be found without scanning everything.
- query plan
- The database's own explanation of how it will execute a query - scans, index uses, join orders.
- table scan
- Reading every row to answer a query - the slow path indexes help avoid.
- composite index
- An index over multiple columns, matching queries that filter them together in order.
- N+1 queries
- One query for a list, then one more per item - a hidden multiplication of database round trips.
Understand it
Matching the index to the question
A composite index serves queries filtering columns left-to-right: an index on (user_id, date) powers WHERE user_id = ? AND date > ?, partially serves user_id alone, and does nothing for date alone. Read your plan: if it shows a scan where you expected an index, either the index is missing or the query does not match its shape.
The N+1 pattern
Fetch fifty expenses, then ask the database for each owner's name separately: fifty-one queries where one join would do. ORMs make this easy to create and invisible until traffic grows. Detect it by counting queries per page render; fix it by fetching related data in the same query or an explicit batch.
Core concepts
Measure first, optimize second
'Feels slow' is not a diagnosis. The database will TELL you how it executes a query - ask with EXPLAIN (or your tool's equivalent). Plans turn performance work from superstition into observation: scan became index seek, rows examined dropped. Before and after, plans or it did not happen.
Indexes are a trade, not a gift
Every index speeds specific reads and taxes every write on the table - storage too. The skill is matching few, well-chosen indexes to your actual hot queries, discovered from logs and plans, not carpet-bombing every column on a hunch.
Examples
Plan before and after
The ledger's list-by-month query scans the whole expenses table. EXPLAIN says Seq Scan, rows examined: all of them. Add composite index (user_id, date); the same plan now shows an index range scan examining only matching rows. Same code, different conversation with the database.
Write tax made visible
Insert one thousand synthetic expenses before and after adding an index and compare insert time. Slower now - that is the maintenance fee. Worth paying for hot reads; wasteful for columns nobody filters.
Common mistakes
Common mistake
Adding an index for every column 'to be safe'.
Fix
Index from evidence: hot queries from logs and plans first. Every extra index charges every insert, update and delete.
Common mistake
Claiming a speedup without capturing plans or timings.
Fix
Before/after evidence or it did not happen - plans pasted, timings recorded honestly, including 'no meaningful difference' results.
Working with your AI agent
Standing rule for any performance task: 'Show me the query plan BEFORE changing anything, and again after. Include rows examined.' This keeps agents honest about optimization claims and teaches you the database's vocabulary simultaneously.
Build it
The module’s real project. Your agent builds; you direct and inspect.
Project: Query Clinic
Run a performance clinic on the Ledger: capture plans, add one well-chosen composite index through a migration, eliminate an N+1 from the list endpoint, and document the whole investigation with before/after evidence.
Requirements
- Slowest realistic query identified from actual plans, not intuition - investigation notes included
- Composite index added via up/down migration matching that query's filter shape
- Query plan captured BEFORE and AFTER showing the access path change
- N+1 eliminated from an endpoint listing related records - query count asserted before vs after
- README documents the investigation: problem, plan excerpts, fix, honest timing observations
Context for your agent
Guide a performance clinic on my Expense Ledger. Step 1: seed enough synthetic expenses that full scans visibly matter. Step 2: capture EXPLAIN output for the main list-by-month and summary queries; identify the worst access path together and explain the plan's vocabulary to me. Step 3: I approve one composite index; deliver it as an up/down migration. Step 4: find and fix an N+1 in the expense-list endpoint, asserting query counts. Evidence for every step goes in the README - plans, not adjectives.
Verify it - twice
Check every item yourself first. Then hand your agent the prompt and compare verdicts.
Verification prompt - paste into your AI agent
Audit my Query Clinic as a skeptical database engineer. Run the plans yourself if you can. Verify PASS or FAIL with evidence: (1) the claimed index genuinely serves the targeted query shape - try near-miss queries that should NOT benefit and confirm they do not, (2) the N+1 fix truly reduced round trips - inspect the assertion, (3) migrations include a working rollback, (4) README claims match what the plans show - call out any adjective pretending to be a measurement. Then seed ten times more rows than I did, re-run the target query, and report whether my fix still holds or merely moved the cliff.
Run this against “Database Indexing and Performance” after your own checklist pass. Where the agent and you disagree, investigate before moving on.
Review questions
Answer out loud. If you cannot, revisit before moving on.
Read your before/after plan excerpts aloud and translate each line.
Why does (user_id, date) not help a query filtering only by date?
Where did you SEE the N+1 before fixing it - and what would it have cost at scale?
You can now
- Diagnose query performance from plans instead of folklore
- Choose and migrate indexes matched to real query shapes
- Detect and eliminate N+1 access patterns structurally
Up next
Background Jobs and Queues