Part 2 of 5 · 1 min read
Every index must be maintained on every write
An insert, update, or delete must update every index on the affected columns, not just the table.
Think of a store that keeps its inventory in a master ledger, plus separate catalogs sorted by price, by category, and by supplier. Every single sale means updating the master ledger and all three catalogs, not just one.
In plain words: when a row is inserted, updated, or deleted, the database does not only change the table's own data. It must also update every index that covers any of the changed columns, keeping each index's own structure, whether a B+-tree, a hash index, or any other kind covered earlier in this level, consistent with the new data. A table with five indexes means a single insert can trigger up to six separate structural updates: one for the table, and one for each index.
A worked example. Inserting a new row into orders, a table with indexes on customer_id, placed_on, and order_id, requires the table's own row insert plus three separate index updates, one per index.
The key lines. No new SQL; index maintenance happens automatically and invisibly inside every INSERT, UPDATE, and DELETE.
Index maintenance
Index maintenance is the automatic work of updating every index covering a changed column whenever a row is inserted, updated, or deleted, keeping each index consistent with the table's current data.
Example: updating a master ledger and three separate catalogs for one single sale is index maintenance across four structures at once.