Rows for Transactions, Columns for Questions

Two queries against the same table.

-- one order, every column
SELECT * FROM orders WHERE id = 91827;

-- one column, every order
SELECT region, SUM(total) FROM orders
 WHERE placed_at >= '2026-01-01'
 GROUP BY region;

The first touches one row and wants all 40 of its columns. The second touches 80 million rows and wants three of them.

No single physical layout is good at both.

Two workload shapes

OLTP — online transaction processing. Small numbers of rows, fetched by key, read and written by users in the critical path of a product. Latency matters because a human is waiting. This is your application database.

OLAP — online analytical processing. Enormous numbers of rows, aggregated down to a handful of numbers, read by analysts and dashboards. Throughput matters more than latency, and the data is usually read-only.

Running both on one database is possible and often correct at small scale. It stops working when the analytical scans start evicting the transactional working set from cache, and the checkout page gets slow every time someone opens a dashboard.

Why row storage loses on analytics

A row store keeps each row’s columns together on a page — which is exactly right for SELECT * by id.

For the aggregate, it is close to worst case. To read region and total, the engine loads pages containing whole rows, pulling all 40 columns through memory bandwidth and cache to use 3 of them. Roughly 90% of the bytes moved are discarded.

Column storage

Store each column in its own file, with values in the same row order across files:

region.col   [ EU,  US,  EU,  APAC, US, ... ]
total.col    [ 42,  17,  93,  8,    55, ... ]
placed_at.col[ ... ]

Row 3 is now the third entry in every file. To answer the aggregate you read region, total and placed_at and never touch the other 37 columns. On a 40-column table that is a tenfold reduction in bytes read before any other optimisation.

Compression, and why sorting matters so much

A column holds values of one type, from one domain — and that compresses far better than a row’s mixture of ints, strings and timestamps.

Take a region column with four distinct values across 80 million rows. Bitmap-encode it: one bitmap per distinct value, one bit per row.

region = EU    1 0 1 0 0 1 ...
region = US    0 1 0 0 1 0 ...
region = APAC  0 0 0 1 0 0 ...

WHERE region IN ('EU','US') becomes a bitwise OR of two bitmaps — an operation CPUs do at tens of gigabytes per second.

Now sort the whole table by region. Every bitmap becomes long runs of identical bits, which run-length encoding reduces to almost nothing:

region = EU    20,000,000 ones, then zeros

Since the data is read-only, some warehouses store the same data sorted several different ways, and let the planner pick the copy that suits the query. Space is cheap; scanning is not.

Materialised views and cubes

If the same aggregate is computed constantly, compute it once and store the result. A materialised view is a query result kept on disk and refreshed when the underlying data changes.

A data cube takes it further: precompute aggregates across every combination of a few dimensions, so SUM(total) by region and month is a lookup rather than a scan.

The cost is the usual one, in a new place. A materialised view is derived data, so every write must eventually update it, and it is stale between refreshes. It also makes the queries it anticipated fast and does nothing for the ones it did not — which is why warehouses keep the raw data too, and treat cubes as a cache rather than as the source of truth.

What to take away

Access pattern decides layout. Rows for fetching whole records by key, columns for scanning a few fields across many records. Column stores win on the bytes they avoid reading, and sorting is what makes their compression extraordinary — which is why analytical systems are append-only, heavily sorted, and physically separate from the database serving your users.