Column stores
The conceptHow a dashboard totals a billion sales without reading whole rows
A column store keeps all values of one column together on disk, not each row's values together. The B-tree pages from earlier lessons hold whole rows, so reading one field loads the rest of its row too.
Take a sales table with 50 columns and 10 million rows, and the report "revenue per country". A row store reads all 50 columns of every row; a column store opens only the country and revenue files.
Rows still line up by position: the 7th value in every column file belongs to row 7. That way the database can stitch a full row back together when a query needs one.
What it’s forEstimates how much a scan reads from disk in each layout.
bytes read ≈ N × Σ wᵢ (over only the columns the query uses)
- N
- number of rows scanned, e.g. 10 million
- wᵢ
- bytes per value in column i, e.g. 8 for a number
- Σ
- add up the widths; a row store sums every column, a column store only the ones queried
What it givesThe disk traffic of a scan, which in analytics is usually what limits speed.
- Row store: 10,000,000 rows × 50 columns × 8 bytes = 4 GB
- Column store, 2 columns: 10,000,000 × 2 × 8 bytes = 160 MB
- That is 2/50 = 4% of the bytes, before any compression