# DDIA Ch. 4: the column store reads only what it needs

## Concept

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.

## Provenance

*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.

## Prerequisites

The surprise is about physical layout, not query engines. These help; each line
notes where it shows up.

1. **Row-major vs column-major storage** — a row store keeps a row's columns
   next to each other on disk; a column store keeps all of one column's values
   together. This single choice is the whole exercise.
2. **What a table scan reads** — to aggregate a column with no usable index, the
   engine reads the table's pages start to finish. In a row store those pages
   hold *every* column, interleaved.
3. **Column pruning (projection pushdown)** — a column store can skip the file
   regions for columns a query doesn't reference. The row store cannot; the
   unwanted columns share pages with the wanted ones.
4. **Parquet** — a columnar file format that stores each column as its own
   chunk with its own size, which is how we can measure "bytes the query must
   read" exactly.

### Where to learn them

- **Row vs column stores (#1–#3):** DDIA §"Column-Oriented Storage" itself is
  the best short treatment; the DuckDB and ClickHouse docs both have clear
  "why columnar" explainers.
- **Parquet layout (#4):** the Apache Parquet format docs (row groups → column
  chunks → pages) — enough to see why per-column sizes are readable from the
  file's metadata.

If only one is new, make it #1 — everything follows from row-major vs
column-major.

## Environment

Deterministic data, so the sizes and bytes-read reproduce exactly; wall-clock
timings depend on your hardware and will differ (the ratio is the point):

- macOS 26.5.2 (Darwin 25.5.0), arm64
- DuckDB 1.5.5 on Python 3.12.13 (run via `uv`); `sqlite3` from the standard
  library as the row store — no Docker
- One synthetic fact table, 10,000,000 rows × 30 columns

## Mental model

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_key
```

- **Row store (SQLite).** Rows are stored whole. A full scan reads every page,
  and every page holds all 30 columns interleaved — so the query reads the
  entire ~1.2 GB table to get at 2 columns.
- **Column store (Parquet, read by DuckDB).** Each column is a separate chunk.
  The query reads only the `date_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`.

## Setup

```
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 layouts
```

Do **not** read the output yet — make each prediction first.

## Steps

### Step 1 — the setup (calibration)

**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?

**Do.** Run the script; read the size block.

**Observe.**

```
fact table: 10,000,000 rows x 30 columns
query: SELECT date_key, sum(quantity) GROUP BY date_key   (touches 2 of 30 columns)

on-disk size of the wide table:
    row store    (SQLite) =   1248.4 MB
    column store (Parquet)=   1347.6 MB
```

Roughly the same size on disk either way (~1.3 GB). Same data, same 30 columns.
So far the two layouts look interchangeable.

### Step 2 — bytes read (the surprise)

**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.")

**Do.** Read the bytes block.

**Observe.**

```
bytes the query must READ:
    row store    scans every column of every row =   1248.4 MB  (100%)
    column store reads only date_key + quantity  =     7.56 MB  (0.6% of the file)
    -> the column store reads 165x less data
```

**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.

### Step 3 — the payoff, and the free columns

**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?

**Observe.**

```
wall-clock, best of 3 (yours will differ; timings are not the point, bytes are):
    row store    (SQLite) =   2585.7 ms
    column store (DuckDB) =     14.0 ms   -> 185x faster

the 26 unused columns are free in the column store:
    narrow (4-col) Parquet: query reads 7.56 MB, 12.7 ms
    wide  (30-col) Parquet: query reads 7.56 MB, 14.0 ms   (same bytes read; the extra columns cost nothing)
```

~**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.

## What you should see

- Both layouts ~1.3 GB on disk; same data.
- Bytes read for a 2-column query: row store **100%** (~1,248 MB) vs column
  store **0.6%** (7.56 MB) — **~165× less**.
- Wall-clock ~**180× faster** for the column store (corroborating; partly the
  engine).
- The column store reads the **same** 7.56 MB whether the table is 4 or 30
  columns wide.

## Why

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.)

**The boundary — this is an OLAP win, and an OLTP loss.** Column layout wins
when you scan many rows but few columns. Invert it — fetch or update *one whole
row* by key, `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.

### Go deeper

1. **Widen it.** Bump `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.
2. **Ask for a whole row.** Add `SELECT * FROM facts WHERE id = 5000000` to both
   and compare. Predict which layout wins now — this is the OLTP case where the
   result flips.
3. **Select more columns.** Change the query to touch 10 of the 30 columns.
   Predict how the column store's bytes-read scales (linearly in columns
   touched) and where it would stop beating the row store.