OLTP vs OLAP

What it is

OLTP (Online Transaction Processing) databases are designed for high-frequency, short-duration read and write operations: inserting an order, updating a user's balance, looking up a customer by email. They use row-oriented storage (all columns of a row are stored together on disk), are heavily indexed for point lookups, and enforce ACID transactions. Examples: PostgreSQL, MySQL, Oracle, SQL Server, Amazon Aurora, CockroachDB.

OLAP (Online Analytical Processing) databases are designed for complex aggregation queries over large datasets: "total revenue by product category by region for Q3", scanning millions or billions of rows across a few columns. They use columnar storage (all values of a column are stored together, enabling vectorized processing and high compression of similar values), are denormalized into star/snowflake schemas, and typically have weaker write performance. Examples: Amazon Redshift, Google BigQuery, Snowflake, ClickHouse, DuckDB.

HTAP (Hybrid Transactional/Analytical Processing) databases attempt to serve both workloads by maintaining both row-oriented and columnar representations of the same data internally. Examples: TiDB (row store + TiFlash columnar replica), SingleStore (formerly MemSQL), Google AlloyDB, Oracle Database with in-memory column store.

Why it exists

The storage layout difference is fundamental. In row-oriented storage, a query that aggregates one column (e.g., SUM(amount)) across 1 billion rows must read all 1 billion rows from disk, including dozens of irrelevant columns. In columnar storage, the same query reads only the amount column, which is stored contiguously, allowing 10–100x less I/O and CPU work. The columnar format also enables aggressive dictionary encoding and run-length encoding compression: a column with only 100 distinct country values across 1 billion rows compresses to near zero. Conversely, an OLTP single-row UPDATE touches exactly one disk block in row-oriented storage; in columnar storage, updating a row requires rewriting every column file that contains that row.

When to use

• Use OLTP for application databases serving user-facing requests: user accounts, orders, inventory, session state — any workload with high write throughput and sub-millisecond point-lookup requirements.
• Use OLAP for business intelligence, reporting, and analytics requiring aggregations over large historical datasets.
• Use OLAP (ClickHouse specifically) for real-time analytics on time-series event data where queries must scan billions of rows and return sub-second results.
• Consider HTAP (TiDB, SingleStore) when you need fresh analytics on operational data without ETL lag and can accept the increased cost and operational complexity.
• Use DuckDB (embedded OLAP) for local data exploration, ad-hoc analytics on files (Parquet, CSV), and lightweight analytical workloads that don't justify a full warehouse deployment.

When not to use

• Don't run complex analytical queries on your OLTP database — full table scans for aggregations lock pages, consume I/O bandwidth, and degrade transaction performance for application users.
• Don't use a data warehouse for OLTP workloads — Snowflake and BigQuery have high per-write latency (seconds to minutes for small individual inserts).
• Don't adopt HTAP to "avoid ETL" without first understanding the cost — HTAP systems are significantly more expensive per unit of storage and compute than separated OLTP + OLAP architectures.
• Don't assume read replicas solve the OLAP problem — a PostgreSQL replica still uses row-oriented storage and will underperform a columnar system for aggregation-heavy queries.

Typical architecture

  OLTP vs OLAP Storage Layout:

  ROW-ORIENTED (OLTP — PostgreSQL heap):
  ┌──────────────────────────────────────────────┐
  │ Row 1: [id=1, name="Alice", country="US", amount=100.00] │
  │ Row 2: [id=2, name="Bob",   country="UK", amount=250.00] │
  │ Row 3: [id=3, name="Carol", country="US", amount=75.00]  │
  └──────────────────────────────────────────────┘
  SUM(amount) must read ALL columns of ALL rows → high I/O

  COLUMNAR (OLAP — Redshift / BigQuery / Parquet):
  ┌─────────────────────┐ ┌─────────────────────────┐
  │ id column:          │ │ amount column:           │
  │ [1, 2, 3, ...]      │ │ [100.00, 250.00, 75.00] │
  └─────────────────────┘ └─────────────────────────┘
  SUM(amount) reads ONLY amount column → minimal I/O

  HTAP (TiDB):
  ┌──────────────────────────────┐
  │  TiKV (row store, OLTP)      │◀── App writes (INSERT/UPDATE)
  │  TiFlash (columnar replica)  │◀── Sync via Raft replication
  │  TiDB SQL layer              │──▶ Routes queries to TiKV or TiFlash
  └──────────────────────────────┘
          

Pros and cons

Implementation notes

For ClickHouse (the leading open-source OLAP system for real-time analytics), use the MergeTree engine family and choose the primary key (ORDER BY) based on the most common query filters. ClickHouse stores data in parts that are merged in the background; the primary key determines the on-disk sort order and enables range scans to skip irrelevant data. Use ReplacingMergeTree for upsert semantics (deduplication happens during merge) and AggregatingMergeTree with materialized views to pre-aggregate frequently computed metrics. ClickHouse achieves tens of billions of rows per second scan throughput on a single node by combining columnar storage with vectorized SIMD execution.

For the OLTP side, avoid running analytical queries against the primary database. If real-time analytics on operational data is needed without ETL lag, consider: (1) a read replica with a reporting schema, (2) Change Data Capture feeding a real-time OLAP system (Debezium → Kafka → ClickHouse or Druid), or (3) an HTAP system like TiDB if the use case justifies the cost. The CTE/window function capabilities of modern OLTP databases (PostgreSQL) can handle moderate analytical queries against reasonably sized datasets — measure first before assuming a dedicated OLAP system is required.

Common failure modes

• Analytical queries killing production OLTP: A data analyst runs a long-running aggregation query against the production PostgreSQL database; it acquires shared locks on large tables, blocking application writes and causing transaction timeouts.
• Assuming a warehouse is always better: A team moves a 100MB dataset to Snowflake for "analytics"; Snowflake's query startup overhead means ad-hoc queries are slower than DuckDB running locally against Parquet files.
• HTAP cost surprise: A team adopts TiDB to "unify" OLTP and OLAP; the TiFlash columnar replica doubles storage costs; the engineering team underestimated the operational complexity of the TiDB cluster.
• Columnar index misuse: Trying to use a columnar OLAP system as an OLTP replacement: high-frequency point lookups against Redshift result in poor performance and high cost because each query scans a column file rather than using an index.
• Skipping partition pruning: A BigQuery table is date-partitioned but queries omit the date filter; every query scans the full table rather than pruning to the relevant partition, resulting in 100x higher costs than expected.

Decision checklist

• Is the primary workload transactional (high-frequency single-row reads/writes) or analytical (aggregations over large datasets)?
• What is the acceptable data freshness? ETL lag of minutes-to-hours (OLAP) vs real-time access to operational data (HTAP or read replica)?
• Are analytical queries already degrading production OLTP performance, justifying separation?
• Have you evaluated DuckDB for lightweight analytical needs before deploying a full cloud warehouse?
• If choosing HTAP, have you modeled the additional cost (columnar replica doubles storage) and operational complexity?
• Are all analytical queries using partition pruning (date filter on partitioned tables) to avoid full-table scans?

Example use cases

• E-commerce OLTP + OLAP: PostgreSQL handles order placement, inventory updates, and user sessions with sub-5ms response times. CDC via Debezium streams changes to Redshift where BI analysts query 3 years of order history for trend analysis.
• Real-time analytics on events (ClickHouse): A SaaS platform tracks 500M user events/day. ClickHouse ingests events in real time from Kafka and answers "active users in the last 30 minutes by feature" in under 100ms.
• HTAP with TiDB: A fintech company uses TiDB for account balances and transactions (OLTP) and enables TiFlash for same-node analytical queries on transaction history for real-time fraud scoring — avoiding ETL lag while accepting 2x storage cost.

Related patterns

• Data Warehouse — The standard OLAP implementation pattern with dimensional modeling.
• Normalization vs Denormalization — OLTP favors normalization; OLAP favors denormalization for query performance.
• CQRS — OLTP/OLAP separation is a storage-layer expression of the CQRS principle of separating read and write models.

Further reading

• ClickHouse Documentation
• Column-Stores vs Row-Stores: How Different Are They Really? (Abadi et al., SIGMOD 2008)
• TiDB Architecture Documentation