"How much revenue did each store bring in over January?" — a seemingly harmless question from the business. And it's capable of knocking over your application's live database. The catch isn't the size of the data — it's that this is a query from another world. Databases and the queries against them split into two big classes, and those classes are built oppositely: what's good for one is agony for the other. Let's go through both worlds and the bridge between them.
Two ways of reaching for data
OLTP (online transaction processing) is the world of applications — the ordinary work with users. Someone placed an order, opened a profile, updated a cart. Each such query touches a few rows found by key: found via an index, read or changed, answered in milliseconds. The data here is the current state of the world right now. This is exactly what B-trees and the whole discipline of transactions are tuned 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 runs over millions of rows but takes just two or three columns from each and rolls them into a single total — a sum, a count, an average. The data here is the history of every event over years. And the bottleneck is completely different: not "how fast can we find the right row" but "how many bytes per second can we push through a scan."
The same SQL language can do both. But storing data for these two kinds of query pays off differently — and everything else follows from that.
The data warehouse and ETL
Letting analysts straight into the application's live database is bad on both sides. Their heavy scan queries eat resources away from user transactions (and at peak hour users will feel it). And in a large company there are many OLTP systems — the website, the warehouse, delivery, the CRM — and one query still can't ask all of them at once.
So analytics is moved to a separate database — a data warehouse. It's a read-only copy of data from all the OLTP systems, gathered in one place. It's assembled by an ETL process — three steps: extract (pull from the sources), transform (reshape into a form convenient for analytics), load (into the warehouse). It runs either as periodic dumps (say, overnight) or as a continuous stream of events.
A small company doesn't need a warehouse — its data fits in an ordinary PostgreSQL, or even a spreadsheet. The warehouse shows up when there are many OLTP systems and the history has grown to terabytes.
The star schema: facts and dimensions
In the application world, database schemas differ for every task. But analytical warehouses are almost all built the same way — as a star schema.
At the center of the star is the fact table: one row per event ("a customer bought such-and-such a product at such-and-such a moment"). It has hundreds of columns and billions of rows. Around the center are the dimension tables: product, store, customer, date, promotion. The fact references the dimensions by foreign keys, and the dimensions themselves answer "who, what, where, when, and why" about each event.
Even the date is pulled out into a separate dimension — a table with one row per calendar day and flags like "holiday/weekday." Without it you can't ask "how do weekend sales compare to weekdays." (There's a variant where dimensions are split into sub-tables even further — it's called a "snowflake" — but in practice the flatter star usually wins: it's easier for the analyst to work with.)
Columnar storage: why analytics flies
Ordinary OLTP databases store data by rows — a whole row sits on disk as one chunk. That's perfect for "read the whole order." But an analytical query against a hundred-column table touches only three — and a row store still has to lift whole rows off disk, with all the unneeded columns.
Columnar storage flips the layout: now each column lives in its own separate file, and a row is reconstructed by position (the fifth value in each file is the same fifth row). A query reads only the columns it needs — already a manyfold win. But then a second, even stronger effect kicks in. Values within one column resemble each other (the "product" column has billions of rows but maybe ten thousand distinct values), so columns compress beautifully. A billion-row column shrinks to megabytes, filters turn into fast bitwise operations, and the scan runs up against the CPU cache rather than the disk.
This is exactly how ClickHouse, Vertica, Redshift, and the Parquet file format work. And it's precisely why ClickHouse is not a replacement for PostgreSQL but a tool from a different world. The price for read speed is expensive writes: you can't insert a single row "into the middle" of compressed sorted columns, so columnar databases accept writes the LSM way — accumulate in memory and flush in batches.
Materialized summaries
If a thousand queries a day compute SUM(net_price) along the same axes, honestly scanning everything each time is wasteful. The answer can be computed ahead of time and stored in a materialized view — a table with the query's result already prepared (in PostgreSQL that's a materialized view). The extreme form of this idea is an OLAP cube: a precomputed grid of totals over combinations of dimensions (date × product × store), where "revenue for yesterday" is a single cell read instead of scanning millions of rows.
The price is flexibility. In a cube you can't ask for something that isn't among its dimensions (say, "the share of sales of products under 100 rubles," if price wasn't set up as a dimension). That's why warehouses still keep the raw events and hold summaries on top — as an accelerator for the most frequent queries.
Where this applies
The "OLTP or OLAP" fork shows up earlier than you'd think. The very first "sales by day" dashboard is already an analytical query, and the question "run it against the live database or set up a separate store" is exactly what this article is about. The answer, step by step: while the data is small — a PostgreSQL read replica and materialized views are enough; once the history has grown and heavy scans have become routine — you set up a separate columnar store and a stream of events into it.
Where beginners stumble:
- Running analytics against the live database. One heavy scan at peak hour, and the response time of ordinary user queries slides into the tail.
- Building one "universal" schema for everything. A normalized OLTP schema is awkward for the analyst, and a star is awkward for the application. These are two different views of the same data — and that's fine.
- Using ClickHouse for point reads ("find an order by id") — on that pattern a columnar store loses cleanly to PostgreSQL. And vice versa.
- Hiding all answers in cubes. A summary with no raw events is a dead end: the very next new business question will demand a dimension the cube doesn't have.
What to read next: B-trees and LSM — the engines under both worlds; PostgreSQL or ClickHouse — the same fork in practice; modeling in ClickHouse and PostgreSQL materialized views — both worlds in action.