Analytics and
column storage
Same data, completely different access pattern. Why warehouses lay data out sideways.
→ next · ← back · N notes · F full screen
Two very different ways to use a database.
| Transaction processing (OLTP) | Analytics (OLAP) | |
|---|---|---|
| Typical read | A few rows, looked up by key | Aggregate over millions of rows |
| Typical write | Random, low-latency, from users | Bulk import (ETL) or event stream |
| Used by | End users, through the app | Analysts, for decisions |
| Data means | Latest state of things | History of events over time |
| Size | Gigabytes to terabytes | Terabytes to petabytes |
Data warehousing
A separate database, built for questions.
Extract, transform, load.
Copy data out of every OLTP system into one read-only database, shaped for analysis. Analysts can query freely without slowing the business down.
Star schema: one huge fact table, many small dimensions.
Facts are events: each payment is a row, often with 100+ columns.
Dimensions answer who, what, where, when, how: merchant, method, date, region.
A snowflake schema normalises dimensions further.
Column-oriented storage
Read only the columns you need.
Store each column on its own.
A fact table can have hundreds of columns, but a typical query touches four or five. In a row store you still read every column of every row. In a column store each column lives in its own file, and row k is the k-th entry in every file.
Columns compress beautifully.
WHERE country IN ('DE','NL') = bitwise OR of two bitmaps. AND across columns = bitwise AND.
Few distinct values per column → one bitmap per value → long runs of zeros → run-length encoding. Then the CPU chews through compressed chunks in its cache using SIMD: vectorised processing.
Sort order and writes.
Pick a sort key
Sort whole rows by e.g. date, then merchant. Date-range queries skip most data, and the first sort column compresses into a handful of runs.
Several sort orders
Replicas can store data in different orders, so different queries each find a helpful one (C-Store, Vertica).
Writes go LSM-style
Inserting into the middle of sorted compressed columns is painful. Buffer writes in memory, then merge into new column files in bulk.
Precomputed aggregates
Do the work once, read many times.
Materialised views and data cubes.
A materialised view stores the query result. A virtual view is just a saved query.
A data cube precomputes totals across combinations of dimensions, e.g. revenue by date × merchant.
Upside
Some queries become instant reads.
Downside
Less flexible: you can only slice by dimensions you precomputed. Updates on write cost extra.
Now, our system.
Where do reports run?
Are monthly settlement or finance reports hitting the production PostgreSQL primary? A replica? A warehouse?
Wide scans
Which of our queries scan millions of rows but only use a few columns? Those are column-store candidates.
Sort and partition by date
Our data is naturally time-ordered. Are big tables partitioned or clustered by date so ranges skip old data?
Precompute
Which daily totals do we recompute on every dashboard load that could be a materialised view?
Encoding and evolution
How data survives being written by one version of your code and read by another. Chapter 4.