OLTP vs OLAP: Why the Data Layout Changes Everything

Every data-intensive system eventually hits the same fork: the database that is perfect for running the application is the wrong database for understanding it. Get this wrong and you either bolt reporting onto production until analytical scans start starving live traffic, or you copy data around with no clear idea of why. This post is the reasoning behind that fork — and a small repo you can run to watch it happen on your own machine.

Runnable companion: oltp-to-olap-pipeline on GitHub. Clone it, then make up && make seed && make etl && make bench reproduces every number below.

Two workloads that want opposite things

"OLTP" (online transaction processing) and "OLAP" (online analytical processing) are not two products you shop for — they are two access patterns, and they pull storage in opposite directions.

	OLTP (operational)	OLAP (analytical)
Typical question	"What is order #4821's status?"	"Revenue by category by month, last 3 years?"
Access pattern	Many small reads/writes, point lookups	Few queries, each scanning millions of rows
Columns touched	Most columns of a few rows	A few columns of most rows
Data model	Normalized (3NF) — avoid update anomalies	Denormalized star schema — avoid joins
Physical layout	Row-store	Column-store
Who it serves	The application and its users	Analysts, BI, data science
Freshness	Live	Loaded periodically via ETL

The last two rows are the ones most engineers skip past — and they are exactly where the architecture lives.

Row-store vs column-store: the crux

Consider "sum revenue by category by month" over three years of orders. It reads four columns — quantity, unit price, order date, category — across millions of line items, and aggregates.

A row-store (Postgres, MySQL) keeps every column of a row physically together on a page. That is ideal for "fetch this one order and everything about it". But for our analytical query it drags all twelve columns of every row off disk just to use four:

Row-store page layout (one order line, all columns together):
[id|order_id|product_id|qty|price|status|created|updated|...] [next row...] ...
                 ▲scan wants these▲   ▲ but must read the whole row anyway ▲

Column-store layout (each column stored contiguously):
qty:    [3 1 2 4 1 2 ...]   ← read only this
price:  [12.00 8.50 ...]    ← and this
status: [paid paid ...]     ← skip entirely

A column-store (DuckDB, ClickHouse, Redshift) keeps each column's values together. The same query reads only the columns it needs, and each column compresses far better because neighbouring values are similar (all prices, all categories). Less IO, less to decompress, vectorized aggregation. That is the entire source of the speed-up.

The headline: the row-vs-column choice is about access pattern, not about which database is "better". Neither layout is wrong — they are wrong for the other job.

The star schema: modelling for reads

On the analytical side we also change the logical model. Normalized 3NF is built to make writes safe (a price lives in exactly one place). Analytics wants the opposite — as few joins as possible — so we reshape into a star schema:

          dim_date
             │
dim_customer─┼─ fact_sales ─┬─ dim_product
             │              │
          (who)          (what / when / how much)

• fact_sales — the measures. Its grain is one order line item; every metric you can ever compute is bounded by that choice, which makes grain the single most important modelling decision.
• dim_* — the descriptive context you filter and group by (customer, product, date).

Revenue is pre-computed and stored on the fact row, so the dashboard never re-derives it and can't get it wrong.

The ETL bridge — and what a warehouse actually is

The two stores are connected by ETL (extract, transform, load). In the lab it is deliberately the simplest thing that teaches the idea: DuckDB's Postgres extension reads the operational tables directly, SQL reshapes 3NF into the star, and the result is written to the warehouse file.

That bridge is the definition of a data warehouse (or a data lake): a place that receives operational data and re-stores it in a layout built for analysis. "Just point the dashboard at the read replica" skips this step — and inherits both the wrong layout and contention with live traffic.

One honest caveat, captured as an ADR in the repo: the lab does a full rebuild each run. Real pipelines load incrementally, and adopt change-data-capture (CDC) only when a genuine freshness SLA demands it. Freshness is a cost, not a free upgrade.

Measure it, don't assert it

The point of making it runnable is that you get a number instead of a claim. make bench runs the identical question on both engines (seven runs, warm-up discarded). On my laptop, with the default seed of ~50k orders / ~150k line items, it prints:

Analytical query: revenue by product category by month
  (median of 7 runs, warm-up discarded)

  PostgreSQL (row-store, 3NF joins)   median   57.2 ms   (222 rows)
  DuckDB     (column-store, star)     median    4.3 ms   (222 rows)
  → column-store is ~13.4x faster on this workload

Same 222-row answer, ~13x less time. Absolute numbers depend on your hardware and the seed size; the ratio is the durable lesson. Bump SEED_ORDERS and the gap widens — which is itself the point: the column-store's advantage grows with scan size, because that is the workload it was built for.

What I'd say in an interview

• OLTP vs OLAP is a workload distinction, not a product one. Name the access pattern first, then the storage follows.
• Normalization serves writes; the star schema serves reads. You keep both, and ETL is the bridge — that is what a warehouse/lake is for.
• Row-store drags whole rows; column-store reads whole columns and compresses them. That single fact explains the order-of-magnitude difference on analytical scans.
• The fact table's grain is the decision everything downstream depends on.
• And when you can, bring the measured ratio to the conversation instead of asserting it. This repo exists so I can.