Columnar storage is a way of laying data out on disk column by column instead of row by row. It’s the single biggest reason an analytical database can scan a billion-row table in seconds while your application database would grind.
Row storage vs. column storage
Take a table of orders with 40 columns. A row-oriented database — Postgres, MySQL, most transactional databases — stores each row’s 40 values together in a page:
[id:1, customer:88, product:"Widget", amount:19.99, ...40 fields]
[id:2, customer:12, product:"Gizmo", amount:44.50, ...40 fields]
That’s ideal for OLTP work: “fetch order 4471” or “insert this order” touches one contiguous chunk of disk.
A column-oriented database stores each column’s values together instead:
id: [1, 2, 3, 4, ...]
customer: [88, 12, 5, 91, ...]
amount: [19.99, 44.50, 7.25, 130.00, ...]
Fetching one whole order now means reading from 40 separate places — which is why you don’t run an application on a columnar database.
Why analytical queries get faster
Analytical queries are the mirror image of transactional ones: they touch very few columns across very many rows. SELECT date_trunc('month', created_at), SUM(amount) FROM orders GROUP BY 1 needs two columns out of 40.
A row store has to read every row in full to get at those two values, so it moves roughly twenty times more data off disk than the query actually uses. A column store reads only the two column files. On a wide table that alone is often an order-of-magnitude difference in I/O.
Columnar layouts also enable two further tricks. Vectorized execution processes a block of values from one column in a tight loop, which is far friendlier to modern CPUs than hopping between fields of a row. And because most engines keep per-block statistics like min and max, a filter such as WHERE created_at >= '2024-01-01' lets them skip entire blocks without decompressing them.
Compression
Values in a single column are all the same data type and usually similar to each other, which compresses far better than the mixed bag you get in a row. A country column with 200 distinct values across 500 million rows collapses under dictionary encoding; a sorted created_at column collapses under delta encoding; a mostly-null column collapses to almost nothing under run-length encoding.
Ten-to-one compression ratios are ordinary. That means less storage cost, and — because the bottleneck for a big scan is usually disk and network rather than CPU — less data to move, which makes queries faster again.
This is what people mean when they say a data warehouse is “optimized for analytics.” Snowflake, BigQuery, Redshift, ClickHouse, and DuckDB are all columnar, as are the file formats like Parquet that back most data lakes.
Related terms
Further reading
Put it to work
- Product performance dashboard — Dashboard
- Website analytics — Overview
