Storage Layout: Row vs Columnar

Concept. A storage layout specifies how a table's bytes appear 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.

Module 2 shrank each item so a too-big problem fit. 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 operating system:

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

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

  • Why pages? When you query, the database hauls in whole 64 MB chunks, not piecemeal records. I/O devices are slow, so bulk operations (one 64 MB read vs. 2,000 separate 32 KB reads) keep cost in check.

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 two of them. 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 stores a fixed-size slot array from the bottom upward. Free space remains between them. Each slot stores an (offset, length) pointer, so S0 can point to Row A and S1 can point 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 page moves the row to a new location 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 5:1 to 10:1, around 80 to 90% or more: 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 defeats most encoders.

The scaling law: a column compresses better the more rows it has, because repetition only emerges at scale. That is why BigQuery, Snowflake, and Redshift scan petabytes efficiently: at that scale the columnar files compress heavily, so each byte read covers far more rows. The actual encodings (RLE, dictionary, delta, quantization) are the subject of the next page.

Columnar Made Practical: Parquet

Pure columnar in its textbook form gives each column its own file. That wins the scan but loses everywhere else: one row becomes a seek per column, and a wide table partitioned many ways explodes into a hundred thousand tiny files, which object stores like S3 punish.

Parquet is the open-source columnar format that fixes this. It is 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.

Pure columnar (grey) puts each column in its own file, so one row needs a seek per column and a wide table explodes into 100,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 a wide partitioned table produces roughly 100,000 files. Parquet keeps per-column compression and scan behavior, and it stores the columns as chunks inside each row-group. A row stays within one row-group. 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.