Two Kinds of Workload
Databases serve two very different jobs. OLTP (online transaction processing) handles many small reads and writes such as placing an order or updating a balance. OLAP (online analytical processing) scans huge ranges to answer questions like total revenue per region last year.
How They Differ
- OLTP touches a few rows per query and runs thousands of queries per second.
- OLAP touches millions of rows per query but runs far fewer queries.
- OLTP cares about low latency and strong consistency; OLAP cares about throughput on big scans.
- OLTP stores data row by row; OLAP often stores it column by column.
Why Separate Systems
Running heavy analytics on a transactional database competes for the same resources and slows down user requests. Many teams copy data from the OLTP store into a separate data warehouse tuned for OLAP.
Key idea
OLTP optimizes for many small low latency transactions while OLAP optimizes for large analytical scans, so production systems usually run them on separate engines.