Two stores for one truth

Why does the same data need one database to record it and a different one to answer questions about it?

An organisation has one set of facts: orders placed, payments taken, items shipped. It stores them in a database. Then someone asks how average order value moved by region last quarter, and the answer arrives from an entirely different system, holding a copy of the same facts, arranged differently.

This looks like an obvious mistake. One truth, stored twice, with machinery in between to keep the copies in step — more hardware, more failure modes, and a copy that is always slightly behind. Why would anyone accept that instead of just querying the original? The answer is not organisational inertia, though inertia keeps some of these systems alive. There is a real physical reason, and it is worth digging for.

Compare the two jobs honestly, because they look similar and are not.

The first job: a customer places an order. That touches a handful of rows — one order, some lines, an inventory count — and it must be exactly right, immediately, with a hundred other customers doing the same thing at the same moment. The characteristic shape here is many small operations, each touching few rows, each needing correctness right now.

The second job: average order value by region, last quarter. That touches nothing in particular and everything in general — millions of rows, but only two or three columns out of the forty each row has. And it must be approximately current, not instantaneously current: a report computed from data as of this morning is a perfectly good report.

Now ask the question that decides everything. If you were laying these rows out on disk, how would you arrange them for each job?

For the first, keep each order's fields next to each other. Writing an order is then one contiguous write; reading it back is one contiguous read. That is row-oriented storage, and it is the natural fit.

For the second, that layout is close to a disaster. To average one column across ten million rows, you must read all ten million rows in full — forty columns' worth of bytes — to use one. You pay roughly forty times the input-output you need.

So turn it ninety degrees: store all the values of one column together. Now the average reads exactly the bytes it needs. And something else falls out, which is the part people underestimate: a column holds values of one type, often highly repetitive — a region column across ten million orders might hold a dozen distinct strings. That compresses enormously, so you read far less from disk, and you can compute directly on the compressed form. This is columnar storage, and it is why analytical engines are not merely faster but faster by an order of magnitude or more on this shape of question.

But the same rotation ruins the first job. Writing one order now means touching forty separate column structures. Reading one order back means gathering forty scattered pieces. A layout that is excellent for reading a slice across everything is poor for reading or writing everything about one thing.

Do you see the shape of the answer? The two workloads want opposite physical layouts, and a layout is a property of stored bytes, not a switch you flip per query. That is the irreducible part. Everything else — separate hardware, separate tuning, indexes and constraints versus large scans — follows from it or reinforces it.

There is a second, more mundane reason worth naming: isolation of load. A single analyst's query scanning a quarter of history can consume enough of the machine to slow down every checkout on the site. Keeping the heavy readers off the system that takes money is a decision many teams would make even if the layouts agreed.

Two honest caveats. First, the boundary is not eternal. Modern systems blur it: some databases keep a row store and a column store of the same table simultaneously, and specialised engines serve real-time analytics at low latency. The distinction is between workloads, not between two immovable product categories. Second, the copy is behind. Whether it lags by seconds or by a day is a design choice, but it always lags by something, and any question whose answer must be exactly current at the instant of asking belongs on the recording system.

The common misreading is that the analytical store is a performance optimisation bolted on because the main database was too slow, and that a faster main database would remove the need. It would not. You could give the transactional system unlimited hardware and it would still read forty times more bytes than necessary for a column aggregate, because that is what its layout obliges it to do.

It also helps to be clear that "two stores" does not mean two truths. Only one system accepts new facts. The other derives its contents from that one, which is why disagreements between them are always bugs in the pipeline and never a legitimate difference of opinion — and why "which system is the source of truth" must have exactly one answer, written down.

• How columnar compression schemes let engines compute without decompressing.
• Change data capture, and why it beats nightly batch extraction for keeping the copy fresh.
• Where the boundary is dissolving: hybrid engines and real-time analytical stores.

Every term the collection defines is gathered in the glossary.