Learning Objectives
- Explain how row-oriented and columnar storage physically lay out the same table differently on disk
- Predict which storage layout reads less data for a given query, based on how many rows and columns it touches
- Connect columnar storage back to why it serves OLAP workloads well
Why This Matters
Imagine a report that runs one AVG() over a single column of a 2TB table, and it ends up pulling the entire 2TB off disk to compute it — even though that one column only accounts for 40GB of the table. That's not a bug and it's not bad hardware. It's a direct, predictable consequence of how the table happens to be laid out physically, and it's completely avoidable once you know what to look for. The previous lesson established why OLTP and OLAP pull database design in opposite directions. This lesson gets into how — the actual physical trick that makes one database fast at one job and slow at the other.
Analogy
Picture a classroom's student records: name, age, grade, and homeroom for every student.
One way to organize them: a box of index cards, one card per student, with all four fields written on that one card. Want everything about student #4821? Pull one card, done.
A different way: four separate clipboards. One clipboard has every student's name, in order. A second has every student's age, in the same order. A third has every grade. A fourth has every homeroom. Want everything about student #4821? You now have to check all four clipboards, find their entry on each one, and piece it back together.
But flip the question around. Want the average age of all 500 students? With the clipboards, you grab the one age clipboard and ignore the other three entirely. With the index cards, you have to flip through all 500 cards — reading, and discarding, the name, grade, and homeroom on every single one just to get to the age.
Neither way of organizing the records is "better." They're optimized for opposite jobs.
Same table, two physical layouts
The index-card box is row-oriented storage: every column's value for one row is stored together, contiguously. The clipboards are columnar storage: every row's value for one column is stored together, contiguously, across every row in the table.
This sounds like a small detail. It is, in fact, the single biggest reason OLTP and OLAP databases end up built so differently. Because here's what that physical layout controls: how much data has to move off disk to answer a query.
Say a table has 40 columns and a million rows. A query that fetches one full customer record touches 40 values — one row's worth. A query that computes the average of one column touches a million values — one column's worth, across every row. Those are two completely different amounts of data, and which physical layout serves each one cheaply is a direct consequence of how the bytes are arranged:
- Row-oriented storage puts all 40 values for row 1 next to each other on disk, then all 40 values for row 2, and so on. Fetching one full row is one contiguous read. Computing an average over one column means reading every row in full — all 40 million values — and discarding 39 out of every 40 you touched.
- Columnar storage puts all million values for column 1 next to each other, then all million values for column 2, and so on. Computing an average over one column is one contiguous read of exactly that column — a million values, nothing else. Fetching one full row now means one small read from each of 40 separate column files.
This is exactly why OLAP engines like ClickHouse use columnar storage: analytical queries routinely touch a handful of columns across enormous numbers of rows, and columnar layout means the engine only ever reads the columns the query actually asked for.
A worked example
Take a requests table with 100 columns — status code, latency, user agent, region, and 96 others — and 500 million rows.
Run SELECT AVG(latency_ms) FROM requests. In row-oriented storage, answering this means reading every row in full: 500 million rows times 100 columns each, even though 99 of those columns are irrelevant to the question. In columnar storage, it means reading exactly one column — the latency_ms values for 500 million rows, and nothing else. Roughly a hundredth of the data moves off disk.
Now flip it: SELECT * FROM requests WHERE request_id = 88213991 — one full record, every column. Row-oriented storage answers this with a single contiguous read. Columnar storage has to open all 100 column files and pull one value out of each to reconstruct the row.
Same table. Same hardware. Opposite winner, depending entirely on which physical layout is underneath.
Common Misconception
"Columnar storage is strictly better for reading, so I should just use it everywhere — including my checkout database."
The worked example above only tells half the story: it only looked at reads. Writing behaves the same way in reverse. Inserting one new row into a row-oriented table is one contiguous write. Inserting one new row into a columnar table means touching every single column file — one small write to each of the 100 files, instead of one write to one place. For an OLTP workload doing thousands of single-row inserts a second, that's not a minor inefficiency, it's the same structural tension from the previous lesson showing up again, just one level deeper: the physical layout that makes big scans cheap is the same layout that makes small writes expensive.
Production columnar engines don't actually insert one row at a time into existing column files, though — ClickHouse batches inserts into new immutable parts and merges them in the background, which is exactly what Module 3 covers. That doesn't erase the tension; it's the engineering response to it.
Predict, Then Verify
A table has 20 columns and 1 million rows. For each query, predict which layout — row-oriented or columnar — reads meaningfully less data:
SELECT * FROM orders WHERE order_id = 4821SELECT AVG(total_price) FROM orders
Reveal the answer →
- Row-oriented. One full record, every column — exactly what row-oriented storage stores contiguously. Columnar storage would need a separate small read from each of the 20 column files to reassemble the same row.
- Columnar. One column, across every row — exactly what columnar storage stores contiguously. Row-oriented storage would have to read all 20 columns of all 1 million rows, discarding 19 out of every 20 values it touched.
Summary
Row-oriented storage groups every column's value for one row together on disk; columnar storage groups every row's value for one column together instead. That single physical choice decides how much data a query has to move: row-oriented storage is cheap for fetching full records and expensive for scanning one column across everything, columnar storage is the exact opposite. Neither layout is universally faster — they're mirror images, each optimized for the access pattern the other one is bad at. You can now look at a query and predict which layout serves it cheaply, and explain why OLAP engines like ClickHouse are built on columnar storage.
Quiz
1. A table has 40 columns and 2 million rows. A query runs SELECT customer_id, order_total FROM orders WHERE order_date > '2026-01-01', touching roughly 2 of the 40 columns across a large slice of rows. Which storage layout reads meaningfully less data for this query?
2. You need to fetch a single customer's full record — all 40 columns — by customer_id. Which layout is naturally suited to this, and why?
3. Suppose you switch a table from row-oriented to columnar storage but keep running the same OLTP workload — lots of single-row inserts and single-row updates. What's the most likely consequence?
4. Why does columnar storage make full-row lookups less efficient, in physical terms?
5. A table has 100 columns. A report runs SELECT AVG(latency_ms) FROM requests across 500 million rows. Roughly how much of the table does a columnar layout need to read, compared to a row-oriented layout?
6. Why do OLAP engines like ClickHouse specifically choose columnar storage over row-oriented storage, given everything covered in this lesson?