OLTP vs OLAP

Two Database Paradigms

OLTP

Online Transaction Processing. Single-row lookups, <10ms latency, normalized schemas, high write throughput. MySQL, PostgreSQL, DynamoDB. Used for application backends.

OLAP

Online Analytical Processing. Large aggregations, seconds-minutes latency, denormalized schemas, read-heavy. Snowflake, BigQuery, Redshift. Used for analytics and reporting.

The Key Difference

OLTP: SELECT * FROM orders WHERE id = 123 (reads 1 row). OLAP: SELECT region, SUM(amount) FROM orders GROUP BY region (scans millions of rows). Columnar storage makes OLAP aggregations fast.

Code Example

-- OLTP: Fast single-row lookup
SELECT * FROM orders WHERE order_id = 12345;
-- Reads 1 row from millions. Indexed. < 1ms.

-- OLAP: Aggregation over large dataset
SELECT
region,
DATE_TRUNC('quarter', order_date) AS quarter,
SUM(amount) AS total_revenue,
COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY region, quarter
ORDER BY total_revenue DESC;
-- Scans millions of rows. Columnar storage. 2-30 seconds.

Best Practices

• Know when to use OLTP vs OLAP
• Understand columnar vs row storage
• Design schemas appropriate for the access pattern
• Use CDC to bridge OLTP and OLAP

Interview Tips

• Compare OLTP vs OLAP trade-offs
• Explain columnar storage benefits
• Design a CDC pipeline