OLTP vs OLAP
Transactional systems vs analytical systems — completely different design priorities.
OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) are two fundamentally different ways of using databases. Confusing their priorities is the #1 cause of slow, painful data systems.
The split
OLTP — running the business
Stores and processes individual transactions in real time. Powers your apps, e-commerce sites, banking systems.
Example: an ATM withdrawing $100
OLAP — understanding the business
Aggregates historical data for analysis, reporting, and decision-making. Powers BI dashboards, data science.
Example: "show me total ATM withdrawals by region for last quarter"
Side-by-side comparison
| Aspect | OLTP | OLAP |
|---|---|---|
| Purpose | Daily business operations | Analysis and insights |
| Data type | Current, real-time | Historical, aggregated |
| Normalization | Highly normalized (3NF) | Denormalized (Star/Snowflake) |
| Operation mix | INSERT, UPDATE, DELETE heavy | SELECT (read-only) |
| Query type | Simple, single-row | Complex aggregations |
| Query volume per query | Small (1 row to 100s) | Massive (millions of rows) |
| Concurrency | Thousands of users | Tens of users |
| Response time | Sub-second | Seconds to minutes OK |
| Update frequency | Continuous | Batch (nightly/hourly) |
| Schema design | Entity-relationship (ER) | Star / Snowflake |
| Backup criticality | Critical, frequent | Less critical, periodic |
| Storage size | Smaller (current data) | Much larger (historical) |
| Typical users | Customers, employees | Analysts, executives |
Real-world examples
OLTP examples
- Bank ATM — withdraw $100, balance updates immediately
- E-commerce checkout — order placed, inventory decremented, payment processed
- Airline reservation — book seat, mark unavailable, send confirmation
- POS systems — ring up sale, update inventory, print receipt
- User registration — create account, send welcome email
OLAP examples
- Sales dashboard — "total revenue by region by month for last 3 years"
- Customer segmentation — "show customers who bought X but never Y"
- Fraud detection — "find accounts with unusual transaction patterns"
- Inventory forecasting — "predict next quarter demand by SKU"
- Marketing attribution — "which campaigns drove the most revenue?"
Typical technology stack
OLTP databases
- PostgreSQL
- MySQL
- Oracle Database
- SQL Server
- MongoDB (NoSQL OLTP)
OLAP / data warehouses
- Snowflake
- Google BigQuery
- Amazon Redshift
- Databricks (Lakehouse)
- ClickHouse
Why we need both
Don't run analytics queries on your OLTP database — a single "SUM(amount) for the last 5 years grouped by region" can lock tables and bring down your transactional system.
Instead, the standard architecture is:
- OLTP database handles real-time transactions
- An ETL/ELT pipeline (Airflow, dbt, Fivetran) copies data nightly to the warehouse
- OLAP warehouse stores historical, denormalized, query-ready data
- BI tools (Tableau, Power BI, Looker) query the warehouse