OLTP vs OLAP

One database is built to change a single row fast; the other is built to read a billion rows fast. They are not the same machine.

DE Foundations · Beginner · ~5 min

In plain English

A corner shop till and a stocktake are both about the same goods. One has to serve one customer in seconds; the other has to count everything in the building. You would not use one to do the other's job.

Why it's worth your time

Almost every 'why is the database slow' incident in a young company is an analytical query running on a transactional database.

If you remember three things

Row storage suits one whole record; column storage suits one column of everything
The pipeline exists to move data between these two shapes
A warehouse has no cheap single-row update — that becomes your problem

Overview

Every data platform starts with the same split. OLTP (online transaction processing) serves the application: tiny reads and writes, one order or one user at a time, indexed to find a single row in milliseconds, and row-oriented because the app wants the whole row. OLAP (online analytical processing) serves analysis: scans over months of history, a handful of columns at a time, column-oriented and compressed so a query touches gigabytes instead of terabytes. Running analytics on the OLTP database is the classic first mistake — the query is slow because the storage layout is wrong for it, and while it runs it competes for the same buffers and locks your checkout flow needs.

In an interview

OLTP databases serve the app: small, indexed, row-oriented, tuned for many concurrent single-row reads and writes. OLAP systems serve analysis: column-oriented, compressed, tuned for scanning huge ranges of a few columns. The whole job of a data pipeline is to move data from the first shape to the second, because a query pattern that suits one is pathological on the other.

Production defaults

Never point BI at production: give analytics its own copy, replicated by CDC
Statement timeout: set one on every human-usable credential against the OLTP database
Publish the lag: state the analytical copy's freshness where analysts can see it

What breaks

App latency spikes when someone opens a dashboard — A scan evicted the buffer pool. Move the query to the warehouse and add a statement timeout.
Warehouse updates take minutes for a handful of rows — Column stores rewrite files. Batch the corrections into one MERGE per partition.

Watch it explained

What is ETL | What is Data Warehouse | OLTP vs OLAP — codebasics, 8:06