The 8 KB Page

Postgres reads and writes in fixed 8,192-byte pages, and every table, index and TOAST relation is a numbered sequence of them starting at page 0. The page is the unit of I/O, the unit of caching in shared_buffers — 128 MB of them on pg-primary today, which is 16,384 slots, and 2,097,152 once Chapter 10 sizes the pool to the hardware — the unit written whole to the write-ahead log after a checkpoint, and the unit the planner counts when it prices a scan.

That single number explains a surprising amount. It is why a row cannot be arbitrarily wide, why an update to one integer can cost 8 KB of WAL, why a table's size in rows tells you almost nothing about the cost of reading it, and why inventory can hold 12,000 rows and occupy 900 MB.

Why the Block Is Fixed

The operating system and the storage device both move data in blocks, so a database that pretended otherwise would spend its life on partial reads. Fixing the size makes three things simple at once. The buffer cache becomes an array of identical slots rather than a heap allocator. Free space accounting becomes one byte per page rather than a map of extents. And crash recovery gets a tractable argument: because a page write can be torn by an operating system crash, Postgres writes the full image of each page to WAL on its first modification after a checkpoint, and a fixed-size image is a fixed-size cost.

The size is set by BLCKSZ when the server is compiled, and the server reports it back as a read-only parameter. No packaged distribution ships anything other than 8192, and changing it means building your own server, rebuilding every extension against it, and reloading the cluster from a dump — the on-disk format is not compatible across block sizes. Treat it as a constant of the universe rather than a tuning knob. The 8 KB choice also sets two ceilings you will meet eventually: a single relation tops out at 32 TB, because a relation addresses at most 4,294,967,295 pages, and the number of rows a table can hold is whatever fits in those pages.

The Three Regions of a Page

Every page opens with a 24-byte header. It carries pd_lsn, the WAL position of the last change to this page, which is what lets recovery decide whether a replayed record has already been applied; pd_checksum, the page checksum; pd_lower and pd_upper, the two offsets that bracket the free space; pd_special, where the special area starts; the page size and layout version; and pd_prune_xid, a hint about whether the page has cleanup pending.

After the header, two structures grow toward each other. From the front comes the line pointer array: 4 bytes per entry, each holding a byte offset into the page, the length of what it points at, and two flag bits. From the back come the tuples themselves, written downward from the end of the page. The gap between pd_lower and pd_upper is the free space, and the page is full when adding one more line pointer and one more tuple would close it. Nothing is sorted: a new row goes wherever there is room, and physical order on the page carries no meaning.

The last region is the special space, and on a heap page there is none: pd_special equals the page size, so a table page and an index page are distinguishable at a glance. Index access methods use it for their own bookkeeping — a B-tree keeps the links to its left and right sibling pages there, and a range scan walks sideways across leaf pages instead of going back to the root.

Line Pointers and the ctid

A row's physical address is the pair (page number, line pointer slot), and Postgres exposes it as the system column ctid. Every index entry in the database stores that pair. This is the single most consequential fact about the layout, because it means an index does not point at bytes — it points at a slot, and the slot can be repointed to somewhere else on the same page without any index knowing. That indirection is what lets a page be compacted in place, and it is the machinery behind the heap-only-tuple optimization that Chapter 7 takes apart.

SELECT ctid, product_id, warehouse_id, on_hand
  FROM inventory
 WHERE product_id = 4471 AND warehouse_id = 2;

  ctid   | product_id | warehouse_id | on_hand
---------+------------+--------------+---------
 (91,17) |       4471 |            2 |       1

The contested box of strawberries lives in slot 17 of page 91 of the inventory relation. That is an address, not an identity: for a one-shot re-read inside the same transaction it is the fastest access path there is, and for anything that outlives the statement it is wrong. Application code that stores a ctid and comes back to it later is storing a pointer into a file that reorganizes itself — the next topic shows this one changing under a single UPDATE.

What Does Not Fit

A tuple cannot span pages. That one rule produces the practical row-width ceiling of a little under 8 KB, and it is what TOAST exists to work around — the TOAST topic has the mechanism. A table can also have at most 1,600 columns, and that limit is further squeezed by the same rule — 1,600 int columns come to 6,400 bytes and fit on a page, while 1,600 bigint columns come to 12,800 bytes and do not, so the declaration succeeds and the first insert fails.

The arithmetic for how many rows land on a page is worth doing by hand once. Subtract the 24-byte header, leaving 8,168 bytes, and charge each row its aligned tuple length plus 4 bytes for the line pointer. A 100-byte row therefore costs 104 bytes and about 78 fit. An inventory row — 24 bytes of tuple header plus a bigint and two int columns — is 40 bytes, costs 44 with its pointer, and 185 fit on a page. Those numbers are what every scan cost estimate in Chapter 9 is built from, and they are why a wide table is expensive even for a query that names two narrow columns: the page holding those two columns holds all the others as well, and the read pulls in the lot.

Reading a Real Page

The pageinspect extension turns all of this from theory into output. get_raw_page() hands back a raw 8 KB block from any fork of a relation — main, fsm, vm or init — and page_header() decodes the first 24 bytes of it. Every function in the extension is superuser-only and reads pages directly, so the right place to run it is a scratch copy or a spare replica, not a production primary in the middle of a Saturday peak.

SELECT * FROM page_header(get_raw_page('inventory', 91));

    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 3A/7C0918B0|    -4821 |     0 |    92 |  7512 |    8192 |     8192 |       4 |         0

Read that left to right and the page describes itself. The LSN says which WAL record last touched it. The checksum is a real stored value because clusters initialized on 18 have data checksums on by default. lower at 92 means the line pointer array runs from byte 24 to byte 92, which is 17 slots of 4 bytes; upper at 7512 means the topmost tuple starts there, so 7,420 bytes in the middle are free. A page with room for 185 rows is holding 17, and this is one of the denser ones left: 12,000 live rows spread over 115,200 pages average well under a single row each. special equal to the page size confirms there is no special area, so this is a heap page and not an index page. version 4 is the page layout version, and a zero prune_xid says nothing on the page is currently a candidate for cleanup.

Why Page-Level Thinking Pays

Several later topics stop being separate facts once the unit is in your head. The planner's cost model prices a scan in pages read, not rows returned. The buffer cache hit ratio counts page requests. Full-page writes after a checkpoint are one 8 KB image per page touched. And bloat, the subject of Chapter 7, is not "extra rows" — it is pages that hold almost nothing and still have to be read.

SELECT relpages, reltuples FROM pg_class WHERE relname = 'inventory';

 relpages | reltuples
----------+-----------
   115200 |     12000

SELECT pg_size_pretty(pg_relation_size('inventory'));   -- 900 MB

Twelve thousand rows at 185 per page need 65 pages. That is a little over half a megabyte. inventory occupies 115,200 pages and 900 MB, roughly 1,700 times what the live rows require, and every sequential scan of that table reads all 115,200 of them whether they contain anything or not. How the table got that way is Chapter 7's argument, and it needs the next topic's row header first. relpages and reltuples are estimates that VACUUM, ANALYZE and a few DDL commands refresh, and here they agree with the disk to the page.