Storage Layout: Row vs Columnar

Concept. A storage layout specifies how a table's bytes are stored on disk. A row-oriented layout stores all columns of a row together. A column-oriented layout stores all values of a column together.

Intuition. Row-oriented Listens stores each listen (listen_id, user_id, song_id, rating, listen_time) as one contiguous record on disk. Column-oriented storage stores all user_id values together, then all rating values, then all listen_time values. AVG(rating) reads the rating values without reading the other columns.

In Module 2, we saw how to break down a too-big problem. This module is about the data that is still too big: how a table sits on disk, and how you scan, sort, and join it without ever holding it all in RAM.

How Tables Live on Disk

Two layers between your table and the OS:

  • OS Files. The OS's basic building blocks: unstructured bytes.

  • DbFiles. The database's custom files, packed into fixed 64 MB pages. Every table you query lives as a DbFile.

  • Why pages? When you query, the database hauls in 64 MB batches, not single records. I/O devices are slow, so bulk operations (one 64 MB read vs. 2,000 separate 32 KB reads) amortize costs.

Inside a 64 MB Page: The Storage Layout Question

Three options for how to lay out the bytes inside a page:

  1. Row storage. Pack all columns of one row together.

  2. Columnar storage. Pack all values of one column together.

  3. PAX (row-grouped columnar). Columns kept contiguous within row-groups. This is what Parquet does, covered below.

The first two are the fundamental contrast. Same data, two layouts, very different I/O for the same query:

Same logical Listens table stored two ways: row-oriented packs whole rows together into pages; columnar puts each column in its own file.

Figure 1. The physical layout decides which bytes a query reads. The same logical Listens table appears in both layouts. Row-oriented storage packs whole rows into a page, so reading row 5 returns all four columns in one read and a write is one append. Columnar storage gives each column its own file, so AVG(rating) touches only the rating file and skips the other three. Which layout wins depends on the query: SELECT AVG(rating) favors columnar (row layout wastes about 75% of the I/O on this 4-column toy, far more on wide tables), while SELECT * WHERE listen_id = 42 and inserts or updates favor row, since one record is one packed page rather than a seek-and-reassemble across four files.

Row-Oriented: The Slotted Page

A 64 MB row-oriented page uses a slotted page design to handle variable-length rows and the fragmentation that updates create.

Slotted page layout: a single 64MB row-oriented page. Data records grow from the top down; the slot array grows from the bottom up; free space sits in the middle and shrinks as both grow toward each other.

Figure 2. A 64 MB slotted page stores variable-length records from the top downward and a fixed-size slot array from the bottom upward. Free space remains between them. Each slot stores an (offset, length) pointer, so S0 points to Row A and S1 to Row B. An in-place update writes the row without moving it when the row keeps the same size. When a row grows, the DB moves the row to a new location inside the page and updates the slot, so references like page_id, slot_id remain stable.

Columnar: Compression at Scale

Same idea inverted: a page holds many values for a single column. Sequential bytes are all the same type, and that's exactly what compression algorithms exploit aggressively.

More data means better compression. A single column of Spotify's Listens barely compresses at nine rows, but at a hundred million rows the same column commonly shrinks 5x to 10x: popular songs repeat millions of times, active users have thousands of listens, timestamps run nearly sequential. Rows compress too, but far less, because the mix of types in one row makes it hard for encoders.

Compression saves I/O. Column encodings reduce the bytes a scan reads. Review RLE, dictionary, and delta encoding in M2 and vector quantization.

Columnar Made Practical: Parquet

Pure columnar gives each column its own file. That makes scans fast but loses everywhere else: one row becomes a seek per column, and a table with 1,000 columns needs 1,000 files.

Parquet is an open-source columnar format that fixes this. It uses a PAX layout: first slice the rows into row-groups (about 128 MB, roughly a million rows), then store each column contiguously inside the group as a column chunk, itself split into roughly 1 MB pages (Parquet's page is a finer unit than the 64 MB DbFile page above). One file holds the whole table, a row stays in one place, and a footer carries per-row-group min/max statistics, so a query reads the footer and skips the column data of groups that cannot match.

Directory partitioning. Data systems often partition Parquet datasets by a column such as year, storing files in separate directories:

  • year=2024/: skipped

  • year=2025/: read

  • year=2026/: skipped

For WHERE year = 2025, the query engine reads files in year=2025/ and skips the other year directories.

Pure columnar (grey) puts each column in its own file, so one row needs a seek per column and 1,000 columns produce 1,000 files; one Parquet file (emerald) splits rows into row-groups with each column a contiguous chunk inside the group, plus a footer of min/max stats that lets a query skip whole row-groups.

Figure 3. Parquet stores columnar data inside row-groups in one file. A pure columnar layout stores one file per column, so reading one row performs a seek per column and 1,000 columns produce 1,000 files. Parquet keeps per-column compression and scan behavior. A row stays within one row-group, and row-groups support writes and parallel scans. The footer stores per-row-group min/max statistics, and predicates can skip groups without reading their column chunks. Encodings stop at row-group boundaries, which reduces compression slightly.