Write Amplification: One Row Change Can Mean Nine Index Writes
Every index on a table is maintained synchronously, so inserting one row into a table with eight indexes means nine structures to modify, each with its own page reads, page writes and possible node splits. Deletes and non-HOT updates cost the same way. This is write amplification, and it is why an application that gradually accumulated indexes for reporting can find its inserts have quietly become ten times more expensive than they need to be.
Questions this Concept answers
- Why is index maintenance charged to the transaction doing the write rather than deferred to a background process?
Why Extra Database Indexes Slow Down Writes Through Write Amplification and Buffer Pool Pressure
An index is not free: because each secondary index is a separate ordered structure (typically a B-tree) that must be kept consistent with the table, every insert, update, or delete must be applied to…