When Indexes Hurt: Writes and Selectivity

After three lessons on why indexes help, interviewers flip it: 'Why not just index every column?' A strong candidate explains that indexes have real costs, on writes and in cache and storage, and that some indexes the planner will never even use.

This lesson covers the two big reasons an index can hurt: write amplification and low selectivity.

An index must stay in sync with the table. Every INSERT, every DELETE, and every UPDATE to an indexed column must also update the index structure. This is write amplification: one row change becomes one table write plus one write per affected index.

A table with eight indexes pays roughly nine times the write work of an unindexed one. On write-heavy or high-throughput tables, that is a serious tax.