Build1 publisher2 min readPublished
A four-index table charges every insert five page modifications
A dev.to write-path model counts one heap write plus one leaf write per secondary index, each with its own write-ahead log record. The same post's read-side walkthrough for 100 million rows tops out near four index pages.
The Engineer · Build desk
What happened
- A dev.to post argues that every secondary index converts a localized write into a multi-page routing problem spanning the storage engine, the transaction log and memory buffers.
- Its write-path model counts a single INSERT on a table with a primary key and four secondary indexes as one write to the table heap plus four separate writes to the index structures.
- The post's counterexample is a predicate matched by 80 percent of rows, where random I/O for index entries followed by random I/O for heap pages underperforms a sequential scan of contiguous blocks.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- cost Every insert on the table pays for the index, at five page modifications and five sets of log records where there was one row.
- decision A sixth index has to clear a 20 percent rise in per-insert page modifications on a five-page baseline, so a review that only shows a faster plan leaves the proposal unpriced.
- constraint Tuning key size to widen B-tree fanout buys about one page of depth across the 100-to-500 range. Page layout can solve only that much of an indexing problem; index count is the lever with the larger effect.
Count the pages one row insert touches on the dev.to post's example table: five, the heap page plus one leaf per secondary index [1]. Each modified page also produces a write-ahead log record, so crash recovery has five changes to replay for one logical row [4]. Add a fifth secondary index and the ceiling moves to six, a 20 percent increase charged to every insert the table takes to serve one query plan [2].
"Up to" is the post's own wording, and it is the careful part of the formula [5]. K+1 counts pages modified, and it is derived from an insert, where every index has to receive an entry [5]. For the figure to transfer to a specific table, you need to know how many of those page modifications land on pages already resident in the buffer cache and how many force a read first.
The read side is a hand-worked example: 100 million rows, branching factor 200, at most about four index pages per lookup, against millions of pages for a sequential heap scan [8]. Run that example at the ends of the branching-factor range the post calls typical, 100 to 500 [7], and depth moves between 4 pages and roughly 3 [3]. The read-side win is close to fixed for a given table size, while the write-side count rises by one page per index added [4].
Selectivity decides whether the traversal is worth doing at all. The post's counterexample is a predicate on status_code = 1 where 80 percent of rows match: random I/O to fetch the index entries, then random I/O again for the heap pages, losing to a sequential read of contiguous blocks [9]. The case that runs the other way is the index-only scan, where the index carries every column the query requests and the executor never touches the table heap [10].
Maintenance is the third line item. B-tree leaf and branch nodes hold strict sorted order and occupancy invariants, so they need background reorganisation to keep fragmentation down [11].
The post asserts two of its opening costs without measuring them. It says unchecked indexing accelerates buffer-cache eviction and increases recovery time during node failures [2], and puts no number on either. Recovery is the one a team can test on its own hardware, because replay work follows the logged page modifications the same insert already generated [4].
The standard the post sets is to balance read-path acceleration against the compounding storage and CPU costs of index maintenance [13]. That cost is charged to INSERT, UPDATE and DELETE, so an index proposal reviewed only on query latency misses it [14].
What to watch
- Whether the post's authors publish measured write amplification or recovery figures to replace the K+1 ceiling.
- Whether the K+1 count holds for UPDATE and DELETE, since it is derived from an INSERT example.
- An insert p99 comparison before and after dropping a duplicate index would test whether the 20 percent step shows up on real hardware.