Notesstorage🌱 seedling
OLTP vs OLAP
Also: OLTP · OLAP · Online Transaction Processing · Online Analytical Processing · Transactional vs Analytical Workloads · HTAP
OLTP serves many small, latency-sensitive requests that each touch a few whole rows by key. OLAP runs few, large queries that scan millions of rows but only a few columns, and the two want opposite physical layouts.
Why it exists
One database can't be optimal for both. A big analytic scan competes with user transactions for disk, CPU and buffer cache, and evicts the hot pages users need, so tail latency for users climbs.
How it works
| OLTP | OLAP | |
|---|---|---|
| Read | a few rows by key, via a Secondary Index or B-Tree | aggregates over huge ranges |
| Write | random single-row inserts and updates | bulk loads, append-mostly |
| Columns | all of a row | a few of many |
| Metric | latency per request | rows scanned per second |
| Layout | row store | Column-Oriented Storage |
- The usual answer is to copy data from OLTP systems into a separate Data Warehouse (ETL or Change Data Capture), so the workloads can't hurt each other.
- HTAP systems try to serve both from one system, typically by keeping a row store and a columnar replica of the same data.
Trade-offs
| Choice | Gains | Costs |
|---|---|---|
| analytics on the OLTP primary | fresh data, nothing to build | scans hurt user latency |
| read replica for reports | isolates load | still a row store: reads every column |
| separate warehouse | layout built for scans | data lags; another pipeline to run |
In practice
- Amazon's 2024 Redshift fleet study found that real warehouses aren't pure OLAP: nearly 40% of queries were writes (INSERT, COPY, DELETE, UPDATE, CREATE TABLE AS), and the median query ran in about 100 ms.
Pitfalls
- Running dashboards against the production primary "for now", until a quarterly report takes the site down.
- Assuming the warehouse is fresh: it lags the source by minutes to a day.
Connections
- Part of
- Storage
- Builds on this
- Column-Oriented StorageData Warehouse
- In real systems
- ClickHouse
Recall
These cards are also in your review deck, scheduled with spaced repetition.