Why Your Analytics Query Reads a Hundred Million Rows in Two Seconds (Columnar Storage Explained)

01 / 06

How a row store reads more than it should

A row-oriented database (MySQL, PostgreSQL, Oracle in its default mode) stores each record as a contiguous block on disk: name, then age, then email, then the next forty-seven fields, then the next row, and so on. When you ask for a single column across all rows, the disk still reads complete rows. It has no choice. The age value is wedged between the name and the email, so reading the age means dragging everything on either side of it into memory.

For a table with fifty columns, that means roughly 98 percent of the bytes loaded from disk are thrown away immediately. On a hundred-million-row table where each row is a kilobyte wide, the database reads about a hundred gigabytes to extract two gigabytes of ages. The other ninety-eight gigabytes travel from disk to memory and straight into the garbage.

02 / 06

Turning the table sideways

A column-oriented database stores each column as its own contiguous file (or file segment). All hundred million names sit together in one block. All hundred million ages sit together in the next. All emails in the next. The data is the same; the layout on disk is rotated ninety degrees.

Now when you ask for the average age, the database opens one file: the ages column. It never touches names, emails, or any of the other forty-eight columns. Instead of a hundred gigabytes, it reads roughly two gigabytes. The disk does less work. The bus moves less data. The CPU waits less. That is most of the speedup, and it comes from reading less, not from computing faster.

03 / 06

Why columns compress so well

The Short mentioned that identical ages like "22, 22" compress into "22 times 2." That is a real technique called run-length encoding (RLE): store each distinct value once along with a count of how many times it repeats in sequence. On a sorted age column, where thousands of consecutive rows might share the same value, RLE can shrink the data dramatically.

But RLE is only one option. Column stores stack multiple compression schemes because all values in a column share the same data type, which means the compressor can make type-aware assumptions that a general-purpose algorithm cannot.

Dictionary encoding replaces repeated strings (say, a country column with values like "US", "UK", "IN") with small integer codes and stores a lookup table once. A four-byte integer code is far cheaper than a variable-length string, especially when the column has only a few dozen distinct values.

Bit-packing shrinks integers that do not need their full bit width. If every age in the column is between 0 and 127, seven bits per value is enough. Store a million ages in seven megabits instead of thirty-two, and the savings are immediate.

Delta encoding stores the difference between consecutive values instead of the values themselves. For a column of monotonically increasing timestamps, the differences are tiny and compress further.

Real columnar engines layer these. Apache Parquet, the file format behind most data lakes, applies dictionary encoding first, then RLE or bit-packing, then a general compressor like Snappy or Zstandard on top. The result is often five to ten times smaller than the same data stored row by row.

04 / 06

Vectorised execution: making the CPU care

Once you have a tight, contiguous array of ages in memory, a second optimisation unlocks. Instead of looping over ages one at a time, the query engine loads a batch of values (typically 1,024 to 4,096 at once) into a CPU register and processes them together. This is vectorised execution.

Modern CPUs support SIMD (Single Instruction, Multiple Data) instructions that can add, compare, or filter four, eight, or even sixteen integers in a single clock cycle. A row store can rarely use SIMD because the values it needs are scattered across records with different types in between. A column store hands the CPU exactly what SIMD wants: a flat array of the same type, packed tightly, no gaps.

ClickHouse, DuckDB, and Snowflake all use vectorised engines. The performance difference is not theoretical. ClickHouse's benchmarks on the same hardware regularly show analytic queries finishing in a fraction of the time they take on a row-oriented engine, and vectorised execution is a large part of why.

05 / 06

The cost nobody mentions in forty-six seconds

Columnar storage is not free. The layout that makes analytics fast makes transactions painful.

Inserting a single new user into a row store means appending one block at the end of a file. Inserting a single new user into a column store means appending a value to every one of fifty separate column files, coordinating them so the positions stay aligned. That is fifty writes instead of one, and if any of them fails partway through, the columns disagree about how many rows exist.

Updating a single field is worse. In a row store, you find the row and overwrite one spot. In a column store, you have to locate the right offset in one column file and rewrite part of it, without disturbing the others. Most column stores handle this by batching updates into a delta store and merging them later, which adds latency and complexity.

This is why the world did not simply switch to column stores and forget row stores. The two layouts serve different jobs. Row stores power OLTP: checkout flows, login requests, single-record lookups where you need the whole row fast. Column stores power OLAP: dashboards, reports, aggregations across millions of rows where you need a handful of columns. Many modern systems (Google BigQuery, Amazon Redshift, CockroachDB's analytics mode) run both layouts side by side.

06 / 06

From research paper to every data warehouse

The idea of storing attributes separately goes back at least to Copeland and Khoshafian in 1985. MonetDB at CWI in the Netherlands built a working column-oriented engine in the 1990s. But the paper that brought column stores into the mainstream was C-Store, published in 2005 by a team led by Michael Stonebraker at MIT. C-Store showed that a column-oriented architecture, combined with compression and late materialisation (assembling full rows only at the very end of query execution), could outperform row stores on analytic workloads by an order of magnitude.

Stonebraker commercialised C-Store as Vertica. Within a few years, every major cloud provider had a columnar analytics engine: Amazon Redshift (2012), Google BigQuery (columnar internally from the start), Snowflake (2014). Open-source formats like Apache Parquet (2013) and ORC made column layout the default for data lakes. Today, if you run an analytical query against a large dataset, you are almost certainly reading columnar data, whether you know it or not.