Where your data actually lives: pages, heap, and the buffer pool

When you write prisma.expense.findMany() you picture a table as a spreadsheet — rows stacked neatly, one after another, and the database somehow "goes to" the rows you asked for. That mental model is fine for writing queries and completely wrong for understanding performance. Under the hood there are no rows the database can address individually. There is a file full of fixed-size blocks called pages, and the smallest thing Postgres will ever read or write is one entire page. Everything expensive or surprising about database performance falls out of that one fact.

This is the same move the networking track made with packets: fetch() felt like "send a message," but underneath it was bytes on a stream. Here findMany feels like "get these rows," but underneath it is "read these 8KB pages off disk into memory." Once you see the page, indexes, caching, VACUUM, and even transactions stop being magic.

A table is a heap of 8KB pages

On disk, a Postgres table is just one or more files, and each file is a sequence of pages (Postgres also calls them blocks) that are 8KB each — a fixed size baked in at compile time. A table with a million rows is not a million things on disk; it's however many 8KB pages it takes to hold a million rows, laid end to end. This default storage — an unordered pile of pages where new rows go wherever there's free space — is called a heap. "Heap" here just means "unordered pile," not the heap data structure from algorithms class. When people say Postgres tables are "heap tables," this is what they mean: the rows are in no particular order on disk, and finding a specific one means either scanning the pile or consulting an index (the next lesson).

Why a fixed size at all? Because fixed-size blocks make everything downstream simpler and faster: the OS reads and writes storage in fixed blocks, caches are sized in fixed units, and "page number 47" can be located by pure arithmetic (47 × 8192 bytes into the file) instead of bookkeeping. The cost is a little internal waste, and it's a rule you can't escape — even to read one 40-byte row, Postgres reads the whole 8KB page that contains it.

Inside one page

A page is not an opaque blob. It has a specific internal layout, and knowing it explains several things you'll hit later. The clever part is that the page fills from both ends toward the middle:

        one 8 KB page
 +----------------------------------+
 | page header (~24 bytes)          |
 +----------------------------------+
 | item pointer array  →→→          |   grows DOWN, toward middle
 | [ptr0][ptr1][ptr2] ...           |   (4 bytes each: offset + length)
 +----------------------------------+
 |                                  |
 |          free space              |
 |                                  |
 +----------------------------------+
 |          ... tuple2              |   grows UP, toward middle
 |   tuple1                         |
 |   tuple0                         |   the actual row data
 +----------------------------------+

At the very start is a small page header (about 24 bytes) with bookkeeping: a checksum, and pointers to where the free space begins and ends. Then comes an item pointer array growing downward from the top — each pointer is a tiny 4-byte entry saying "the row for this slot lives at byte offset X and is Y bytes long." The actual rows are packed in from the bottom of the page, growing upward. The free space is the gap in the middle, and the page is full when the pointer array and the row data meet.

Why the two-ended design? Because it lets a row's identity stay stable even when its bytes move. A row is addressed by its TID (tuple identifier): the pair (page number, slot number) — slot number being its index in that pointer array. If the page gets reorganized and the row's bytes shift, only the pointer's offset changes; the slot number, and therefore the row's TID, stays put. Indexes point at TIDs, so this indirection is what lets Postgres compact a page without rewriting every index. Hold onto TID — it's exactly what a B-tree index hands you in the next lesson.

A row is a "tuple" with a hidden header

Inside the page, each row is stored as a tuple, and a tuple is not just your column values. It starts with a tuple header of about 23 bytes that you never see in a SELECT *. Two of its fields are the reason a whole later lesson exists: xmin and xmax.

• xmin — the ID of the transaction that created this version of the row.
• xmax — the ID of the transaction that deleted (or superseded) it, or zero if it's still live.

Right now just file these away: they're stamped on every single row, they cost bytes on every page, and they are how Postgres decides which version of a row your transaction is allowed to see. That machinery — MVCC — is the fourth lesson in this track. The point for today is that they're physically here, in the tuple header, taking up space in every page whether you asked for them or not.

When a value is too big: TOAST

An 8KB page sets an awkward limit: what about a 50KB JSON blob or a long text field? Postgres will not let a single tuple span multiple pages. Its answer is TOAST (The Oversized-Attribute Storage Technique). When a row's total size would blow past roughly a quarter of a page (about 2KB), Postgres starts compressing the biggest fields, and if that's still not enough it moves them out of line into a separate, hidden TOAST table, leaving just a small pointer in the main tuple.

This has a real performance consequence you can feel. If your main table's rows stay small, more of them fit per page, and a scan touches fewer pages. If you store a big text or jsonb column inline in a hot table, you can bloat the tuples, fit fewer per page, and slow down every query that scans it — even queries that don't select the big column, because they still read the pages. Fable stores chat message text in the row but keeps media as a URL to object storage precisely so message pages stay dense.

The buffer pool: RAM in front of the disk

Reading an 8KB page from disk every time you touch a row would be brutally slow — recall from the networking track that an SSD random read (~100µs) is roughly a thousand times slower than a main-memory read (~100ns). So Postgres keeps a big in-process cache of pages in RAM called the buffer pool (its size is the shared_buffers setting). When a query needs a page, Postgres first checks the buffer pool. If the page is already there, that's a buffer hit — no disk I/O at all. If not, it's a miss: Postgres reads the 8KB page from disk into a free slot in the pool, evicting some other page if the pool is full, and hands it over. The whole game of database performance is making your working set of pages fit in the buffer pool so your hot queries hit RAM.

There's a subtlety worth knowing. Postgres does not use direct disk access — it reads through the operating system's own page cache. So a page you're missing in shared_buffers may still be sitting in the OS cache, making the "read" much faster than a true trip to the platter. This is the famous double-caching situation: the same page can live in both Postgres's buffer pool and the kernel's page cache at once, using RAM twice. It's why the common tuning advice is not to give Postgres all your RAM — a frequent starting point is around 25% of system memory for shared_buffers, deliberately leaving room for the OS cache to back it up. (This is where InnoDB, MySQL's engine, differs and teaches something: its buffer pool typically is sized to most of RAM because InnoDB largely bypasses the OS cache with direct I/O. Same problem, opposite tuning philosophy.)

Sequential vs random: the access pattern that dominates

Two queries can read the exact same number of pages and differ wildly in speed, because of how they read them. Reading pages sequentially — page 0, 1, 2, 3… — is the fast case: the pages are adjacent on disk, the OS reads ahead and prefetches the next ones, and even an SSD is happiest with large contiguous reads. Reading pages at random — page 900, then 12, then 4001 — defeats prefetching and pays the full random-access cost for each one.

This asymmetry is why "the index made it slower" is a real thing. A full table scan reads every page but reads them sequentially. An index lookup reads only a few pages but often jumps to them at random. If a query is going to touch a large fraction of the table anyway, the planner may correctly choose to scan the whole thing sequentially rather than do thousands of random index-driven jumps — Postgres even has two separate cost knobs, seq_page_cost and random_page_cost, to weigh exactly this. Keep this asymmetry in your pocket: it's the hinge of the next three lessons.

Seeing it: EXPLAIN (ANALYZE, BUFFERS)

You don't have to take any of this on faith. EXPLAIN (ANALYZE, BUFFERS) runs a query and reports, among other things, how many pages it found in the buffer pool (shared hit) versus how many it had to read from disk/OS (shared read). Here's an illustrative run of the same query twice (numbers synthesized to show the pattern, not from a specific machine):

-- cold: first time, pages not yet in the buffer pool
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM expenses WHERE group_id = '01H8X...';

 Seq Scan on expenses  (cost=0.00..2891.00 rows=94 width=61)
   (actual time=0.05..48.2 rows=94 loops=1)
   Filter: (group_id = '01H8X...')
   Rows Removed by Filter: 119906
   Buffers: shared read=1091          -- 1091 pages fetched from disk
 Planning Time: 0.12 ms
 Execution Time: 48.9 ms

-- warm: run it again immediately, pages now cached
 Seq Scan on expenses  (cost=0.00..2891.00 rows=94 width=61)
   (actual time=0.03..6.1 rows=94 loops=1)
   Filter: (group_id = '01H8X...')
   Rows Removed by Filter: 119906
   Buffers: shared hit=1091           -- 1091 pages served from RAM
 Planning Time: 0.10 ms
 Execution Time: 6.4 ms

Read the story in the numbers. Both runs did the same sequential scan of ~1091 pages and both threw away 119,906 rows to find 94 — this table has no useful index yet, so Postgres read the entire heap. The only thing that changed is read=1091 became hit=1091: the first run pulled every page off disk, the second found all of them already in the buffer pool, and execution time dropped ~8×. That Rows Removed by Filter: 119906 is the smell of a missing index — you read 1091 pages to return 94 rows. Fixing that is the next lesson.