OLTP vs OLAP
A PedalCity analyst ran "average ride duration by station for the past three years" directly on the app's database at lunchtime. The query took eleven minutes, and unlocks slowed across the city. The query was not wrong. It was sent to the wrong kind of system.
The rule
OLTP runs the business one row at a time; OLAP studies it in bulk.
OLTP (online transaction processing) systems power the product. Every unlock, dock and payment is a small, fast transaction that touches a few rows.
OLAP (online analytical processing) systems answer questions across huge numbers of rows: totals, averages and trends over months.
| OLTP | OLAP | |
|---|---|---|
| Example at PedalCity | The app's PostgreSQL | The analytics warehouse |
| Typical query | Read or change one ride | Scan millions of rides |
| Users | Riders, via the app | Analysts, dashboards, models |
| Data | Current state, normalized | History, often denormalized |
| Storage | Row-oriented | Column-oriented |
| Priority | Fast, safe writes | Fast reads over large ranges |
-- OLTP: one rider, right now
UPDATE rides SET ended_at = now(), fare = 45 WHERE ride_id = 88121;
-- OLAP: three years, every station
SELECT start_station, AVG(duration_min)
FROM fact_rides
WHERE ride_date >= '2023-09-01'
GROUP BY start_station;There is a third reason to keep them apart: history. An OLTP system stores the current state. If a rider changes plan from "monthly" to "annual", the old value is overwritten. An OLAP system keeps what things were like over time, so you can still ask "how many monthly riders did we have in March?"
This is why pipelines exist at all. Data is born in OLTP systems and copied to OLAP systems for study.
The shortcut
Read the query and ask: "How many rows does it touch, and who is waiting?" A few rows with a rider waiting → OLTP. Millions of rows with an analyst waiting → OLAP.
"Charge rider 5521 for ride 88121"
One row, and a rider is waiting at the dock. OLTP.
Drills
Answer fast — trust the rule.
Which workload is OLAP?
Why is running a three-year analysis on the app's database a bad idea?
A rider switches from a monthly to an annual plan. What typically happens to the old value in OLTP and in OLAP?
Which storage layout usually suits OLAP?
Remember
- OLTP runs the business one row at a time; OLAP studies it in bulk.
- Ask how many rows the query touches and who is waiting.
- OLTP keeps current state; OLAP keeps history. Pipelines carry data from one to the other.