CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 2 of 5 · 2 min read

Two different access patterns

OLTP touches a few rows by key; OLAP scans huge numbers of rows to compute an aggregate.

Think of a cashier at a checkout counter. The cashier rings up one customer's items quickly, one transaction at a time. Compare that to an analyst in a back office. The analyst pulls every sales record from the past year, to compute this quarter's total revenue by store.

In plain words: online transaction processing, OLTP, is the pattern behind everyday operational queries. It looks up a small number of records by key, and performs a small number of writes, directly from user input. Online analytical processing, OLAP, is a very different pattern. It scans a large number of records and computes an aggregate, a sum, a count, an average, rather than returning individual rows. OLTP data represents the current state of things, right now. OLAP typically works over history, events accumulated over a long stretch of time.

A worked example. Checking out order 9001 is OLTP: look up the customer, look up each book's price, insert one row into orders and a few rows into order_items. Computing "total revenue by month, across every store, for the last three years" is OLAP: scanning potentially millions of historical order rows to produce a handful of summary numbers.

The key lines.

-- OLTP: a few rows, by key, fast
SELECT * FROM books WHERE book_id = 1;
INSERT INTO orders (customer_id, order_date) VALUES (501, CURRENT_DATE);

-- OLAP: an aggregate over a huge number of rows
SELECT EXTRACT(MONTH FROM order_date) AS month, SUM(unit_price * quantity) AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY month;

OLTP, OLAP

OLTP, online transaction processing, is the access pattern of looking up a small number of records by key and performing a small number of writes from user input, reflecting the current state of the data. OLAP, online analytical processing, is the access pattern of scanning a large number of records to compute an aggregate, typically over data accumulated across a long span of time.

Example: checking out order 9001, a few targeted row lookups and writes, is OLTP; computing total revenue by month across three years of orders, a scan producing an aggregate, is OLAP.