OLTP and OLAP

OLTP and OLAP are the two kinds of work that a company's data is used for. OLTP, short for online transaction processing, means recording each event the moment it happens: an order, a payment, a change of address. OLAP, short for online analytical processing, means asking questions across all the records at once, such as "How much did we sell in each state last year?" Almost every piece of data goes through both. It is recorded first, and questioned later.

Here is how OLTP works. An app's database receives thousands of small requests every second. Each request reads or changes a few rows: one customer, one order, one bank balance. Many of these requests are transactions. A transaction is a group of changes that must all happen together, or not at all. For example, when money moves between two accounts, the debit from one and the credit to the other must both be saved. If the power fails halfway, the database undoes the half-done change. OLTP needs each request to finish in milliseconds and to be exactly right. A relational database is the most common kind of OLTP database.

Here is how OLAP works. An analyst asks a few large questions. Each question reads millions or billions of rows, and adds them up, counts them or compares them. OLAP must read a lot of data fast, but it rarely changes any of it. So the data is usually copied into a separate system built for this work, a data warehouse. Many warehouses store data column by column, as a columnar database does, because that makes large sums much faster.

Why keep the two apart? If an analyst ran a year-long sales question on the app's own database, it could slow down every customer who was trying to pay. And the two kinds of work need opposite designs: one is built for many small, exact changes, and the other for a few huge reads. So a pipeline copies the records from the first system to the second, either every night or within seconds. The page on batch vs stream processing explains the two choices.

Take, for example, a train ticket booking. Holding the seat, reducing the count of seats left and saving the payment is OLTP work, done as one transaction. Finding which routes sold out fastest in Diwali week is OLAP work.

Consider a hospital. Recording a patient's admission at the front desk is OLTP. Counting admissions by illness, month by month, to plan beds for the monsoon is OLAP.

Say a mobile operator records each prepaid recharge as it happens. At the month's end, a report of recharges in each telecom circle, the state-sized regions that operators work in, is an OLAP question.

How are OLTP and OLAP different from a database and a data warehouse? OLTP and OLAP name kinds of work, while a database and a warehouse are products built for one kind or the other. Some products now do both kinds of work in one engine. They are called HTAP databases, for hybrid transactional and analytical processing. They let a company ask questions of live data without waiting for a copy, although most large companies still keep a separate warehouse.