Why are analytics queries usually moved off the OLTP database?

Row-oriented storage and concurrent writers make big scans slow and risky for production

OLTP databases cannot store decimals

Row-oriented storage and concurrent writers make big scans slow and risky for production

SQL is incompatible with analytics

OLTP vs OLAP — the Split that Explains Everything — Semantic Web Academy

Theory

Two workloads, two physics

	OLTP (Transactional)	OLAP (Analytical)
Unit of work	One row at a time (e.g., lookup user #42)	Millions of rows scanned (e.g., sum all sales)
Latency target	< 10 ms	Seconds to minutes
Storage	Row-oriented (Postgres, MySQL)	Column-oriented (Parquet, Snowflake, BigQuery)
Schema	Highly normalised (3NF) to avoid duplication	Denormalised (star schema, OBT) for fast reads
Concurrency	Many small concurrent writers	Few heavy parallel readers

Why the split? Running a massive analytics query (GROUP BY country, SUM(revenue)) against your production OLTP database is like asking a Formula 1 car to deliver groceries. It forces the database to scan the whole disk, locking up memory. Suddenly, real users can't log in to your app because the database CPU is locked at 100% processing your CEO's report.

The Solution: We replicate the app database (OLTP) into a separate warehouse (OLAP). The app stays fast for customers, and analysts can run giant queries independently.

Analogy

OLTP is a convenience store: tiny transactions, thousands per minute, latency measured in heartbeats. OLAP is a distribution centre: very few customers, but each one walks out with a forklift load. Trying to fulfil a forklift order at a convenience-store counter blocks the line for everyone — that's exactly what happens when an analyst runs SELECT * FROM orders against your prod Postgres at 9 am.

Reflect

Most outages caused by analytics teams trace back to one row of architecture diagram: 'BI tool → connects directly to prod DB'. Map your own org's flows and find that line.

›Which dashboards in your org still read from a production OLTP database?
›What would it take — in tooling and in politics — to move them onto a warehouse?

Reading in progress · 0 of 2 activities done