THE FOLD / GRIND / WARM CACHE / THE SEQUENTIAL KEY
THE SEQUENTIAL KEY
one fact, described once by a cache and once by a lock
1 WHAT IT IS · WHAT IT DOES · FACT OR FICTION
An auto-increment key means every insert goes to the same end of the index. That is wonderful for the disk and terrible for a lock: the rightmost page is the only page anybody wants, and every writer wants it at once.
LIT verified live. 50,000 inserts across 512 index pages. Sequential keys move to a new page 512 times — once per page, in order. Random keys move 49,918 times, 97.5× more, touching the whole index constantly. But look at the other end: the last 1,000 sequential inserts all land in just 11 pages. That concentration is the cache win and the contention hotspot, and they are the same number.
LIT verified live. 50,000 inserts across 512 index pages. Sequential keys move to a new page 512 times — once per page, in order. Random keys move 49,918 times, 97.5× more, touching the whole index constantly. But look at the other end: the last 1,000 sequential inserts all land in just 11 pages. That concentration is the cache win and the contention hotspot, and they are the same number.
2 HOW IT WAS WEAVED · AI + HUMAN
Right-edge contention on monotonic keys is why Oracle has reverse-key indexes, why SQL Server documents “last page insert contention”, and part of why UUID v7 exists at all.
AVAN (AI) measured both directions on one run so the trade is visible in a single pair of numbers rather than argued as a preference. It is the same statistic read twice: sequential keys are 97.5× better at staying in cache and, for exactly that reason, put every concurrent writer on the same 11 pages. Neither number is the answer; the pair of them is the decision.
AVAN (AI) measured both directions on one run so the trade is visible in a single pair of numbers rather than argued as a preference. It is the same statistic read twice: sequential keys are 97.5× better at staying in cache and, for exactly that reason, put every concurrent writer on the same 11 pages. Neither number is the answer; the pair of them is the decision.
3 ONE DIMENSION
Where the last thousand inserts landed, under each key scheme.
4 TWO DIMENSIONS · INTERACTIVE
Switch schemes and add writers.
5 THREE DIMENSIONS + AVAN’S INVERSE
The green forward object: a queue forming at the right edge.
AVAN’s addition (the inverse-companion): the forward reading is that sequential keys cause write contention. The inverse is that locality and contention are one property seen from two sides. Everything landing in the same few pages is precisely what makes the index fit in memory, and precisely what makes the writers queue. You cannot buy one without the other, because they are not two effects — they are one fact, described once by a cache and once by a lock. Read backwards, spreading the keys does not solve contention; it pays for it in cache misses, and the bill simply moves to a department that files different tickets.
LIT 50,000 inserts across 512 index pages: sequential keys move to a new page 512 times, once per page in order, while random keys move 49,918 times - 97.5 times more - and the last 1,000 sequential inserts all land in just 11 pages, which is the cache win and the contention hotspot measured as the same number
FIG Right-edge contention on monotonic keys is why Oracle has reverse-key indexes, why SQL Server documents last page insert contention, and part of why UUID v7 exists. AVAN measured both directions on one run so the trade is visible in a single pair of numbers rather than argued as a preference. It is the same statistic read twice: sequential keys are 97.5 times better at staying in cache and, for exactly that reason, put every concurrent writer on the same 11 pages. Neither number is the answer; the pair is the decision.
FIG Right-edge contention on monotonic keys is why Oracle has reverse-key indexes, why SQL Server documents last page insert contention, and part of why UUID v7 exists. AVAN measured both directions on one run so the trade is visible in a single pair of numbers rather than argued as a preference. It is the same statistic read twice: sequential keys are 97.5 times better at staying in cache and, for exactly that reason, put every concurrent writer on the same 11 pages. Neither number is the answer; the pair is the decision.
◆ sealed .dlw.fold → folded to ROOT_0 · a sphere of WARM CACHE · David Lee Wise (ROOT0), with AVAN