§ 1.7section 7 of 25·~6 min
OLTP vs OLAP
Every company's data does two different jobs. Take a shopping app. When Asha taps Pay, the app has to record the order, take the payment, reserve the stock and confirm, in milliseconds, while thousands of other people shop at the same time. Later, someone in the business asks: which cities ordered the most last quarter? Did the festive-season discount actually grow sales?
- OLTP (online transaction processing) runs the business, one small transaction at a time: place an order, update an address, check a balance.
- OLAP (online analytical processing) helps the business understand itself, by reading a lot of history at once: revenue by city by month.
Martin Kleppmann's Designing Data-Intensive Applications (O'Reilly, 2017, chapter 3) is the classic explanation of the difference.
These are workloads, not database brands. PostgreSQL usually runs OLTP, but it can run an analytical query. A warehouse is built for OLAP, but it accepts writes. What decides it is what the queries do.
Side by side
| OLTP | OLAP | |
|---|---|---|
| Purpose | Execute business transactions | Analyse business data |
| Typical question | What is this order's status? | How did order volume change this year? |
| Used by | The app, on behalf of customers and staff | Analysts, managers, dashboards, data scientists |
| Query shape | Short reads and writes aimed at a few rows | Large scans, joins and aggregations |
| Rows touched per query | A handful | Millions to billions |
| Writes | Constant small inserts and updates | Bulk loads or continuous ingestion from pipelines |
| Data | Current state | History, often years of it, from many systems |
| Schema | Normalized, many tables | Facts and dimensions, often denormalized |
| Storage layout | Usually row-oriented | Usually column-oriented |
| "Fast" means | Low latency per transaction, high throughput | Short query time over huge data |
| Examples | PostgreSQL, MySQL, Oracle |