Introduction

The toy store from Essentials addressed data by byte offset. Every record was a line in a file, the index held the exact position of each line, and reads pulled out exactly those bytes. Real engines don't work at that granularity. They read and write in pages: fixed-size blocks, 8 KB in PostgreSQL and 4 KB by default in SQLite, and every row, index entry and bit of free-space bookkeeping lives inside one.

This article covers the page itself: why the size is fixed, how variable-length rows fit inside a page, what a PostgreSQL table file looks like when you open it with pageinspect, and how the buffer pool decides which pages stay in memory. The B-tree in the next article is built entirely from these pages, so it is worth knowing them well.

Why storage is cut into pages

Disks never hand you a single byte. An HDD reads whole sectors, an SSD reads whole flash pages (usually 4 to 16 KB), and the operating system caches files in 4 KB pages of its own. Asking for 30 bytes at offset 1,000,003 costs about the same as asking for the 4 KB around it, because that is what actually moves.

So engines stop pretending and pick a unit. Once every page has the same size, page n of a file starts at byte n * page_size, and the rest of the engine can talk about pages by number. A row's address becomes "page 7, slot 3" instead of a raw byte offset, the cache becomes "keep these N pages in memory", and the B-tree becomes "this page points at those pages".

The size is a trade-off, and engines land in different places:

• PostgreSQL uses 8 KB (BLCKSZ), fixed when the server is compiled. You can't change it per table.
• SQLite defaults to 4 KB and accepts any power of two from 512 to 65,536, set with PRAGMA page_size before the database has any content.
• MySQL InnoDB uses 16 KB by default.

Bigger pages mean fewer I/Os for a scan and wider B-tree nodes. Smaller pages mean a point lookup reads less data it doesn't need, and changing one row dirties less memory. Neither wins everywhere. PostgreSQL has shipped with 8 KB for decades, and very few people rebuild it with another size.

The slotted page

Fixed-size pages and variable-length rows don't fit together naturally. A row with a 3-character name and a row with a 3,000-character bio both have to go in a page, and rows get deleted and updated after they're written.

Almost every engine solves this with a slotted page. The page starts with a small header, then an array of slots (PostgreSQL calls them line pointers), and the row data is packed from the end of the page backwards. The free space is whatever is left in the middle.

 0                                                                    8192
 +--------+-----+-----+-----+------------------+-------+-------+-------+
 | header | lp1 | lp2 | lp3 | -> free space <- | row 3 | row 2 | row 1 |
 +--------+-----+-----+-----+------------------+-------+-------+-------+
                            ^                  ^
                          lower              upper

Two numbers in the header, lower and upper, mark where the slot array ends and where the row data begins. Inserting a row moves upper down by the row's size and lower up by one slot. The page is full when they would cross.

The slot array is there so nothing outside the page needs to know where a row sits inside it. Indexes and other pages refer to a row as "page 7, slot 3", and slot 3 holds the actual byte offset. If the engine wants to move rows around inside the page, say to close the gaps left by deletes, it moves the bytes and updates the slots, and every external reference stays valid.

Here is a slotted page in Go, with a 4-byte header and 4-byte slots:

package page

import (
	"encoding/binary"
	"errors"
)

const (
	PageSize   = 8192
	headerSize = 4 // lower (2 bytes) + upper (2 bytes)
	slotSize   = 4 // offset (2 bytes) + length (2 bytes)
)

var ErrPageFull = errors.New("page full")

// Page is a slotted page: header, slot array growing forward,
// free space, then tuples growing backward from the end.
type Page [PageSize]byte

func NewPage() *Page {
	p := &Page{}
	p.setLower(headerSize)
	p.setUpper(PageSize)
	return p
}

func (p *Page) lower() uint16     { return binary.LittleEndian.Uint16(p[0:2]) }
func (p *Page) upper() uint16     { return binary.LittleEndian.Uint16(p[2:4]) }
func (p *Page) setLower(v uint16) { binary.LittleEndian.PutUint16(p[0:2], v) }
func (p *Page) setUpper(v uint16) { binary.LittleEndian.PutUint16(p[2:4], v) }

func (p *Page) NumSlots() int  { return int(p.lower()-headerSize) / slotSize }
func (p *Page) FreeSpace() int { return int(p.upper()) - int(p.lower()) }

func (p *Page) slot(i int) (off, length uint16) {
	base := headerSize + i*slotSize
	return binary.LittleEndian.Uint16(p[base:]), binary.LittleEndian.Uint16(p[base+2:])
}

func (p *Page) setSlot(i int, off, length uint16) {
	base := headerSize + i*slotSize
	binary.LittleEndian.PutUint16(p[base:], off)
	binary.LittleEndian.PutUint16(p[base+2:], length)
}

// Insert copies tuple into the page and returns its slot number.
func (p *Page) Insert(tuple []byte) (int, error) {
	if len(tuple)+slotSize > p.FreeSpace() {
		return 0, ErrPageFull
	}
	upper := p.upper() - uint16(len(tuple))
	copy(p[upper:], tuple)
	slot := p.NumSlots()
	p.setSlot(slot, upper, uint16(len(tuple)))
	p.setUpper(upper)
	p.setLower(p.lower() + slotSize)
	return slot, nil
}

func (p *Page) Get(slot int) ([]byte, bool) {
	if slot < 0 || slot >= p.NumSlots() {
		return nil, false
	}
	off, length := p.slot(slot)
	if off == 0 { // dead slot
		return nil, false
	}
	return p[off : off+length], true
}

// Delete marks the slot dead. The tuple bytes stay until Compact runs.
func (p *Page) Delete(slot int) {
	if slot >= 0 && slot < p.NumSlots() {
		p.setSlot(slot, 0, 0)
	}
}

// Compact packs live tuples against the end of the page again.
// Slot numbers don't change, so (page, slot) addresses stay valid.
func (p *Page) Compact() {
	type live struct {
		slot int
		data []byte
	}
	var tuples []live
	for i := 0; i < p.NumSlots(); i++ {
		if t, ok := p.Get(i); ok {
			tuples = append(tuples, live{i, append([]byte(nil), t...)})
		}
	}
	upper := uint16(PageSize)
	for _, t := range tuples {
		upper -= uint16(len(t.data))
		copy(p[upper:], t.data)
		p.setSlot(t.slot, upper, uint16(len(t.data)))
	}
	p.setUpper(upper)
}

The behavior to look at is the gap between Delete and Compact:

p := page.NewPage()
a, _ := p.Insert([]byte("ana"))
b, _ := p.Insert([]byte("bruno"))

p.Delete(a)
fmt.Println(p.FreeSpace()) // 8172: deleting freed nothing yet

p.Compact()
fmt.Println(p.FreeSpace()) // 8175: the 3 bytes of "ana" are back

v, _ := p.Get(b)
fmt.Println(string(v)) // bruno, same slot as before

Deleting only marks the slot. The bytes for "ana" stay where they were until something compacts the page, and the dead slot itself is never reclaimed in this version. PostgreSQL works the same way. PageRepairFragmentation in src/backend/storage/page/bufpage.c does what Compact does, and it only runs during pruning or VACUUM, never as part of a DELETE. Unlike this toy, PostgreSQL does reuse dead line pointers once VACUUM has confirmed nothing points at them anymore.

A PostgreSQL heap page, byte by byte

PostgreSQL stores table rows in a heap: an unordered pile of pages where a new row goes into whichever page has room. The real page header is PageHeaderData in src/include/storage/bufpage.h, 24 bytes:

typedef struct PageHeaderData
{
	PageXLogRecPtr pd_lsn;      /* LSN of the last WAL record that changed this page */
	uint16      pd_checksum;
	uint16      pd_flags;
	LocationIndex pd_lower;     /* offset to start of free space */
	LocationIndex pd_upper;     /* offset to end of free space */
	LocationIndex pd_special;   /* offset to start of special space */
	uint16      pd_pagesize_version;
	TransactionId pd_prune_xid;
	ItemIdData  pd_linp[FLEXIBLE_ARRAY_MEMBER]; /* line pointers */
} PageHeaderData;

pd_lower and pd_upper are the same two numbers as in the Go version. pd_special is the start of an area at the very end of the page that index pages use for their own metadata (a B-tree page keeps its sibling links there). Heap pages don't use it, so it points at 8192. pd_lsn ties the page to the write-ahead log, which becomes important in the WAL article.

Each line pointer is an ItemIdData, packed into 4 bytes: 15 bits of offset, 2 bits of state (unused, normal, redirect, dead) and 15 bits of length. Fifteen bits is enough to address any byte in a 32 KB page, which is the largest BLCKSZ PostgreSQL supports.

You can see all of this on a live table with pageinspect:

CREATE EXTENSION pageinspect;
CREATE TABLE users (id int, name text);
INSERT INTO users VALUES (1, 'ana'), (2, 'bruno');

SELECT lower, upper, special, pagesize
FROM page_header(get_raw_page('users', 0));

 lower | upper | special | pagesize
-------+-------+---------+----------
    32 |  8120 |    8192 |     8192

lower is 32: the 24-byte header plus two 4-byte line pointers. upper is 8120 because the two rows took 72 bytes at the end of the page. The line pointers show where:

SELECT lp, lp_off, lp_len, t_ctid
FROM heap_page_items(get_raw_page('users', 0));

 lp | lp_off | lp_len | t_ctid
----+--------+--------+--------
  1 |   8160 |     32 | (0,1)
  2 |   8120 |     34 | (0,2)

ana takes 32 bytes: a 23-byte tuple header padded to 24, 4 bytes for the int, and 4 for the text (a 1-byte length header plus 3 characters). bruno takes 34, but it starts 40 bytes below ana because every tuple begins at an 8-byte-aligned offset. A table of tiny rows spends most of each page on headers and padding, which is one reason a narrow PostgreSQL table takes more disk space than you'd guess from the column types.

t_ctid is the row's own address, (block, line pointer). PostgreSQL exposes it as the hidden ctid column, and it is what every index stores to point back at the heap.

An update writes a new row

Now change one name:

UPDATE users SET name = 'ana maria' WHERE id = 1;
SELECT ctid, * FROM users;

 ctid  | id |   name
-------+----+-----------
 (0,2) |  2 | bruno
 (0,3) |  1 | ana maria

Row 1 moved from (0,1) to (0,3). PostgreSQL never overwrites a row in place. The UPDATE wrote a complete new version into the free space, and the old version is still on the page with its t_xmax set to the updating transaction and its t_ctid pointing at (0,3). Readers whose snapshot started before the update keep seeing ana; everyone else follows the chain to ana maria. That is MVCC, and it gets its own article. What matters for the page is that every update costs a full new tuple worth of free space, and the old one stays until it is pruned or vacuumed.

This also explains the fillfactor storage parameter. With the default of 100, pages are packed full on insert, and an update usually has to put the new version on a different page. A table that is updated heavily can use fillfactor = 90 or lower so that updates fit on the same page, which lets PostgreSQL use HOT (heap-only tuple) updates and skip touching the indexes.

How many rows fit

A quick way to see density is to group rows by the block part of their ctid:

CREATE TABLE big (id int, name text);
INSERT INTO big SELECT i, md5(i::text) FROM generate_series(1, 100000) i;

SELECT (ctid::text::point)[0]::int AS block, count(*)
FROM big GROUP BY 1 ORDER BY 1 LIMIT 3;

 block | count
-------+-------
     0 |   120
     1 |   120
     2 |   120

Each row is a 24-byte header, 4 bytes of int, 33 bytes of text, rounded up to 64, plus a 4-byte line pointer: 68 bytes. (8192 - 24) / 68 is 120. The 100,000 rows need 834 pages, about 6.8 MB, for roughly 3.6 MB of actual values.

Values that don't fit comfortably are moved elsewhere. When a row exceeds about 2 KB (TOAST_TUPLE_THRESHOLD), PostgreSQL first tries to compress its large columns and then moves them to a separate TOAST table, leaving a small pointer in the heap tuple. That's why a SELECT id FROM docs can be fast even when every row carries a 1 MB body: the heap pages only hold the pointers.

Files, forks and segments

A table in PostgreSQL is more than one file:

SELECT pg_relation_filepath('users');
-- base/5/16430

Under the data directory, base/5 is the database and 16430 is the table's relfilenode. Next to it you'll find:

• 16430, the main fork with the heap pages
• 16430_fsm, the free space map, which records roughly how much room each page has so an insert can find a page without reading them all
• 16430_vm, the visibility map, two bits per page saying whether all rows on it are visible to everyone and whether they are all frozen; VACUUM uses it to skip pages, and index-only scans use it to skip the heap
• 16430.1, 16430.2 and so on once the main fork passes 1 GB, since PostgreSQL splits relations into 1 GB segments

A consequence that surprises people: VACUUM marks space inside pages as reusable but almost never returns it to the operating system. It can only truncate empty pages at the very end of the file. A table that grew to 50 GB and then had 90% of its rows deleted stays at 50 GB, with lots of free space that new rows will fill. Shrinking the file needs VACUUM FULL or pg_repack, which rewrite the table.

SQLite: the same page, organized by key

SQLite uses the same slotted layout with different names. Every page in the file belongs to a B-tree, a freelist, or an overflow chain, and a B-tree page has an 8 or 12 byte header, an array of 2-byte cell pointers, and cell content packed from the end of the page. Page 1 also carries the 100-byte database header that starts with SQLite format 3\0.

The larger difference is where rows live. SQLite has no heap. A table is a B-tree keyed by rowid, and the rows sit in its leaf pages in key order. InnoDB does the same with the primary key. This is called a clustered or index-organized table, and it changes the trade-offs:

• A lookup by primary key reaches the row directly at the bottom of the tree. In PostgreSQL the index lookup ends with a ctid, and fetching the row is one more page read in the heap.
• A range scan by primary key reads rows that are physically next to each other. A PostgreSQL heap only keeps that order if you insert in key order or run CLUSTER, which is a one-time rewrite.
• A secondary index can't point at a physical location, because rows move when leaf pages split. SQLite and InnoDB secondary indexes store the primary key, so a lookup walks two trees. PostgreSQL indexes store the ctid, which goes straight to the page but has to be updated whenever a row version moves to another page.
• Random primary keys like UUIDv4 spread inserts across the whole clustered tree and split pages everywhere. A heap doesn't care about key order and just appends.

If your build of the sqlite3 shell includes the dbstat virtual table, you can see the pages:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users VALUES (1, 'ana'), (2, 'bruno');

SELECT name, pageno, pagetype, ncell, unused FROM dbstat;

sqlite_schema|1|leaf|1|3896
users|2|leaf|2|4054

The table is one leaf page holding two cells. The schema table lives on page 1. It has less free space because that page also holds the 100-byte file header, and its one cell stores the full CREATE TABLE text.

The buffer pool

Reading a page from disk on every access would make even a cached workload crawl, so every engine keeps a pool of pages in memory. In PostgreSQL this is shared_buffers, 128 MB by default, split into 8 KB slots. Each slot has a descriptor (BufferDesc in src/include/storage/buf_internals.h) with the page's identity (relation, fork, block number), a pin count, a usage count and a dirty flag.

A pin says "someone is using this page right now, don't evict it". The dirty flag says the page in memory differs from the one on disk. Neither commit nor UPDATE writes the data page itself. Dirty pages are written later by the background writer, by the checkpointer, or by a backend that needs the slot. Durability at commit comes from the WAL, not from the data pages.

When the pool is full, PostgreSQL picks a victim with a clock sweep (StrategyGetBuffer in src/backend/storage/buffer/freelist.c). A hand walks around the buffers in a circle. Every access bumps a buffer's usage count, up to 5. The hand decrements the count of each unpinned buffer it passes and evicts the first one that reaches zero. A page used often survives several trips of the hand; a page touched once is gone on the next one. It approximates LRU without having to reorder a list on every access.

A small version in Go, using the Page from above:

type frame struct {
	pageID uint32
	page   Page
	pins   int
	usage  int
	dirty  bool
	valid  bool
}

type BufferPool struct {
	file   *os.File
	frames []frame
	table  map[uint32]int // page id -> frame index
	hand   int
}

func NewBufferPool(file *os.File, size int) *BufferPool {
	return &BufferPool{file: file, frames: make([]frame, size), table: map[uint32]int{}}
}

func (bp *BufferPool) Fetch(id uint32) (*Page, error) {
	if i, ok := bp.table[id]; ok {
		f := &bp.frames[i]
		f.pins++
		f.usage = min(f.usage+1, 5)
		return &f.page, nil
	}

	i, err := bp.victim()
	if err != nil {
		return nil, err
	}
	f := &bp.frames[i]
	if f.valid {
		if f.dirty {
			if _, err := bp.file.WriteAt(f.page[:], int64(f.pageID)*PageSize); err != nil {
				return nil, err
			}
		}
		delete(bp.table, f.pageID)
	}

	f.page = Page{}
	if _, err := bp.file.ReadAt(f.page[:], int64(id)*PageSize); err != nil && err != io.EOF {
		return nil, err
	}
	f.pageID, f.pins, f.usage, f.dirty, f.valid = id, 1, 1, false, true
	bp.table[id] = i
	return &f.page, nil
}

func (bp *BufferPool) Unpin(id uint32, dirty bool) {
	f := &bp.frames[bp.table[id]]
	f.pins--
	f.dirty = f.dirty || dirty
}

// victim runs the clock: skip pinned frames, decrement usage counts,
// and take the first frame that reaches zero.
func (bp *BufferPool) victim() (int, error) {
	for range len(bp.frames) * 6 {
		i := bp.hand
		bp.hand = (bp.hand + 1) % len(bp.frames)
		f := &bp.frames[i]
		switch {
		case !f.valid:
			return i, nil
		case f.pins > 0:
			continue
		case f.usage == 0:
			return i, nil
		}
		f.usage--
	}
	return 0, errors.New("no unpinned buffers available")
}

The loop runs six times around the ring because a usage count can be at most 5. If every unpinned frame has been decremented five times and still nothing is free, everything is pinned. PostgreSQL raises the same error message in that case. Callers must Unpin what they Fetch, and a page leaves the pool only after its last pin is gone. Forgetting an Unpin here leaks a frame forever, which is why PostgreSQL tracks pins per backend and complains loudly about leaked ones at the end of a transaction.

What this version leaves out is most of the real work: concurrency. PostgreSQL splits the mapping table into 128 partitions with separate locks, packs the pin count, usage count and flags into one atomic integer so a pin needs no lock, and has a separate content lock per buffer so readers and writers of the same page don't collide.

Big scans use a ring

Clock sweep has a weak spot. A sequential scan of a 20 GB table would touch every page once, bump each usage count to 1, and push the hot pages out of the pool on its way through. PostgreSQL avoids that with buffer access strategies: when a sequential scan reads a table bigger than a quarter of shared_buffers, it uses a small private ring of 256 KB and keeps reusing those buffers instead of taking over the pool. VACUUM and COPY get rings of their own. A nightly report that scans a big table doesn't empty your cache.

You can look into the pool with pg_buffercache:

CREATE EXTENSION pg_buffercache;

SELECT c.relname, count(*) AS buffers, sum(b.isdirty::int) AS dirty
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 5;

That's also what EXPLAIN (ANALYZE, BUFFERS) reports. shared hit is a page found in the pool and read is one that had to come from the OS. PostgreSQL 18 includes buffer counts in EXPLAIN ANALYZE without asking.

Two caches

PostgreSQL reads through the operating system, so a page that misses shared_buffers may still be in the kernel's page cache. That's why the usual advice is to give shared_buffers about 25% of RAM and leave the rest to the OS, instead of 80% as you might expect. A read in EXPLAIN doesn't necessarily mean a disk read, only that PostgreSQL had to ask the kernel. PostgreSQL 18 also added asynchronous I/O (io_method, with worker as the default and io_uring on Linux), so sequential scans, bitmap heap scans and VACUUM can have several reads in flight at once instead of waiting on each one.

SQLite has the same layering on a smaller scale. Its pager keeps a per-connection page cache, sized with PRAGMA cache_size, about 2 MB in a stock build, and relies on the OS for the rest.

When a page write goes half-wrong

An 8 KB page is two 4 KB OS pages, and the disk only guarantees that one sector or one flash page is written atomically. If power goes out in the middle of writing a data page, the file can end up with the first half new and the second half old: a torn page. Neither version is correct, and nothing in the page says so.

PostgreSQL deals with this from two directions. With full_page_writes on (the default), the first change to a page after each checkpoint writes the whole page image into the WAL, so recovery can restore a clean copy before replaying changes. Data checksums catch corruption when the page is read back; since PostgreSQL 18, initdb enables them by default, and SHOW data_checksums will tell you about your cluster. SQLite's rollback journal and WAL modes solve the same problem with their own page copies. All of these depend on the log, so the full story waits for the WAL article.

Choosing between layouts

If you're choosing a database, the page format is rarely the reason. It still decides a lot of everyday behavior.

A heap like PostgreSQL's suits write-heavy tables with several secondary indexes and keys that arrive in random order. Inserts go wherever there is space, and indexes point straight at a page. You pay with an extra heap visit on every index lookup, row versions that pile up until vacuum, and files that don't shrink by themselves.

A clustered layout like SQLite's or InnoDB's suits tables that are mostly read by primary key or by primary-key ranges, like an orders table read per customer through a (customer_id, order_id) key. The row is at the end of the index walk and neighbors are physically close. You pay with page splits on random keys and secondary lookups that walk two trees.

The page size is usually a fixed fact of the engine. SQLite is the one case where you choose it, and the default 4 KB matches the OS page and most SSDs; raise it only if you measure large sequential reads that benefit.

Conclusion

Every row lives in a slot of a fixed-size page, every page is addressed by its number, and the buffer pool decides which of those pages are in memory. Most storage behavior people get surprised by comes from that layout: the ctid that changes on update, the table that doesn't shrink after a DELETE, the shared hit count in EXPLAIN.

Next is B-Trees: how those pages are linked into a tree that stays shallow with millions of rows, what happens when a leaf page fills up, and how PostgreSQL's nbtree handles splits without stopping readers.