Skip to content

OLTP vs OLAP

1. Decision

Choose OLTP (Online Transaction Processing) databases for real-time operational applications requiring low-latency point reads/writes, concurrency control, and strict ACID transaction isolation; choose OLAP (Online Analytical Processing) databases for complex analytical reporting, aggregation queries over historical data, and business intelligence dashboards.

2. 30-Second Answer

OLTP databases (e.g., PostgreSQL, MySQL, Oracle) are optimized for transactional write/read operations processing short, concurrent requests (CRUD) on normalized schemas. They utilize row-oriented disk storage, dense B-Tree indexing, and MVCC to ensure fast point lookups (\(<5\text{ms}\)) and strict ACID guarantees. OLAP databases (e.g., Snowflake, ClickHouse, BigQuery, Amazon Redshift) are optimized for complex, read-heavy analytical queries scanning millions to billions of historical records. They utilize column-oriented storage, heavy data compression, sparse indexes, and distributed MPP (Massively Parallel Processing) compute engines to compute aggregations (SUM, AVG, COUNT) rapidly while trading off real-time single-row write capabilities.

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
OLTP Core operational business logic, user authentication, e-commerce orders, inventory updates Aggregating billions of log rows, multi-year historical trend reporting Low (<5ms point reads/writes) Scale-up; Read Replicas; Manual sharding Strict ACID (Serializable, Repeatable Read) Medium
OLAP Business intelligence, ad-hoc data analysis, historical trend metrics, data warehousing Real-time user-facing CRUD, sub-second single-row updates/deletes High query latency for point lookup (>100ms); Sub-second for mass aggregations Horizontal (Decoupled Compute and Storage MPP clusters) Eventually consistent batch loads High

4. Selection Rules

Requirement Best Choice Rationale / Example
ACID Multi-Row Transactions OLTP Payment processing, banking ledgers, reservation systems
Aggregations Across Billions of Records OLAP Annual revenue reporting, user behavioral retention analytics
High Frequency Random Point Writes (<5ms) OLTP Order placement, user profile updates, session creation
Data Compression & Low Storage Cost OLAP Columnar compression (5x-10x) cuts cost on multi-terabyte analytical logs
Complex Star/Snowflake Schema BI Queries OLAP Join multi-dimensional fact and dimension tables across distributed nodes
Real-Time Operational System of Record OLTP Authoritative domain store serving user-facing microservices

5. Tradeoffs

  1. Normalized Row Schema vs Denormalized Columnar Schema
  2. Gain (OLTP): Zero data redundancy; easy single-row updates without data inconsistency.
  3. Lose (OLTP): Expensive disk I/O when reading entire rows to aggregate a single column.
  4. Immediate Single-Row ACID Writes vs High-Throughput Batch Loading
  5. Gain (OLTP): Immediate visibility of transaction commits.
  6. Lose (OLTP): Poor throughput when ingesting millions of unstructured events per second.
  7. Dense Indexing (B-Tree) vs Sparse / Columnar Indexing
  8. Gain (OLTP): Instant retrieval of specific single records by primary/secondary key.
  9. Lose (OLTP): Index maintenance overhead severely degrades bulk write ingestion speed.
  10. Shared Compute-Storage Architecture vs Decoupled Architecture
  11. Gain (OLAP): Scale compute nodes (queries) independently from storage size (S3/GCS buckets).
  12. Lose (OLAP): High cold-start query latency when fetching data from remote object storage into local compute cache.
  13. Operational Integrity vs Analytical Flexibility
  14. Gain (OLTP): Prevents invalid domain states via foreign key constraints.
  15. Lose (OLTP): Lock contention under heavy analytical queries degrades production transactional throughput.

6. Interview Counter Questions

  1. Why shouldn't you run OLAP analytical queries directly on a live OLTP production database?
  2. How does Change Data Capture (CDC) bridge the gap between OLTP and OLAP systems?
  3. What is HTAP (Hybrid Transactional/Analytical Processing) and when is it viable (TiDB, SingleStore)?
  4. How do Star Schema and Snowflake Schema differ in OLAP data modeling?
  5. Why are single-row UPDATE operations extremely expensive in OLAP systems like Snowflake or BigQuery?
  6. How does Decoupled Compute and Storage work in modern cloud OLAP data warehouses (Snowflake/Redshift Serverless)?
  7. What is a Fact Table vs a Dimension Table in an OLAP database?
  8. How does row-based disk page allocation in OLTP lead to I/O amplification for analytical queries?
  9. When would you use a Data Lakehouse (Delta Lake, Apache Iceberg) instead of a traditional OLAP Data Warehouse?
  10. How do you handle schema evolution (e.g., renaming a column) across OLTP to OLAP pipelines?

7. Gold Standard Answers

  1. Heavy analytical queries perform full table scans and memory-intensive GROUP BY aggregations, consuming CPU/RAM and acquiring read locks, which starves OLTP threads and degrades user-facing transaction SLAs.
  2. CDC (e.g., Debezium) captures row-level insert/update/delete events from the OLTP Write-Ahead Log (WAL) and streams them asynchronously via Kafka into the OLAP store, keeping systems synchronized without impacting OLTP performance.
  3. HTAP engines (TiDB, SingleStore) maintain both row-oriented and column-oriented replicas of data in memory/disk simultaneously, allowing real-time analytics directly on transactional data without ETL delay. Viable for operational analytics up to moderate scale.
  4. Star Schema surrounds a central Fact Table with denormalized Dimension Tables (faster joins, higher redundancy). Snowflake Schema further normalizes Dimension Tables into sub-tables (lower redundancy, slower multi-join performance).
  5. OLAP stores partition data into large, immutable compressed columnar files (e.g., Parquet). Updating a single row requires reading the entire file, uncompressing it, mutating the value, recompressing it, and writing a new immutable file version.
  6. Storage resides in cheap cloud object stores (AWS S3/GCS). Compute clusters (stateless worker nodes) spin up on demand to pull only relevant column blocks into local NVMe caches, execute queries, and scale to zero when idle.
  7. Fact Tables contain quantitative numerical measurements/events (e.g., order amount, quantity sold, timestamps). Dimension Tables contain context attributes describing entities (e.g., customer address, product details, store location).
  8. OLTP reads data in 8KB disk pages containing full rows. Fetching a single integer attribute across 10 million rows forces the disk subsystem to load all surrounding unneeded column data, causing 90%+ disk I/O waste.
  9. Use a Lakehouse (Iceberg/Delta Lake) when you need open storage formats (Parquet) on cloud object storage accessible by multiple query engines (Spark, Trino, Flink) without vendor lock-in to a proprietary OLAP warehouse.
  10. Implement schema migration compatibility rules: add new columns as nullable in OLTP, update CDC transformers to map schema versions, update downstream OLAP tables via non-breaking schema evolution before removing old columns.

8. Common Mistakes

  • Running reporting scripts (SELECT SUM(amount) FROM orders) directly on the primary OLTP production database.
  • Attempting to use an OLAP database (BigQuery/Snowflake) as a backend for a real-time web application API.
  • Implementing synchronous double-writing to both OLTP and OLAP inside application code instead of using CDC.
  • Over-indexing an OLTP database to speed up analytical queries, causing severe write performance degradation.
  • Normalizing OLAP schemas into 5+ relational layers, creating massive join bottlenecks in distributed query engines.

9. Keywords

OLTP, OLAP, HTAP, CDC, Debezium, WAL, Fact Table, Dimension Table, Star Schema, Snowflake Schema, Decoupled Compute and Storage, Apache Iceberg, Delta Lake, Parquet, Columnar Store.

10. Cheat Sheet

  • Core Role: OLTP = Operational CRUD transactions (Postgres/MySQL); OLAP = Business intelligence aggregations (Snowflake/ClickHouse).
  • Storage Layout: OLTP = Row Store (Fast point access); OLAP = Column Store (Fast column aggregations & compression).
  • Architecture: OLTP = Coupled compute/storage; OLAP = Decoupled compute (MPP clusters) and storage (Cloud Object Storage).
  • Sync Pattern: OLTP Write-Ahead Log \(\rightarrow\) Debezium CDC \(\rightarrow\) Kafka \(\rightarrow\) OLAP Data Warehouse.
  • Data Model: OLTP = Normalized 3NF Schema; OLAP = Denormalized Star Schema (Fact & Dimension tables).
  • Rule: Never run long analytical queries on OLTP; never route user-facing microservice CRUD traffic to OLAP.

11. Related Decisions