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.