OLTP vs OLAP

The monthly report has started taking eleven minutes, and while it runs everything else on the database gets slower too. That is not a query to be tuned. It is a workload that does not belong on the same shape of storage as the one serving your customers. Both questions turn on the same fact — OLTP and OLAP are not two flavours of the same job, they are judged on opposite things, and a design that wins one loses the other.

An order-entry system is judged on one number: how fast a single order commits. A reporting query is judged on a different number: how fast one question gets answered, where the question touches a slice of everything the business has ever recorded. Neither system is trying to be fast at the other’s job, and neither should be.

Why row storage suits one and column storage the other#

A row store keeps every column of a row next to each other on disk: order id, customer, line items, amount, status, timestamp, all in one contiguous block. Fetching or updating one order is one page read, because everything the order needs is already sitting together. Ask that same layout for the total of one column across a million rows, and it has to read every row in full — pulling in customer, line items, status and timestamp just to add up the amounts — because the layout never separated the column you wanted from the ones you did not.

A column store inverts the layout: every value from one column sits contiguously, across every row. An aggregate over a million rows then reads only the columns the query named, skips the rest entirely, and compresses well besides — a column of repeated statuses or similar amounts has far less entropy than a row that mixes an id, a timestamp and free text. The trade is symmetric: fetching one whole order back out of a column store means visiting as many separate locations as it has columns, which is the query a row store answers in one read.

Column storage is not “no indexes.” Zone maps record the min and max value each block holds, so a query can skip whole blocks without reading them; dictionary and run-length encoding shrink what does get read. The mechanism is different from a B-tree index — pruning ranges of storage rather than walking a tree to one row — not absent.

What normalisation is for, and when it stops paying#

Normalisation exists to remove update anomalies: store a customer’s address in one place, and it can never disagree with itself across two orders. That is exactly what a till needs — every write touches a small, well-defined piece of the data, and the schema’s job is to make sure no write can leave two copies of a fact out of sync.

A report does not update anything. It reads. A star schema denormalises on purpose — one wide fact table joined to a handful of dimension tables — because there is no write to protect from disagreeing with itself, only a scan to make cheap. Every join a normalised schema forces on a query is a join a star schema paid for once, in advance, at load time instead of query time. Normalisation is a defence against a kind of write that an analytical workload never makes.

Picking one on purpose#

The case that shows the conflict clearest is the analytics query run straight against the production transactional database at month end. There is nothing wrong with the query — it is not malformed, it does not need an index someone forgot to add. It is just long: a scan over a year of orders to build a report, on the same machine a checkout is trying to commit against every few milliseconds.

On a modern MVCC database, that long read does not block the checkout’s writes the way it would under lock-based isolation — readers and writers work from their own snapshots and do not queue behind each other. The real damage is quieter: the scan pulls a year of pages through the buffer pool, evicting the handful of hot pages the checkout was relying on staying cached, and it competes for the same disk I/O. On Postgres specifically, a long-running transaction also holds back vacuum, letting dead row versions pile up until the tables and indexes bloat. The checkout queue does not stall because a lock is held; it stalls because the machine underneath it is now doing a different job than the one it was sized for.

That is the real failure: not a bad query, but two workloads with incompatible judgement criteria sharing one machine. The planner’s crossover between an index scan and a full scan, covered in why your index is not being used, is the same argument at a smaller scale — and an analytics query over most of a table is the case where a full scan is genuinely the right choice, not a sign the query needs fixing. The choice between row and column storage is one level up from a related trade covered in B-tree vs LSM-tree: write cost against read cost, decided at the level of the storage engine rather than the table layout.