Rows vs. Columns: How different are they, really?
Comments
Rows vs Columns: How different are they, really? Connor Tsui Published Nov 11, 2026 ยท 12 min read
When we think about structured data, we generally think in terms of rows. Take this simple CSV file, which records some data about bike rides on a bike-sharing app in New York City:
ride_id, started_at, start_area, bike_type, duration_min
1001, 2026-11-02 07:58, Tribeca, classic, 14
1002, 2026-11-02 08:01, Financial District, electric, 6
1003, 2026-11-02 08:01, SoHo, classic, 21
1004, 2026-11-02 08:03, Financial District, electric, 8
1005, 2026-11-02 08:04, Financial District, classic, 15
1006, 2026-11-02 08:07, Tribeca, electric, 1
1007, 2026-11-02 08:08, Tribeca, electric, 9
1008, 2026-11-02 08:12, Financial District, classic, 31
Data in this CSV file is organized into rows, where each row corresponds to a record of data. Each record here is one ride, or one "thing", with everything we know about it on one line.
We tend to organize data like this almost everywhere: think of spreadsheets, JSON objects, C-style structs in an array, SQL tables, etc. This is because it matches how we think: one "thing", and all of the details of that "thing".
This way of thinking fits a lot of the software we write. When you log in to an app, post a comment on a video, add an item to a shopping cart, send a bank transfer, or book a ride, the application will change just one record (or a few, at most).
So it makes sense that the databases behind this software, like Postgres and MySQL, "think in" (or are modeled around) rows as well: they keep each record's details together, so finding, updating, or creating a record means going to one place.
But not everything we ask of a database is limited to one record. If you run the bike-share service that produced the CSV file above, you'll eventually want to learn something from all the data it collects. This kind of work is called analytics. For example, which neighborhood is busiest on Monday and Friday mornings? Do people take longer rides on electric bikes than on classic ones?
Observe how no single record can answer these questions. The answers come from the dataset as a whole, so the only way to get them is to look at all of the records.1
Notice what these questions don't need, however. Finding the busiest neighborhood only needs the start_area and started_at columns, and comparing bike types only needs bike_type and duration_min. Each question may have to look at every row, but it only needs a few of the columns from each one.
And a database that keeps each record's details together can't take advantage of that: to read two columns, it has to read all five. We may think in rows, but to answer questions like these quickly, we have to program in columns.
Programming in columns begins with how the data is stored, but that's only the start. Once data is stored by column, the way a database runs a query changes dramatically: how much data it reads, how many values it handles at a time, how many values the CPU can process with one instruction, how well the data compresses, etc. This post will go over these changes, and by the end, you'll see just how much follows from the "simple" choice to store columns instead of rows.
Row stores and column stores
Let's start with what doesn't change. Whether a database stores rows or columns, the table it shows you when you do SELECT * FROM rides; is the same. You create it with the same SQL, you query it with the same SQL, and you get the same answers. We can say that the two tables are logically equivalent.
CREATE TABLE rides (
ride_id BIGINT,
started_at TIMESTAMP,
start_area TEXT,
bike_type TEXT,
duration_min INTEGER
);
SELECT * FROM rides WHERE ride_id = 1004;
SELECT AVG(duration_min) FROM rides;
Rows vs columns is a physical distinction rather than a logical one: the data represents the same thing, but how that data is actually arranged in memory and on disk is different.
Databases are named after the grouping they choose. A row store keeps each record's values next to each other, just like the CSV we started with. Postgres and MySQL, which we mentioned before, are both row stores. They're built for the small reads and writes behind an application, which is called online transaction processing (OLTP).2
A column store does the opposite: it takes every record apart and keeps each column's values next to each other instead.3 DuckDB and ClickHouse are examples of column stores. They're built for analytics, which is called online analytical processing (OLAP).4
Rows and columns in memory
To make this concrete, imagine that we have to hold our rides in memory ourselves. This isn't how a real database would be implemented, but it's a good approximation of how each kind arranges its data.
In Rust, a row store would be a Vec of structs:
struct Ride {
ride_id: u64,
started_at: Timestamp,
start_area: String,
bike_type: BikeType,
duration_min: u32,
}
type Rows = Vec<Ride>;
A column store will have the same fields, but it will look like a struct of Vecs:
struct Columns {
ride_id: Vec<u64>,
started_at: Vec<Timestamp>,
start_area: Vec<String>,
bike_type: Vec<BikeType>,
duration_min: Vec<u32>,
}
Here is what these data layouts look like side by side: Both of these forms represent the same logical values, but their layout is dramatically different.
Reading only the columns you need
Let's do a fun exercise.5 These are the two SQL queries shown above. Which layout is better for executing each of them?
SELECT AVG(duration_min) FROM rides;
SELECT * FROM rides WHERE ride_id = 1004;
For the AVG query, computing it requires only the duration_min of every ride and nothing else. In the Columns layout, values are stored together in a single Vec, so the query can scan that one array without touching the other four.
For Vec<Ride> (the Rows layout), there is simply no way to read only the durations. Each duration is stored within a Ride, so the query has to read through every Ride and pass over the other four fields every time.
Note that the code can "ignore" those other fields, but the hardware cannot. A CPU doesn't fetch memory one value at a time: it will fetch fixed-size blocks called cache lines (which are 64 bytes on most machines6), fetching the whole line even when the program needs only a few bytes of it.
A Ride is 48 bytes7 and duration_min is only 4 of them, so with the row layout, we have to fetch all 48 bytes to read those 4, which is 12x the amount that we actually need. The CPU can't skip any of these lines either because a Ride is smaller than a cache line, so every cache line of the array has a duration somewhere in it.
This diagram only shows 8 rides for simplicity (which is why the duration_min line is half empty), but the factor of 12 doesn't depend on the size of the table. With one million rides, we might be fetching 48 MB vs 4 MB.
The second query goes the other way. SELECT * FROM rides WHERE ride_id = 1004 wants every field of a single ride, not just one. Once the database finds the record with that ride (probably via an index8), Vec<Ride> has all of its fields next to each other, so reading all of them touches only one or two cache lines (it might overlap two cache lines).9
On the other hand, Columns has spread the same ride over five different arrays, so the query has to fetch at least five cache lines to put it back together. For this query, the Rows layout wins.
Now that we have a better idea of what we're working with, let's go a bit deeper into how we can speed up queries over columnar data! Analytical data processing is an active field of research spanning several decades, so we won't be able to go over every possible optimization. Nevertheless, the rest of this post will give a good overview of the techniques that most modern analytical database systems utilize.
Vectorized execution
The simplest way to run SELECT AVG(duration_min) FROM rides; is to visit one ride at a time: take a ride, add its duration, and repeat, dividing by the length of the column at the end. However, a column store doesn't necessarily have to execute this way. Since all of the durations sit in one contiguous array, it can process them in batches of a few thousand values, where each batch is small enough to stay in the CPU's cache.10 This is called vectorized execution.11
But why would a batch be any faster than one value at a time? To understand this, we first need to understand how a database execution engine actually runs these queries.12
A database has to be able to run any query, so it builds and plans each one with general-purpose pieces called operators. For example, we might have an operator that reads a column, another that performs filters, another that computes a sum, and so on. If the operators pass each other one Ride at a time (this is called the Volcano model13), then for every single ride, the database has to figure out which operator runs next, which column it wants, and what type that column is. All of this bookkeeping and interpretation cost can easily take more time than the summation itself.14
With batches, we only pay for the bookkeeping once per batch instead of once per value, and the actual work turns into a tight loop over an array. So why stop at a few thousand values, and not just process the entire column at once? Operators hand their results to each other, so a small batch of results can stay in the CPU's cache until the next operator picks it up. An operator result the size of an entire column generally cannot fit inside the CPU's cache, so the CPU would have to write it back out to main memory and then read it back in.
For our query, the work on each batch is a single loop:
fn sum(batch: &[u32; 1024]) -> u64 {
let mut sum = 0;
for &duration in batch {
sum += duration as u64;
}
sum
}
In addition to vectorized execution having better cache locality, we can speed up our execution of the query with data-parallel operations. Modern CPUs have instructions that operate on several values at once, called SIMD (single instruction, multiple data). They work best when the values are the same type and are contiguous in memory, which is exactly what a batch of a column is.
So when the compiler sees the for loop above, it can emit SIMD instructions on its own. This is a technique called autovectorization15 that is supported well in compiler backends such as LLVM.16 With AVX-512 (which can load 512 bits at once), one vpaddq instruction adds 8 64-bit durations at once.17
Note that vpaddq doesn't add those 8 durations to each other. A 512-bit register has 8 slots of 64 bits, and each slot holds its own running sum: the first duration is added to the first slot, the second duration to the second slot, and so on. Only after the loop does the code add the slots together to get the final sum (the figure below leaves that last step out).
LLVM IR and AVX-512 assembly This is the output of rustc 1.99.1 with -C opt-level=3 -C target-feature=+avx512f, with the attributes, metadata, and assembler directives removed. Note that the compiler keeps four registers of running sums (zmm0 to zmm3 in the assembly) instead of one. This is a separate optimization from SIMD called loop unrolling:18 the 8 slots in each register come from SIMD, and the four registers come from the compiler doing four steps of the loop in every pass. Together, every pass of the loop adds 4 ร 8 = 32 durations.
This is the optimized LLVM IR:
define i64 @sum(ptr %batch) {
start:
br label %vector.body
; The loop. Every pass adds 32 durations.
vector.body:
; The counter and the four vectors of running sums, which all start at zero.
%index = phi i64 [ 0, %start ], [ %index.next, %vector.body ]
%vec.phi = phi <8 x i64> [ zeroinitializer, %start ], [ %9, %vector.body ]
%vec.phi4 = phi <8 x i64> [ zeroinitializer, %start ], [ %10, %vector.body ]
%vec.phi5 = phi <8 x i64> [ zeroinitializer, %start ], [ %11, %vector.body ]
%vec.phi6 = phi <8 x i64> [ zeroinitializer, %start ], [ %12, %vector.body ]
%0 = shl i64 %index, 2
%1 = getelementptr inbounds nuw i8, ptr %batch, i64 %0
%2 = getelementptr inbounds nuw i8, ptr %1, i64 32
%3 = getelementptr inbounds nuw i8, ptr %1, i64 64
%4 = getelementptr inbounds nuw i8, ptr %1, i64 96
%wide.load = load <8 x i32>, ptr %1, align 4
%wide.load7 = load <8 x i32>, ptr %2, align 4
%wide.load8 = load <8 x i32>, ptr %3, align 4
%wide.load9 = load <8 x i32>, ptr %4, align 4
%5 = zext <8 x i32> %wide.load to <8 x i64>
%6 = zext <8 x i32> %wide.load7 to <8 x i64>
%7 = zext <8 x i32> %wide.load8 to <8 x i64>
%8 = zext <8 x i32> %wide.load9 to <8 x i64>
%9 = add <8 x i64> %vec.phi, %5
%10 = add <8 x i64> %vec.phi4, %6
%11 = add <8 x i64> %vec.phi5, %7
%12 = add <8 x i64> %vec.phi6, %8
%index.next = add nuw i64 %index, 32
%13 = icmp eq i64 %index.next, 1024
br i1 %13, label %bb2, label %vector.body
; Add the running sums together, and return the result.
bb2:
%bin.rdx = add <8 x i64> %10, %9
%bin.rdx10 = add <8 x i64> %11, %bin.rdx
%bin.rdx11 = add <8 x i64> %12, %bin.rdx10
%14 = tail call i64 @llvm.vector.reduce.add.v8i64(<8 x i64> %bin.rdx11)
ret i64 %14
}
This is the x86-64 assembly that LLVM generates:
sum:
# Set the four registers of running sums (zmm0 to zmm3) and the counter
# (rax) to zero.
vpxor xmm0, xmm0, xmm0
xor eax, eax
vpxor xmm1, xmm1, xmm1
vpxor xmm2, xmm2, xmm2
vpxor xmm3, xmm3, xmm3
# The loop. Every pass adds 32 durations.
.LBB0_1:
vpmovzxdq zmm4, ymmword ptr [rdi + 4*rax]
vpaddq zmm0, zmm0, zmm4
vpmovzxdq zmm4, ymmword ptr [rdi + 4*rax + 32]
vpaddq zmm1, zmm1, zmm4
vpmovzxdq zmm4, ymmword ptr [rdi + 4*rax + 64]
vpmovzxdq zmm5, ymmword ptr [rdi + 4*rax + 96]
vpaddq zmm2, zmm2, zmm4
vpaddq zmm3, zmm3, zmm5
add rax, 32
cmp rax, 1024
jne .LBB0_1
# Add the running sums together, and return the result in rax.
vpaddq zmm0, zmm1, zmm0
vpaddq zmm0, zmm2, zmm0
vpaddq zmm0, zmm3, zmm0
vextracti64x4 ymm1, zmm0, 1
vpaddq zmm0, zmm0, zmm1
vextracti128 xmm1, ymm0, 1
vpaddq xmm0, xmm0, xmm1
vpshufd xmm1, xmm0, 238
vpaddq xmm0, xmm0, xmm1
vmovq rax, xmm0
vzeroupper
ret
Note that autovectorization is not unique to sums. Much of the time executing a query (especially a relatively simple one) is spent in tight loops over batches like this one, and the compiler can vectorize many of them in the same way without anyone writing a single SIMD instruction by hand.
Compression
So far
Comments
No comments yet. Start the discussion.