Where Data Actually Lives: Disk, Pages, and Storage Engines

• Understand why "reading from disk" is the most expensive operation in a database, and why it's the root of every performance problem;
• See that a table on disk is not one big unstructured file: the layering of table → heap file → page;
• Meet the buffer pool (page cache) and the WAL log, and understand why "the second query is faster" and why writes hit the log first.

• Read the first two articles in the series so "table" and "execution plan" are fresh:

• A PostgreSQL you can run (a Docker postgres image is plenty).

At the end of the last article we left a question hanging: when the optimizer estimates cost, why does it count "reading the whole table" (Seq Scan) as so expensive?

Because a database spends most of its time waiting on disk.

Look at a few orders of magnitude (approximate):

A mechanical disk is roughly 100,000 times slower than memory. Even with an SSD, you're still more than a thousand times off. A database may process thousands of queries per second, and the first step of almost every query is "move data from disk into memory."

In one sentence: in a database, reading disk is the single most expensive thing you can do — and nearly every optimization is really about reading less disk.

Think of a library. If you know roughly where a book lives, you walk over and pull it out. If you don't, you have to sweep through the shelves row by row. The disk is that shelf rack — every row you sweep costs time.

Last article we built up the concept of a "table." Now the question: what does a table look like on disk?

You might guess: "It's a big file like books.json, with rows lined up one after another." You're in the right direction, but it's not quite that — a database doesn't treat data as one unstructured text file. It organizes storage into several layers.

In PostgreSQL, the main body of a table lives in a heap file. "Heap" means "append records to the file without guaranteeing any order." Think of dropping notes into a box: what goes in first sits in front, what comes later sits behind — no sorting, no pattern.

• The file's name is a number the database assigns automatically (the relation's OID), e.g. 16384;
• When a table exceeds 1GB, PostgreSQL splits it automatically into 16384.1, 16384.2, and so on — multiple physical files, one logical table.

You can find the physical file for a table with one query:

base/5 is the database directory; 16384 is the table file.

The file holds one row after another, but a row is not just a naked list of column values. Each row in PostgreSQL (called a tuple) starts with a tuple header that carries metadata — such as which transaction created or deleted it (this is deeply tied to transactions and MVCC, which we'll cover later; for now just make a mental note):

You don't need to memorize every field of the header. The takeaway: database storage is structured, not raw text.

Here's the crucial question: since a table is "a bunch of records," does a query read "exactly the rows it needs, row by row"?

No. A database reads and writes disk in pages — a page is a few kilobytes, and every I/O grabs at least one page.

The slowest part of a disk is seeking — a mechanical disk has to move its read head, and an SSD has to locate flash cells. If every read of a 30-byte row paid a full seek cost, performance would be unusable. So operating systems and databases batch disk I/O into larger chunks: read one chunk at a time and use every useful row inside it.

It's like reading a book: you don't open the book once per character — you open a page and read all the words on it.

PostgreSQL's default page size is 8KB (8192 bytes); MySQL InnoDB defaults to 16KB. The layout of a page is roughly:

• Page header: metadata about the page itself (page number, where free space starts, ...);
• Item pointers: an "address plate" for each row, pointing at where the row data lives;
• Row data: the actual records, stacked from the back of the page toward the front;
• Special area: auxiliary information related to the page (e.g. the visibility map).

How many rows fit in one 8KB page depends on row size — anywhere from a few hundred to a couple of thousand.

Since everything is page-based, how do you point at "a specific row"? PostgreSQL gives every row an internal address called a TID, formatted as "page number + offset within page":

This TID matters a lot — in the next article you'll see that an index doesn't store the data itself; it stores TIDs, and it's the TID that lets you pull a row out of the heap.

You've now internalized "reading disk is expensive." What's a database to do? The answer is humble: keep the pages you read often in memory.

A database reserves a chunk of memory at startup just to cache data pages. In PostgreSQL it's called shared_buffers (default 128MB); in MySQL it's the Buffer Pool. The read path goes like this:

So you'll observe: the same query is slow the first time and fast the second. The first run copies the page from disk into memory; the second run hits memory directly.

What if the pool is full? Evict the least-recently-used pages (roughly an LRU policy) to make room. This is the same "trade space for time" idea as HTTP caching in the browser.

And there's the complete answer to "why is Seq Scan so expensive" from last article: it's not that one read is costly — it's how many pages you have to read. Scanning a whole table means touching every page (at least the first time); a primary-key lookup touches a single page. The difference isn't the cost of "reading a page," it's the number of pages.

Now the write path. You might assume "writing" means: modify the page, write it back to disk. But two problems hide here:

1. Random writes are slow: every UPDATE/INSERT can touch a different page, and random writes are far slower than sequential writes;
2. Crash consistency: the database can lose power at any moment. If it only modified half a page in memory and never flushed it, data is lost or corrupted.

PostgreSQL's answer is the WAL (Write-Ahead Log):

The key point: the data page's disk write can be deferred, but the WAL must hit disk first. If the database crashes, it replays the WAL (redo) to restore any changes that hadn't yet been written to the data pages, returning to a consistent state.

Why is this actually faster? Because WAL is sequential append — the log always writes to the end of the file, using contiguous disk regions far more efficiently than random rewrites of data pages. Small cost, huge payoff: crash recovery for free.

Finally, a unifying concept: the storage engine — the layer responsible for "how data is organized on disk and in memory."

"A table" is just a logical idea. The same table can have totally different physical organization across databases:

• PostgreSQL: heap table. Data lives in the heap, in no particular order; indexes are stored separately, and index entries point to row TIDs. The order you insert is basically the order stored.
• MySQL InnoDB: index-organized table. The primary key index directly "hugs" the entire row; rows are stored sorted by primary key. Secondary indexes store primary-key values, not row addresses.

This has a direct consequence: an InnoDB table must have a primary key (otherwise it silently creates a hidden one), because the primary key is its physical arrangement; a PostgreSQL table can get by without a primary key and still store data.

This distinction will be central to the next article — when we discuss how indexes work, the two databases behave differently because of this storage organization. Seeds planted.

Everything so far is row storage: all the columns of a record live next to each other. This suits "fetch a whole record by its key" workloads (OLTP — placing an order, viewing a detail page).

One scenario prefers the opposite: analytical queries often want "one column, scanning many rows" (OLAP — computing an average price). If you store by column, all the values of one column sit contiguously, so scanning only needs to read that column's data — a big drop in I/O. This is columnar storage, used by systems like ClickHouse. Keep it in mind as a direction; this series focuses on row-oriented relational databases.

Create a table, load tens of thousands of rows, then:

pg_table_size tells you "how many pages it takes at minimum" — divide by 8KB and you'll know the page count.

Run the same query twice with EXPLAIN ANALYZE:

Compare the two Execution Time and Buffers values — the second run is usually faster, because the pages are already in shared_buffers. That's the buffer pool at work.

Install the pageinspect extension and look at a page directly:

You'll see the offset of each item within the page — the physical embodiment of the "item pointer → row data" structure we described.

• Reading disk is the most expensive operation in a database; every optimization revolves around reading fewer pages;
• A table's physical form: table → heap file → pages, and the database reads/writes disk in pages (8KB by default in PostgreSQL);
• The buffer pool keeps hot pages in memory — that's why "the second query is faster";
• WAL writes the log first and the data later, trading sequential writes for crash recovery;
• The storage engine decides physical organization: PostgreSQL uses a heap table; MySQL InnoDB uses an index-organized table.

Keywords: disk I/O、random read / sequential write、page、heap file、TID、buffer pool (shared_buffers)、WAL、checkpoint、storage engine、row storage、PostgreSQL、InnoDB

This article nailed down where data lives and how a database reads it: disk reads are expensive, so we read by page, cache pages, and write the log first. One puzzle remains — reading by page is the how; the which pages to read decision sits with the optimizer, and what does the optimizer lean on to "read fewer pages"?

Next we enter the index principle: B+ trees. You'll find that an index is a map for locating a target row within a few pages — and the pages and TIDs from this article are exactly the coordinates on that map.