Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 36 ยท Architecture and Lifecycle

OLTP vs OLAP

Compare transactional OLTP and analytical OLAP systems, understand row versus column storage, and see why analytics should not run on the production database.

Two very different jobs hide behind the word "database". One job is running the business moment by moment: place an order, update an address, charge a card. The other is asking big questions about the past: revenue by region over three years. The first is OLTP (online transaction processing); the second is OLAP (online analytical processing). Data engineering exists largely to move data from systems built for the first job into systems built for the second.

Two workloads

OLTP OLAP
Typical query Fetch or change one order Sum revenue by month across millions of rows
Rows touched A few Millions or billions
Columns touched Most of the row A few columns
Latency goal Milliseconds Seconds to minutes
Concurrency Thousands of small transactions Few large queries
Data Current state History
Examples PostgreSQL, MySQL for an app BigQuery, Snowflake, ClickHouse

OLTP systems protect correctness with transactions (ACID guarantees) and normalized tables that avoid duplicate facts. OLAP systems favour fast scans and denormalized shapes that avoid joins.

Row stores and column stores

The physical layout explains most of the difference.

A row store keeps all fields of a record together on disk. Reading or updating one whole record touches one place, which is ideal for OLTP. A column store keeps each column together. A query that sums one column reads only that column, skips everything else, and compresses very well because similar values sit side by side.

Row layout

Record by record id, name, amount together.
Fast point lookup One read per order.
Slow wide scans Reads every column.
1 / 2

You can feel the difference with a small simulation. We store the same table as a list of rows and as a dict of columns, then count how many values each approach touches to compute the average of one column.

Python Row layout versus column layout

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The result is the same, but the column layout reads a quarter of the values here; with 100 columns it would read one hundredth. This is a simulation of the principle, not a timing of a real engine, but it is exactly why warehouses store columns.

Why not run analytics on the production database?

It is tempting: the data is already there. Three problems appear:

  1. Interference. A heavy report can slow down checkout for customers.
  2. Wrong shape. Normalized tables need many joins for analysis, and queries get slow and complex.
  3. No history. OLTP stores current state. When a customer moves, the old address is overwritten, and last year's regional report can no longer be reproduced.

The standard answer is a separate analytical store, fed by a pipeline from the OLTP system.

Seeing the history problem

Here is the "no history" issue, with a fix: keep each change as a new row with validity dates instead of overwriting.

Python Overwrite versus keep history

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

This pattern is a "slowly changing dimension", covered later in the modeling chapter. The takeaway: analytical stores keep history that operational stores discard.

The architecture that results

A typical platform copies data out of OLTP systems on a schedule (or continuously) into an analytical store:

flowchart LR
    A["App database<br/>(OLTP)"] --> B["Pipeline<br/>(ingest + transform)"]
    C["APIs and logs"] --> B
    B --> D["Warehouse or lakehouse<br/>(OLAP)"]
    D --> E["Dashboards"]
    D --> F["ML features"]

The pipeline is the bridge between the two worlds.

HTAP and the blurred line

Some systems try to do both (HTAP: hybrid transactional/analytical processing), keeping a row store for writes and a column copy for reads. They help for moderate analytics on fresh data, but a separate warehouse is still the norm for company-wide analysis because it can combine many sources and scale independently.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which workload fits a column store best?

    Column stores read only the columns needed, which makes large aggregates fast.
  2. 2. Why is running heavy reports on the production OLTP database risky?

    Heavy scans compete for resources with latency-sensitive transactions.
  3. 3. What does OLTP typically store?

    OLTP databases hold current operational state and optimize many small reads and writes.
  4. 4. How can an analytical store answer 'which city did the customer live in last January?'

    Versioned rows with valid_from and valid_to preserve the past.

Summary

  • OLTP serves many small transactions on current state; OLAP serves large analytical scans over history.
  • Row stores suit point reads and writes; column stores suit aggregates and compress well.
  • Keep analytics off the production database; copy data into an analytical store through a pipeline.
  • Analytical stores keep history that OLTP overwrites.

Next: ETL vs ELT