One table, two opposite ways to read it
OLTP touches a few rows by key, most of each row, in milliseconds. OLAP scans billions of rows but reads only a few columns.
Just add indexes?
Bad: Index for the reports
Aggregates still read most rows; every index slows checkout’s writes.
Good: A real column store
Covering indexes are partial column copies. Past a point, the real thing is cheaper.
50×
less I/O reading 2 of 100 columns
Assume 1 billion orders, 100 columns averaging 8 bytes: 800 GB. Summing amount by city reads just city and amount: 16 GB, before compression.
One city column: 9 MB of names becomes about 100 bytes
Assume 9-byte names, 1M rows, 8 cities. Dictionary codes need 3 bits each, 24× smaller. Sorted, run-length encoding stores just 8 runs.
Five years of orders, sorted by date. A query wants one month.
Zone maps
Each 1 MB block records its min and max. Redshift skips every block that can’t match: up to 98%.
Keep scans off the primary: stream changes out
Long scans steal checkout’s I/O and hold back vacuum. CDC streams each change through Kafka to ClickHouse for live dashboards and a warehouse for analysts.
The price of columns: small writes
- One inserted row touches every column file
- So engines buffer writes and merge in the background
- Load in batches; treat updates and deletes as bulk jobs
Remember
Rows for checkout, columns for analytics, fed by CDC. Reading 2 of 100 columns is 2% of the bytes.