Part 4 of 5 · 2 min read
Write amplification from many indexes
More indexes multiply the physical work behind every logical write.
Write amplification from many indexes
Think of a delivery company dropping off one package at a warehouse. That single drop-off still requires separately updating a dozen different tracking spreadsheets, each maintained for a different purpose.
In plain words: write amplification, introduced in the earlier LSM-tree page as a cost of scattered random writes, also applies here in a different form: one logical write, such as a single INSERT, can amplify into several physical writes, one for the table and one for each index covering the inserted columns. A table with many indexes therefore makes every single write noticeably more expensive, even though the original statement looks like one simple operation. This is the central reason indexes are not free, and why adding one should always be a deliberate decision weighed against the table's actual write volume.
A worked example. A table with ten indexes turns one INSERT into eleven physical write operations: one for the table's own row, and ten for maintaining each index.
The key lines. No new SQL; this amplification is a structural consequence of how many indexes exist on the written columns.
Write amplification (index count)
Write amplification from index count is the multiplication of one logical write into several physical writes, one for the table and one for each index covering the changed columns.
Example: one package drop-off triggering updates across a dozen separate tracking spreadsheets is write amplification from maintaining many structures at once.
Remember this
Every index is a standing bet that its read benefit will outweigh its write cost, forever, on every future write. An index added for a report that ran once, years ago, and never ran again, is still losing that bet on every single write today.
Quick notes
- Every index must be updated on every insert, update, or delete that touches its columns.
- A table with many indexes turns one logical write into several physical writes.
- Over-indexing means paying this maintenance cost on indexes that provide little or no read benefit.
- Write amplification from index count grows directly with the number of indexes on a table.
- An index should be added deliberately, weighed against the table's actual query and write patterns.