Rows vs Columns: How different are they, really?

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.¹

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).²

A column store does the opposite: it takes every record apart and keeps each column's values next to each other instead.³ DuckDB and ClickHouse are examples of column stores. They're built for analytics, which is called online analytical processing (OLAP).⁴

#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.⁵ 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<u32>, 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 machines⁶), fetching the whole line even when the program needs only a few bytes of it. A Ride is 48 bytes⁷ 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 index⁸), 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).⁹ 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.¹⁰ This is called vectorized execution.¹¹

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.¹²

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 model¹³), 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.¹⁴ 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 autovectorization¹⁵ that is supported well in compiler backends such as LLVM.¹⁶

With AVX-512 (which can load 512 bits at once), one vpaddq instruction adds 8 64-bit durations at once.¹⁷ 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).

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
}

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, we've only looked at data that is already in memory. In most database systems, data lives on persistent storage (a disk, an SSD, or object storage like S3), and bringing it into memory is usually the bottleneck of query execution (in other words, queries are I/O bound). So the size of our data is very important: the fewer bytes a query has to read, the less time it spends waiting on I/O.

The naive way to make the data smaller is to take each block of it and run it through a general-purpose compression algorithm.¹⁹ This kind of compression should be familiar to anyone who has ever dealt with zip files! It treats the data as just a big pile of bytes, and it doesn't care what those bytes actually mean, which is why it works on everything (including both rows and columns).

The problem is that this kind of compression is a "black box": once a block is compressed, the database can't see inside of it anymore. For example, if you wanted to read the one-millionth line of a zipped CSV file, there is no way to jump straight to that line, so you would have to unzip (decompress) everything that comes before it just to access a single line.²⁰ The ability to read any one piece of the data without reading the rest of it is called random access, and this kind of compression is unable to support fast random access.

A database system will have the exact same problem with black-box compressed blocks of data: to read the duration of ride 1004, it has to decompress the entire block (potentially multiple blocks!) that the ride lives in.

This is another place where columns help. The values in a column all have the same type, and they tend to be similar to each other, which means that a column compresses much better than a row does. We can already see this in our example above: start_area only has 3 distinct values, bike_type only has 2, and ride_id is always increasing.

More importantly, we don't actually need to use black-box compression. We can instead use lightweight encodings: simple schemes that compress the data while retaining the structure and the meaning of the values.²¹ Instead of turning a column into a big pile of bytes, a lightweight encoding takes advantage of one specific pattern in it (for example, a column with only a few distinct values, or a column of numbers that are all small) to store the same values in less space.

Retaining the structure means that we can keep random access. With most lightweight encodings, the database system can jump straight to the value that it wants without decoding everything around it.

Retaining the meaning helps as well. Lightweight encodings are "lighter weight" than black-box compression, but they aren't free, since decoding them still takes CPU time. However, because the encoded values still mean something, a query can often do its work directly on the encoded data without decoding it first, which skips much of that cost. This computation over compressed data is a form of "late materialization".²²

This is exactly how we built Vortex, an open-source, extensible columnar file format and data processing library. It has a whole collection of lightweight encodings, and it can stack them on top of each other to make the data even smaller.

We've written a lot about how this works. Check out our other blogs!

• Vortex: One Format for Any Shape for an overview of how Vortex works
• FastLanes for integers
• ALP for floats
• FSST for strings
• BtrBlocks for choosing an encoding for each column
• Push-down compute for working on compressed data
• LIKE on compressed strings without decompressing them

#Thinking in columns

We started with data from a CSV file and one "simple" choice of storing data in rows vs columns. Yet we have seen just how much follows from that choice, as well as the consequences of choosing one or the other.²³

Those consequences are the reason that most database systems are built for either transactions or analytics, and not both.²⁴ The way a database executes queries and the way it compresses data are both built on top of this one choice, so it isn't something that a system can just flip later. A column store's strength in serving analytical queries is exactly what makes it bad for reading and changing one record at a time, and the opposite is true for a row store.

There is also a lot more to columnar data than what we can fit in a single introductory post. For example, we never talked about how a column store actually organizes its data on persistent storage,²⁵ how it avoids reading data that a query doesn't need,²⁶ how vectorized execution isn't the only way to run a query more efficiently,²⁷ and we completely ignored writes and modifications to data!²⁸ Each one of these topics is a whole area of active research. If you want to keep going, the CMU database courses are a great place to start, and all of their lectures are free to watch online.²⁹

In the future, we hope to post more about these kinds of topics, as we engage with them quite a lot working at Spiral!