Row vs column storage: an interactive comparison of OLTP and OLAP layouts

A row store keeps each row’s values together on disk, which is ideal for reading or changing one record: the shape of most application traffic, or OLTP. A column store keeps each column’s values together, which is ideal for queries that scan millions of rows but only a few columns: the shape of analytics, or OLAP. PostgreSQL and MySQL store rows; ClickHouse, BigQuery, Snowflake, Redshift, DuckDB and Parquet files store columns.

The playground stores one orders table both ways and shows, for four queries, which cells each layout has to read and what that costs at the table size you choose.

SELECT country, sum(amount) FROM orders GROUP BY country

Why columns win for analytics

Totalling revenue by country needs 2 of the table’s 7 columns. A row store cannot read part of a row, so for 10 million orders it scans all 1.5 GB of pages. A column store reads just the country and amount files, and because a column holds similar values side by side it compresses well: 38 MB, 39 times less. Counting orders by status reads one column of 5 distinct values, 4 MB. Three more effects add to that:

• Compression per column. Dictionary encoding turns repeated strings into small integers, run-length encoding stores long runs once, and delta encoding stores sorted timestamps as small differences.
• Skipping blocks. Each block records the minimum and maximum of each column, so a filter on the sort key skips whole blocks without reading them.
• Vectorized execution. Processing a column as an array lets the CPU apply one operation to many values at once, which is much faster than walking rows.

Why rows win for applications

Fetching one order by id is a B-tree walk to a single page in a row store: 4 page reads, 33 KB. A column store has to read and decompress a block from each of the 7 column files to rebuild that one row, 335 KB. Looking up a customer’s latest orders is worse without an index: the column store scans the whole customer_id column, 47 MB against the row store’s 188 KB. Writes follow the same pattern. A row store updates one page in place; a column store has to touch every column file, so it is built for large batched inserts and treats updates and deletes as expensive background rewrites.

So most systems use both: the application writes to a row store, and change data capture or a nightly export copies the data into a column store or Parquet files for analytics. Note that “wide-column” databases such as Cassandra and HBase are not column stores; they store rows grouped by partition key.

A columnar table in ClickHouse

-- ClickHouse: a MergeTree table stores each column in its own file, sorted by ORDER BY
CREATE TABLE orders (
    order_id    UInt64,
    customer_id UInt64,
    created_at  DateTime,
    country     LowCardinality(String),   -- dictionary-encoded
    status      LowCardinality(String),
    amount      Decimal(12, 2),
    note        String
) ENGINE = MergeTree
ORDER BY created_at;

-- reads 2 of the 7 columns, compressed, in blocks of 8,192 rows
SELECT country, sum(amount) FROM orders GROUP BY country;

In a system design interview

Separate the two workloads when you see both: “Orders live in PostgreSQL, where checkout reads and writes single rows through indexes. Every change flows through CDC into ClickHouse, sorted by time, where dashboards aggregate months of orders by reading only the columns they need. The dashboards are a few seconds behind, which is fine for analytics.” Expect the follow-ups: how fresh the analytics need to be, how deletes for privacy reach the column store, and how you choose its sort key.

Read the guide to analytics and OLAP or data lakes and lakehouses, see the B-tree behind row stores’ indexes, or watch a strong candidate design an ad click aggregator.

Questions people ask