databases
Indexes — the promise and the cost
9 min read
Fully authored
Indexes turn table scans into lookups; every index taxes writes.
An index is a separate data structure that lets you find rows without scanning the whole table. It turns a linear scan into a log-time lookup. But every index taxes writes and costs storage. Choose wisely.
Speed comparison — with vs without index
Sequential scan
1.0s
Indexed lookup
10.0ms
The 4 index types
B-tree (default): ordered, supports range queries, equality lookups, sorts. 99% of your indexes.
Hash: only equality, O(1) but no ranges. Rare in Postgres; common in DynamoDB.
GIN (inverted): for JSONB, arrays, full-text. Slow to build, fast to query.
BRIN (block-range): for time-series with natural correlation. Tiny (0.1% of table size).
The write penalty every senior knows
Every INSERT/UPDATE writes to the table AND every index on affected columns. 5 indexes = 6x the write amplification. This is why: (1) drop unused indexes, (2) build indexes CONCURRENTLY, (3) don't over-index write-heavy tables. Postgres pg_stat_user_indexes shows unused ones.
The 3 index patterns to know
Covering index (include cols):
CREATE INDEX ON users(email) INCLUDE (name) — query gets everything from index, no table lookup. 2-10x faster.Composite index (multi-column):
CREATE INDEX ON orders(user_id, created_at DESC) — must be prefix-usable. Reversed order changes performance.Partial index (WHERE):
CREATE INDEX ON users(email) WHERE active — only index active rows. Smaller, faster, updates less.Practice what you just read
Every foundation concept has a companion quiz to close the loop.