Loading...
System Design and AI Engineering, by Roni Das. 972 interactive lessons.
Start LearningThe short answerOLTP databases such as PostgreSQL and MySQL handle many tiny reads and writes of single rows while your app runs; OLAP engines such as DuckDB, Snowflake and BigQuery scan millions of rows to answer summary questions, so use OLTP to run the business and OLAP to study it.
Online Transaction Processing
the shop counter
Online Analytical Processing
the back office
Read across a row to compare one thing. Every word that may be new is explained just below the table.
| Aspect | OLTP | OLAP |
|---|---|---|
| Main job | Run the live app: record what happens, one event at a time. | Answer questions about everything that already happened. |
| Typical query | Get order 4,812,113. Insert one new order. | Sum revenue by country for 2025 over every order. |
| Rows touched per query | One to a few hundred. | Millions to billions. |
| Writes | Constant small inserts and updates, many users at once. | Big batch loads, often every hour or every night. |
| Storage layout | Row store: a whole row sits together on a page. | Column store: each column sits together, compressed. |
| Speed target | Milliseconds per query. | Seconds, sometimes minutes, for very large scans. |
| Users | Customers, through your app. | Analysts, data scientists, dashboards. |
| Data shape | Normalized: many small tables joined by keys. | Often denormalized: wide tables or a star schema. |
| Examples | PostgreSQL, MySQL, SQL Server, Oracle, MongoDB. | DuckDB, Snowflake, BigQuery, ClickHouse, Redshift. |
| In our test (5M rows) | Point lookup 0.13 ms, total by country 344 ms. | Point lookup 1.09 ms, total by country 20 ms. |
Real situations, and the pick we would make in each one.
One small write that must be safe and fast. This is the textbook OLTP job.
An index finds a handful of rows for one customer in well under a millisecond.
That reads every order but only three columns. A column store skips everything else.
Repeated big scans on your live database would slow down real customers. Point the dashboard at a copy.
The check itself is a fast OLTP read. The rules behind it are usually learned from history in an OLAP system.
At this size PostgreSQL answers the reports too. Add an OLAP engine when reports start to hurt the app.
we ran this, here is what happened
We made one table of 5,000,000 fake orders: id, customer, country, status, amount and date. Both engines loaded the exact same CSV file, so they hold identical data.
PostgreSQL 18 is a row store and a classic OLTP database. DuckDB 1.5 is a column store built for OLAP. Both had a primary key on the order id, which gives each one an index on it.
Then we timed three jobs on each: find one order by id (1,000 random ids), add up revenue per country for 2025 (every row), and insert 1,000 new orders one at a time.
Where it ran: Apple M4, macOS 15.6, PostgreSQL 18.6 (Homebrew, default settings) and DuckDB 1.5.6 (pip), Python 3.12. Two full runs on 4 October 2026; the table shows run 1 and the range across both.
AGG_SQL = """
SELECT country, count(*) AS orders, round(sum(amount), 2) AS revenue
FROM orders
WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2026-01-01'
GROUP BY country ORDER BY revenue DESC
"""
# point lookup, 1,000 random ids (PostgreSQL shown; DuckDB uses ? instead of %s)
for i in ids:
t0 = time.perf_counter()
con.execute("SELECT * FROM orders WHERE order_id = %s", (i,)).fetchone()
lat.append(ms(t0))
# 1,000 single-row inserts, each its own transaction
for k in range(1000):
con.execute(
"INSERT INTO orders VALUES (%s, 1, 'IN', 'placed', 99.00, TIMESTAMP '2026-10-04 10:00:00')",
(ROWS + 1 + k,),
)"postgres": {
"version": "18.6 (Homebrew)",
"table_mb": 453.8,
"aggregate_ms_median": 343.9,
"aggregate_top": [["GB", 185898, 465149680.32], ["US", 185912, 463962694.81], ...],
"point_ms_median": 0.132,
"insert_1000_seconds": 0.37,
"point_plan": ["Index Scan using orders_pkey on orders (cost=0.43..8.45 rows=1 width=36)",
" Index Cond: (order_id = 42)"]
},
"duckdb": {
"version": "1.5.6",
"file_mb": 121.4,
"aggregate_ms_median": 20.2,
"aggregate_top": [["GB", 185898, 465149680.32], ["US", 185912, 463962694.81], ...],
"point_ms_median": 1.089,
"insert_1000_seconds": 1.652
}| What we measured | OLTP | OLAP |
|---|---|---|
| Find one order by id (median)PostgreSQL about 6 to 8 times faster | 0.13 to 0.15 ms | 0.91 to 1.09 ms |
| Revenue per country, all 5M rows (median)DuckDB about 17 to 38 times faster | 344 to 491 ms | 13 to 20 ms |
| 1,000 single-row insertsPostgreSQL about 4 times faster | 0.37 to 0.54 s | 1.65 to 2.04 s |
| Size of the table on diskthe column store compressed it to about a quarter | 454 MB | 121 MB |
What this shows
Each engine won the job it was built for. PostgreSQL found one row and saved one row faster, because a whole row sits in one place and its index points straight at it. DuckDB added up 5 million rows 17 to 38 times faster, because it read only the date, country and amount columns, packed tightly together. Same data, same SQL, opposite winners.
What this test does not show: One laptop, warm caches, one user at a time. A real OLTP system serves thousands of users at once, which is where PostgreSQL's design pays off even more. DuckDB is an in-process engine; Snowflake and BigQuery are cloud services with the same column idea but different costs. The script is scripts/labs/compare/oltp_vs_olap.py in our repository.
Running big reports on the live app database.
One heavy scan can push customer queries out of memory and slow everyone. Send reports to a read replica or an OLAP copy.
"OLAP means a cube."
Older OLAP tools pre-built cubes of totals. Most modern OLAP is simply a column store that scans fast, so you can ask new questions without building anything first.
Using a warehouse as the app's database.
Column stores are slow at single-row writes. In our test DuckDB took about 4 times longer for 1,000 small inserts. Keep live writes in an OLTP database.
"SQL means OLTP."
SQL is just the language. PostgreSQL, DuckDB, Snowflake and BigQuery all speak it. What differs is how the data is stored underneath.
Copying everything every night when only a little changed.
Use change data capture (CDC), which streams only new and changed rows from the OLTP database to the OLAP one.
OLTP records the business as it happens, with many small, fast reads and writes of single rows. OLAP analyses what happened, with a few big queries that read millions of rows. That is why OLTP stores rows together and OLAP stores columns together.
Neither. SQL is a query language used by both. PostgreSQL (OLTP) and DuckDB (OLAP) ran the exact same SQL in our test and returned the same totals. They just stored the data differently.
Snowflake is mainly an OLAP data warehouse, with column storage built for analysis. It also offers hybrid tables, a row-based storage engine Snowflake describes for transactional work such as high-concurrency updates.
MySQL is an OLTP database. Its default storage engine, InnoDB, is built around transactions that follow the ACID rules, with row-level locking so many users can write at once. Teams often copy MySQL data into a warehouse for analysis.
No. The old idea of pre-built cubes has faded, but OLAP itself has grown: column stores like BigQuery, Snowflake, ClickHouse and DuckDB are OLAP engines, and they are widely used.
For small data, yes: PostgreSQL handled a 5-million-row total in a third to half a second in our test. Products called HTAP (hybrid transactional and analytical processing) try to do both at scale. Most large companies still keep two systems.
From the System Design course, in the order we would read them.
Primary sources only: standards, vendor docs and our own runs. Each one shows its date and the day we opened it.
OLTP vs OLAP is one row in a much bigger table. Our System Design course has 769 lessons on networks, databases, caching, scaling, messaging, security and reliability, each drawn step by step, so you can explain the trade-off in an interview and pick right at work. 18 lessons are free to read, with no card needed.
the hands-on parts are real runs, like this one
course 1
From absolute beginner to principal engineer, drawn step by step.