OLTP vs OLAP: Choosing How To Structure Data

A shop keeps its data in a database. Two different jobs use that data.

The first job is the shop app. A customer buys a pen, and the app saves the order. This job is called OLTP, short for online transaction processing.

A transaction is one small change that must fully succeed or fully fail.

The second job is analysis. An analyst asks a question about the whole business. For example: how much did we earn from paid orders in each country?

This job is called OLAP, short for online analytical processing.

Both jobs use the same shop data. This is the shop as in the SQL chapters: Asha is still in IN, and order 103 is still there.

In this chapter you will learn:

1. How the two jobs differ.
2. Why the app stores its data in many small tables.
3. Why analysts want one wide table instead.
4. How tables are written to disk, by row or by column.
5. Why you copy data into a separate database for analysis (a warehouse).

A workload is the kind of work a database does all day. The shop has two workloads on the same day.

This table puts them side by side:

The data is the same. The best table design for one job is not the best for the other. The next sections show each job with real queries.

The app keeps the shop in normalized tables. Each fact is stored once. The first query below shows the three tables as its input.

A customer opens order 101. The app runs this query:

SELECT o.order_id, o.status, o.amount, oi.product, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_id = 101
ORDER BY oi.product;

Say there is an index on order_id in both tables. An index works like the index of a book: it finds rows fast.

With those indexes, the query reads only order 101 and its two lines. It never touches orders 102 to 106.

Now Omar buys a bag. The app saves the order in one transaction:

BEGIN;
INSERT INTO orders (order_id, customer_id, status, amount, order_date)
VALUES (107, 5, 'paid', 30.00, '2026-01-22');
INSERT INTO order_items (order_id, product, quantity)
VALUES (107, 'Bag', 1);
COMMIT;

BEGIN and COMMIT wrap both inserts into one transaction. If the second insert fails, the database undoes the first one. Nobody ever sees half an order.

Order 107 is only for this example. Imagine we undo it. The later examples use orders 101 to 106.

You know normalization from chapter 5: each fact is stored once. The app needs this because it changes data all day.

Here is the recap for Asha's country, IN:

With one copy, the app updates one row and cannot forget another. This is the update anomaly from chapter 5.

The cost is on the read side. Showing order 101 needed a join. For the app, that is fine, because it reads only a few rows each time.

An analyst asks: what is the paid revenue by country? The country is in customers and the amount is in orders, so the query needs a join.

Dev has no country. COALESCE shows the word unknown for a missing value. Here is the query on the normalized tables:

SELECT COALESCE(c.country, 'unknown') AS country,
       SUM(o.amount)                  AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY COALESCE(c.country, 'unknown')
ORDER BY revenue DESC;

Check it by hand. The paid orders are 101, 102, 104 and 106. Orders 101 and 102 belong to Asha (IN): 40.00 + 25.00 = 65.00.

Order 104 is Carla (US): 15.50. Order 106 is Dev, who has no country: 80.00.

GB has no row. Ben has only a refunded order, and the WHERE removed it. Omar (IN) has no orders at all.

Notice what the query does. It reads every order and changes nothing. This is the OLAP shape: read a lot, then add it up.

Analysts ask many questions like the last one. Each one repeats the same join. A real shop has many more tables, so the joins pile up.

So the data is copied into a data warehouse. A data warehouse is a separate database built for analysis. There, one wide table holds one row for each order, with the customer name and country already copied in.

This query builds the wide table from the shop tables, and then shows it:

CREATE TABLE sales_wide AS
SELECT o.order_id, o.order_date, o.status, o.amount,
       c.name AS customer, c.country
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

SELECT * FROM sales_wide ORDER BY order_id;

You built a table like this in chapter 5 (shop_report). The revenue question now needs no join, and it gives the same three rows: unknown 80.00, IN 65.00, US 15.50.

The country repeats on many rows. In the app, that would be a problem.

Here it is fine, because nobody edits these rows by hand. When the shop changes, a load job replaces the rows (see "Copying data: batch and CDC"). This is denormalization on purpose.

A wide table is the simplest analytical shape. Chapter 8 teaches a tidier shape built from facts and dimensions. It is called a star schema.

You see a table as rows and columns. The database writes it to disk in one of two layouts. Take the first three rows of the wide table, with three columns:

In the row layout, one whole row is in one place. In the column layout, one whole column is in one place. This changes which jobs are cheap:

The app mostly does the first two jobs, so it likes rows. The analyst mostly does the third job. The revenue query needs 3 columns of a table that may have 50, and column storage skips the other 47.

Values in one column look alike (IN, IN, GB), so a column also takes less space on disk. PostgreSQL stores tables by row. Warehouses such as BigQuery, Snowflake and Redshift store them by column.

You could run the revenue query on the app database. For a tiny shop it works. As the shop grows, it causes three problems:

1. Load. The query reads every order. It uses the same computer power (CPU), memory and disk as the customers do. Paying for an order can become slow.
2. Shape. The app tables are built for writes. Every analytical question pays for joins.
3. History. A normal app table overwrites old values. It keeps only the current state, unless someone builds extra history tables.

See problem 3 in action. Order 105 is pending today.

Tomorrow the customer pays. The amount is 22.00 (we made up this number for the example). The app updates the order:

UPDATE orders SET status = 'paid', amount = 22.00
WHERE order_id = 105;

SELECT order_id, status, amount
FROM orders WHERE order_id = 105;

Now nobody can answer: how many orders were pending last week? The old value is gone from the app table. Later examples in this chapter use order 105 as it was (pending).

A warehouse can keep both states. Each load adds a row with the date it was loaded:

SELECT order_id, status, amount, loaded_on
FROM order_history
WHERE order_id = 105
ORDER BY loaded_on;

The shop keeps selling, so the copy must repeat. There are two common ways:

• Batch: once a night, copy the orders from the day before.
• CDC, short for change data capture: read the change log and copy each change soon after it happens. The change log is a file where the database records every change.

A copy job can fail halfway, and then you run it again. A safe job gives the same result when you run it twice. This is called idempotent.

A batch job often gets this by deleting a day and loading it again:

BEGIN;
DELETE FROM sales_wide WHERE order_date = DATE '2026-01-11';
INSERT INTO sales_wide
SELECT o.order_id, o.order_date, o.status, o.amount,
       c.name, c.country
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date = DATE '2026-01-11';
COMMIT;
-- Run the whole script twice, then check:
SELECT order_id, status, amount, country
FROM sales_wide WHERE order_date = '2026-01-11';

Only order 103 was placed on 2026-01-11. The second run deleted the first load, then loaded the day again. The table still has one row.

This copy keeps only the newest version of each day. Keeping every version, as in the two-row table earlier, is a different design that later chapters teach.

Do not choose by the name of a tool. Choose by the work. Ask who is asking, how many rows they read, and whether they change data.

This table shows four shop questions:

Some products try to do both jobs in one system. The two workloads still exist. For each query, ask which job it is.

Each exercise opens in the ERD canvas, which is a tool where you draw tables and links. Each one gives you a business story and rows to check your design against.

The last three exercises are optional. Try them after the chapters they name.