The Page Is Full, So Cut It in Half
Last timeThe Tree Everything Uses
Most inserts touch one page. Occasionally a page is full, it splits, and the split can travel all the way to the top. That rare event is what indexes cost on a write.
Reading the structure is settled: two storage reads, almost regardless of table
size. Writing to it is where the cost of having an index lives, and almost all
of that cost comes from an event that happens rarely.
The easy case
Insert a row. The database descends the index exactly as a lookup does, which
is nearly free because the upper levels are in memory, and arrives at the
bottom page where the new key belongs. If that page has room, the key goes in
at the right position within the page, the page is written back, and the insert
is finished.
One read, one write. Nothing above the bottom page changes, because no boundary
between pages moved, so every signpost in every level above is still correct.
This is what happens for the great majority of inserts, and it is why adding
one index to a lightly written table is not something anybody notices.
Splitting a full page
Sometimes the page is full. Then it is cut in half.
The lesson stops here
5 more paragraphs to go
You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.
The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.
See the planThe contentsThis is the reading half
Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.
The contents