OLTP vs OLAP Fundamentals

Two workloads that want opposite things

Every data-intensive system eventually splits into two workloads that want opposite things from a database, and confusing them is how you take down checkout with a dashboard.

OLTP: row store, normalized

OLTP (Online Transaction Processing) is your product's operational database: place an order, update a balance, mark a message read. The access pattern is many small, high-concurrency transactions, each touching a few rows by primary key or a narrow index. You want low write latency (single-digit ms), strong isolation, and thousands of concurrent connections. The physical layout that serves this is a row store: a row's columns are stored contiguously, so fetching or updating one whole record is one disk/page read. Postgres, MySQL, and DynamoDB are OLTP engines. The schema is normalized to avoid update anomalies.

OLAP: column store, denormalized

OLAP (Online Analytical Processing) is your analytics engine: revenue by region by day, funnel conversion, cohort retention. The access pattern is a few huge queries that scan millions to billions of rows but touch only a handful of columns, aggregating as they go. The layout that serves this is a column store: each column is stored contiguously, so a SUM(revenue) GROUP BY region reads only the revenue and region columns off disk and skips the other 40. Because a column holds one data type with low cardinality, columnar data compresses 5x to 20x (run-length, dictionary, delta encoding), which means less I/O, and engines run vectorized execution (process a batch of column values per CPU instruction) instead of row-at-a-time. Snowflake, BigQuery, ClickHouse, and Redshift are OLAP engines, usually fed a denormalized star schema (fact table plus dimension tables) so a query joins less.

  row store (OLTP)                 column store (OLAP)
  [id|name|region|rev] [id|...]    [id,id,id,...] [region,region,...] [rev,rev,...]
  read one row = 1 page            SUM(rev) reads only the rev column, compressed

Never run analytics on the OLTP primary

A single GROUP BY scan over the orders table evicts your hot rows from the buffer pool, holds read locks or MVCC snapshots that bloat, saturates I/O, and burns the connection your checkout path needed. The analytical query might run for 30 seconds; during those 30 seconds your p99 checkout latency triples. Isolation is not optional, it is the whole point.

How data moves OLTP to OLAP

Three patterns. ETL (extract, transform, then load) transforms before loading, classic for warehouses. ELT (load raw, transform in the warehouse) is now dominant because warehouse compute is cheap and elastic. CDC/streaming tails the OLTP write-ahead log and streams changes continuously. The axis is freshness vs simplicity: a nightly batch load is simple and fine for finance reporting; a real-time dashboard needs CDC or streaming and more moving parts.

Interview nuance: a read replica is not an analytics store. A Postgres replica is still a row store with OLTP layout; pointing dashboards at it isolates the primary from lock contention but still runs column-scan queries on a row engine, which is slow and steals replica resources. Use a replica for read scaling of OLTP-shaped queries, and a real column store for analytics.

Recap: OLTP is row-store, normalized, small high-concurrency transactions (Postgres/DynamoDB); OLAP is column-store, denormalized star schema, huge scans with compression and vectorized execution (Snowflake/BigQuery/ClickHouse); never analyze on the OLTP primary because scans destroy transactional latency; move data via ETL, ELT, or CDC trading freshness for simplicity.