Lesson 12 · Storage engines · Module 1

Rows or Columns

Every table you have ever written has to be flattened into a single sequence of bytes before it touches a disk. There are only two sensible orders to flatten it in, and the choice decides which queries are cheap for the lifetime of that table. It is not a tuning knob. It is the shape of the data.

The win in this lesson: given a query pattern, you will be able to say which layout wins and name the mechanism — which blocks get read, what compresses and why, what the CPU is handed — rather than reciting "columnar is for analytics".

1. Two questions, one table

Take one table of events: a timestamp, a country, a device, an amount. Forty columns in real life; four here. Two questions arrive against it.

Question A. "Show me everything about event 88312." One row, every column.

Question B. "Revenue by country for July." Two columns, a billion rows.

These are not the same question made bigger. They are opposite shapes, and the documentation for a database built entirely around the second one names the split plainly:

"Analytics, also known as OLAP (Online Analytical Processing), refers to SQL queries with complex calculations (e.g., aggregations, string processing, arithmetic) over massive datasets. Unlike transactional queries (or OLTP, Online Transaction Processing) that read and write just a few rows per query and, therefore, complete in milliseconds, analytics queries routinely process billions and trillions of rows."
ClickHouse documentation, What is ClickHouse?

Hold on to the two verbs. OLTP reads and writes a few rows. OLAP processes billions of rows but only some of the columns. Everything below is the consequence of trying to serve one of those with a layout built for the other.

2. The two layouts on disk

First, the read unit

Lesson 08 said the currency is pages touched, not rows returned. That fact is doing all the work here, so it is worth hearing it stated by a second, unrelated system:

"data is stored on disk in chunks called blocks (usually fixed sizes, e.g., 4 KB or 8 KB). Blocks are the smallest units of data read from disk to memory… Even if only part of a block is needed, the entire block is read into memory (this is due to disk and file system design)"
ClickHouse documentation, What is ClickHouse?

So the only question that ever matters is: of the bytes that must cross the disk boundary, what fraction did you actually want? Layout decides that, because layout decides what ends up adjacent.

The two orders

"In a row-oriented database, consecutive table rows are sequentially stored one after the other. This layout allows to retrieve rows quickly as the column values of each row are stored together."

"ClickHouse is a column-oriented database. In such systems, tables are stored as a collection of columns, i.e. the values of each column are stored sequentially one after the other. This layout makes it harder to restore single rows (as there are now gaps between the row values) but column operations such as filters or aggregation become much faster than in a row-oriented database."
ClickHouse documentation, What is ClickHouse?

That second sentence contains both halves of the lesson, cost and benefit, in one breath. Here is the same claim as a picture.

The same three rows, the same two orders that exist. Row-major puts a row's values together; column-major puts a column's values together. Every other property in this lesson — the I/O, the compression ratio, what the CPU is handed, the cost of a single-row write — is a consequence of which of those two pictures you chose.

The same shape, written down as a file format

Parquet is the columnar layout as a portable file rather than a database. Its specification is short enough to read in full, and the layout section is essentially the bottom half of the diagram:

4-byte magic number "PAR1"
<Column 1 Chunk 1>
<Column 2 Chunk 1>
...
<Column N Chunk 1>
<Column 1 Chunk 2>
<Column 2 Chunk 2>
...
<Column N Chunk 2>
...
<Column 1 Chunk M>
<Column 2 Chunk M>
...
<Column N Chunk M>
File Metadata
4-byte length in bytes of file metadata (little endian)
4-byte magic number "PAR1"

"In the above example, there are N columns in this table, split into M row groups."

"Readers are expected to first read the file metadata to find all the column chunks they are interested in. The columns chunks should then be read sequentially."
Apache Parquet documentation, File Format

Read that reader protocol again, because it is the whole argument in two sentences: find the chunks you are interested in, then read those sequentially. A reader that wants two of forty columns issues two sequential reads and never learns the other thirty-eight exist. A row-major reader has no such option — the other thirty-eight are physically in between.

Note also the row groups. Columnar does not mean "one enormous column"; it means column-major within a horizontal slab, so a scan can still be split across workers by slab. ClickHouse calls its slab a granule: "Each data part is logically divided into granules. A granule is the smallest indivisible data set that ClickHouse reads when selecting data", and the primary key "does not reference individual rows but blocks of 8192 rows called granules."

3. What each layout makes cheap

Query shape	Row-major cost	Column-major cost
One row, all columns (point read by key)	One block. The row is contiguous.	One seek per column, N columns, then reassemble.
Two columns, all rows (aggregate)	Every block of the table, to keep 5% of the bytes.	Two runs. The rest is never opened.
Insert one row	One append.	One append per column file.
Update one field of one row	Rewrite that row in place.	Rewrite a compressed block, in a sorted part, for one value.
Add a column	Touches every row's on-disk format.	A new run alongside the others.

The middle row is the one people quote, and the numbers behind it are not subtle. The ClickHouse introduction runs a query that "selects and filters just a few out of over 100 existing columns":

SELECT MobilePhoneModel, count() AS c
FROM metrica.hits
WHERE
    RegionID = 229
    AND EventDate >= '2013-07-01'
    AND EventDate <= '2013-07-31'
    AND MobilePhone != 0
    AND MobilePhoneModel NOT IN ('', 'iPad')
GROUP BY MobilePhoneModel
ORDER BY c DESC
LIMIT 8;

"the query processed 100 million rows in 92 milliseconds, a throughput of approximately over 1 billion rows per second or just under 7 GB of data transferred per second."
ClickHouse documentation, What is ClickHouse?

Seven gigabytes a second is not magic hardware. It is five columns out of a hundred, so ninety-five hundredths of the table never moved. The docs put the mechanism in one sentence, and it is the sentence to memorise:

"Because the block-wise storage and transfer from disk to memory is aligned with the data access pattern of analytical queries, only the columns required for a query are read from disk, avoiding unnecessary I/O for unused data."
ClickHouse documentation, What is ClickHouse?

The word doing the work is "aligned"

Nothing here says columnar is faster. It says the physical order matches the access pattern. That is the same claim as Lesson 02's partition key and Lesson 08's sorted leaves, wearing a third costume: put the things that get read together, next to each other. When they are adjacent, the read unit is full of things you wanted. When they are not, you pay full price for the block and throw most of it away. Change the access pattern and the identical layout becomes the wrong one — which is precisely what happens in section 5.

4. Compression is not a bonus — it falls out of the layout

Look at the bottom tape in the diagram again. Inside the country run, every neighbour is a country. Inside the amount run, every neighbour is a number of the same scale. Compression is a bet on your neighbours being predictable, and column-major order rigs that bet:

"Column-stores are particularly well suited for such compression as values of the same type and data distribution are located together."

"Users can specify that columns are compressed using various generic compression algorithms (like ZSTD) or specialized codecs, e.g. Gorilla and FPC for floating-point values, Delta and GCD for integer values, or even AES as an encrypting codec."
ClickHouse documentation, Why is ClickHouse so fast?

That list of codecs is the point. Delta is only meaningful if the next value is near this one — true of a sorted timestamp column, meaningless for a row made of a timestamp, a country and a float. A generic compressor on a row-major block is being shown a repeating sequence of unrelated types; on a column-major run it is being shown a thousand near-identical values. Same algorithm, different neighbours, order-of-magnitude different result.

Sorting compounds it, which is why column stores insist on a sort order. MergeTree lists among the reasons to choose a primary key: "Improve data compression. ClickHouse sorts data by primary key, so the higher the consistency, the better the compression."

And the payoff loops back into the I/O argument rather than being a separate storage win:

"Data compression not only reduces the storage size of the database tables, but in many cases, it also improves query performance as local disks and network I/O are often constrained by low throughput."
ClickHouse documentation, Why is ClickHouse so fast?

So the chain is: column-major puts like next to like → like next to like compresses hard → hard compression means fewer bytes on the wire → fewer bytes on a throughput-bound link means a faster query. Three links, each one mechanical. None of them is available to a row store, because its neighbours are heterogeneous by construction.

The CPU gets the same gift

A run of a thousand values of one type is also exactly what a modern CPU wants to be handed:

"'Vectorization' means that query plan operators pass intermediate result rows in batches instead of single rows. This leads to better utilization of CPU caches and allows operators to apply SIMD instructions to process multiple values at once."
ClickHouse documentation, Why is ClickHouse so fast?

Row-at-a-time execution pays interpreter overhead per row and per operator. Batch-at-a-time pays it per batch, and the batch is a dense array of one type — so it fits a cache line, branches predictably, and can be summed by a single instruction across several lanes. You can bolt vectorised execution onto a row store, and people do; you just have to build the dense arrays first, which is work the column store already did when it wrote the file.

5. Where the answer flips

Go back to the top tape. Now run question A against it: everything about event 88312. Row-major fetches one block and the row is already assembled. Column-major must visit every column's run, at a different offset in each, and stitch the pieces back together — and the docs say so without flinching: the columnar layout "makes it harder to restore single rows (as there are now gaps between the row values)".

Writes are worse, and they are worse for two compounding reasons:

Fan-out. One logical row becomes N physical writes, one per column run. A row store appends once.
Immutability. Those runs are sorted and compressed in bulk. Changing one field means decompressing a block, editing a value, recompressing, and rewriting it — for a single number. This is why column stores are built on merge-based storage and prefer large batched inserts: the MergeTree family is described as "designed for high data ingest rates and huge data volumes", which is a statement about bulk, not about latency for one row.

The decision rule, in one line

Ask what fraction of the columns a query touches, and what fraction of the rows.

Few rows, most columns → row store. Most rows, few columns → column store. A workload that is genuinely both is not one table in one layout; it is two systems, and the honest design document says so rather than hoping a knob exists.

Which is exactly what real architectures do. The transactional system keeps the row store, and a feed — CDC, a queue, a nightly export — lands the same data in Parquet or a column store for the analytical half. The duplication is not sloppiness. It is the admission that one physical order cannot serve two opposite access patterns, and that the second copy is cheaper than making the first one do a job its layout forbids.

6. Residual risk

Adding the column store fixes the scan and buys you four new problems, all of which will be someone's incident:

Staleness becomes a product decision. The analytical copy lags the transactional one by however long the pipeline takes. That is Lesson 01's problem in a new place: if a human reads a dashboard and then acts on the transactional system, you have built a read-your-writes violation with a bigger gap than any replica.

Deletes and updates are not free. "Correct one row" is trivial in the source system and a rewrite in the columnar copy. Regulatory deletion, backfills and late-arriving corrections all land on the expensive side.

Wide-format inserts punish small batches. Trickling single rows into a column store produces many tiny parts and constant merging. The ingest path has to batch, which means a buffer, which means a thing that can be lost or can fall behind.

Schema drift. Two copies of the truth need two schemas to agree forever, which is the compatibility contract Lesson 13 is about. Adding a column is cheap in the column store and a migration at the source, so the two drift in the direction of whoever forgets.

None of those argues against the move. They argue that "we will add a column store" is a sentence with an operational bill attached, and the design review should ask who pays it.

7. Check yourself

8. Back to your world

Find the slowest reporting query in whatever system you own and work out its two fractions: what share of the columns does it name, and what share of the rows does it touch? If the answer is "four columns of thirty, over everything since January", the row store is moving roughly seven times the bytes the query needs, and no index will fix that — an index prunes rows, and this query wants nearly all of them. That is the signal for a columnar copy, and it is a measurement, not a taste.

If the answer is instead "most columns, a handful of rows", stay where you are. The fix is upstream, in the query or the access pattern, and swapping the layout would make it worse in the specific way section 5 describes.

Ask me things. "what exactly is a Parquet row group and how big should it be?" · "how do Postgres column-store extensions compare to a separate warehouse?" · "walk me through delta and dictionary encoding with real numbers" · "what is a hybrid or PAX layout, and who uses one?" · "I think we should just add indexes to the reporting tables instead. Grill me."