Rows vs Columns: The Cache-Line Math That Decides Your Database Layout
A 48-byte record holds a 4-byte column you actually need. Reading that one column through a row-oriented layout pulls twelve times more bytes from memory than the query uses, because the CPU cannot fetch four bytes witho
A 48-byte record holds a 4-byte column you actually need. Reading that one column through a row-oriented layout pulls twelve times more bytes from memory than the query uses, because the CPU cannot fetch four bytes without fetching the 44 bytes sitting next to them. Multiply that by a hundred million rows and a layout choice you never thought about becomes the difference between scanning 4 gigabytes and scanning 48.
That arithmetic, from a recent SpiralDB post that walked through it line by line, is the sharpest way to understand why the database world split in two. Postgres and MySQL store rows. DuckDB and ClickHouse store columns. Both answer the same SQL with the same results, yet the performance gap between them on the wrong workload is not a few percent — it is routinely an order of magnitude, and it comes from physics, not cleverness.
This article rebuilds that gap from first principles: the memory layout, the execution model, and the compression tricks, then looks at the row store's surprisingly effective countermoves.
Two layouts, one logical table
The first thing to understand is what does not change. Whether the engine stores rows or columns, CREATE TABLE is the same SQL, SELECT is the same SQL, and the answers are identical. Row versus column is a physical distinction, not a logical one.
A row store keeps each record's values next to each other, exactly like a line in a CSV file or a struct in an array. A column store tears every record apart and keeps each column's values next to each other instead. Systems programmers will recognize this as the classic array-of-structs versus struct-of-arrays split, and that mapping is exact: a row store is an array of structs, a column store is a struct of arrays. The difference is that a database makes the choice about on-disk layout, where it interacts with compression and I/O, not just the CPU cache.
The idea is older than its current popularity. The Decomposition Storage Model proposed storing each column separately in a SIGMOD paper back in 1985. What changed is the workload: analytics, where a query touches every row but only a few columns, became important enough to build entire systems around.
The cache-line math that starts everything
A CPU does not fetch memory one value at a time. It fetches fixed-size blocks called cache lines — 64 bytes on most server and desktop chips, 128 on Apple's M-series. If the value you want lives inside a line, you get the whole line whether you asked for it or not.
Take a ride-sharing record with five fields: an ID, a timestamp, two strings, and a 4-byte duration. Laid out as a struct it occupies about 48 bytes. A query computing the average duration needs only those 4 bytes per record, but the row layout forces the hardware to fetch all 48 — twelve times the useful bytes — and because the record is smaller than a cache line, no line can be skipped. The column layout puts every duration in one contiguous array, so the same query reads exactly 4 bytes per record and nothing else.
You can feel the shape of this in a few lines of Python. This is not a benchmark of your database — interpreter overhead dominates pure Python — but it shows the two memory layouts side by side:
rides_rows = [
{"id": i, "area": "soho", "kind": "classic", "duration": i % 60}
for i in range(1_000_000)
]
total = sum(r["duration"] for r in rides_rows) # walks a million separate dicts
durations = [i % 60 for i in range(1_000_000)] # one contiguous array
total = sum(durations) # tight loop over a single buffer
The row version walks a million separate objects and chases a pointer for every field. The column version walks one buffer. In a native engine, that difference is the 12x byte amplification above, plus the ability to skip every column the query never asked for.
Why column scans vectorize and row scans do not
Storage is only the beginning. The execution model changes with it.
A general-purpose engine plans every query from reusable operators — scan, filter, aggregate. In the classic Volcano model, operators pass one row at a time between them, so for every single row the engine re-dispatches to the next operator, re-checks which column it wants, and re-checks the type. That per-row bookkeeping can easily cost more than the arithmetic itself.
A column store instead hands operators batches of a few thousand values — a common batch size is 1024. Bookkeeping is paid once per batch, and the batch is small enough to stay in the CPU cache until the next operator picks it up, so intermediate results never round-trip to main memory. The actual work collapses into a tight loop over a contiguous array.
Tight loops over contiguous arrays are exactly what modern CPUs accelerate. With AVX-512, a single vpaddq instruction adds eight 64-bit integers at once — eight lanes, each keeping its own running sum, combined only at the end. Compilers do this automatically: the SpiralDB post shows rustc compiling a plain summation loop into AVX-512 code that also unrolls the loop four ways, so each pass processes 32 values. Nobody wrote a SIMD intrinsic. The layout made the loop simple enough that LLVM could vectorize it for free.
Row layouts rarely get this treatment, because their loops chase pointers and touch heterogeneous fields — the two things SIMD hates.
Compression is where columns pull away
Most query time is not compute; it is waiting for bytes from disk or object storage. Size is speed, and this is where columns gain their largest edge.
The naive approach is black-box compression: zip each block. It works on everything but destroys random access — reading one value means decompressing everything before it. A database that must decompress a whole block to read one ride's duration has lost the game.
Column values share a type and tend to resemble each other, so they support lightweight encodings: schemes that shrink data while keeping its structure and meaning. Two you can implement in twenty lines:
def rle_encode(values):
out = []
for v in values:
if out and out[-1][0] == v:
out[-1][1] += 1
else:
out.append([v, 1])
return out
def delta_encode(values):
out, prev = [], 0
for v in values:
out.append(v - prev)
prev = v
return out
Run-length encoding turns a column with two distinct values into pairs of value and repeat count. Delta encoding turns a steadily increasing ID column into small gaps, which then bit-pack into far fewer bits per value. Because the structure survives, the engine keeps random access — it can jump to the value it wants. Because the meaning survives, the engine can often filter and aggregate directly on encoded values without decoding them first, a technique called late materialization.
Real systems stack these encodings. DuckDB applies lightweight compression per row group and made the DICT_FSST string encoding the default in its v2.0.0 storage format. On-disk formats like Parquet split tables into row groups — an idea going back to the PAX paper from VLDB 2001 — and store each column separately inside the group, with per-block minimum and maximum statistics so queries can skip blocks entirely.
The row store's countermove
None of this means row stores are helpless, and the Hacker News discussion of the SpiralDB piece surfaced the strongest counterargument immediately: a covering index. An index on exactly the columns a query needs lets the engine answer from the index alone, never touching the full rows. That is, in effect, a narrow columnar copy of part of the table, maintained by the row store for you:
CREATE INDEX idx_rides_area_time ON rides (start_area, started_at);
-- answered entirely from the index; the base table is never read
SELECT start_area, started_at FROM rides WHERE start_area = 'SoHo';
Vendors pushed the idea much further. Sybase IQ ran column-oriented storage commercially in the 1990s. SQL Server added columnstore indexes in 2012, letting one table carry both layouts. Oracle's Database In-Memory option keeps a columnar mirror of row tables for analytics. And the boundary keeps blurring: CedarDB, the commercial version of the Umbra research system, runs transactions and analytics on one engine, while Databricks' Lakebase keeps Postgres for transactions and rewrites the same data into columnar form in the background.
The honest conclusion from the classic SIGMOD 2008 study — titled, almost word for word, like the question this article asks — is that the layout explains most of the gap, but execution and compression techniques decide how much of that potential a system actually realizes.
What columns give up
The trade is real, and it is why the world did not simply switch. A point lookup that reads every field of one record touches one or two cache lines in a row store, but fans out to at least five separate arrays in a column store. Writes hurt more: inserting one record means appending to five different places, five I/O operations instead of one.
The standard answer is as old as C-Store in 2005: keep a small write-optimized store beside the big read-optimized one and merge in the background. Modern columnar systems still work this way, buffering inserts and rewriting row groups in batches rather than updating columns in place. It works, but it is a fundamentally more complicated write path, and it is why your banking app still runs on a row store.
Choosing a layout, mechanically
The decision procedure fits on an index card. If the workload reads and writes a few whole records at a time — logins, carts, payments — choose a row store. If it scans most of the data but only a few columns — dashboards, aggregations, model features — choose a column store. If you genuinely have both, reach for the hybrids: a covering index or columnstore index inside your row store, or an analytical engine reading columnar files produced from your transactional system.
The one-step diagnostic is to look at your slowest queries and count what they touch. Rows touched times columns needed, divided by total columns, tells you how much of every fetched byte is useful work. When that fraction is small, the cache-line math from the beginning of this article is already taxing you, and no amount of query tuning on the wrong layout will give it back.
Originally published on Dispatch.
Originally published by Dev.to AI. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.