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.

Recall

These cards are also in your review deck, scheduled with spaced repetition.