OLTP vs OLAP
Published
A database serves two very different kinds of question. "Place order 1004 for Asha" and "show me order 1004" are transactions: small, specific, thousands per second, and they need an answer in milliseconds. "What was our revenue by city, per hour, for the last 90 days?" is an analytical question: it reads hundreds of millions of rows, a few times an hour, and a few seconds is fine.
OLTP (online transaction processing) is the first kind. OLAP (online analytical processing) is the second. Trying to run both on the same database is one of the most common ways a growing company takes down its own checkout.
The finance team runs a revenue report directly on the production Postgres at 11 am. Checkout latency jumps from 40 ms to 4 seconds while it runs. Why would a read only report slow down writes? What would you change?
The layout on disk is the whole story#
Disks and SSDs read in pages (8 KB in Postgres), never single values. So what matters is which values sit next to each other. A row store keeps each order's fields together. A column store keeps each column's values together. Run the three queries on both layouts.
| order_id | user_id | city | item | amount | placed_at |
|---|---|---|---|---|---|
| 1001 | 42 | Pune | Biryani | 320 | 18:02 |
| 1002 | 77 | Delhi | Thali | 210 | 18:03 |
| 1003 | 42 | Pune | Dosa | 140 | 18:05 |
| 1004 | 19 | Kochi | Appam | 180 | 18:06 |
| 1005 | 88 | Delhi | Momos | 160 | 18:08 |
| 1006 | 23 | Pune | Misal | 120 | 18:09 |
| 1007 | 77 | Kochi | Biryani | 340 | 18:11 |
| 1008 | 51 | Delhi | Chaat | 90 | 18:12 |
That is the entire reason OLTP and OLAP systems exist separately. Fetching or updating one order wants the order's fields together. Summing one column over a billion orders wants that column's values together, and wants to skip the other 30 columns entirely. A column of similar values (city names, amounts) also compresses extremely well, often five to ten times, and is processed with vectorised CPU instructions a batch at a time.
Assumes 8 bytes per value and a single disk, which flatters neither system. Real OLAP engines also run the scan across many cores and machines, which is why BigQuery answers this in a couple of seconds.
The two worlds side by side#
| OLTP | OLAP | |
|---|---|---|
| Typical query | Read or write a few rows by key | Scan and aggregate millions or billions of rows |
| Volume | Thousands to millions of queries a second | A few to a few thousand a minute |
| Latency target | Milliseconds | Seconds is fine |
| Data | Current state | History, often years |
| Schema | Normalised, many tables | Denormalised, wide fact tables (star schema) |
| Storage | Row oriented | Column oriented, compressed |
| Writes | Constant small updates | Bulk loads or appends, rarely updates |
| Examples | Postgres, MySQL, DynamoDB, MongoDB | BigQuery, Snowflake, Redshift, ClickHouse, DuckDB |
Why the report slowed checkout#
A big analytical query on the OLTP primary reads the whole table, evicts the hot pages that checkout depends on from the buffer cache, saturates disk and CPU, and in some databases holds locks or prevents cleanup (a long running Postgres transaction stops vacuum from removing dead rows). The fix is to give analytics its own copy:
- checkout, untouched
- 40ms
- warehouse behind live
- ~1min
- scanned in seconds
- 90days
- reports on the primary
- 0
Options, from simplest to most capable:
- A read replica for reports. Isolates the primary's CPU and cache, but it is still a row store, so big scans are still slow.
- Nightly ETL (extract, transform, load) into a warehouse. Simple and cheap, but data is up to a day old.
- Change data capture (CDC) streaming every change through Kafka into the warehouse, so analytics are a minute or so behind. This is the modern default.
ELT is the now common variant where raw data is loaded first and transformed inside the warehouse with SQL (dbt is the usual tool). And HTAP systems (TiDB, SingleStore, AlloyDB's columnar engine) try to serve both workloads from one system by keeping a columnar copy beside the rows, useful when you cannot afford a pipeline but need fresh analytics.
| Choice | What you gain | What you pay | Pick it when |
|---|---|---|---|
| OLTP database (row store) | Fast point reads and writes, transactions, constraints, indexes on keys. | Large scans and aggregates are slow and hurt everything else on the box. | Running the product: orders, users, payments, inventory. |
| OLAP warehouse (column store) | Scans and aggregates over billions of rows in seconds, cheap storage via compression. | Poor at single row lookups and updates, data arrives with some delay, another system to feed. | Dashboards, reporting, analytics, feature generation for ML. |
In the interview#
Checkpoint#
1. Why is a column store faster for SUM(amount) over all orders?
2. Why can a read only report slow down writes on the primary?
3. What does change data capture do?
- OLTP: many small reads and writes, row store, milliseconds.
- OLAP: few huge scans and aggregates, column store, seconds.
- Column stores read only needed columns and compress 5 to 10x.
- Never run big reports on the primary. Replica, ETL, or CDC to a warehouse.
- CDC: Debezium + Kafka into ClickHouse, BigQuery or Snowflake.
OLTP serves the application: many small reads and writes by key, millisecond latency, current state, normalised schemas, and row oriented storage like Postgres or MySQL, because fetching or updating one record wants its fields together on one page. OLAP answers analytical questions over history: few queries that scan and aggregate millions or billions of rows, where seconds are fine, using column stores like BigQuery, Snowflake, Redshift or ClickHouse, which read only the needed columns, compress them heavily and process them in vectorised batches. Running analytics on the production primary competes for disk, CPU and cache and can stall checkout, so I give analytics its own copy: a read replica as a stopgap, nightly ETL when a day of delay is fine, or change data capture through Kafka into a warehouse when it needs to be within a minute.