an index speeds reads and is paid for on every insert and update; the wrong index costs more than none
Indexes slow writes because every insert, update, or delete must update the index structures as well as the table itself. A single logical write becomes multiple physical writes to disk.
Most relational databases store indexes as B-trees. When you insert a row, the database appends the row to the table's heap (the main data file). Then, for each index on that table, it must navigate the B-tree to find the correct leaf page and insert the new key. If the leaf page is full, the page splits into two, which can ripple upward and rebalance the tree. Each of those steps means reading and writing pages that would otherwise never be touched.
Consider a table with one million rows and a B-tree index with a fanout of 100. The tree has three levels: a root, 100 internal nodes, and 10,000 leaf pages. An insert typically reads the root (usually cached), one internal node, and one leaf page, then writes the leaf page. If a split occurs, it also writes an internal node. So a single indexed insert can write two or three 8 KB pages. Without the index, the insert might write just one 8 KB page to the heap. That is a two- to three-fold increase in write I/O per row, and it grows with the number of indexes.
Many developers assume an index only costs something when it is used. In fact, an index costs something on every write to its table, whether or not any query ever uses it. An insert into a table with five indexes must maintain five separate B-trees. An update that changes an indexed column must delete the old key and insert the new one. Even a delete must remove the key from every index. The read speed you gain is real, but it is paid for in advance by every write.
Another misconception is that more indexes mean faster queries. A single query can typically use one index per table for row filtering. Adding an index on a column you never filter on does nothing for reads, but it still taxes every write. That is the hidden tax the video mentions.
The write penalty is not universal. It applies to traditional B-tree storage engines. In log-structured merge-tree (LSM) databases, writes are first buffered in memory and later merged into sorted runs, so the cost of index maintenance is amortized and less immediate. Columnar databases often compress and store data differently, making per-column indexes less relevant. And if a table is almost never written, the write cost is negligible, so indexing freely is fine.
There are also indexes you cannot avoid. Primary key and unique constraints require an index to enforce uniqueness. Those indexes will slow writes, but they are a necessary part of the data integrity contract. The rule is not "never index"; it is "index what your queries actually need, and measure the write cost."
Cram If an index makes queries faster, is it not a free win to just add more of them?
Rep It is not free. Every index you add comes with a bill you pay on every write, and the wrong one costs more than it helps.
Cram Wait, adding an index makes writes slower? That feels backwards.
Rep An index is a separate lookup structure. When you insert a row or update a value, the database has to update every index that touches it, right then.
Cram So one insert does extra work for each index on the table?
Rep Exactly. Insert one row and the engine also inserts matching entries into every relevant index. Ten indexes means ten updates for that single row.
Cram Smells like a lot of extra bookkeeping.
Rep It is maintained bookkeeping, and it is done on every insert, update, and delete, not once. The write pays for the read speed you use later.
Cram But reads are way more common than writes, are they not?
Rep For many workloads, yes. That is why some indexes earn their cost. But index every column and your write path bulldozes under the extra upkeep.
Cram So what does a good index actually cost to keep?
Rep Disk space, plus the write time of keeping it current. A bad or duplicate index spends all that and never gets used by a query.
Cram And a query that never uses it still pays to keep it updated?
Rep Every write pays to maintain it, even if no read ever touches it. An unused index is pure ongoing cost with no benefit.
Cram Makes sense. So the rule is not simply add an index.
Rep Match the index to the queries you actually run. Enough indexes to serve your reads fast, not so many that every write gets buried in upkeep.