OLTP vs OLAP: Why Analytics Needs a Different Database
Row stores vs column stores, normalized vs dimensional schemas, and why you do not run dashboards on the production database.
Data WarehousingBeginner2 min readUpdated 11 Oct 2026
Two workloads
| OLTP (operational) | OLAP (analytical) | |
|---|---|---|
| Example | Checkout, bank transfer | Monthly revenue by region |
| Query shape | Read or write a few rows by key | Scan millions of rows, few columns |
| Concurrency | Thousands of tiny transactions | Fewer, heavy queries |
| Schema | Normalized (3NF) | Dimensional (star schema) |
| Storage | Row-oriented | Column-oriented |
| Examples | PostgreSQL, MySQL, DynamoDB | Snowflake, BigQuery, Redshift, Databricks SQL |
Why column storage wins for analytics
SELECT region, SUM(net_sales) FROM sales GROUP BY region;A row store reads every column of every row. A column store reads only region and net_sales, and similar values in a column compress very well.
Why not query production directly?
- Heavy scans slow down customer-facing transactions.
- Operational schemas are normalized for writes, so analytical questions need many joins.
- History is often overwritten, so "what was true last month" is lost.
So data is copied (ETL/ELT) from OLTP sources into a warehouse modeled for analysis.
Finished this note?