When a stored row is rewritten at a larger size and no longer fits on its current page, what options does the storage engine have, and how does it find a page with enough free space for a new row in the first place?

## Two related problems A storage engine constantly answers two placement questions: *where does this new row go?* and *what if a row no longer fits where it is?* Both come down to how free space is tracked and how much of it was left available. ## Tracking free space Scanning the table to find a page with room would be absurd, so engines maintain a **free-space map**: a compact side structure recording, per page, roughly how many bytes remain. It is compact by design, often just a few bits per page giving a bucketed figure, so that a map covering a huge table still fits in a handful of pages and stays cached. A tree or hierarchical layout over those summaries lets the engine descend to a suitable page in a couple of reads. Two properties matter: - **It is approximate.** Bucketed values under-report or over-report. The engine treats the map as a hint and confirms against the target page, falling back to another candidate if the space is gone. - **It can be stale.** Space freed by deletions typically becomes visible in the map only after background maintenance has processed the page. So a table can have plenty of dead space and still extend its file, because the engine did not know the space was reusable yet. On insert, the engine picks a page with enough room, writes the row, adds a slot, and updates the map's entry. If nothing suitable exists, the file is extended with a fresh page, which is why insert-heavy tables grow in bursts. ## When a row grows A rewrite can enlarge a row: a variable-length column gets a longer value, a previously NULL column receives data. The engine tries, in order: **1. Fit it in place.** If the new size is no larger, or the immediately following bytes are free, nothing else is needed. **2. Compact the page.** Deletes and earlier rewrites leave holes scattered through the row area. Sliding the live rows together merges those holes into one contiguous region. Because rows are addressed as (page, slot) and the slot holds the offset, this reorganisation is invisible outside the page: only slot offsets change. This is the cheapest recovery and it is why fragmentation within a page is a transient condition rather than a permanent loss. **3. Push a large value out of line.** If the growth came from a big variable-length column, moving that value to overflow storage and leaving a small pointer often shrinks the row enough to keep it on its page. **4. Relocate the row to another page.** Now the row's physical address changes, and every index entry pointing at the old address is stale. Two families of solution exist: - **Update the index entries.** Correct and keeps lookups at one hop, but expensive: every index on the table must be touched, so a table with six indexes pays six index writes for one row move. - **Leave a forwarding pointer.** The old slot becomes a stub pointing to the row's new location. Index entries stay valid, and the cost is deferred: every subsequent lookup that arrives at the old address pays one extra page read to follow the redirect. In engines that do this, accumulated *migrated rows* or *row chaining* is a recognised performance problem, detectable in statistics and cured by reorganising the table. Multi-version engines change the picture slightly: an update writes a new version rather than modifying the row in place, so the question becomes where that new version goes. Placing it on the same page enables an optimisation where indexes need not be touched at all, and placing it elsewhere forces the full index maintenance. Either way the underlying tension is identical: keeping the new image on the same page is what avoids index work. ## Prevention: reserving space Since same-page rewrites are the cheap path, engines let you reserve free space when filling pages, commonly called a **fill factor** or **pctfree**. Filling pages only to, say, 80 or 90 percent leaves room for rows to grow or for new versions to land locally. The tradeoff is direct and quantifiable. Reserving 20 percent means every sequential scan reads about 25 percent more pages forever, and the buffer pool caches 20 percent less useful data. So: - **Append-mostly tables never updated after insert** (event logs, audit trails, immutable facts): fill pages completely. Reserved space is pure waste. - **Tables with frequent in-place growth**, especially where the growing column starts NULL or short and later becomes long: reserve meaningfully, because relocation or forwarding is far more expensive than a few percent of extra scan I/O. - **Bulk loads followed by heavy updates**: load with reserved space, or accept a reorganisation later. ## Diagnosing in production Symptoms of getting this wrong: point lookups that gradually slow down without any change in data volume (accumulating forwarded rows), a table file much larger than the sum of its live row sizes (fragmentation plus reserved space plus unreclaimed dead space), and write throughput dropping as each update triggers index maintenance for relocation. The remedies are a reorganisation or rebuild to restore locality, a fill-factor change to prevent recurrence, and schema changes that stop rows from growing after insert, such as giving a column a realistic initial value rather than leaving it NULL until later.