Why layout decides cost
Bytes are bytes — so why would how you arrange the same records on disk change the price of a query by 50×? Because the cost of a question is mostly the cost of the bytes you must read to answer it, and layout decides which bytes those are.
The same data, two shelvings
Start from the puzzle. You have one table — say a million user records, each with twenty fields. The contents are fixed. Yet the same filter query can cost ten dollars under one storage choice and twenty cents under another, with no change to the data and no change to the hardware. The only difference is the order the bytes sit in. To see why, forget databases for a moment and walk into a library.
Imagine a library that has decided to shelve its books by complete book: all of book A's pages together, then all of book B's pages, and so on. This is the obvious, natural way — and it's great for the request "bring me the whole of book A." You walk to one spot, grab a contiguous run of pages, done.
Now someone asks a different question: "What is the third sentence of every book in the library?" Suddenly the layout fights you. Page 3 of each book is buried in the middle of its own pile, so you must walk to every shelf, open every book, read one sentence, and move on. You physically handle the entire library to collect a thread of text that would fit on a single page.
So picture a second library that shelved differently: it keeps all the page-3s together on one shelf, all the page-4s on the next, and so on by page number. The "third sentence of every book" question now reads one shelf and walks past the rest. The books are identical down to the letter. The shelving decided which question is cheap and which is ruinous.
A table is logically a grid of rows (records) and columns (fields). Storage is one-dimensional — a file is a line of bytes — so something must decide the order in which grid cells are written. There are two:
- Row-major (row storage): write all fields of record 1 contiguously, then all fields of record 2, and so on. Grabbing one whole record is a single contiguous read — like shelving by complete book.
- Columnar (column storage): write all values of field 1 contiguously, then all values of field 2, and so on. Grabbing one field across all records is a single contiguous read — like shelving by page number.
That's the entire idea. Same cells, same total bytes; only the traversal order differs. Everything else in this lesson — the 50× — falls out of this one choice plus the fact that storage is read in blocks, not cells: you pay for the chunks you touch, not the bytes you ultimately use.
The physical layout, byte by byte
Here is a four-record, four-column table written both ways. Read each layout left-to-right as the order bytes actually sit on disk.
LOGICAL TABLE
┌──────┬─────────┬───────┬────────┐
│ id │ name │ age │ city │
├──────┼─────────┼───────┼────────┤
│ 1 │ Ada │ 36 │ London │
│ 2 │ Bo │ 41 │ Oslo │
│ 3 │ Cy │ 29 │ Lagos │
│ 4 │ Di │ 52 │ Tokyo │
└──────┴─────────┴───────┴────────┘
ROW-MAJOR (one record, then the next — "by complete book")
┌─────────────────────────┬─────────────────────────┬─────────────────────────┐
│ 1 │ Ada │ 36 │ London │ 2 │ Bo │ 41 │ Oslo │ 3 │ Cy │ 29 │ Lagos ... │
└─────────────────────────┴─────────────────────────┴─────────────────────────┘
a whole row is contiguous → cheap to read/append/update ONE record
COLUMNAR (one field across all rows — "by page number")
┌───────────────┬───────────────────┬───────────────┬──────────────────────────┐
│ 1 │ 2 │ 3 │ 4 │ Ada │ Bo │ Cy │ Di │ 36 │ 41 │ 29 │ 52 │ London │ Oslo │ ... │
└───────────────┴───────────────────┴───────────────┴──────────────────────────┘
id column name column age column city column
a whole column is contiguous → read ONLY the fields a query needs
Row-major is the layout of a working system: an app saving an order, a sign-up writing a new user. You touch one whole record at a time, and you want that to be one quick, contiguous write. Asking such a system "what's the average age across all ten million users?" means dragging every full record past the CPU just to pluck one number from each — exactly the open-every-book problem.
Columnar is the layout of a reporting system: the questions are "average age," "count by city," "revenue last quarter" — each touching a few columns but most of the rows. Storing each column together means a query reads a handful of tidy shelves and ignores the rest of the warehouse.
Once each column is stored contiguously, three independent savings stack up — and together they are where the order-of-magnitude difference comes from:
- Column projection. A query selecting 2 of 20 columns reads only those two column-runs and seeks past the other eighteen. In row-major, those 18 unwanted fields are interleaved between the bytes you want, so the disk drags them in regardless. Reading less is the first and biggest win.
- Predicate pushdown via block statistics. A columnar file is split into blocks, and each block stores cheap per-column metadata — typically
minandmax. For a filter likeage > 90, the engine checks a block whosemax ageis 52 and skips the entire block unread. The data the query can't possibly match is never fetched. - Compression. A column is all one type with similar, often repeating values (a
citycolumn is a handful of names over and over). Same-kind, repetitive data compresses far better than a row's jumble of an int, a string, a date side by side. Better compression means fewer physical bytes on disk and fewer bytes to move per query — the win compounds with the first two.
A worked "bytes scanned" example
This is the whole argument in numbers. A table of 20 equal-width columns; a dashboard query reads 2 of them and filters on a third with a selective predicate. Assume each layout stores the same logical data.
| Stage | Row-major scan | Columnar scan |
|---|---|---|
| Columns the query needs | 2 of 20 (but layout forces all 20) | 2 of 20, read directly |
| After column projection | 100% of bytes | ~10% of bytes |
| After predicate pushdown | 100% (no per-block skip) | blocks failing min/max dropped → ~3% |
| After compression | ~50% (rows compress too, but less — mixed types) | ~1–2% (uniform columns squeeze ~3×) |
| Bytes actually read | ~0.5–1× | ~0.01–0.02× (≈ tens of × cheaper) |
These are order-of-magnitude figures, not exact ones — the point is the shape. Row-major data compresses too (often ~2×), so the real win comes from projection and pushdown; compression mainly compounds them. Projection cut the read by ~10×, pushdown skipped most of what remained, and compression shrank the rest — and on cloud object storage you are billed by bytes scanned, so this table is the invoice. That is how layout alone turns a ten-dollar query into a roughly ten-cent one. The flip side: the same columnar layout is a poor choice for "insert one new user," which must now touch twenty separate column-runs instead of one contiguous row.
So which workloads pick which
| Row-major | Columnar | |
|---|---|---|
| Best at | Read/write one whole record | Scan a few columns over many rows |
| Cheap operation | Insert, update, point lookup | Projection, filter, aggregate |
| Expensive operation | Aggregate over one field of all rows | Insert/update a single record |
| Compresses | Poorly (mixed types per row) | Well (one type, repetitive per column) |
| Workload (F3) | OLTP — transactional, row-at-a-time | OLAP — analytical, scan-heavy |
| Typical home | App database | Warehouse / data lake |
The analytical side — warehouses, and the lakes of files on object storage you met in F3 — runs scan-heavy questions all day, so it stores data columnar almost by reflex. The columnar formats you'll hear named (Parquet on disk, Arrow in memory) are concrete encodings of exactly this idea; we defer their specifics to lesson 04.
Why this matters for post-training data
This lesson is plain, general data engineering — it predates machine learning and governs every analytics warehouse on earth. But it is also precisely why post-training corpora are stored columnar, which is the claim lesson 04 opens with.
A post-training dataset is a big table: each row is one example, with columns like prompt, response, source, quality_score, language, token_count, safety_label. You almost never want every column of every row. You want "all rows where quality_score > 0.8 and language = en, just the prompt and response." That is a column-selective, row-selective read — projection plus pushdown — the exact pattern columnar is built to make cheap.
Post-training reads are almost always column- and row-selective: you filter on metadata columns and pull a few text columns, over corpora large enough that bytes-scanned is the dominant cost. Row-major would force every filtering pass to drag full examples — long response text included — past the reader just to check a score. Columnar reads the score column, skips blocks that fail the filter, and fetches the heavy text only for surviving rows. Same data, a fraction of the I/O.
That is the whole reason the field reaches for columnar storage for training corpora — not anything ML-specific, just this lesson applied to a particularly large, particularly scan-heavy table.