Part 2 of 7 · 2 min read
Two kinds of workload
Lookups that serve users, and scans that serve analysts.
Think of a bank. A cashier finds one customer's account, adds a deposit and hands over a receipt: small, quick and exact. In the back office, an analyst asks for the total deposits of every branch over the last year: large, slow and approximate in time. The two jobs need different tools, even if the data is the same.
The book "Designing Data-Intensive Applications" explains how the names came about. In the early days, one write to the database matched one commercial deal: a sale, an order, a salary. Later the word "transaction" stuck for any small group of reads and writes that forms a logical unit, even outside money. Applications used the database to look up a few records by key through an index, and to insert or change records from user input. Because these applications are interactive, the pattern is called online transaction processing.
OLTP
OLTP (online transaction processing) is the workload of an interactive application: many small reads and writes, each touching a few records found by key, with low delay.
Example: "Show order 101", "add this item to the cart", "mark payment 55 as paid".
Databases then began to serve analysis, which looks very different. An analytic query scans a huge number of records, reads only a few columns of each and calculates totals, counts or averages, rather than returning the raw rows.
OLAP
OLAP (online analytic processing) is the workload of analysis: few queries, each scanning a large share of the data, reading a few columns and computing aggregates.
Example: "Total revenue of each store in January", or "How many more bananas than usual sold during the promotion?".
The book gives a comparison table. A short version:
| Property | OLTP | OLAP |
|---|---|---|
| Typical read | A few records, by key | An aggregate over many records |
| Typical write | Random, low delay, from user input | Bulk load or a stream of events |
| Used by | Customers, through an app | Analysts, for decisions |
| Data represents | The latest state | The history of events |
| Size | Gigabytes to terabytes | Terabytes to petabytes |
A worked example. A shop has 500 million sales rows.
- OLTP query: "Show sale 4,812,330". One index lookup, about 3 page reads, under 1 millisecond.
- OLAP query: "Total sales per store for 2026". It must read the store and amount of every sale in 2026. If 2026 has 100 million rows, the database touches 100 million rows once. An index does not help much, since nearly all of those rows are needed.
The book adds that SQL worked for both at first. But the tools of the first half of the chapter, the indexing structures of modules 3 and 4, are good for OLTP and not for analytic queries.
Read it as
OLTP asks "what is the state of this thing?". OLAP asks "what happened, in total, over time?".
Check yourself:
- Which workload reads many rows but few columns?
- Why does an index help OLTP more than OLAP?