OLTP vs OLAP: Two Databases Built for Different Jobs
You need to store orders from customers and run reports on total revenue. Should both use the same database? Probably not. They have fundamentally different needs.
OLTP (Online Transaction Processing) handles daily operations. Every purchase, booking, or message is recorded immediately. Fast, short queries, high concurrency.
OLAP (Online Analytical Processing) handles historical analysis. Monthly reports, trend analysis, big aggregations. Slow, complex queries, low concurrency.
| Dimension | OLTP | OLAP | |-----------|------|------| | Queries | Short, frequent | Long, complex | | Data | Current state | Historical | | Rows per query | Few | Millions | | Concurrency | High | Low | | Latency | Milliseconds | Seconds to minutes |
Key sources: "Database Internals" by Alex Petrov, Kimball's "The Data Warehouse Toolkit."
Schema Design
OLTP databases use normalized schemas. Many tables with foreign keys. No redundancy. Designed for fast writes.
OLAP databases use denormalized schemas. Wide fact tables with dimensions. Redundancy is acceptable. Designed for fast reads and aggregations.
Common Mistakes
- Running reports on production OLTP databases. Reports slow down checkout.
- Using OLAP for user-facing transactions. Millisecond writes take seconds.
- One database for everything. Bad at both workloads.
Real Architecture
Many companies use Change Data Capture (CDC) to bridge OLTP and OLAP:
PostgreSQL (OLTP) --CDC--> Kafka --> BigQuery (OLAP)
Key Takeaways
- OLTP is for fast, small queries for daily operations.
- OLAP is for big, slow queries for historical analysis.
- They require different storage engines: row-oriented versus column-oriented.
- Do not mix workloads. Your users and analysts have different needs.