Data warehouse vs database
One changes a few rows safely while people are watching; the other reads most of them at once. Same data, opposite priorities — and pointing a year-end summary at the system taking today's orders is where the trouble starts.
Same records, opposite jobs — like a doctor seeing one patient against a researcher reading a decade of case notes. The doctor has to be right about the person in front of them now; the researcher needs every file at once, and asking the doctor to run that study empties the waiting room.
Somebody has told you that the reporting queries need a warehouse, and it is not obvious whether that means new software or a new schema on what you already run. It usually means neither, until you know which question is actually being asked. The distinction is about workload, not product — the same relational engine sitting under your application can serve either need, and can serve both of them badly at once, which is exactly how this comparison tends to get raised in the first place.
Two workloads, not two products#
| Aspect | Database (OLTP) | Warehouse (OLAP) |
|---|---|---|
| How data arrives | One row at a time, as a transaction happens. | In batches, loaded after the fact. |
| How it is read | A handful of rows, found by key. | Most of a table, scanned and summarised. |
| What correctness demands | A write that lands whole, seen consistently by whoever reads it next. | An answer that agrees across sources, however many rows it touches. |
| What the schema is shaped for | Cheap, safe writes. | Cheap, wide reads. |
The standard names for these two workloads are OLTP, online transaction processing, and OLAP, online analytical processing. Both names describe a pattern of reads and writes, not a piece of software — the distinction is workload, not product. An engine sold and configured as an ordinary relational database can run an OLAP-shaped workload if you point enough scanning queries at it; a system marketed as a warehouse is still, underneath, answering to the same relational logic as anything else, just arranged and loaded to make wide reads cheap. Buying a product with “warehouse” in its name does not, by itself, answer which workload you actually have running against it.
What each one is allowed to be slow at#
Every schema is a choice about what it accepts being slow, because nothing is fast at everything. The transactional side accepts a slow full-table report — nobody expects a single accountant’s query to come back instantly — because it must never be slow for the customer standing at the till, waiting on one order to be confirmed. The analytical side makes the opposite trade: it accepts a slow single-row lookup, because nobody is waiting on one row, in exchange for a scan across a year of them staying cheap.
That choice shows up in the schema itself, not just in what runs fast. Removing duplication — normalisation — is what buys the transactional side its cheap, safe writes: a fact lives in exactly one place, so one write touches one row and nothing in the schema can end up disagreeing with itself. The bill for that arrives at read time. A question that needs several of those facts back together has to walk the relationships between them, one join per table the normal form split apart, and a report reading a wide slice of the business pays that walk on every run. An index that makes a normalised schema’s point lookups fast is dead weight on a query scanning most of the table anyway, and the shape that keeps a write cheap on the transactional side is the same shape that makes the year-end query join its way through several tables to reach an answer a warehouse schema would have kept in one place.
When the replica stops being enough#
The failure that shows up over and over is a read replica pressed into service as a warehouse. It is the cheapest-looking move available: the data is already there, it is already current, and nobody has to model anything before the first report runs. For a while nothing about it looks wrong. It keeps working until the reports get real, and then three things arrive together — a query that reads most of a large table and holds resources while it does, a schema still shaped for writes that makes that query slower every time someone adds to it, and a change on the transactional side that quietly breaks a report nobody realised depended on the column that just moved.
The replica solved exactly one problem: keeping the heavy reporting query off the system a customer is waiting on. It never touched the other set of problems — an agreed shape for what one reporting row means, a schema arranged so a wide question stays a short one, a load that survives the transactional schema changing under it — and it is easy to mistake solving the first for having solved all of them, because the dashboard was working right up until it wasn’t. The point where a query planner gives up on an index and reads the whole table is the normal case for this kind of report, not the exception a transactional workload is tuned to avoid, and a schema that was never shaped for scanning pays for every one of those wide reads in full.
None of that makes the replica a mistake — it is a perfectly reasonable first step. What turns it into one is leaving it there once real decisions start getting made from what it reports — the moment a shortcut that was supposed to buy time starts being treated as the finished thing. Working out how to model what comes after that point is its own subject, closer to the persistence roadmap than to a comparison page, and this article is not a node on it — the ground it touches belongs to that roadmap, not to this page.
| Situation | Take | Because |
|---|---|---|
| A customer is waiting on the answer | Database | It is built for one row, right now. |
| The question spans a year | Warehouse | Built to scan, not to seek. |
| The data changes as you read it | Database | Consistency is the point. |
| Several sources must agree | Warehouse | Somewhere has to hold the agreed version. |
IF YOU REMEMBER ONE THING
Warehouse and database name two workloads, not two products, and each is deliberately slow at the other’s job. A read replica moves the heavy query off the machine a customer is waiting on and settles nothing else, which is why it works right up until somebody starts making decisions from what it reports.
Questions people also ask
5 QUESTIONSCan one database do both jobs?
A single relational engine can technically run both workloads, and for a small enough amount of data it can get away with it for a while. What breaks first is not capability, it is contention: a scan reading most of a table holds resources a transaction is waiting on, and a schema shaped to make one write cheap makes the other kind of query walk through more of the schema to answer.
Is a data warehouse a different product?
No. Warehouse and database name workloads, not products — the same relational engine can run either, well or badly, and it can also be asked to run both against the same tables at once, which is exactly the setup this article's third section describes going wrong. Nothing about buying a particular piece of software settles which workload you actually have.
What is a data mart?
A narrower slice of the same analytical workload, scoped to one team's or one subject's recurring questions rather than the whole organisation's. It answers the same kind of question a warehouse does — wide reads across history — just over less of the data, which keeps it out of this article's scope: it is a modelling choice within the analytical side, not a third workload alongside the two compared here.
Where does a data lake fit?
Upstream of the question this article asks, more than beside it. A lake holds data in whatever shape it arrived in, with no schema imposed until something reads it, so it is not answering either workload's question yet. It commonly feeds a warehouse rather than replacing the choice between the two workloads described here.
Do I need a warehouse for one application?
Usually not from day one. One application's own database can often answer its own reporting questions well enough without a second workload alongside it. The signal that you need one is combining what more than one source recorded, or noticing that reporting queries are competing with the transactions the application depends on to stay correct.