A query over a 30-column, 10-million-row table that references just 2 columns makes a row store read 100% of the data and a column store read 0.6% — 165× less — purely from row-major vs column-major layout. And the 26 unused columns cost the column store nothing.
A data-warehouse fact table is often ~100 columns wide, but a typical analytics query touches only a handful. In a row store, a table scan reads every column of every row — even the 28 you never mention — because a row's columns are stored contiguously. A column store keeps each column separately, so it reads only the columns the query references. On a 30-column, 10-million-row table you will run a query that needs just 2 columns and watch the column store read 0.6% of the data the row store must read — and answer ~180× faster.
Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 4 — Storage and Retrieval, §"Column-Oriented Storage":
Although fact tables are often over one hundred columns wide, a typical data warehouse query accesses only four or five of them at one time.
Example 4-1 in the book is exactly this: a query over the whole fact_sales table that needs only date_key, product_sk, and quantity. Here you measure what row-major vs column-major layout does to the bytes such a query must read.
The surprise is about physical layout, not query engines. These help; each line notes where it shows up.
uv) and sqlite3 from the standard library as the row store — no Docker. One synthetic fact table, 10,000,000 rows × 30 columns.
We build one fact table: date_key, product_sk, quantity, and 26 filler columns (30 total). We store it two ways and run one query that references only two columns:
SELECT date_key, sum(quantity) FROM facts GROUP BY date_keydate_key and quantity chunks; the other 28 columns are never touched.The measurement that matters is bytes read — pure storage layout, no engine cleverness. The whole exercise is one script, code/column_scan.py.
cd study/ddia/ch04/code
# DuckDB via uv (installs it into a throwaway env with a compatible Python);
# sqlite3 is in the standard library. No Docker.
uv run --with duckdb python3 column_scan.py # ~30 s: builds 10M rows, both layoutsDo not read the output yet — make each prediction first.
Predict. The table is 30 columns, 10M rows, ~1.2 GB on disk in both layouts. The query references 2 columns. Before looking: what fraction of the table's bytes does each store have to read to answer it?
Roughly the same size on disk either way (~1.3 GB). Same data, same 30 columns. So far the two layouts look interchangeable.
Predict. Now the real question. To compute sum(quantity) GROUP BY date_key, how many megabytes must each engine actually read off disk? (The instinct: "a database only reads what the query needs — so both read a little.")
0.6%. The row store reads all 1,248 MB — every one of the 30 columns, because they are interleaved on the pages it must scan. The column store reads 7.56 MB: just the two column chunks the query names. Same query, same data, 165× less data read, purely from how the bytes are laid out.
Predict. 165× less data read — what does that do to wall-clock time? And: does it matter that the table has 26 columns the query never touches?
~185× faster, tracking the bytes. And the last two lines are the deeper point: a 4-column table and a 30-column table make the column store read the same 7.56 MB — the 26 extra columns are never opened. In a column store, the columns you don't query are free.
The difference is entirely row-major vs column-major layout. A row store keeps each row's columns contiguous, so the physical unit on disk — a page — holds all 30 columns of the rows it contains. To sum one column across all rows, the engine must read every page, which means pulling all 30 columns off disk even though it decodes only 2. The cost of a scan therefore scales with the width of the row: add columns and every scan gets slower, even queries that never mention them.
A column store stores each column as its own contiguous region (in Parquet, a column chunk with its own byte range). Reading date_key and quantity means reading two chunks — 7.56 MB — and the other 28 chunks are simply not fetched. The cost of a scan scales with the columns the query references, not the width of the table. Since analytics queries famously touch a handful of columns from very wide fact tables, the win is the ratio of total columns to used columns — here about 165×. (The wall-clock 185× includes DuckDB's vectorized execution on top; the 165× bytes-read figure is the pure storage effect and is what reproduces exactly.)
SELECT * FROM facts WHERE id = ?, the OLTP pattern — and the row store's contiguity wins decisively: it reads one row in one place, while the column store would gather 30 separate column chunks to reassemble it. That is exactly why operational databases are row stores and warehouses are column stores; the same data wants opposite layouts for opposite workloads.
FILLER to 96 (100 columns) and re-run. The column store's bytes-read and time barely move; the row store's climb with the table width. Predict the new ratio first.SELECT * FROM facts WHERE id = 5000000 to both and compare. Predict which layout wins now — this is the OLTP case where the result flips.Sources: DDIA 2e, Ch. 4, §"Column-Oriented Storage" (Example 4-1) · Apache Parquet format docs (column chunks) · DuckDB.