Row vs Column Store: Why the Same Query Reads 41x More Bytes
"What's the average session duration?"
One column. One number. The table has twenty columns and five million rows, and the query touches exactly one of them — a four-byte integer, 19 MiB of actual data.
Depending on how that table is stored on disk, answering it costs you between 26 MiB and 1.1 GiB of reading. Same rows, same answer, same machine. A 41× spread, decided entirely by a choice someone made when they wrote the file out.
This is the row-store versus column-store question, and it's usually explained with a diagram and left there. Let's make it concrete, then measure it.
Why the layout decides the cost
Disks and filesystems don't hand you individual values. They hand you blocks. Whatever else is sitting in the block you asked for arrives whether you wanted it or not.
So the only question that matters is: is the data you need contiguous?
The concrete version
Four rows are enough to see the mechanic before scaling it up:
| id | user | duration_ms | country |
|---|---|---|---|
| 1 | u201 | 812 | US |
| 2 | u105 | 340 | IN |
| 3 | u883 | 275 | DE |
| 4 | u017 | 960 | US |
A row store packs each record whole — one block per record:
[1,u201,812,US] [2,u105,340,IN] [3,u883,275,DE] [4,u017,960,US]
A column store packs it the other way — one block per column:
[1,2,3,4] [u201,u105,u883,u017] [812,340,275,960] [US,IN,DE,US]
Scale that to the real rig — twenty columns, five million rows — and the two queries below are exactly this, just bigger.
Query one: fetch everything about a record
SELECT * FROM events WHERE id = 4
In the row store, the record is one block. One read, wherever that block lives — you get there via any index that resolves id to a location, not necessarily the primary key.
In the column store, that same index gets you to a position, not a block. The record's twenty fields live in twenty different column files. Fetching the whole record means opening all twenty of them.
Row store wins here. This is the shape of most OLTP queries: fetch or update one entity, by some key.
Query two: average one column across everything
SELECT AVG(duration_ms) FROM events
In the row store, there's no WHERE clause to seek on — averaging every row means touching every row, so you walk the entire file, twenty fields at a time, using six bytes out of every 224 you read.
In the column store, duration_ms is the only block you open. The other nineteen columns are never touched.
An index doesn't rescue the row store here. No index changes the fact that an aggregate with no filter has to read every row's value. (The one structure that would help is an index holding just this column — which is a column store in miniature.)
Column store wins here, and this is the shape of most analytical queries: touch a few columns, out of many, across everything.
The rig
Five million rows, twenty columns — a synthetic web-events table with a realistic cardinality mix: fat high-entropy strings (session_id, URLs), low-cardinality categoricals (country, device, browser), and numerics. Target column is duration_ms, an int32.
Measured with two real files on disk — one written row-by-row, one written column-by-column — the plainest real instance of each layout, so the bytes-read numbers below are actual bytes, not an estimate.
Query: SELECT avg(duration_ms). Every path below returns 300,009.9226 — that agreement is the correctness oracle, and it's what tells you the projected reads didn't quietly skip anything.
Two metrics, deliberately separated
Bytes required — the bytes the layout structurally forces you to read. For the row store that's the whole file; there's no way to reach every twentieth field without traversing the ones between. For the column store it's the exact size of that one column's chunks, read straight from the file footer.
This number is deterministic and cache-independent, which is why I use it as the headline instead of OS-level I/O counters. Counters measure your page cache's mood on the day you ran the benchmark. Footer arithmetic measures the layout.
Wall clock — best of one run, warm page cache, single machine. It's a sanity check on the bytes number, not the headline. A cold cache would widen these gaps, not narrow them.
Query two, measured
| Layout | Bytes required | Time |
|---|---|---|
| Row store, full scan | 1,066.3 MiB | 7.70s |
| Column store, all columns (not projected) | 458.5 MiB | 0.54s |
| Column store, just the one column | 25.8 MiB | 0.03s |
Raw column, uncompressed: 19.1 MiB (5M × int32).
Row store → column store, projected: 41× fewer bytes, 257× less time.
Put the row-store number in per-row terms and it stops sounding abstract. That file is ~224 bytes per row. The value you want is about six characters. You read 224 bytes to use six, five million times over.
Look at the middle row before you draw a conclusion, though: a column store that reads all twenty columns still beats the row store — binary columnar decode beats text parsing even before you throw anything away. That's what makes it dangerous to stop measuring there. It looks like a clean win over the row store, and you never learn you left another 18× on the table by not telling it which column you wanted:
read_all_columns(table)[col].mean() # reads all 20 columns: 458.5 MiB
read_columns(table, [col])[col].mean() # reads 1 column: 25.8 MiB
Same file, same reader, one argument telling it which column you actually want. Naming the column isn't an optimization — it's the point. A column store only pays off if the query asks for fewer columns than it has.
What column stores buy beyond bytes read
The scan numbers above aren't the whole reason to reach for a column store.
Compression. Same-type values sitting together compress far better than a row store's mixed-type blocks — dictionary encoding for repeated strings, delta encoding for sorted numbers. My target column is a bad example on purpose: duration_ms is uniform random, so compression inflated it slightly above raw — 25.8 MiB compressed against 19.1 MiB actual. Real columns — timestamps, enums, sorted IDs — compress far harder. This benchmark's ratios are, if anything, conservative.
Per-column statistics. Column stores carry metadata — minimum, maximum, null count — per chunk, letting an engine skip whole chunks it already knows can't match a filter, without reading them at all. In this rig's file, the footer alone was enough to tell me where every byte went, with zero rows read:
| Column | Size | Share |
|---|---|---|
session_id |
154.6 MiB | 33.7% |
longitude |
43.3 MiB | 9.5% |
latitude |
43.3 MiB | 9.4% |
timestamp |
40.2 MiB | 8.8% |
duration_ms |
25.8 MiB | 5.6% |
| … 15 others | 151.3 MiB | 33.0% |
One column — a random 32-char hex session ID — is a third of the file. That's the part a diagram never conveys: the benefit isn't uniform across your schema, it's concentrated in not reading your fattest, highest-entropy columns.
Column-level security. Because a sensitive column's bytes are already segregated, encrypting or restricting just that column doesn't touch the rest of the row. A row store doing the same thing has to reach into every block that field is mixed into.
What row stores buy beyond indexed lookups
Point lookups aren't the only place row stores win.
Writes. Updating one record is one write, to one place, in a row store. The same update against a column store touches every column's file — write amplification that's the mirror image of the scan numbers above.
Most real workloads want most of a row. Rendering an order, processing an event, loading a user's profile — these want most of the twenty columns, not one. A row store is the layout that matches that access pattern by default, which is the real reason OLTP databases default to it — not just the occasional indexed WHERE id = ?.
Honest caveats
- Warm cache, one machine, single run. Ranking is structural and won't move; absolute times will.
- Wide table, one column. At 20 columns the ratio is ~41×. At 5 columns it'd be ~10×. The advantage scales with how much of your schema you're ignoring — which, in analytics, is usually most of it.
- The two files are instruments here, not the subject. I picked the plainest real row-store and column-store layout to get honest bytes-read numbers, not to rank file formats. Format-level questions — schema evolution, splittability, write throughput — are a different post.
Takeaways
- Count bytes, not seconds, when you're evaluating a layout. Seconds mix in decoder quality and cache state. Bytes-required is the property you're actually choosing between.
- Match the layout to the query shape. Point lookups favor row stores. Wide scans of a few columns favor column stores. Neither layout is wrong — they answer different questions.
columns=is the point, not an optimization. A column store that reads everything still beats a row store, which is exactly what hides the bigger win sitting behind one keyword argument.- Column stores buy more than bytes read. Compression, chunk-skipping statistics, and column-level security all come from the same physical segregation that makes the scan numbers above possible.
- Row stores buy more than indexed lookups. Cheap single-record writes, and a layout that already matches the "give me most of the row" access pattern most OLTP workloads actually have.
The mundane version: the cheapest bytes are the ones you never read — but only if your layout matches how you read, and how you write.
Rig: a synthetic 5-million-row, 20-column events table, generated once with a fixed seed so the numbers are reproducible. Bytes-required for the column store comes from per-column metadata the layout already carries — the exact compressed size of that one column's chunks, summed, without reading a row. For the row store it's the file size, since no subset read is possible without passing through everything between the start of the file and the value you want. Every path returned the same mean (300,009.9226), which is the check that the projected reads weren't skipping data.
0 Comments