CSE Roadmap

Common tier · about 3 hours · 7 parts

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?