◐ ai-teacher

🧱 Foundations · Chapter 2

OLTP vs OLAP

One system answers 'what is true right now for this one ride?'. The other answers 'what happened across ten million rides?'

Study this lesson free in the app →

The basics

OLTP vs OLAP is the split between systems that run the business (one ride, one payment, right now) and systems that analyse it (every ride, last quarter).

The problem it solves: one database cannot be great at both. Booking a cab touches one row and must be exact in milliseconds. "Average fare by city for July" sweeps 40 million rows. Run the second on the first system and bookings slow down; run the first on the second and every write is painfully slow.

The mental model: the pharmacist and the census office. The pharmacist fetches one box at a time, thousands of times a day, never wrong. The census office reads one column across millions of records. The difference underneath is how bytes sit on disk: row storage keeps a whole record together; columnar storage keeps one field together.

Words you must know 5

OLTP
many small, exact, concurrent reads and writes (Postgres, MySQL, DynamoDB)
OLAP
big scans and aggregations over history (Snowflake, BigQuery, Redshift)
ACID
the transaction guarantees that make OLTP safe
Columnar storage
read only the columns a query needs, and compress them hard
CDC
change data capture; streams OLTP changes into the analytical side

Explained

Same data. Two completely different questions.

SELECT * FROM rides WHERE ride_id = 'r_9001' — one row, right now, must be correct to the paisa.

SELECT city, avg(fare) FROM rides WHERE month = '2026-07' GROUP BY city — 40 million rows, a few seconds is fine.

A storage layout that is excellent at the first is bad at the second. That is the whole lesson.

The pharmacy and the census

OLTP is a pharmacist. You walk in with one prescription. They find that exact box, hand it over, take payment, update the register. Thousands of customers, each touching one tiny thing, all day, without mistakes.

OLAP is a census office. Nobody asks about one person. They ask "how many households in Shillong own two vehicles?" To answer it you sweep entire columns of millions of records.

Ask the pharmacist to run a census and the queue out the door stops moving.

The real difference is row vs column

Row storage (OLTP) keeps all fields of one record physically together. Fetching one ride = one seek. Perfect for point reads and updates. Indexes and ACID transactions make it safe.

Column storage (OLAP) keeps every value of one field together. avg(fare) reads only the fare column and skips the other 40 fields entirely — then compresses it hard, because similar values sit next to each other.

Snowflake, BigQuery, Redshift, Databricks SQL and Parquet files are all columnar. Postgres, MySQL, Oracle and DynamoDB are built for the point-read world.

Picture the bytes

ROW store  →  [r1: id,city,fare][r2: id,city,fare][r3: ...]
              great: give me r2
              bad:   sum every fare (touches everything)

COL store  →  [id: r1,r2,r3...][city: r1,r2,r3...][fare: r1,r2,r3...]
              great: sum every fare (one column, compressed)
              bad:   update r2's fare (rewrite a chunk)

What high-volume platforms actually do

They do not choose. They run both and connect them.

The operational store is deliberately kept small: it holds hot, recent, in-flight data. Seven years of ride history has no business being there.

OLTP = few rows, many times, must be exact. OLAP = many rows, few times, must be fast.

T for transaction. A for analysis.

Gotchas that show up in interviews

The question behind the question

When an interviewer asks "OLTP or OLAP?", they are testing whether you can pick storage from an access pattern.

Answer template: "What is the read shape? Point lookups by key, or aggregate scans over a time range? How fresh must it be, and who is hurt if it is slow — a customer or an analyst?" Then choose.

Customer-facing latency → operational store. Analyst-facing depth → warehouse. Both → replicate, don't compromise.

Words to know

OLTPOnline Transaction Processing — many small, exact, concurrent reads/writes.
OLAPOnline Analytical Processing — large scans and aggregations over history.
ACIDAtomicity, Consistency, Isolation, Durability — the transaction guarantees OLTP stores provide.
Columnar storageValues of the same field stored together, so scans read only the columns needed.
CDCChange Data Capture — streaming a database's row changes out to other systems.
Separated storage and computeWarehouse design where data sits in object storage and query clusters scale independently.

In two minutes

Test yourself

1. Why is a columnar store faster for SELECT avg(fare) FROM rides?

  1. It caches results automatically
  2. It reads only the fare column and skips all other fields
  3. It uses more indexes
  4. It stores fewer rows
Show the answer

It reads only the fare column and skips all other fields — Column pruning plus high compression. Row stores must touch every record to reach one field.

2. Where should the live wallet balance for a rider be read from?

  1. Snowflake
  2. The operational database
  3. The raw data lake
  4. A BI dashboard
Show the answer

The operational database — Customer-facing, point-lookup, must be current and exact — that is OLTP territory. The warehouse holds the history of balance changes, not the live source of truth.

3. Which is the worst idea in a columnar warehouse?

  1. A large batch MERGE once an hour
  2. Millions of single-row UPDATEs per minute
  3. Scanning a year of data
  4. Partitioning by date
Show the answer

Millions of single-row UPDATEs per minute — Columnar files are written in large immutable chunks. Tiny frequent updates force constant rewrites — the exact workload OLTP stores exist for.

4. Which of these is a columnar analytical store?

  1. MySQL
  2. Postgres with default tables
  3. BigQuery
  4. DynamoDB
Show the answer

BigQuery — BigQuery stores data by column (Capacitor format). MySQL and Postgres are row stores; DynamoDB is a key-value store built for point reads — all three serve the OLTP world.

5. Ops wants rides per city per hour for the last 90 days — about 120 million rows — on a dashboard refreshed every 10 minutes. Where should it run?

  1. A columnar warehouse fed by CDC from the operational database
  2. The production Postgres, with a new index on (city, created_at)
  3. The mobile app, computed on each phone
  4. A cron job emailing a CSV every 10 minutes
Show the answer

A columnar warehouse fed by CDC from the operational database — A 120 M-row aggregate every 10 minutes is an OLAP workload. The index option is tempting, but it slows every booking write and still leaves the scan on the database that takes payments.

6. A table has 100 million rows and 40 similar-width columns. A query reads 2 columns. Before compression, roughly how much less data does a column store read than a row store?

  1. The same — the row count is identical
  2. About 2× less
  3. About 20× less
  4. About 100 million× less
Show the answer

About 20× less — The column store reads 2 of 40 columns: 1/20 of the bytes. Row count is the same, which is why 'the same' is tempting — but the row store has to drag all 40 fields of every row through the disk. Compression then usually adds another several-fold saving.

There are 8 questions, 3 puzzles and 4 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.

Learn it so it stays. Read it, test yourself, then recall it on two different days. The app schedules that for you, free.

Start this lesson →