Chapter 3: How Data Lives on Disk — Files, Pages, and Blocks

Before we learn SQL and schema design, let's understand what's happening at the lowest level. As a C programmer, you'll appreciate knowing what the database is actually doing with your data on disk.

The Fundamental Constraint: Disk is Slow

Everything in database design traces back to one physical reality:

RAM is 1000x faster than SSD, and 100,000x faster than HDD. Every database optimization exists to minimize disk I/O.

Pages: The Unit of I/O

Databases don't read individual rows from disk. They read pages (also called blocks) — fixed-size chunks, typically 8 KB (PostgreSQL) or 16 KB (MySQL/InnoDB).

Why pages?

• Disk hardware reads in blocks anyway (512B or 4KB sectors)
• Amortizes the cost of a disk seek across many rows
• Simplifies memory management (fixed-size buffers)
• Enables efficient caching (cache whole pages in RAM)

Inside a Page

A typical page layout (PostgreSQL style):

Row Storage (Heap)

In a heap (the default storage for a table), rows are stored in no particular order. New rows go wherever there's free space.

// Conceptual C representation of a row (tuple) on disk:
struct TupleHeader {
    uint32_t xmin;        // Transaction that created this row
    uint32_t xmax;        // Transaction that deleted this row (0 = alive)
    uint16_t infomask;    // Flags (null bitmap, visibility)
    uint16_t natts;       // Number of attributes
    uint8_t  null_bitmap[]; // Which columns are NULL
};
// Followed by actual column data (packed, variable-length)

The Buffer Pool (Page Cache)

Reading from disk every time would be impossibly slow. Databases maintain a buffer pool — a large region of RAM that caches frequently-accessed pages.

Key concepts:

• Clean page — in-memory copy matches disk. Can be evicted freely.
• Dirty page — modified in RAM but not yet written to disk. Must be flushed before eviction.
• Buffer hit — page already in RAM. Fast (~100ns).
• Buffer miss — page not in RAM, must read from disk. Slow (~100μs SSD).
• Hit ratio — percentage of accesses served from RAM. Target: >99%.

Row-Oriented vs Column-Oriented Storage

File Layout on Disk

A PostgreSQL database on disk looks like:

# PostgreSQL data directory
/var/lib/postgresql/data/
├── base/                    # Database files
│   └── 16384/              # One directory per database (OID)
│       ├── 16385           # Table "users" — main data file (heap)
│       ├── 16385_fsm       # Free Space Map (which pages have room)
│       ├── 16385_vm        # Visibility Map (which pages are all-visible)
│       ├── 16386           # Index on users.id
│       └── ...
├── pg_wal/                 # Write-Ahead Log (transaction log)
│   ├── 000000010000000000000001
│   └── ...
└── postgresql.conf         # Configuration

Why This Matters

Understanding physical storage explains:

• Why indexes speed up queries — they tell the DB which page to read instead of scanning all pages
• Why full table scans are slow — must read every page from disk
• Why RAM matters — more RAM = more pages cached = fewer disk reads
• Why row size matters — smaller rows = more rows per page = fewer pages to read
• Why sequential access is faster — reading consecutive pages avoids random seeks