Databases · intermediate
Database index
A database index is an auxiliary data structure that orders or organizes selected values so qualifying rows can be located without scanning the entire table.
Why it matters
The right index can change a query from reading most rows to reading a small targeted range, while every index also consumes storage and adds maintenance work to writes.
Mental model
How to reason about database index
The table stores records; the index stores searchable keys plus row locators or included data. The query planner chooses it only when the estimated access path is cheaper than alternatives.
Analogy
A book's topic index points from selected terms to pages. It speeds those lookups but takes space and must be updated whenever pagination or indexed topics change.
Examples
See the boundary, not just the happy path
Worked example · Selective lookup
CREATE INDEX ON orders (customer_id);Queries filtering a small fraction of orders by customer can traverse the index instead of scanning every order.
Worked example · Composite order
INDEX (tenant_id, created_at)This ordering supports a tenant equality condition followed by a created-at range; reversing or omitting the leading key changes usability.
Useful contrast · Planner chooses a scan
SELECT * FROM events WHERE status = 'common'If most rows qualify, random index lookups may cost more than a sequential scan, so ignoring the index can be correct.
Common mistakes
Misconceptions to remove early
Indexing every column
Each index increases storage, cache pressure, and insert, update, and delete work. Add indexes for demonstrated access paths and measure their effect.
Ignoring column order in a composite index
A multi-column index is not an unordered bag of fields. Its leading-key order determines which predicates and sort orders it can efficiently serve.
Quick check
Can you predict the result?
1. Why might a database ignore a valid index?
- • The planner estimates that another path, such as a sequential scan, will cost less
- • Indexes are used only for INSERT
- • An index can never support equality predicates
2. What is the main write-side cost of an index?
Keep building