Explain how a slotted page stores variable-length rows: what sits in the page header, where do the rows live, and why is there a slot or line-pointer array in between?

## The problem Rows are variable-length: NULLs, VARCHAR, differing column counts across versions. Storing them in a fixed page requires answering three questions: how to find row k inside the page, how to reclaim space when a row is deleted, and how to let external structures (indexes) refer to a row without breaking when the page is reorganised. The slotted page answers all three with one design. ## The layout Read a page from both ends toward the middle. **1. Page header** (tens of bytes at offset zero). Typical fields: the page's own identifier or checksum, the **page LSN** used by crash recovery to decide whether a logged change is already applied, offsets marking the start and end of free space, the count of slots, and flags such as *page is full* or *page has dead rows*. **2. Slot array**, immediately after the header, growing toward higher offsets. Each slot is small, on the order of 4 bytes, and holds: the offset where the row begins in this page, the row's length, and a status. A row's address is therefore (page number, slot number), a compact identifier commonly called a row id, tuple id or ctid depending on the engine. **3. Row data**, written from the end of the page downward. **4. Free space**, the gap between the growing slot array and the descending row area. Inserting a row consumes from both ends: one slot at the top and the row's bytes at the bottom. The page is full when the gap can no longer hold both. ## Why the indirection matters If indexes stored byte offsets, the engine could never move a row within its page, because every index entry pointing at it would break. With slot numbers, moving is free: rewrite the row's bytes elsewhere in the page and update its slot's offset. Nothing outside the page notices. This enables **in-page compaction**. Deleting rows leaves holes scattered through the row area. Rather than maintaining a free list of fragments, the engine simply slides the remaining rows together when it next needs space, updating offsets in the slots. The result is contiguous free space again, and page-internal fragmentation never accumulates permanently. Slot status carries further value: - **Dead or unused** marks a slot whose row is gone but whose slot number must not be reused until every index entry referencing it has been removed, otherwise an index could point at a different row. - **Redirect** lets a slot point to another slot on the same page. Engines use this when a row is rewritten in place (for example an update that stays on the page) so old references still resolve without touching indexes. ## The row itself Inside the row area, a row begins with its own small header: visibility or version information, a NULL bitmap with one bit per column, sometimes the column count and flags such as *has off-page values*. Fixed-width columns follow, often aligned to word boundaries, then variable-length columns each preceded by a length. Reading column seven means walking the layout, which is why fixed-width columns and column ordering can affect access cost and padding waste in some engines. ## Ordering within a page is not logical ordering Slot 1 need not hold the row with the smallest key, or the oldest row. After compaction and reuse of freed slots, physical position within a page is arbitrary. This is one physical reason a query without ORDER BY has no guaranteed order: the engine returns rows in whatever order it walks slots, which changes as the page is modified. ## Interaction with indexes An index entry stores the key plus the row address (page, slot). Lookups therefore cost the index traversal plus one page read per matching row, and the address's stability requirements explain much of the engine's bookkeeping: dead slots that cannot be reused early, cleanup processes that remove index entries before freeing slots, and redirect slots that keep addresses valid across in-page rewrites. ## What an interviewer is checking That you can explain (a) the three regions and the free-space gap, (b) that a row is identified by slot number rather than byte offset, and (c) the consequence: rows can be relocated within a page for compaction without touching indexes. Everything else, headers, NULL bitmaps, redirects, is supporting detail.