OLTP vs OLAP

Almost every data system you will ever touch falls into one of two camps, defined entirely by the shape of the work it does.

OLTP — Online Transaction Processing. Thousands of tiny concurrent transactions: read one user, update one order, insert one row. This is the database behind the live application. It is normalized, indexed for point lookups, and fully transactional (ACID). Think PostgreSQL, MySQL, SQL Server, Oracle.

OLAP — Online Analytical Processing. A handful of enormous read-only queries that scan millions-to-billions of historical rows to produce an aggregate: total revenue by region, daily active users by cohort. This is the analytics / BI database. Think Snowflake, BigQuery, Amazon Redshift, ClickHouse, DuckDB.

The one decision that drives everything: row vs column

Every difference in that table is downstream of a single physical choice: how the bytes are laid out on disk.

A row store keeps all the columns of a single row physically contiguous — one 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.

That one decision cascades:

Why the row store wins OLTP. Fetching one customer by primary key is a single B-tree walk plus one page read, because every field of that row is already adjacent. B-tree lookups are O(log n) — finding one row in ~100M records traverses only ~3-4 tree levels. Inserting, updating, or deleting one row touches one place. This is single-row locality + point-index seeks, and it is exactly what a live app does all day.

Why the column store wins OLAP. Three mechanisms compound:

1. Column pruning (projection). An aggregate over one or two columns reads only those columns’ blocks and skips every other column entirely. A row store must read every row — all columns — just to reach the few it needs.
2. Compression. A column holds values of a single type and domain, so same-type adjacency compresses far harder than mixed-type rows. Analytical compression is roughly 5-20x, and 30x+ on low-cardinality columns (order-of-magnitude, dataset-dependent). Fewer bytes on disk means fewer bytes to scan.
3. Vectorized execution. Contiguous same-type arrays let the engine process batches of thousands of values with SIMD / CPU-vector instructions and tight cache locality, crushing SUM / COUNT / AVG / GROUP BY.

The aha from the widget: the SQL barely changes; the storage layout flips the winner. Columnar is not “just faster everywhere” — it is poor at single-row point lookups and high-frequency single-row writes, because one insert must touch every column’s separate structure (write fan-out across N column files), and updating compressed blocks usually means append/rewrite rather than in-place edits. Great scans, poor point writes.

Why analytics on the OLTP box is harmful

So why not just run the big aggregate on production? Even under MVCC — where Postgres and MySQL/InnoDB readers generally do not block writers — a long analytical query on the live database hurts in ways that have nothing to do with classic lock blocking:

State the harm precisely. Long readers under MVCC do not usually lock out writers, but they do pin resources, hold long snapshots (bloat pressure), and can block schema or maintenance operations. The buffer-pool eviction and CPU/IO starvation are what actually slow your users. The standard fix is structural: isolate analytics on a separate system.

The bridge: ETL, ELT, and zero-ETL

If analytics lives in a separate columnar warehouse, something has to move the data there. That pipeline has evolved:

• ETL — Extract, Transform, Load. Transform the data on a separate processing tier before loading it into the warehouse. The older pattern; it fits when cleansing must precede load or the target compute is weak.
• ELT — Extract, Load, Transform. Load raw data into the warehouse first, then transform in-warehouse using its massively-parallel (MPP) compute. This is the modern default for cloud warehouses (Snowflake, BigQuery, Redshift), with dbt as the dominant in-warehouse transform tool.
• Zero-ETL / CDC. The newest evolution streams committed OLTP changes (change data capture) to the warehouse with minimal or no pipeline — for example AWS Aurora-to-Redshift zero-ETL, or ClickHouse’s ClickPipes / PeerDB.

The destination shapes the data into a star schema — the standard OLAP dimensional model. One (or a few) central fact table holds the quantitative measures (sales amount, quantity) plus foreign keys, surrounded by dimension tables holding descriptive attributes (time, product, customer, geography). It deliberately denormalizes dimension attributes into wide tables, trading redundancy for fewer joins and faster reads — the exact opposite philosophy from normalized OLTP design, which minimizes redundancy to keep writes safe. (A snowflake schema re-normalizes those dimensions; Kimball generally advises against it because the extra joins can hurt read performance.)

What about HTAP — one database for both?

HTAP — Hybrid Transactional/Analytical Processing is the convergence story: run OLTP and OLAP on the same system and data without moving it. The term was coined by Gartner in 2014. Real systems include TiDB (a row store, TiKV, plus a columnar replica, TiFlash), SingleStore, SAP HANA, Oracle Database In-Memory, and the columnar-augmented OLTP engines MySQL HeatWave and Google AlloyDB.

It is tempting to declare the divide solved. It is not — and the honest trade-offs matter:

• The data-format dilemma. You cannot simultaneously maximize performance isolation, data freshness, and workload-specific optimization. Something gives.
• Dual-format designs pay a write-amplification tax. Every transaction writes the row store and a columnar copy, roughly doubling write IO and adding format-conversion CPU.
• Single-format designs degrade one side. Research has measured analytical queries on a row store degrading ~50% when more than 95% of scanned data is unused, transactions on a column store degrading >20%, and OLTP throughput dropping up to ~42% from sharing hardware with analytics. (Workload-specific benchmark figures, not universal constants.)

The strongest market signal is that the industry chose separate engines over convergence. The two leading analytics platforms acquired dedicated Postgres OLTP engines rather than extend their columnar engines into HTAP: Snowflake bought Crunchy Data (announced June 2025), and Databricks bought Neon (May 2025). DuckDB’s rise — an embedded columnar OLAP engine, the “SQLite for analytics” — pushed analytics local and in-process for many workloads. The dominant production pattern is explicitly not one converged database: a row-store OLTP system (overwhelmingly Postgres) for the live app, plus a columnar OLAP system for analytics, connected by CDC / zero-ETL. Treat HTAP as a real-but-niche convergence attempt with honest caveats, not the winner.

Quick check

Next

You now know why analytics data lives somewhere else and what that somewhere looks like — columnar, denormalized, fed by CDC. Next time you write a heavy GROUP BY, you will know which kind of engine you actually want underneath it.