How OLTP and OLAP Work at Scale

The Architecture Pattern: At companies like Amazon, Google, or Meta handling 10 million daily active users and 100,000 orders per hour, OLTP and OLAP exist as distinct but tightly connected layers.

1
User Operations Hit OLTP: Add to cart, place order, update address go to OLTP databases behind application services. These handle thousands to tens of thousands of writes per second, maintain transactional integrity for operations like "payment capture plus inventory decrement", and must complete within 100ms p50 and 250ms p99.
2
Events Stream to Analytics: Every transaction and event streams into an append only event log or message bus. At Amazon scale, this means millions of events per second landing in object storage. Batch pipelines run every 5 minutes, hourly, or nightly depending on freshness requirements.
3
OLAP Processes Historical Data: Data lands in a data lake, then transforms and loads into a columnar warehouse. Queries scan billions of rows, join multiple large tables, and aggregate heavily. Latency is acceptable at seconds to tens of seconds p95 for dashboards, minutes for offline jobs.
4
Results Return to Serving: OLAP feature stores compute user segments or model scores offline, then push compact derived tables or key value snapshots into fast OLTP stores or caches for online serving. The OLTP layer never sees the full historical dataset.
Real Scale Example: Netflix runs complex OLAP workloads on petabyte scale usage logs to drive content recommendations and A/B test analysis. Daily financial reporting tolerates 24 hour lag, while near real time fraud detection requires sub 5 minute lag.

Typical Data Volumes
10K/sec
OLTP WRITES
1M/sec
EVENTS STREAMED
50B
ROWS SCANNED
❗ Remember: The OLTP layer handles current state for low latency serving. The OLAP layer handles historical analysis across all data. Data flows one direction: from OLTP to OLAP, never the reverse except for precomputed results.

💡 Key Takeaways

✓OLTP databases handle 10,000+ writes per second with 100ms p50 latency, maintaining ACID guarantees for core product flows

✓Event streaming captures millions of events per second from OLTP into append only logs landing in object storage

✓OLAP warehouses store petabytes in columnar format, scanning billions of rows with query latencies of seconds to minutes p95

✓Data freshness varies by use case: daily reports tolerate 24 hour lag, fraud detection needs sub 5 minute lag

📌 Examples

1E-commerce platform: OLTP handles order placement at 100K orders/hour, streams to data lake, OLAP computes GMV by region scanning 10 billion order records

2Netflix: OLTP serves video playback decisions in milliseconds, streams viewing events, OLAP analyzes petabytes of logs for content recommendations

← Back to OLAP vs OLTP Trade-offs Overview