OLAP vs. OLTP: The Distinction That Explains How Data Systems Are Built
When a customer places an order on an e-commerce site, several things happen in milliseconds. The order gets recorded. Inventory gets decremented. A confirmation gets triggered.
The database handling all of this needs to be fast, precise, and reliable under concurrent load from thousands of simultaneous users doing thousands of different things.
When an analyst asks how order volume trended over the past eighteen months, broken down by product category and customer region, something very different needs to happen. The system needs to scan potentially billions of rows, aggregate across multiple dimensions, and return a coherent result. Speed still matters, but it's measured in seconds rather than milliseconds, and precision under concurrent write load is not the concern.
These two workloads are different enough that they require different kinds of systems. The first is OLTP. The second is OLAP.
OLTP stands for Online Transaction Processing. These are the systems that run the business in real time: the e-commerce platform, the CRM, the ERP, the banking application, the reservation system. Their defining characteristic is that they handle large numbers of short, discrete transactions, each touching a small number of rows, with strict requirements for consistency and reliability. If two customers try to buy the last unit of a product simultaneously, the OLTP system has to handle that correctly. If a payment transaction fails halfway through, the system has to roll back cleanly. These are not problems that analytical systems typically have to solve.
OLTP databases are typically highly normalized, in the sense covered in the database normalization piece elsewhere in this blog. Normalization reduces redundancy and makes writes efficient. When a customer updates their address, you update one row in one table, and that change is immediately reflected everywhere that record is used. The tradeoff is that analytical queries, which need to assemble information from many tables, require many joins and can be slow on normalized schemas at scale.
OLAP stands for Online Analytical Processing. These are the systems built for querying and analyzing large volumes of historical data: data warehouses, data marts, analytical databases. Where OLTP systems are optimized for fast individual writes, OLAP systems are optimized for fast aggregations across large datasets. They typically store data in denormalized structures, star schemas being the most common, that trade write efficiency for read efficiency. They often use columnar storage, which organizes data by column rather than by row, making it possible to read only the columns relevant to a query rather than entire rows.
The performance characteristics of these two system types are essentially inverse. An OLTP system that handles ten thousand concurrent transactions per second would be completely overwhelmed by an analytical query scanning ten years of order history. An OLAP system optimized for that analytical query would be poorly suited to handling thousands of small concurrent writes with strict consistency guarantees. This is why organizations typically run both, separately, rather than trying to handle both workloads on a single system.
The movement of data between these two system types is what ETL pipelines exist to handle. Data gets created in OLTP systems, extracted on some schedule, transformed into the structures that OLAP systems expect, and loaded into the analytical environment. Historically this happened in batch, often overnight. More recently, streaming architectures have made it possible to move data from operational to analytical systems with latency measured in seconds rather than hours, narrowing but not eliminating the gap between the two.
A category of systems called HTAP, Hybrid Transactional and Analytical Processing, has emerged to try to serve both workloads from a single system. The appeal is obvious: if you could run analytical queries directly against your operational data without a separate analytical system and an ETL pipeline between them, you'd eliminate a significant amount of infrastructure complexity and data latency. Several modern database systems make varying claims in this direction. In practice, the workloads remain different enough that most organizations above a certain scale still find dedicated OLAP systems necessary for serious analytical work.
For practitioners entering the data field, the OLTP versus OLAP distinction is one of the first frameworks worth internalizing, because it shapes so many downstream decisions. Which database technology is appropriate for a given use case. Why data warehouses are structured differently from operational databases. Why ETL pipelines exist. Why analytical queries that seem straightforward can be expensive to run against a production database. These questions become much easier to reason about once you have a clear mental model of what each type of system is designed to do and why.