Skip to content
BENIBIX Learn

OLTP, OLAP and HTAP

Level 4 · Models and workloads

Why the database behind an app is not the one behind its reports. OLTP serves many small writes, OLAP serves big reads, and HTAP tries to do both.

Course contentsLesson 11 of 11

After this lesson you can

  • Tell an OLTP workload from an OLAP one
  • Explain why reports move off the app database
  • Say what denormalization trades away

12 mincovers 4 glossary entries

Worth reading first: Data Models and ER Diagrams

Why apps and reports need different databases

OLTP and OLAP are two kinds of database work. OLTP is the app. It does thousands of tiny reads and writes a second, each on a few rows. OLAP is the report. It asks a few big questions, and each one reads millions of rows. HTAP tries to run both in one system.

An OLTP database answers questions like what is in this cart, or did this payment go through. Each query finds one or two rows by their key. It answers in a few thousandths of a second. It keeps each row together on disk, so it can read or change one whole record fast.

An OLAP system answers questions like what were sales by region each month for three years. One query scans millions of rows but only a few columns. So it keeps data by column, not by row, and reads only the columns it needs. A warehouse is built for this kind of work.

Run that big report on the app's database and the app slows down for everyone. The report grabs the disk and memory that checkouts need. That is why most firms copy data out of the app database into a warehouse. The app keeps its speed, and the report gets a system built for it.

Tables for reports are often denormalized. That means copying data into one wide table so a query needs fewer joins. An orders table might carry the customer's city and the product's group right on each row. It uses more space and repeats facts. In return, a report reads one table instead of five.

HTAP runs both kinds of work in one system. It keeps a row copy for the app and a column copy for reports. It keeps the two in step. Reports see data that is seconds old, not last night's. The cost is a harder system to run, and few teams need it on day one.

People often build a report right on the app database. The data is already there. It works on day one, with a thousand orders. At a million orders, the same report slows checkout each time it runs. Moving reports off early costs far less than moving them in a hurry.

OLTP, OLAP and HTAP side by side

WorkloadTypical questionRows per queryKeeps data by
OLTPFind this orderOne to a fewRow
OLAPSales by month for three yearsMillionsColumn
HTAPBoth, on the same dataA few or millionsA row copy and a column copy

Which one to pick

  1. 1

    OLTP

    Pick it for the app itself. That means carts, logins, payments and anything a user waits on.

  2. 2

    OLAP

    Pick it for reports and charts, fed by a copy of the app's data.

  3. 3

    HTAP

    Pick it only when reports need data that is seconds old. A nightly copy is too slow for them.

The app reads whole rows, one at a time. The report reads two columns across millions of rows.

Worked example

A sales report slows checkout

Emma's shop runs a monthly sales report on its app database. With 2 million orders, it takes 4 minutes, and checkout pages time out while it runs.

She copies orders into a warehouse each night. The report now takes 6 seconds there, and checkout never notices it.

The words for this lesson4

Check yourself

Why does a big report slow down an app when both share one database?

Finished this lesson?

Marking it complete also marks its 4 glossary entries learned, which updates your roadmap and your saved page. 0 are already marked.