"What was each store's revenue in January?" — an innocent business question fully capable of taking down the production database. The reason is not the data size but the fact that this query belongs to a different world: databases and queries split into two big classes with opposite access patterns, and what is easy for one is painful for the other. Let's walk through both worlds and the bridge between them.
Two access patterns
OLTP (online transaction processing) is the world of applications. A user placed an order, opened a profile, updated a cart: each request touches a few rows by key — found via an index, read or updated, answered in milliseconds. The data is the current state of the world. This is exactly what B-trees and the whole discipline of transactions are built for.
OLAP (online analytical processing) is the world of analytics. The questions come not from a user but from an analyst: "how many more bananas did we sell during the promotion than usual?" Such a query scans millions of rows but takes two or three columns from each, folding them into an aggregate — a sum, a count, an average. The data is the history of events over years. The bottleneck is not seek time but bandwidth: how many bytes per second a scan can pump through.
The same SQL can do both — but the storage subsystems underneath these queries need to be different.
The data warehouse and ETL
Letting analysts into the production database is a bad idea from both sides: their scanning queries steal resources from user transactions, and a company's dozens of OLTP systems (site, warehouse, delivery, CRM) cannot be queried as one anyway. So analytics lives in a separate database — the data warehouse: a read-only copy of data from all OLTP systems, assembled by an ETL process (extract — transform — load): pull from the sources, reshape into an analytical schema, load into the warehouse — via periodic dumps or a continuous event stream.
Small companies do not need a warehouse — their data fits into a regular PostgreSQL or even a spreadsheet. The warehouse appears when OLTP systems multiply and the history grows to terabytes.
The star schema: facts and dimensions
OLTP schemas are diverse; in analytics almost every warehouse looks the same — the star schema. At the center is the fact table: one row per event ("a customer bought a product at this moment"), hundreds of columns, billions of rows. Around it are dimension tables: product, store, customer, date, promotion. A fact references dimensions by foreign keys; the dimensions answer the who, what, where, when, how, and why of the event.
Even the date is its own dimension: a row per calendar day with attributes like "holiday/weekday" — otherwise you cannot ask "how do weekend sales compare to weekdays." The variant with further-normalized dimensions (product category as its own table) is called a snowflake, but in practice the more denormalized star usually wins — it is easier for analysts to work with.
Columnar storage: why analytics flies
OLTP databases store data row by row: the whole row sits together on disk, which is perfect for "read the order in full." But an analytical query over a hundred-column fact table touches three of them — and a row-oriented database still lifts entire rows off the disk.
Columnar storage flips the layout: each column is its own file, and rows are reassembled by position (the k-th value of every file is one row). A query reads only the columns it needs — already an order-of-magnitude win. Then the second effect kicks in: values within one column resemble each other (the "product" column has billions of rows but ten thousand distinct values), so columns compress extremely well — bitmaps per distinct value, run-length encoding. A billion-row column shrinks to megabytes, filters become bitwise operations, and the scan's bottleneck moves from the disk to the CPU cache. This is exactly how ClickHouse, Vertica, Redshift, and the Parquet format are built — and why ClickHouse is not a PostgreSQL replacement but a tool of the other world.
Writes get more expensive in exchange: you cannot insert a row "into the middle" of sorted compressed columns — so columnar databases accept writes the LSM way: accumulate in memory, flush in batches.
Materialized aggregates
If a thousand queries a day compute SUM(net_price) over the same axes, honestly re-scanning every time is wasteful. The answers get cached in a materialized view — a table holding a precomputed query result (in PostgreSQL, a materialized view). The extreme form is the OLAP cube: a grid of aggregates over dimension combinations (date × product × store), where yesterday's total is one cell read instead of millions of rows.
The price is flexibility: a cube cannot answer what is not among its dimensions ("share of sales from products under $1" — if price is not a dimension). So warehouses keep the raw events and treat aggregates as an accelerator for routine queries.
Where this applies
The OLTP-or-OLAP fork shows up sooner than you expect: the first "sales by day" dashboard is already an analytical query, and "run it against production or set up separate storage" is precisely this article's question. The staged answer: while the data is small — a PostgreSQL read replica and materialized views; once history has grown and scanning queries are daily life — a separate columnar store fed by an event stream.
Where beginners stumble:
- Running analytics on the production database. One heavy scan at peak hour — and user request latency moves into the tail.
- Building a "universal" schema. A normalized OLTP schema is awkward for analysts; a star is awkward for the application. They are two representations of the same data, and that is fine.
- Picking ClickHouse for point reads ("find order by id") — columnar storage loses to PostgreSQL outright on that pattern, and vice versa.
- Hiding all answers in cubes. Aggregates without raw events are a dead end: the first new business question will need a dimension that is not there.
What to read next: B-trees and LSM — the engines under both worlds; PostgreSQL or ClickHouse — the practical fork; ClickHouse modeling and PostgreSQL materialized views — both worlds in practice. The primary source is Martin Kleppmann, "Designing Data-Intensive Applications," chapter 3.