The idea: the same table, two ways to lay it out
A table is a grid of rows and columns, but a disk stores a line of pages, and it reads and writes a whole page at a time. A row store puts the values of one row next to each other, so a page holds a few whole rows. A column store puts the values of one column next to each other, so a page holds one column of many rows. The page fills the same table, sales(id, region, price, qty), into both layouts and runs the same query on both. The highlighted pages are the ones each store has to read.
Row store: one page per row
PostgreSQL heap pages, InnoDB index pages, SQLite and most databases behind web apps store rows. A lookup by primary key finds the row's page through an index and reads one page, which already has every column of the row. An insert, update or delete also changes one page. That is exactly the work of OLTP (online transaction processing): many small reads and writes of whole rows (Demo: point lookup id = 5, Demo: insert one row).
The cost shows up in a query over many rows that needs few columns. To add up price, the row store reads every page of the table, and three quarters of what it reads are ids, regions and quantities it throws away.
Column store: one page per column
A column store keeps each column in its own pages, in the same row order, so position 5 of every column belongs to row 5. SUM(price) reads only the price pages (Demo: SUM(price)); a filter reads the filter column first and then only the columns it returns (Demo: AVG(price) WHERE region = 'EU'). That is the work of OLAP (analytics): scans of a few columns over millions of rows. Vertica, ClickHouse, Snowflake, BigQuery, Amazon Redshift and DuckDB store columns, and so do the Parquet and ORC file formats.
The price is paid on whole rows. SELECT * WHERE id = 5 must read the id column to find the position, then one page of every other column, and stitch the values back together (tuple reconstruction). One new row means one write in every column.
Columns compress
Values of one column are of one type and often repeat, especially in sorted or slowly changing data. Run-length encoding (RLE) stores a run of equal neighbours once, with a count. In a row page the neighbours are different columns of one row, so there is nothing to merge (Demo: run-length compression, then COUNT).
Here the column store goes from 32 to 24 cells and the row store stays at 32. With real data the gap is larger: column stores also use dictionary, delta and bit-packing encodings, and typically compress 5–10×. A query can even work on the runs directly: COUNT(*) WHERE region = 'US' reads 3 runs instead of 8 values.
When to pick which
| Workload | Row store | Column store |
|---|---|---|
| Fetch one row by key | ✓ 1 page | ✗ 1 page per column |
| Insert, update, delete one row | ✓ 1 page | ✗ 1 page per column, often batched |
| Aggregate one column over all rows | ✗ every page | ✓ that column only |
| Filter on one column, return another | ✗ every page | ✓ the two columns |
| Compression | low (mixed types in a page) | ✓ high (RLE, dictionary, delta) |
| Typical use | OLTP: orders, users, payments | OLAP: reports, dashboards, data warehouses |
Many systems use both. Analytics columns are fed from a row-store database by change data capture or nightly loads. Some engines are hybrids: SQL Server columnstore indexes, MySQL HeatWave, Oracle In-Memory and Citus columnar tables on PostgreSQL keep a column copy beside the rows. Column stores themselves buffer new rows in a row-format delta store and merge them into the columns in batches.
See also Inside an InnoDB index page and Inside a PostgreSQL heap page for what a row-store page really holds, and LSM tree vs B+ tree for another storage trade-off between reads and writes.
What the page leaves out
Real pages are 8–16 KB and hold hundreds of rows or thousands of column values, and a column chunk spans many pages; here a page holds 12 cells. The page counts reads only, not CPU: column stores also gain from running tight loops over one type (vectorized execution, SIMD) and from late materialization. It leaves out indexes on the column store (zone maps with the min and max of each chunk, which let a scan skip chunks), sort keys and projections, dictionary and delta encoding, the delta store and its merge, and caching: a page already in memory costs nothing to read again.