Why do indexes slow down write operations?

Because when a row is inserted, updated, or deleted, the database has to change not just the table's data but every related index too - and an index is a separate structure that also needs to be kept up to date.

Let's break down the mechanics at the B-tree index level:

#1. INSERT

When a new row appears, the DBMS has to:

1. write the row into the table (the main action)
2. insert a key into the B-tree index, finding the right leaf and inserting the value there
3. rebalance the tree if needed (splitting a node, rebuilding references)

So one write operation turns into several extra operations on the index structure.

#2. UPDATE

If a column that's part of an index gets updated, the database effectively does:

• a delete key from the index
• an insert key back into the index

That's why UPDATE on indexed columns is one of the most expensive operations.

#3. DELETE

Deleting a row means not only removing the data, but also removing the reference from the index, which also means walking the tree and modifying a leaf.

#4. Why this slowdown is noticeable

A B-tree is stored as pages on disk/in memory. Maintaining an index requires:

• extra I/O operations
• locking the index's pages
• node-balancing operations

The more indexes a table has, the longer any write takes, because every data operation means modifying every index.

#Summary

Indexes speed up reads but slow down writes, because every INSERT/UPDATE/DELETE requires updating the B-tree, finding the right spot in the index structure, and possibly rebalancing it. That's why an excessive number of indexes is a direct hit to write performance.