The 5 Minute Guide to OLTP vs OLAP
OLTP and OLAP are terms for defining data management systems. While OLTP defines systems with fast and reliable queries, OLAP emphasizes batch processing and data aggregation. In this article, we discuss the key differences between OLTP and OLAP including definitions and examples of each.
What is OLTP?
OLTP stands for online transactional processing. A transaction is considered any database operation such as a read or write handled by the database. OLTP systems are characterized by high volume and update-intensive queries. In an OLTP system, the accuracy and integrity of database transactions takes a top priority. Specifically, OLTP transactions are meant to be ACID (atomic, consistent, isolated, durable) compliant. This means one transaction completes before another begins.
Why is this important? Because OLTP systems are meant to be used concurrently by hundreds of users. If one user makes updates to a record, the system needs to guarantee that any subsequent queries reflect the latest state. Otherwise, data will become inaccurate and out of sync. This is why OLTP systems generally assume ACID compliance with each transaction.
OLTP example
An online banking app is an example of an OLTP system. A banking app must support millions of concurrent users and guarantee the accuracy and consistency of data at all times. If a user makes a withdrawal, this activity must be captured before any subsequent queries on the user's account take place. Otherwise, the user could make additional withdrawals without the first transaction clearing!
What is OLAP?
OLAP stands for online analytical processing. OLAP systems are characterized by data aggregations and batch processing. OLAP systems perform long-running queries on historical data to generate meaningful results. Unlike OLTP, OLAP systems aren't concerned with the processing, collection, and consistency of data. Rather, OLAP uses sizable queries to gain insights from aggregated data.
OLAP Example
Any business analytics or reporting tool is an example of an OLAP system. If a bank has an OLTP system for processing transactions (aka a mobile banking app), it also has an OLAP system for running reports on that data. For example, if the bank wants an internal report on all users having 5 or more transactions a week, they will use an OLAP system to run these more CPU intensive queries.
Conclusion
While OLTP is concerned with the processing of data, OLAP emphasizes the analysis of data. OLTP systems are often used in conjunction with OLAP system, especially in enterprise environments where business intelligence is required. In fact, OLAP systems typically run off the data generated by OLTP systems. While non-relational data stores like MongoDB are blurring the line between these paradigms, OLTP and OLAP remain fundamental to describing data management systems today.
