When to Use OLTP vs OLAP: Decision Criteria

The Decision Framework: The choice isn't about which is "better" but matching system characteristics to workload requirements. Here's how to think through it in an interview. Choose Separate OLTP and OLAP When: First, workload separation is clear. User facing operations need sub 200ms p99 latency with thousands of concurrent writes per second, while analytics queries scan terabytes taking seconds to minutes. Running both on the same database causes lock contention, cache thrashing, and p99 latency spikes from 100ms to multiple seconds. Second, data volume exceeds what OLTP can efficiently handle. If you're storing years of historical data totaling tens of terabytes to petabytes, OLTP indexes and row oriented storage become prohibitively expensive. Analytics queries would perform full table scans that starve transactional traffic. Third, analytics freshness tolerates lag. If daily reports can use 24 hour old data or dashboards accept 15 minute lag, batch ETL from OLTP to OLAP is feasible. You avoid the complexity of maintaining real time consistency across both systems. Consider Keeping Everything in OLTP When: You're at early startup scale. With under 1 million users, gigabytes of data, and hundreds of queries per second, a single well tuned OLTP database with read replicas handles both transactional and analytical workloads. Operational simplicity outweighs optimization. Analytics queries are simple and infrequent. If you're running a few dashboard queries per hour that touch recent data with selective indexes, OLTP performance remains acceptable. The overhead of maintaining separate systems isn't justified. You need absolute freshness. Some use cases like real time fraud detection or dynamic pricing require analytics on data that's milliseconds old, not minutes. Streaming analytics or HTAP (Hybrid Transactional Analytical Processing) systems bridge this gap, though they trade off raw throughput. The Cost Trade Off: OLAP clusters and data lakes can cost 2x to 5x more than OLTP alone at petabyte scale, but they free OLTP from analytics load. A table with 5 indexes in OLTP means 6 writes per INSERT, dropping throughput from 50,000 to 8,000 inserts per second. For write heavy workloads (over 80% writes), this is catastrophic. Moving analytics to OLAP removes index overhead, restoring OLTP write throughput. The Hybrid Middle Ground: Micro batch or streaming analytics systems like Apache Flink or ksqlDB provide sub minute freshness by processing event streams before they land in the warehouse. You maintain both: streaming for real time alerts and fraud detection (latency under 1 minute), batch OLAP for complex historical analysis (latency 15 minutes to hours).