Quick Answer: Databases don't just dump raw rows onto a disk sequentially. Instead, they chunk data into blocks called "pages" (typically 8KB). To prevent massive performance bottlenecks when row sizes change, pages use a clever bi-directional layout: tiny pointers grow downward from the top, while the actual variable-length row data grows upward from the bottom.
When we write a SELECT * FROM users query, it's easy to picture a database as a massive, perfectly organized Excel spreadsheet living on a hard drive. But if you actually look at how relational databases hold and represent data on disk, that mental model falls apart quickly.
If databases simply stacked rows on top of each other, changing a user's bio from ten characters to ten thousand characters would be an absolute nightmare. The database would have to shift every single subsequent row physically down the disk just to make room.
Instead, database engines handle this using a highly optimized internal architecture. Let's break down exactly how it works.
What is a database page?
A database page is the smallest unit of data storage and transfer managed by a relational database engine. Typically about 8 kilobytes in size, a single page acts as a fixed-size container that holds multiple rows of data alongside the metadata needed to manage them.
When you ask the database for a single row, the disk doesn't just read that one specific row. Hard drives and SSDs are optimized for block storage. The database reads the entire 8KB page into memory, processes what you asked for, and returns it. Because a single page contains many rows, the way those rows are arranged inside the page dictates how fast your application can read and write data.
Why do databases use bi-directional page layouts instead of sequential rows?
Storing rows sequentially from top to bottom causes massive performance bottlenecks during data updates. If a specific row's data size increases, the database would have to physically shift every single subsequent row downward to make room, resulting in a cascade of slow, expensive disk writes.
Imagine you are managing seating in a crowded movie theater. If you seat everyone shoulder-to-shoulder with no gaps, a late arrival who suddenly needs extra space forces the entire row of people to stand up and shuffle down. It's wildly inefficient. Databases avoid this "shuffling" problem by completely separating the location of the row from the data of the row.
How does the internal layout of a database page work?
Pages use a bi-directional architecture where a directory of pointers grows downwards from the top, while the variable-length row data grows upwards from the bottom. When data changes, the database simply writes the new data into the empty middle space and updates the tiny pointer at the top.
Here is exactly how a standard database page is structured:
| Page Section | Location | Purpose |
|---|---|---|
| Page Header | Very Top | Stores a few bytes of fixed-length metadata (page ID, amount of free space). |
| Slot Array (Pointers) | Grows Downwards | Holds tiny pointers detailing the exact memory locations (e.g., Pointer X to Y) where each row's data lives. |
| Free Space | Middle | The empty gap between the pointers and the data, waiting to be used. |
| Row Data | Grows Upwards | The actual variable-length data of your rows, filling in from the bottom of the page. |
Because of this layout, if I query for "Row 1", the database reads the top of the page, finds the pointer, and instantly jumps to those specific memory locations. If I update "Row 1" to be much larger, the database doesn't shift anyone else around. It simply writes the new, larger data into the free space growing upward from the bottom, and updates the pointer at the top to point to the new location.
What causes database page fragmentation?
Fragmentation occurs when rows are updated or deleted, leaving behind abandoned "holes" of empty space within the data section of the page. Because the database leaves the old data where it was and writes the new updated data elsewhere in the page, this wasted space eventually requires a process called compaction to squeeze the active data back together.
If you constantly update data in your application, your pages will eventually run out of free space in the middle. The database engine has to periodically step in, sweep away the abandoned ghost records, and reorganize the page layout to reclaim that space. This is why routine database maintenance is a reality for any heavily trafficked system.
Frequently Asked Questions (FAQ)
How big is a standard database page?
Most traditional relational databases, like PostgreSQL and SQL Server, use an 8KB page size by default. Others, like MySQL's InnoDB engine, default to a 16KB page size.
What is database compaction?
Database compaction is a background maintenance process that reorganizes a page's layout to remove empty gaps left by deleted or updated rows. In systems like PostgreSQL, this is famously handled by the VACUUM process.
Does updating a row always change its physical location on disk?
Yes, if the new data is larger or the database uses a Multi-Version Concurrency Control (MVCC) model, the database usually writes the new version of the row into the page's free space. It then updates the pointer and abandons the old data location until compaction runs.
Top comments (0)