OLTP vs OLAP
The two workloads compared on every axis that actually differs — plus an honest account of where the line has blurred, where it has not, and why the distinction still decides your architecture.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
Which properties genuinely differ between the two workloads, and which supposed differences are folklore?
Whoever has to decide whether this company needs a second data system yet. That decision is usually made from a diagram someone saw at a conference; it should be made from four named constraints — isolation, history, multiple sources and query shape — and this lesson is the material for naming them (Trusting Data).
The unit being compared is one statement, and the axis that generates every other difference is the pair (rows touched, columns touched). Almost everything on the comparison table below is downstream of that pair, which is why the table is short and the consequences are long.
Learn the comparison as a table of adjectives — transactional versus analytical, normalised versus denormalised, row versus column, fast versus big — and use it to classify systems. It is a real distinction and the table is broadly right, so this survives most conversations.
Classifying by adjective produces "we are analytical, so we need a warehouse" for a company whose entire dataset fits in memory on one machine and whose real constraint was one long-running report (Workload Isolation).
- Classifying by adjective produces "we are analytical, so we need a warehouse" for a company whose entire dataset fits in memory on one machine and whose real constraint was one long-running report (Workload Isolation).
- It also produces the opposite: "we are operational, our database is fine", right up to the point where a five-year question arrives and the operational schema has been overwriting the answer monthly (Slowly Changing Dimensions).
- The adjectives do not survive contact with products. Operational databases acquire columnar storage options; analytical systems acquire row-level updates and streaming ingestion. Anyone reasoning from a product's category rather than from their own query shape is now reasoning from a label (The Lakehouse).
- It hides that the two workloads have opposite *failure signatures*, which is the difference that matters most on-call. One fails loudly and is monitored; the other succeeds and is not (The Pipeline Succeeded. The Data Is Wrong.).
- It invites the conclusion that a warehouse is the same kind of system with more capacity, and then a team ports operational habits — per-row updates, narrow point queries, many small writes — into a system built on the assumption that none of those happen (File Size and the Small-Files Problem).
What is actually happening
- Start from rows touched per statement. Few rows means a key exists, which means an index pays, which means random access is the dominant pattern, which means row-contiguous pages are the right layout (B+ Tree Internals: Pages, Splits, Merges).
- Many rows and few columns means no key exists, so the pattern is sequential, so the right layout is one that lets you decline to read the columns you did not name (Row vs Column Storage).
- Everything else follows from those two. Latency versus throughput, high versus low concurrency, normalised versus denormalised, current state versus history, mutable versus append-only — none is an independent design choice; each is what the access pattern already implied (Analytical Data Modeling).
- The failure signature follows too. Operational work is synchronous and someone is waiting, so failures surface as errors and timeouts. Analytical work is asynchronous and nobody is waiting, so a wrong result has no natural detector and must be given one (Data Quality).
- The blurring is real but partial. A single-node analytical engine reading columnar files makes "small analytics" nearly free, and open table formats gave file-based storage transactional updates. What has not changed is the physics: a layout optimised for scanning one column of a billion rows is still a bad layout for updating one row of a billion (Open Table Formats, DuckDB Concepts).
The axes that actually differ
Most comparison tables of OLTP and OLAP list ten differences as if they were ten independent choices. They are not. The first two rows below generate the rest, and the last column of the table says how.
The value of reading it this way is that it tells you what to do when a real system does not match a row. If a workload touches many rows and *all* their columns — a bulk export, a full-table migration — then the columnar argument weakens and you should expect the usual advice to fit poorly. The table is not a taxonomy of products; it is a derivation.
The last row is the one most often left off, and on-call it is the most useful. Operational systems fail by stopping. Analytical systems fail by continuing.
| Axis | OLTP | OLAP | Why it follows |
|---|---|---|---|
| Rows per statement | One, or a few reachable by key | Millions to billions | The application asks about one entity; the analyst asks about a population. This is the root axis. |
| Columns per statement | Most or all of a narrow row | A few of a wide row | A record is written whole; an aggregate needs two or three attributes. This is the second root axis. |
| Predicate selectivity | Very high — a primary key | Very low — a date range covering most of the table | Selectivity is the entire condition under which an index pays, and it is absent on the right. |
| What is stored | Current state, overwritten in place | History, mostly append-only | The application only needs now; the analyst asks what was true then. |
| Success metric | Latency at a high percentile | Throughput — work completed per unit of resource | Someone is waiting on a page load. Nobody is waiting on a nightly model in the same way. |
| Concurrency | Thousands of tiny transactions | Tens of large queries | One system multiplexes many small units; the other splits one large unit across many workers. |
| Write pattern | Random single-row inserts and updates | Bulk append; occasional rewrite of a whole partition | Single-row updates are precisely what columnar layouts and large row groups are worst at. |
| Normalisation | Normalised — write each fact once | Denormalised — read each fact without joining | Optimising the write path and the read path pull in opposite directions; you cannot have both maxima. |
| Physical layout | Row-oriented pages, B-tree indexes | Column chunks, per-block statistics, partition directories | Follows directly from the two root axes and from nothing else. |
| Failure signature | Loud: errors, timeouts, pages | Silent: the job succeeded and the number is wrong | One is a system failing. The other is a system succeeding at the wrong thing, which no liveness check detects. |
One decision, ten consequences
The diagram below is the same argument as the table, drawn as a causal chain rather than a list. Read it as a single question at the top with two answers, and notice that after that question nothing else is really chosen — each subsequent box is implied by the one above it.
This is why the distinction is worth teaching before formats, partitioning or warehouses. Every one of those topics is a detail on one branch of this diagram, and a learner who has the branch can usually reconstruct the detail. A learner who memorised the detail cannot reconstruct the branch.
It is also why "which system should we use" is the wrong first question. The right first question is what the statements look like, and the systems fall out of it.
Where the line has genuinely blurred — and where it has not
Two things have genuinely changed in the last decade and both deserve to be said plainly. First, single-node analytical engines reading columnar files made "small analytics" almost free: a dataset that would once have justified a cluster now runs on a laptop, which removes the scale argument for a large fraction of companies. Second, open table formats gave file-based storage atomic commits, snapshot isolation and row-level deletes, which removes the "lakes cannot update" argument that used to force a warehouse.
What has not changed is the physics underneath. A layout that lets you read one column of a billion rows without touching the others is, by construction, a layout where changing one row means rewriting or shadowing a chunk. Every system that offers both does so by keeping two representations and reconciling them, which is the same separation with the seam moved inside the product.
So the decision is still real, but it is a decision about constraints rather than about categories. The options below are ordered by commitment, and the honest observation is that most companies belong two rows higher than the one they pick.
Which of the four constraints — isolation, history, multiple sources, query shape — is actually failing right now?
when The working set fits comfortably in memory, reports run outside peak hours, no historical questions are being asked, and there is one source.
cost Nothing to build. You are one growth curve from the first incident, and unless you start retaining history now you are also accruing an unanswerable question for later.
when Isolation is the only constraint that has actually bitten. Schema, history and query shape are all still fine.
cost A second instance to operate and a lag you must publish to consumers. Solves isolation completely and does nothing at all for history, sources or query shape.
when The data fits on one large machine, the team is small, and query shape is the binding constraint rather than scale.
cost An export to keep fresh and one more copy to govern. Removes the query-shape problem almost entirely for a small fraction of the operational weight of a cluster, and is the row most teams skip past.
when Several sources must be joined, history must be preserved and versioned, and many consumers need one governed shared model.
cost A platform: ingestion, modelling, testing, orchestration, lineage, access control, and a rotation that carries it. The largest commitment here and the only option that addresses all four constraints.
when The organisation genuinely needs analytics on current operational state and can accept one system answering both questions.
cost Both workloads' failure modes inside one blast radius, and an isolation boundary that is now a configuration setting rather than a separate machine.
Which row a named product belongs in moves over time — operational databases gain columnar storage options, analytical engines gain row-level mutation and low-latency ingestion, and single-node engines gain remote object-store readers. Treat a product's position here as something to verify in its current documentation rather than something to remember. The four constraints do not move.
How to build it
Most important first.
- Name the constraint before naming the system. Isolation, history, multiple sources, query shape — write down which of the four is actually failing today, because each has a different cheapest fix and only one of them requires a platform (The OLTP to OLAP Journey).
- Start keeping history on the day you can, regardless of which system you choose. It is the only one of the four constraints that cannot be solved retroactively (Keeping Raw History: The Recovery Position and the Liability, Event vs Snapshot Modeling).
- Match the physical decision to the workload, not to the product category. If the analytical query is scan-shaped, it wants columnar storage and pruning whether it runs in a warehouse, in a single-node engine, or in an extension inside the operational database (Physical Data Layout).
- Give the analytical side a monitoring story of its own before it has consumers, because it will not inherit one — task success is not data correctness and the operational monitoring stack only knows about the former (Data Observability).
- Keep the boundary explicit. The operational schema is the application's; the analytical model is the platform's contract with its consumers; the transformation between them is where the two vocabularies are reconciled on purpose (Model Layering).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- The operational side promises transactional correctness for one small unit of work, and promises nothing about history, reproducibility or any consumer outside itself.
- The analytical side promises consistent results over whatever snapshot it read, and promises nothing about completeness — completeness is a property of the pipeline that filled it and is measured, not received (Reconciliation).
- Neither side promises that a metric means the same thing on both. The same word — order, active, revenue — routinely has an operational definition and an analytical one, and nothing in either system detects the divergence (Two Dashboards, Two Numbers).
- A hybrid system that serves both promises both workloads' guarantees and inherits both workloads' failure modes inside one blast radius. That is a real trade, not a free lunch.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check that spans both sides is reconciliation for a closed period: the same aggregate computed operationally and analytically should agree, and the moment it does not you have localised the problem to the journey between them (Reconciliation).
- It misses periods that are still open, anything wrong in the same way on both sides, and every case where the two systems are computing genuinely different definitions of the same word — which is the most common cause of a mismatch and the one reconciliation is least able to distinguish from a bug (Two Dashboards, Two Numbers).
- It also misses the direction of the error. Agreement proves consistency, not correctness; two systems can be consistently wrong because one derives from the other (Source of Truth).
- The operational side is the definition of fresh — a committed row is immediately visible — and it is also the definition of forgetful. Freshness and history are traded against each other by the schema, not by the pipeline.
- The analytical side chooses staleness deliberately in exchange for scan-friendly layout, compaction and precomputation. Removing that staleness removes the thing that was paying for query performance (Cost vs Freshness).
- The hybrid position — current-state analytics on operational data — is exactly the position where the trade is hardest, which is why systems that offer it are architecturally interesting rather than obviously superior (Batch vs Streaming Ingestion).
- The operational schema changes at the application's pace and for the application's reasons; the analytical model changes at the pace of the questions being asked. Coupling them directly means each team's roadmap is now a dependency of the other's (Data Contracts).
- Products migrate across this comparison over time — an operational database gaining a columnar option, an analytical engine gaining row-level deletes — so any decision recorded as "we chose X because it is the OLAP one" ages badly. Record the constraint instead of the category (Comparing Analytical Warehouses).
- The distinction itself has not evolved and shows no sign of doing so, because it is a statement about access patterns rather than about technology.
- Choosing wrongly in the direction of "too little" is recoverable if — and only if — you retained the raw history. The platform can be built later; the history cannot be recreated later (The Raw Landing Zone).
- Choosing wrongly in the direction of "too much" is recoverable too, at the cost of a year of engineering and a lot of migration. It is the more common error and the less discussed one (Data Platform Anti-Patterns).
- Choosing a hybrid and then needing to split is the hardest of the three, because consumers have been written against a system where operational and analytical objects share a namespace and nobody tracked which was which (Data Lineage).
What can go wrong
- Running the analytical query on the operational primary and paying for it in latency somewhere else (Workload Isolation).
- Building an analytical platform for a constraint that a replica and a scheduled query would have satisfied, then spending the following year operating it (Data Platform Anti-Patterns).
- Porting operational habits into the analytical system: single-row updates, thousands of tiny files, point queries by key (File Size and the Small-Files Problem).
- Porting analytical habits into the operational system: wide scans, long transactions, denormalised tables maintained by triggers.
- The mitigation failing: a hybrid engine adopted to avoid the choice, which now has both workloads' operational risk on one on-call rotation.
- "OLAP is the modern one." They are contemporaries and both are essential. Every analytical system in the world is downstream of an operational one, and a company with only the analytical half has nothing to analyse (Data Engineering and Database Engineering).
- "A warehouse is the same kind of system with more capacity." Different layout, different execution model, different concurrency assumptions, different attitude to updates. Shared SQL syntax is the least significant thing the two have in common (The Data Warehouse).
- "HTAP means the distinction is over." It means one product is attempting both. The access patterns are still opposite, and the engine resolves that by keeping two representations internally — which is the same separation, moved inside a box (Separating Storage from Compute).
- "We are too small for this to matter." The isolation and layout parts may genuinely not matter yet. The history part matters on day one, and it is the only part with no retrofit (Keeping Raw History: The Recovery Position and the Liability).
Operating it
- On the operational side: latency percentiles, buffer cache hit ratio and oldest snapshot age (Percentiles: Which One, and How Many Users Is That?).
- On the analytical side: bytes scanned per query, prune ratio, freshness per dataset and the results of the data tests (Scan Cost, Freshness Monitoring).
- Across both: one reconciliation query per important metric per closed period, running on a schedule, alerting on divergence. It is the only signal that observes the boundary itself (Reconciliation).
- At small scale the distinction is a performance detail and both workloads coexist happily on one machine. That is not a failure of the theory; it is what the theory predicts when the whole dataset is resident in memory (Working Set: Why Performance Falls Off a Cliff).
- At 10x it becomes a resource contention problem: the two workloads want the same cache and the same connections, and one of them is latency-sensitive (Workload Isolation).
- At 100x it becomes a physical layout problem, and the two workloads want mutually exclusive layouts. That is the point at which the separation stops being an operational convenience and becomes structural (Row vs Column Storage).
- Operational cost is driven by write amplification and by keeping the working set in memory; it scales with transaction rate and with index count (Write, Read and Space Amplification).
- Analytical cost is driven by bytes scanned, bytes shuffled and repeated recomputation; it scales with history retained and with how badly the layout matches the predicate (What Actually Drives Data Platform Cost).
- The cost of the boundary itself — the extraction, the transport, the extra copy, the people who maintain it — is real, ongoing, and routinely omitted from the comparison that justified building it.
- Separating the workloads buys isolation, history, joinable sources and scan performance. It costs a second copy of the company's data with its own freshness, its own security surface, its own on-call and its own ways of being silently wrong.
- Not separating them keeps one system, one truth and one operational burden, and accepts that some questions will be slow, some will be expensive, and some will simply have no answer.
- A hybrid engine buys a single system and costs the isolation guarantee: what used to be two blast radiuses is now one, configured rather than physical.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe access-pattern argument — rows and columns per statement determining layout, indexing, concurrency and failure signature — holds regardless of vendor and has held for decades, because it is a statement about what storage devices and caches do.
- SCALE-SPECIFICBelow the point where the working set stops fitting in memory, both workloads run acceptably on one machine and the entire separation argument reduces to isolation. Above it, the layouts are mutually exclusive and the separation is structural rather than a matter of taste.
- WAREHOUSE-SPECIFICWhich side of this comparison a named product sits on moves over time: operational engines add columnar options, analytical engines add row-level mutation and low-latency ingest. Reason from your query shape, and verify a product's current position rather than recalling it.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns what changes when either workload stops fitting on one machine: partition tolerance, cross-shard transactions, and why a distributed aggregate is a fundamentally different problem from a distributed point lookup.
- — DevOps / Production Engineering owns the delivery half of this decision — a second data system is a second thing to provision, monitor, patch, back up and page someone about, and that cost belongs in the comparison that justifies it.