A scan reads whatever the layout makes contiguous. A row store reads whole rows even when the query named three columns of twenty; a column store reads only the named columns, and compresses them better because a column holds like values. Over a billion two-hundred-byte rows that is 100.00 GB against 6.00 GB — and one I/O against twenty when the query wants a whole row back.
Core ideas
One formula per layout, one compression ratio that is part of the layout rather than separate from it, and one query that flips the result.
In a row store a row is one run of bytes, so reading any column of it reads all of it: a query naming three columns of twenty still moves the full 200 B per row. In a column store each column is its own run, so the same query moves 3 × 10 B per row. Neither engine is reading the wrong number of rows; they are reading different amounts of each row.
A column holds a million values of one type and often one distribution, which is what compresses; a row holds a timestamp, a name and a price side by side, which does not. Quoting a columnar scan without its ratio hides half the effect — here 5× against 2×, which turns a factor of ten in bytes named into a factor of 16.67 in bytes read.
The two scans tie when the named columns reach columns × column ratio / row ratio, which at these ratios is 50 of 20 — so no selection flips the scan here. The flip comes from a different query: fetching one whole row is 1 I/O in a row store and 20 in a column store, one page per column.
The material
Two formulas, the compression that is part of each of them, and the point lookup that undoes the whole comparison.
A row store keeps the values of one row contiguously; a column store keeps the values of one column contiguously. Bytes scanned is the volume a query must read from storage.
For r rows of total width w across c columns, with j columns named and compression ratios ρ_row and ρ_col, the row store scans r·w/ρ_row and the column store scans r·(w/c)·j/ρ_col.
The row store's formula contains no j. That is the entire asymmetry: naming fewer columns cannot make a row store read less, because the unit it stores and the unit the device delivers are both the row. A query naming one column of twenty scans exactly as many bytes as a query naming all twenty.
A factor of 16.67 from a query that named a seventh of the columns is more than the seven you might expect, and the extra comes from the compression ratios: 10/3 from the columns and 5/2 from the compression, multiplied. That is why the ratio has to travel with the claim.
Set the two byte counts equal and solve for the number of named columns: j* = c × ρ_col/ρ_row. At c = 20, ρ_col = 5 and ρ_row = 2 that is 50, which is past all twenty columns — so at these ratios no scan, however wide, makes the row store cheaper. Drop the columnar ratio to 2×, which is what happens when the columns hold high-cardinality values that do not compress, and the crossover falls to exactly 20: the two layouts tie on a query that reads everything.
Fetch one whole row by key. In a row store it is one page read: the row is contiguous, so the page holding any of its columns holds all of them. In a column store it is twenty page reads, one per column, because the twenty values live in twenty different places. That is a factor of twenty in the row store's favour on the same table, and it is not a scan at all.
So “columnar is always faster” is a claim about analytical scans wearing the clothes of a claim about storage. The honest form names the query shape: for scans over a few columns of many rows, columnar by a large factor; for retrieving whole rows one at a time, row store by a factor of the column count. A system doing both is a system that keeps the data twice, and that is a decision with a storage bill rather than a clever trick.
Two things held fixed here. The scan runs at 1 GB/s in both layouts, so nothing in the comparison is hiding a bandwidth difference — the whole effect is bytes. And the compression ratio is an input on this course rather than something derived: what a codec achieves on your data is measured, and the break-even between the ratio and the CPU it costs to decompress belongs to Scaling Laws and Cost.
Interactive
And the whole-row lookup where the answer reverses
Method
Four steps, and the fourth is the one that keeps the answer honest.
Which columns, and how many rows. The column list is the only input the two formulas disagree about, and it is the one people summarise away when they describe a workload.
rows × full width ÷ row ratio and rows × named widths ÷ column ratio. Keep both compression ratios in the arithmetic; leaving them out is the most common way to get a plausible wrong answer here.
The comparison is about bytes, so hold the scan rate fixed across the two layouts. If you want to argue that one layout also reads faster, that is a separate claim and it needs its own measurement.
Price a whole-row fetch as well as the scan: 1 I/O against one per column. A layout decision made on the scan alone is a decision made on half of the workload.
Two formulas, the same bandwidth, and then the query the comparison forgets.
The row store is not reading more rows. It is reading whole rows, because a row is what is contiguous there, and the query named three columns out of twenty. Every byte of the difference is layout.
The crossover is worth computing even when it lands outside the table, because it says how far outside. A query would have to name fifty of twenty columns to make the row store cheaper, which is not close; drop the columnar ratio to 2× and it falls to exactly 20, at which point a full-width scan ties. That is the number to quote when someone asks how robust the comparison is.
For a faded rehearsal, keep the table and make the query name ten columns instead of three. The supplied first move is that the columnar scan becomes 20.00 GB. Compute the two times and the ratio, then say what the crossover becomes if the columnar compression falls to 3×. Check all three in the lab before opening the quiz.
You should be able to compute bytes scanned in both layouts from a row width, a column count, a selection and two compression ratios; convert both to times at a stated bandwidth; and find the crossover in named columns.
Something to change about this lesson? Write it down, tick it off when it is handled, and export the list when you want to act on it. Kept in this browser only: nothing is sent anywhere, and nobody is notified.
No recommendations yet for this lesson.