Why does adding indexes to a table slow down INSERT, UPDATE and DELETE statements, and roughly how does that cost scale as more indexes are added?
answer
- index = redundant sorted copy, kept in sync synchronously
- 1 insert + N indexes = N+1 structures written
- sorted position → random page, not an append
- page splits, WAL volume, cache pressure
- reads use one index; writes pay for all of them
basics
~20 sIndexes are separate ordered structures that must stay consistent with the table, so every write also writes to each affected index — in its own sorted position, on a different page, inside the same transaction and its log. One insert into a table with five indexes is six structures updated, not one.
solid answer
~60 sAn index is a physically separate, ordered copy of some columns plus a pointer back to the row. It is not maintained lazily — it must be correct the instant the transaction commits, or queries reading through it would miss rows. So an `INSERT` into a table with 5 indexes performs 6 modifications: the row itself, plus one entry per index. Each index entry lands at its *sorted* position, which is a different, often random page — so you pay page reads, page dirtying, possible page splits, and extra write-ahead-log volume for all of them. `DELETE` is the same in reverse. `UPDATE` costs index maintenance for each index whose columns changed. Cost grows roughly **linearly in the number of indexes** for the entry-count part, but real-world degradation is often worse than linear because more indexes means more distinct pages touched per transaction, so buffer-cache pressure and log volume rise and the working set stops fitting in memory. That is the tradeoff: every index is a subsidy paid by every writer to make some readers faster.
code
text · 10 linesorders(id PK, customer_id, status, created_at, region, sku)
indexes: PK only -> 1 structure written per insert
+ (customer_id) -> 2
+ (status, created_at) -> 3
+ (region) -> 4
+ (sku) -> 5
each extra index = tree descent + leaf page read + dirty page + WAL records,
at a page location decided by the key value (usually not the page you just touched)go deeper
State that every index is a separate structure that must be updated in the same transaction, so N indexes means N extra writes per row change.
Add why sorted placement causes random page I/O, mention page splits and write-ahead-log volume, and note that updates only touch indexes on changed columns.
Talk about where the curve bends — working set versus RAM — plus contention on hot leaf pages, background cleanup of dead entries, and replication log volume.
Frame indexes as a standing tax with an explicit budget: read benefit accrues per query while write cost accrues per row change, so index count should be governed by the write-path service level of the table.
## What an index physically is A secondary index is an independent on-disk structure — typically a B+Tree — holding the indexed column values in sorted order, each paired with an identifier for the corresponding row (a physical row address, or the primary key). It is a redundant, reorganised copy of part of the table. Redundant data has to be kept in sync, and in a transactional database that sync happens synchronously, inside the same transaction as the write, because the index must never disagree with the table at commit time. If it could lag, a query using the index would return wrong results. ## What one write actually does For an `INSERT` into a table with N secondary indexes: 1. Place the new row in the table structure (a heap page, or the clustered/primary structure). 2. For each of the N indexes: compute the key, descend that index's tree to find the correct leaf page, read that page (from cache or disk), insert the entry in sorted position, and mark the page dirty. 3. Write log records covering all of the above, so the whole set is recoverable and atomic. Step 2 is the expensive part, and the words "in sorted position" are why. Rows are appended to the table in whatever order they arrive, so table writes are often sequential and cache-friendly. Index entries go where their *value* belongs. Inserting a user with a random UUID or a random email touches a leaf page determined by the hash-like spread of that value — essentially a random page in a large tree. With five such indexes, one logical insert dirties five scattered pages plus the table page. A `DELETE` mirrors this: every index entry pointing at the row must be found and removed or marked dead, which means descending every index. An `UPDATE` is the nuanced one: only indexes containing a *changed* column strictly need a key change. But depending on the storage engine, an update that relocates the row can force every index to be touched anyway, because the pointers stored in them must be corrected. ## Secondary costs that add up - **Page splits.** When a leaf page is full and a new entry belongs in the middle of it, the page must split into two, rewriting entries and updating the parent. Random-ordered keys cause splits constantly; monotonically increasing keys mostly append to the rightmost page and split rarely. - **Write-ahead log volume.** Every index modification is logged. More indexes means a bigger log per transaction, which means more log I/O, more to ship to replicas, and longer recovery. - **Buffer cache pressure.** Index pages compete with table pages for memory. Every additional index enlarges the working set, and once the working set exceeds RAM, each write starts requiring a physical read to fetch the target leaf page before it can be modified. This is where the degradation curve bends: performance is fine, fine, fine, then falls off a cliff. - **Lock and latch contention.** Concurrent inserters modifying the same hot leaf page — for example, all appending to the right edge of a timestamp index — serialise on that page. - **Background cleanup.** Dead index entries left by deletes and updates have to be reclaimed by a background process, and more indexes means more of that work. ## How it scales The entry-writing work is linear in the number of indexes: 10 indexes is roughly 10 times the index maintenance of 1. But the *observed* throughput drop is often super-linear once memory limits are crossed, because random page touches multiply and cache hit rate collapses. A useful mental model: `write cost ≈ table write + Σ (index write)`, and each index write is potentially a random read plus a random dirty page plus log. Read benefit, meanwhile, does *not* scale with index count — a query uses one or two indexes, not all of them. So the tenth index typically adds full write cost and near-zero aggregate read benefit unless it serves a real, frequent query. ## The practical takeaway Indexes are not free storage decorations; they are a standing tax on every writer. On read-dominated tables the tax is worth paying generously. On write-heavy tables — ingest pipelines, event logs, queue tables, hot counters — each index needs to justify itself against a named query, and "someone might want to search that someday" is not a justification.
- If indexes cost so much on writes, why is a primary key index almost never questioned?Because it enforces a constraint the data model requires and serves the lookups nearly every other access depends on, including foreign-key checks and row identification in replication. Its key is also usually monotonic or synthetic, so inserts append to the right edge of the tree with few splits, making it one of the cheapest indexes to maintain.
- Why do randomly distributed keys make index maintenance more expensive than sequential ones?A sequential key always belongs at the rightmost leaf, so that page stays in cache and splits are rare and clean. A random key belongs at an unpredictable leaf, so each insert may need a physical read of a cold page, dirties a different page, and can split a page mid-way, rewriting entries and updating the parent. The same number of entries costs far more I/O.
- Does the cost of index maintenance depend on the size of the table?Somewhat. The tree descent is logarithmic, so depth grows slowly, but the real driver is whether the index's hot pages fit in memory. On a small table everything is cached and maintenance is nearly free; on a table far larger than RAM, each index write may require a physical read before the modification, which is orders of magnitude slower.
A library adds a card catalogue by author, by subject, and by publication year. Shelving one new book now means writing three cards and filing each in a different drawer, in the right alphabetical spot.
saying these in an interview costs you the question
- Thinking indexes are updated lazily or by a background process after commit.
- Claiming index maintenance is negligible because trees are logarithmic — ignoring random page I/O and log volume.
- Believing only INSERT pays index cost, and that DELETE is free.
- Assuming more indexes always means faster queries overall, with no write-side consequence.
- Not distinguishing sequential key inserts from random key inserts when discussing cost.