What is OLTP vs OLAP?

Core Definition
OLTP (Online Transaction Processing) handles millions of small, concurrent user actions in real time. OLAP (Online Analytical Processing) analyzes years of accumulated historical data to answer complex business questions.
The Core Problem: Product teams need two completely different capabilities from their data systems. One system that tries to do both usually does neither well at scale.

Think about an e-commerce platform. When a user places an order, the system must update their cart, decrement inventory, charge their payment method, and confirm the order within 50 to 200 milliseconds p99 while thousands of other users shop simultaneously. This is OLTP: strict correctness, low latency, high concurrency for short operations.

OLTP Characteristics:

Designed for transactional workloads with row oriented storage and highly normalized schemas. Each table represents a single entity like users, orders, or payments. Operations are point lookups and small updates on specific rows. Systems enforce ACID guarantees so money is never created or lost and inventory counts stay accurate. Typical working set is gigabytes to low terabytes of hot, mutable data.

OLAP Characteristics:

Designed for analytical workloads with column oriented storage and denormalized schemas. Instead of asking "What is order 12345?", you ask "What is our conversion rate by country over the last 18 months?" These queries scan terabytes to petabytes of historical data, often taking seconds to minutes. Data is loaded in batches or micro batches, so it's usually slightly stale compared to the OLTP source.

✓ In Practice: OLTP handles the "write path" of core product operations. OLAP handles the "insight path" across all historical data. At scale, you separate them and design the integration between them.

💡 Key Takeaways

✓OLTP optimizes for low latency transactional guarantees on hot, mutable data with sub 10ms database latency

✓OLAP optimizes for high throughput analytics on large, mostly immutable data scanning terabytes to petabytes

✓OLTP uses row oriented, normalized schemas for point lookups; OLAP uses column oriented, denormalized schemas for aggregations

✓A single database attempting both workloads causes contention, lock timeouts, and p99 latency spikes from 100ms to multiple seconds

📌 Examples

1OLTP: User places order, system updates inventory count, processes payment, confirms order within 100ms while handling 10,000 concurrent users

2OLAP: Analyst queries "Show me daily active users by region for last 18 months" scanning 50 billion event rows, completing in 15 seconds

← Back to OLAP vs OLTP Trade-offs Overview