Index Maintenance

Every write has to update every relevant index too

Indexes don't just sit passively waiting to be read — they must be actively maintained every time the underlying data changes. This is the concrete, ongoing cost side of the fundamental read/write trade-off introduced back in 20.1.

What happens on a write

Consider students with a clustered index on id, plus non-clustered indexes on email, department, and gpa. Now run:

sql
INSERT INTO students (id, name, department, gpa, email)
VALUES (1000001, 'New Student', 'CS', 3.2, 'new.student@example.com');

This single INSERT isn't just "add one row to the table." The engine must also:

1. Insert the new row into the clustered index structure (in id-sorted position).
2. Insert a new entry into the email non-clustered index (in sorted position, possibly triggering a node split — recall 20.2's B-Tree rebalancing).
3. Insert a new entry into the department non-clustered index.
4. Insert a new entry into the gpa non-clustered index.

That's four structures updated for one row insert. If this table instead had 10 non-clustered indexes, every single insert, update, or delete touching any indexed column would need to update up to 10 separate structures — the write cost scales directly with the number of indexes.

The fundamental trade-off, restated concretely

This is exactly why "just add more indexes" is not a free performance win: each additional index on a table makes every future INSERT/UPDATE/DELETE slightly (or, with many indexes, significantly) slower. A read-heavy table with rare writes can comfortably carry many indexes; a write-heavy table (e.g., a high-volume logging or transaction table) needs to be much more conservative about how many indexes it carries.

Fragmentation and rebuilding

Beyond the per-write cost, indexes can develop fragmentation over time: as rows are inserted, updated, and deleted, B+ Tree nodes split, merge, and can end up with wasted space or a less-than-optimal physical layout on disk (e.g., logically adjacent leaf pages no longer being physically adjacent). This degrades read performance gradually. Database administrators periodically run rebuild or reorganize operations (e.g., REINDEX in PostgreSQL, ALTER INDEX ... REBUILD in SQL Server) to restore an index to a compact, efficient layout.

Edge cases

• Bulk-loading a large amount of data is often faster with indexes temporarily dropped, then rebuilt fresh afterward in one pass — rather than paying the per-row index-update cost thousands/millions of times during the load.
• An index on a column that is updated extremely frequently (like a "last modified" timestamp touched on every write) pays this maintenance cost constantly, even if that column is rarely used in WHERE clauses — a case where the ongoing cost may outweigh any real read benefit.
• Some engines defer or batch certain index-maintenance work (e.g., using a small unindexed "insert buffer" merged periodically) as an internal optimization, but the conceptual cost — every index must eventually reflect every write — still applies.

Interview-style Q&A

Q: Why does adding more indexes to a table slow down writes? A: Because every INSERT/UPDATE/DELETE touching an indexed column must update every one of those index structures, not just the table itself — more indexes means more structures to keep in sync on every write.

Q: What is index fragmentation, and why does it matter? A: Fragmentation is the gradual degradation of an index's physical layout efficiency from ongoing inserts/updates/deletes; it can slow down reads over time and is fixed via periodic rebuild/reorganize operations.

Key takeaway: every index is a recurring, ongoing cost on every relevant write, not just a one-time setup cost — this is the direct, mechanical reason behind the read/write trade-off from 20.1.