Storage Layout: Row vs Columnar

Concept. A storage layout is the on-disk arrangement of a table. Row-oriented stores all columns of one row together; column-oriented stores all values of one column together.

Intuition. Row-oriented Listens packs each listen (listen_id, user_id, song_id, rating, listen_time) as one chunk on disk. Column-oriented stores all the user_id values together, then all the rating values, then all the listen_time values. "Average rating across a billion listens" reads one column instead of every row.

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 same logical Listens table in two physical layouts. The row-oriented file (left) packs whole rows per page, so reading row 5 returns all four columns in one read and a write is one append; the columnar files (right) give 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 packs variable-length rows from the top down and a fixed-size slot array from the bottom up, with free space shrinking in the middle as the two grow toward each other. Each slot is an (offset, length) pointer (S0 to Row A, S1 to Row B), so an update in place is free when the row keeps its size. If a row outgrows its footprint, the page moves it and repoints its slot, which keeps external references like page_id, slot_id 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 is columnar made practical. Pure columnar (grey, left) stores one file per column, which is ideal for a scan but turns a single row into a seek per column and a wide partitioned table into roughly 100,000 tiny files. Parquet (emerald, right) keeps the columnar win, reading and compressing each column on its own, but packs the columns into row-groups inside one file: a row stays local, the file count collapses, and the row-group becomes the natural unit for writes and parallel scans. The footer's per-row-group min/max statistics let a predicate skip whole groups unread. The only cost is a sliver of compression, since an encoding run cannot span a row-group boundary, a small price for the gains in row access, file count, and write and parallelism simplicity.

Next

Case Study 3.1: BigQuery Scaling → The canonical columnar-analytics system that powers Google and is the blueprint for modern cloud data warehouses.