Index write cost
Each index adds work to every write. Here 9 of 10 indexes serve no query, but all 3,150 writes/s update them. Remove the unused indexes and keep the one that reads need.
See it on the board
The same traffic runs on two boards. The simulator measured both when this page was built.
| Measure | Goal | 10 indexes, 1 in use | 1 index in use |
|---|---|---|---|
| p99 latency | under 50 ms | 10.1 s Fail | 26 ms Pass |
| CPU use | under 85% | 120.9% Fail | 64.2% Pass |
| Monthly cost | 8 USD/mo | 8 USD/mo | |
| Error rate | 13.1% | 0% |
Key numbers
| Number | Value |
|---|---|
| Writes per second, one database vCPU | 2,500 writes/s |
| Default index count | 1 indexes |
| Disk operations for one write | 2 IOPS |
Index · 4 pages read per query · disk writes per insert: 2
How it works
An index is a second copy of some columns, kept in sorted order. A read that matches the index can find rows without a scan. This is why indexes are useful. But every write must also update each index. A table with 10 indexes does 10 extra updates for each insert.
In the mini board the database receives 350 reads and 3,150 writes each second. Only one of its ten indexes serves the read query. The other nine serve no query at all, yet each of the 3,150 writes updates all ten. The disk and the CPU spend most of their time on index work that nobody uses. p99 and CPU pass their limits.
Remove the nine unused indexes. The writes become cheaper, and p99 falls below 50 ms. The one useful index stays, so the reads keep their short lookup.
How do you know which index is unused? Use evidence: the database can count how often each index is read. An index with no reads over a full business cycle is a candidate. Check rare jobs, such as month-end reports, before you drop it.
The balance is different for each workload. A read-heavy table can carry many indexes. A write-heavy table should carry few.
In the levels you keep the indexes that queries use and remove the rest, and you check write time and CPU.