OLTP vs OLAP: The two jobs every Database is asked to do

OLTP (online transaction processing) is the workload that runs a business: thousands of small, fast reads and writes, like placing an order or updating a balance. OLAP (online analytical processing) is the workload that understands a business: big, heavy queries that scan millions of rows to answer questions. Almost every data architecture decision of the last thirty years comes back to one fact: these two jobs are so different that they need different systems.

An Analogy: The Till and the Accountant

Picture a busy restaurant. At the front is the till, processing one order at a time, hundreds of times a day. Each interaction is tiny and must be fast and flawless: take the order, charge the card, print the receipt, next customer. That is OLTP.

At the end of the month, the accountant sits down with every receipt to answer bigger questions. Which dishes make the most profit? Are weekends growing? How does this quarter compare to last year? She touches thousands of records in one sitting, but nobody is waiting in a queue behind her. That is OLAP.

Now imagine the accountant doing her month-end review at the till, in the middle of the lunch rush. That is what happens when a business runs heavy analytics on its live operational database, and it is why the two workloads were separated.

OLTP: Running the Business

OLTP systems are the databases behind applications: the app database in an e-commerce store, the core banking system, the booking engine. Their defining traits follow directly from the job. Each operation touches a handful of records and must complete in milliseconds, because a customer is waiting. Correctness is non-negotiable: when money moves between two accounts, both updates must happen or neither must, a set of guarantees database engineers call ACID. And the data reflects the current state of the world: your balance now, the stock level now.

Classic OLTP databases include PostgreSQL, MySQL, Oracle, and SQL Server. These are the direct descendants of the relational systems whose story we tell in Data Platform Evolution.

OLAP: Understanding the Business

OLAP workloads ask a different kind of question: not "what is customer 4127's balance?" but "how did average balances move across every branch over the last three years?" A single query might scan hundreds of millions of rows, group them, and aggregate them. The volume of queries is low, but each one is heavy, and the data in view is historical: OLAP systems accumulate the past rather than overwriting it.

This is the workload that data warehouses, and later lakehouses, were purpose-built for. Snowflake, BigQuery, Redshift, and Databricks SQL are all, at heart, OLAP engines.

Why One System Can't Do Both Well

The obvious question: databases are powerful, so why not just run the reports on the application database? Two reasons, one practical and one structural.

The practical reason is contention. An analytical query scanning fifty million order rows competes for the same computing resources, and sometimes the same locks, as the transactions serving customers. The month-end report slows the checkout; in bad cases it takes it down. No business trades a working checkout for a faster report.

The structural reason is that the two workloads want data physically arranged in opposite ways. OLTP databases store data by row: all the fields of one order sit together, so fetching or updating that order is one quick read. OLAP engines store data by column: all the order totals sit together, all the dates sit together. To average ten million order totals, a columnar engine reads just the totals column and skips everything else, often a hundred times less data than reading every full row. That single design difference is much of why a warehouse can answer in seconds a question that would grind an application database for an hour.

There is a small but growing class of systems that try to serve both workloads at once (sometimes labelled HTAP, hybrid transactional/analytical processing), but for the overwhelming majority of organisations, the separation remains the norm, and for good reason.

What About All the Other Databases?

Reading this far, you might object: the database world is much bigger than this. Key-value stores like Redis, document databases like MongoDB, wide-column stores like Cassandra, graph databases like Neo4j, time-series databases, search engines like Elasticsearch, and lately vector databases powering AI applications. Doesn't all that variety break the neat two-way split?

It doesn't, because OLTP versus OLAP is a split of workloads, not products. Almost any database can be placed by asking one question: does it serve an application in milliseconds, or answer questions across history? By that test, most of the specialised systems above sit firmly on the OLTP side; they simply optimise the transactional job for a particular shape of data. Redis serves single values at extreme speed, MongoDB serves flexible documents, Neo4j serves questions about connections, and vector databases serve similarity lookups for AI features, but all of them exist to answer an application quickly, right now. A smaller group lives on the analytical side, such as time-series engines crunching metrics history and columnar engines like ClickHouse built for real-time analytics.

So the honest picture is a spectrum with two poles rather than two boxes, and a crowd of specialists clustered near the OLTP end. The pattern in this article still decides the architecture: whatever runs the application stays protected, and data flows out of it into whatever answers the questions.

How Data Gets From One to the Other

Since the systems are separate, data must flow between them, and that flow is the day job of data engineering. Pipelines regularly extract new and changed records from OLTP systems, reshape them for analysis, and load them into the OLAP platform. How that reshaping is ordered is the subject of ELT vs ETL (coming soon), and how often it happens, in nightly batches or as a continuous stream, is the subject of Batch vs Streaming (coming soon).

What the Separation Unlocks

It's worth pausing on why all this pipeline work earns its keep. Once data flows safely out of the systems running the business and into a platform built for questions, a whole layer of capabilities becomes possible that could never run against a live application database.

• Business intelligence and self-serve analytics: dashboards and reports refreshing for the entire company, with analysts free to explore at will, none of it endangering operations.
• Data science: experiments, cohort analysis, and forecasting over the full granular history rather than samples.
• Machine learning and AI: models for churn, demand, fraud, and recommendations are trained on exactly this accumulated history, and it is the same governed foundation that makes a company's own data usable by LLM applications.

Increasingly, the loop even closes. Insights computed on the OLAP side get pushed back into the operational tools where work happens, such as a churn-risk score written into the CRM or a customer segment synced to an ad platform, a pattern known as Reverse ETL or operational analytics. The OLTP world runs the business, the OLAP world learns from it, and the learnings flow straight back into the running. None of that exists without the separation, and without the engineering that bridges it.

OLTP vs OLAP at a Glance

OLTP: Running the Business

OLTP systems are the databases behind applications: the app database in an e-commerce store, the core banking system, the booking engine. Their defining traits follow directly from the job. Each operation touches a handful of records and must complete in milliseconds, because a customer is waiting. Correctness is non-negotiable: when money moves between two accounts, both updates must happen or neither must, a set of guarantees database engineers call ACID. And the data reflects the current state of the world: your balance now, the stock level now.

Classic OLTP databases include PostgreSQL, MySQL, Oracle, and SQL Server. These are the direct descendants of the relational systems whose story we tell in Data Platform Evolution.

OLAP: Understanding the Business

OLAP workloads ask a different kind of question: not "what is customer 4127's balance?" but "how did average balances move across every branch over the last three years?" A single query might scan hundreds of millions of rows, group them, and aggregate them. The volume of queries is low, but each one is heavy, and the data in view is historical: OLAP systems accumulate the past rather than overwriting it.

This is the workload that data warehouses, and later lakehouses, were purpose-built for. Snowflake, BigQuery, Redshift, and Databricks SQL are all, at heart, OLAP engines.

Why One System Can't Do Both Well

The obvious question: databases are powerful, so why not just run the reports on the application database? Two reasons, one practical and one structural.

The practical reason is contention. An analytical query scanning fifty million order rows competes for the same computing resources, and sometimes the same locks, as the transactions serving customers. The month-end report slows the checkout; in bad cases it takes it down. No business trades a working checkout for a faster report.

The structural reason is that the two workloads want data physically arranged in opposite ways. OLTP databases store data by row: all the fields of one order sit together, so fetching or updating that order is one quick read. OLAP engines store data by column: all the order totals sit together, all the dates sit together. To average ten million order totals, a columnar engine reads just the totals column and skips everything else, often a hundred times less data than reading every full row. That single design difference is much of why a warehouse can answer in seconds a question that would grind an application database for an hour.

There is a small but growing class of systems that try to serve both workloads at once (sometimes labelled HTAP, hybrid transactional/analytical processing), but for the overwhelming majority of organisations, the separation remains the norm, and for good reason.

What About All the Other Databases?

Reading this far, you might object: the database world is much bigger than this. Key-value stores like Redis, document databases like MongoDB, wide-column stores like Cassandra, graph databases like Neo4j, time-series databases, search engines like Elasticsearch, and lately vector databases powering AI applications. Doesn't all that variety break the neat two-way split?

It doesn't, because OLTP versus OLAP is a split of workloads, not products. Almost any database can be placed by asking one question: does it serve an application in milliseconds, or answer questions across history? By that test, most of the specialised systems above sit firmly on the OLTP side; they simply optimise the transactional job for a particular shape of data. Redis serves single values at extreme speed, MongoDB serves flexible documents, Neo4j serves questions about connections, and vector databases serve similarity lookups for AI features, but all of them exist to answer an application quickly, right now. A smaller group lives on the analytical side, such as time-series engines crunching metrics history and columnar engines like ClickHouse built for real-time analytics.

So the honest picture is a spectrum with two poles rather than two boxes, and a crowd of specialists clustered near the OLTP end. The pattern in this article still decides the architecture: whatever runs the application stays protected, and data flows out of it into whatever answers the questions.

How Data Gets From One to the Other

Since the systems are separate, data must flow between them, and that flow is the day job of data engineering. Pipelines regularly extract new and changed records from OLTP systems, reshape them for analysis, and load them into the OLAP platform. How that reshaping is ordered is the subject of ELT vs ETL (coming soon), and how often it happens, in nightly batches or as a continuous stream, is the subject of Batch vs Streaming (coming soon).

What the Separation Unlocks

It's worth pausing on why all this pipeline work earns its keep. Once data flows safely out of the systems running the business and into a platform built for questions, a whole layer of capabilities becomes possible that could never run against a live application database.

• Business intelligence and self-serve analytics: dashboards and reports refreshing for the entire company, with analysts free to explore at will, none of it endangering operations.
• Data science: experiments, cohort analysis, and forecasting over the full granular history rather than samples.
• Machine learning and AI: models for churn, demand, fraud, and recommendations are trained on exactly this accumulated history, and it is the same governed foundation that makes a company's own data usable by LLM applications.

Increasingly, the loop even closes. Insights computed on the OLAP side get pushed back into the operational tools where work happens, such as a churn-risk score written into the CRM or a customer segment synced to an ad platform, a pattern known as Reverse ETL or operational analytics. The OLTP world runs the business, the OLAP world learns from it, and the learnings flow straight back into the running. None of that exists without the separation, and without the engineering that bridges it.

OLTP vs OLAP at a Glance

Conclusion

OLTP keeps the business running; OLAP explains how it's going. Nearly everything in modern data architecture, warehouses, lakes, pipelines, and the roles that build them, exists to serve the second workload without ever endangering the first. Once you see this split, the rest of the field starts making sense.

This article is part of our data engineering knowledge base. See the full story of how the separation happened in Data Platform Evolution, or continue with ELT vs ETL (coming soon!)