How Is Database Data Physically Stored?

SQL sees a table of rows. The engine sees records — rows encoded as bytes — grouped into fixed-size pages, stored in a database file that is nothing but an array of pages. Below the engine, the file system and the device speak in storage blocks (typically 4 KB), and the SSD stores those in NAND pages and erase blocks that its controller remaps freely. Each layer has its own unit, and the confusion in most performance conversations comes from mixing them: "the query touched 100 rows" says nothing about cost until you know how many pages those rows were on.

Nothing at the top of the hierarchy has bytes of its own. A table is a catalog entry: a name, a column list, a pointer to the file. A row is what a query *returns*; it is assembled from a record at the moment the page is in memory. The first physical thing is the record, and the first thing the engine can actually read is the page.

Every value has to become a byte string of known length, or of a length that can be discovered without guessing. An INT is four bytes, little-endian on x86; a BIGINT eight; a TIMESTAMP eight (microseconds since 2000 in PostgreSQL); a BOOLEAN one. These fixed-size types are the easy case: their position inside a record is known from the table definition alone, so the engine reaches them by arithmetic, header + bitmap + 0 for the first, + 4 for the next.

TEXT, VARCHAR, BYTEA, JSONB and NUMERIC are variable-size. Their bytes are preceded by a length word (PostgreSQL's varlena header: 1 or 4 bytes), and because they push everything after them around, the fixed part of the record carries an offset to each one — or the engine walks the lengths in order, which is what PostgreSQL does and why a column at the end of a wide row is a little more expensive to reach than one at the front.

NULL is not a value and gets no bytes. A NULL bitmap near the head of the record has one bit per column; a set bit means "skip this column entirely, there is nothing here". That is why NULL costs almost nothing in storage and why WHERE col IS NULL cannot be answered by looking at the data area at all — the NULL-semantics interview question covers what it does to comparisons.

Put together, a record for users (id INT, name TEXT, email TEXT) is a row header (its total length, its column count, flags such as "has NULLs", and whatever the engine needs for transactions), the NULL bitmap, the fixed-size id, an offset for name and one for email, then the two strings. The next lesson, Records on Disk, takes this apart byte by byte; here the point is only that every part is either fixed-size or reachable through a length or an offset. Nothing is delimited, nothing is scanned character by character, nothing needs escaping.

The header is what makes a record self-describing enough to be skipped: read its length word and you know where the next record would start, without understanding its contents. That is the property the naive text file lacked.

Records are packed into pages of a fixed size chosen when the cluster is created — 8192 bytes in PostgreSQL, 16384 in InnoDB. A page opens with a page header: a checksum or CRC over the page contents so a torn or corrupted page is detected on read rather than silently trusted; the LSN of the last WAL record that changed it, which is how recovery decides whether a logged change is already on disk; and two pointers, lower and upper, that bound the free space. Between the header and the records sits the slot directory, the subject of Slotted Pages; the records themselves fill the page from the end backwards.

Metadata about pages lives outside them: a free-space map records roughly how much room each page has so an insert can be sent to a page that will take it, and a visibility map (PostgreSQL) marks pages whose tuples are all visible to every transaction so index-only scans and VACUUM can skip them. The catalog holds the rest — which file, how many pages, what the columns mean.

The number that matters most on a page is not in any field: it is how many records fit. At ~77 bytes plus a 4-byte slot, an 8 KB page holds about a hundred of these users records; at 400 bytes, about twenty. Row width decides how many pages a scan touches and how many rows an index lookup gets for free with the one it wanted.

A database file is Page 0, Page 1, Page 2, … with no separators and no index of its own: page N is at byte N × 8192, so a page number is an address, not a search key. PostgreSQL keeps one such file per table (and one per index) at base/<database OID>/<relfilenode>, split into 1 GB segments named 24576, 24576.1, 24576.2. InnoDB keeps the table and all its indexes in one <table>.ibd file, itself an array of 16 KB pages grouped into extents of 64.

Below the file, the kernel and the device work in 4 KB blocks, and the device promises atomicity for one block at a time. An 8 KB page is two of them, so a power cut between the two writes produces a torn page: the first half new, the second half old, the checksum wrong. Engines defend against this above the device: PostgreSQL writes a full copy of each page into the WAL the first time it is modified after a checkpoint (full_page_writes), InnoDB writes every page twice through its doublewrite buffer. Write-Ahead Logging takes it from there.

Latency across the layers is the reason any of this matters. A page already in the buffer pool costs ~100 ns of pointer chasing; in the OS page cache, a few microseconds and a system call; on an SSD, 50–100 µs; on a spinning disk, 5–10 ms of seek. A query plan is, at bottom, a bet on how many pages will be at which level.