OLTP and OLAP

In short: OLTP is the many small queries of an application such as a checkout, and OLAP is the few large reporting queries that read all the rows.

Problem

The till and the accounts

The shop's database does two very different jobs at once. The customer at the till asks about one order and wants the answer right away. The owner on Monday morning asks "how much did we sell by month and by category", and that needs all the orders of the year.

The first are thousands a minute, small and by key. The second are a few a day, but each reads everything. The same database is rarely good at both. That is why there are different kinds of databases, and this module is about them.

The idea

OLTP: many small queries

OLTP (online transaction processing) is the job of the till: read or change a few rows that you know by key. The products in order 42:

SQLdatabase: shop

SELECT p.name, oi.quantity, oi.unit_price
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 42;

SELECT p.name, oi.quantity, oi.unit_price FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.order_id = 42;

▶ RunRestore the queryFresh database

The plan shows that the database does not read the whole table. It searches (SEARCH) with the index of the primary key and goes straight to the two rows:

SQLdatabase: shop

EXPLAIN QUERY PLAN
SELECT p.name, oi.quantity, oi.unit_price
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 42;

EXPLAIN QUERY PLAN SELECT p.name, oi.quantity, oi.unit_price FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.order_id = 42;

▶ RunRestore the queryFresh database

OLTP queries also write. A purchase is one transaction: the order, its row and the stock are written together or not at all:

SQLdatabase: shop

BEGIN;
INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 3, '2026-10-25', 'new');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (61, 9, 2, 3.2);
UPDATE products SET stock = stock - 2 WHERE id = 9;
COMMIT;

SELECT name, stock FROM products WHERE id = 9;

BEGIN; INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 3, '2026-10-25', 'new'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (61, 9, 2, 3.2); UPDATE products SET stock = stock - 2 WHERE id = 9; COMMIT; SELECT name, stock FROM products WHERE id = 9;

▶ RunRestore the queryFresh database

Try it

The customer asks

The customer calls about order 15. Show its status and date (columns status and ordered_at).

SQLdatabase: shop

SELECT status, ordered_at FROM orders;

SELECT status, ordered_at FROM orders;

Check▶ Run

The idea

OLAP: few big queries

OLAP (online analytical processing) is the job of analysis: gather, group and compare many rows. The revenue by month for 2026:

SQLdatabase: shop

SELECT substr(o.ordered_at, 1, 7) AS month,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled' AND o.ordered_at >= '2026-01-01'
GROUP BY month
ORDER BY month;

SELECT substr(o.ordered_at, 1, 7) AS month, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.status <> 'cancelled' AND o.ordered_at >= '2026-01-01' GROUP BY month ORDER BY month;

▶ RunRestore the queryFresh database

A key does not help here. It needs all the order rows, so the plan starts with SCAN:

SQLdatabase: shop

EXPLAIN QUERY PLAN
SELECT substr(o.ordered_at, 1, 7) AS month,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled' AND o.ordered_at >= '2026-01-01'
GROUP BY month
ORDER BY month;

EXPLAIN QUERY PLAN SELECT substr(o.ordered_at, 1, 7) AS month, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.status <> 'cancelled' AND o.ordered_at >= '2026-01-01' GROUP BY month ORDER BY month;

▶ RunRestore the queryFresh database

With 152 rows this is instant. With 50 million rows the report reads gigabytes and loads the disk, the memory and the processor for minutes. Meanwhile the till waits. And an index does not help, because the report wants everything anyway.

The idea

The data warehouse

The solution is for the reports not to touch the website's database. Every night a program copies the data into a separate database for analysis, called a data warehouse. The copying is called ETL: extract, transform, load.

During the transform the tables are usually merged into one wide table: everything about one sale in one row. This is denormalisation, which in the website's database would be a mistake: the name of the product repeats in every row. Here it is on purpose, because the data is only read and the report needs no JOIN. CREATE TABLE … AS SELECT makes a new table from the result of a query:

SQLdatabase: shop

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT * FROM sales ORDER BY order_id, product LIMIT 3;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT * FROM sales ORDER BY order_id, product LIMIT 3;

▶ RunRestore the queryFresh database

The cancelled orders are removed already during the copy, so the reports do not have to remember them.

More precisely, if you are curious

Real data warehouses (ClickHouse, BigQuery, Snowflake, Amazon Redshift) store the data by columns, not by rows. A revenue report reads only the columns day, category and amount and skips the rest. That is what the lesson about columnar databases is for. The wide table is usually a "fact table" with small lookup tables around it (dimensions: date, product, store), and the whole is called a star schema.

Try it

A report by category

From the table sales: the revenue by category for 2026 (columns category and revenue, rounded to a cent), from the largest down.

SQLdatabase: shop

SELECT * FROM sales LIMIT 5;

SELECT * FROM sales LIMIT 5;

Check▶ Run

Exercise

How many orders by city

From the table sales: how many orders there are from each city (columns city and orders), from the most down, and for equal ones by the name of the city. A customer without a city is a row with NULL.

SQLdatabase: shop

SELECT city, order_id FROM sales LIMIT 5;

SELECT city, order_id FROM sales LIMIT 5;

Check▶ Run

The idea

The kind of data and the kind of question

OLTP and OLAP are the first split. The rest come from what the data is like and what you ask about it. Each lesson in the module is one such pair, and most of them have a simulator of the real database in the browser:

The data and the question	Kind of database	Examples
I fetch by key, fast, often	key-value	Redis, Valkey, DynamoDB
A whole document with nested parts	document	MongoDB
What happened and in what order	event stream	Kafka, Redpanda
Who is connected to whom, in how many steps	graph	Neo4j
Sums over millions of rows, over time	columnar, time series	ClickHouse, TimescaleDB
Words in text	full-text search	Elasticsearch, OpenSearch
Similar in meaning	vector	pgvector, Qdrant
More data and queries than one server can take	cluster	replication, sharding

At the end is the lesson on how to choose. The answer is almost always: a relational database for the truth and another kind next to it only for the question it is weak at.

Test

Check yourself

1. Which one is an OLTP query?

The revenue by month for the last three yearsThe average rating by categoryThe cart of customer 42 while they are paying

2. In the wide table sales every row is one product of an order. How do you count the orders?

COUNT(DISTINCT order_id)SUM(quantity)COUNT(*)

3. Why are reports usually not run on the database the website works on?

Because they are forbiddenBecause SQL cannot do themBecause they read everything and slow down the small queries of the customers

Check

Start the lesson over

Related lessons

• Builds on: ACID: all or nothing · BEGIN, COMMIT, ROLLBACK · Indexes · EXPLAIN: the query plan
• Continues in: Key-value and the cache · Graph databases · Columnar and time series databases · How to choose a database
• Tasks on this topic: Kinds of databases