OLTP vs OLAP

Transaction processing and analytics read data in opposite ways. This topic covers why they usually live in separate systems, how data moves between them, and how a warehouse is laid out.

Overview

Databases serve two very different kinds of work. Online transaction processing (OLTP) is the work an application does for its users: place an order, update a profile, read one account. Online analytical processing (OLAP) is the work analysts and dashboards do: total revenue by region last quarter, or the conversion rate per campaign. The first touches a few rows many times a second. The second reads millions of rows to produce one small answer.

Because the access patterns are opposite, the best storage layout for each is opposite too. That is why most companies copy data from their operational databases into a separate analytical system, a data warehouse.

Two workloads

Who asks — The application, for its users — Analysts, reports, dashboards
A typical query — Read or write a few rows by key — Scan many rows, aggregate a few columns
Writes — Many small inserts and updates — Bulk loads, or a stream of appends
What matters — Low latency per request, correctness under concurrency — Throughput over large scans
Data — Current state — History over months or years
Usual storage — Row-oriented, with B-tree indexes — Column-oriented, compressed
Examples — PostgreSQL, MySQL behind an app — Amazon Redshift, Google BigQuery

Why not run both on one database

At small scale you can, and many teams start that way. As data grows, a long analytical scan competes with user requests for disk, memory and CPU. It pushes the hot pages that user requests need out of the cache, and a slow report can make the checkout page slow.

A read replica looks like the easy fix, but it has its own catch. In PostgreSQL, a long query on a hot standby can conflict with a change the standby must replay, most often the primary's vacuum removing old row versions the query still needs. Replay then waits, for up to max_standby_streaming_delay (30 seconds by default), and after that the conflicting query is canceled. Turning on hot_standby_feedback prevents these cleanup conflicts, the most common kind, but the primary then keeps dead rows longer, and tables can bloat. A replica is fine for light reporting; heavy analytics wants its own system.

The data warehouse

A warehouse is a separate database built for analytics. Data is copied into it from each operational system. In the classic flow, ETL (extract, transform, load), the data is cleaned and reshaped before loading. In the newer ELT flow, raw data is loaded first and transformed inside the warehouse with SQL.

The warehouse also brings data from many systems into one place, so a single query can join orders with billing and marketing data. The cost is freshness: the warehouse is behind production by however long the pipeline takes.

Star schema

Warehouses are usually modeled as a star schema. In the middle is a fact table: one row per event, such as one line of a sale, holding numbers you add up (quantity, price) and keys to the dimensions. Around it are dimension tables that describe the who, what, where and when of each event: the product, the store, the customer, the date.

A typical query filters and groups by dimension columns and adds up fact columns.

1 — Books — 2 — 30.00
1 — Games — 1 — 60.00
2 — Books — 1 — 15.00
2 — Games — 3 — 180.00

A snowflake schema goes one step further and splits dimensions into sub-dimensions (a product table pointing to a separate category table). It saves some space but adds joins; star schemas are simpler to query.

Row storage and column storage

An OLTP database stores each row's values together, which is ideal for reading or writing one whole record. An analytical query usually reads a few columns across a huge number of rows, so a warehouse stores each column separately instead.

The table below shows the same two sales laid out both ways on disk.

Row — 1, Books, 2, 30.00 — 2, Games, 1, 60.00 — next rows — next rows
Column — id: 1, 2 — category: Books, Games — quantity: 2, 1 — amount: 30.00, 60.00

Less I/O — Amazon Redshift's example: a query using 5 of 100 columns reads about 5% of the data
Better compression — One column holds one type of value and often repeats, so encodings like run-length work well
Bigger blocks — Redshift uses 1 MB blocks, where typical row stores use 2 KB to 32 KB
Cost for writes — Inserting one row touches every column file, so warehouses load in batches

BigQuery also stores data by column, and it separates storage from compute so each can scale on its own.

What you will be able to do

Tell an OLTP workload from an OLAP one by its queries, and name the storage each wants.
Explain why heavy analytics on the production database, or on a hot standby, causes problems.
Sketch an ETL or ELT pipeline into a warehouse and say what it costs in freshness.
Design a small star schema and write the aggregate query it serves.
Explain why column storage reads less and compresses better.

Key concepts

OLTP: Online transaction processing: many small reads and writes by key for an application, where latency and correctness matter.
OLAP: Online analytical processing: large scans that aggregate a few columns over many rows, where throughput matters.
Data warehouse: A separate analytical database that collects history from many operational systems, such as Amazon Redshift or Google BigQuery.
ETL and ELT: Extract, transform, load: reshape data before loading it. ELT loads raw data first and transforms it inside the warehouse.
Fact table: The central table of a star schema: one row per event, with numeric measures and keys to dimensions. Usually by far the largest table.
Dimension table: A table that describes one side of an event, such as the product, store or date, used to filter and group facts.
Star schema: A fact table linked to its dimension tables by keys. A snowflake schema splits dimensions further.
Column-oriented storage: Each column stored separately, so a query reads only the columns it uses and each column compresses well.

When to use it

Designing an analytics or reporting feature in a system design interview
Explaining why reports should not run on the production OLTP database
Discussing ETL or ELT pipelines from operational databases into a warehouse
Choosing between a row store and a column store for a workload

Interview questions

Why is column-oriented storage better for analytics than row-oriented storage?
Design the analytics side of an online store.
Can you run analytics on a PostgreSQL read replica instead of building a warehouse?
What is a star schema, and why do warehouses use it?
ETL or ELT: what is the difference, and which would you choose?