OLTP vs OLAP

It is the same data, and you already know SQL — so why does every serious team copy that data into a second, completely different kind of database before drawing a single chart?

The answer is not bureaucracy. It is physics: the way the bytes are laid out on disk makes one database brilliant at serving your app and hopeless at analytics, and the other one the exact reverse. Almost every data system you will touch falls into one of these two camps, and the camp is decided entirely by the shape of the work.

The first camp is OLTP — Online Transaction Processing — the database behind the live application. Its day is thousands of tiny concurrent transactions: read one user, update one order, insert one row. It is normalised, indexed for point lookups, fully ACID. Think PostgreSQL, MySQL, Oracle. The second is OLAP — Online Analytical Processing — the analytics database. Its day is a handful of enormous read-only queries that scan millions of historical rows to produce one aggregate: revenue by region, active users by cohort. Think Snowflake, BigQuery, Redshift, DuckDB.

One decision drives all of it: row vs column

Every row of that table is downstream of a single physical choice — how the bytes sit on disk. A row store keeps all the columns of one row contiguous: a customer’s id, name, region, and balance sit together on one page. A column store keeps each column’s values contiguous in its own block: all the regions together, all the amounts together, across millions of rows.

From that one choice, everything else follows. The row store wins OLTP because fetching one customer by primary key is a single B-tree walk plus one page read — every field of that row is already adjacent — and a write touches one place. That is single-row locality and point seeks, exactly what a live app does all day. The column store wins OLAP through three effects that compound: it reads only the column(s) the query touches and skips the rest (column pruning); a column of one type and domain compresses far harder than mixed rows, often 5–20× (compression); and contiguous same-type arrays let the CPU process thousands of values at once with vector instructions (vectorised execution). The same SUM that crawls on a row store flies on a column store — and the SQL never changed.

The flip side is real, too: a column store is poor at single-row lookups and high-frequency single-row writes, because one insert must touch every column’s separate structure. Great scans, poor point writes. Neither layout is “just faster”; each is shaped for one kind of work.

Why analytics on the OLTP box is harmful

So why not just run the big aggregate on production? Even under MVCC — where a long reader does not usually block writers — a giant analytical scan on the live database hurts in ways that have nothing to do with locks:

State the harm precisely: the long reader does not lock out writers, but it evicts the hot working set from the buffer pool, steals CPU and IO from transactions, and holds a long snapshot. That is what slows your users. The standard fix is structural — isolate analytics on a separate system.

The bridge, and the convergence that didn’t win

If analytics lives in a separate columnar warehouse, something must move the data there. That pipeline has three forms: ETL transforms the data on a separate tier before loading it; ELT loads raw data into the warehouse first and transforms it in place using the warehouse’s parallel compute (the modern default, with dbt the dominant transform tool); and zero-ETL / CDC streams committed changes straight across with little or no pipeline. The destination shapes the data into a star schema — a central fact table of measures and keys, ringed by dimension tables of descriptive attributes — deliberately denormalised, trading redundancy for fewer joins, the exact opposite of normalised OLTP design. (That schema is the next lesson.)

It is tempting to think one database could do both — that is HTAP, hybrid transactional/analytical processing (TiDB, SingleStore, SAP HANA). But the honest trade-offs bite: a dual-format system pays a write-amplification tax (it writes the row store and a columnar copy), and a single-format one degrades the other workload measurably. The clearest signal is what the market did — the two biggest analytics vendors bought dedicated Postgres OLTP engines rather than converge: Snowflake acquired Crunchy Data, Databricks acquired Neon. The dominant pattern is not one converged database; it is a row-store OLTP system for the app plus a columnar OLAP system for analytics, glued by CDC.

Practice