Learning Objectives
- Classify a described database workload as OLTP or OLAP based on its access pattern
- Explain why a single storage design rarely serves both OLTP and OLAP well
- Predict a concrete consequence of forcing one database to serve both workloads at scale
Why This Matters
If you've ever watched checkout traffic slow to a crawl right when someone opens the sales dashboard, you've felt this divide firsthand. Engineers who don't recognize it reach for the wrong fix — indexing everything in sight to speed up one slow report, then watching write throughput collapse under the extra index-maintenance work. Recognizing that these are two fundamentally different jobs, before you write a line of code or provision a server, is what tells you you're about to make that exact mistake.
Analogy
Picture a corner store. The cash register handles one customer at a time: scan an item, look up its price, update the count on the shelf, take payment, done. It has to be fast — someone is standing there — and correct, even when three registers are ringing up sales simultaneously.
Now picture the store's accountant at year end. She doesn't care about any single transaction. She wants to scan every receipt from the entire year and total them up by category: how much in produce, how much in dairy, which month was busiest. She doesn't care which receipt happens to be on top of the pile — she cares about the total across all of them, and she can take a few minutes to get there.
The register and the accountant are doing fundamentally different jobs with the same underlying data. That's the OLTP/OLAP divide.
Two different jobs
Every database exists to answer questions about data, but the shape of the question varies enormously depending on who's asking and why.
OLTP (Online Transaction Processing) is the register's job: lots of small, independent operations, each touching a handful of rows. "Get user 4821's cart." "Insert this order." "Decrement this item's inventory count by one." Each of these needs to happen right now — a customer is waiting — and needs to stay correct even when thousands of other customers are doing the same thing at the same moment.
OLAP (Online Analytical Processing) is the accountant's job: queries that span millions of rows to produce a small number of summarized answers. "What were total sales by region last quarter?" "Which product category grew fastest?" Each of these touches a lot of data but doesn't care about any single row's identity. It's fine if the answer takes a few seconds — nobody's blocked waiting on it mid-checkout — but it has to stay correct and responsive even when someone asks it to scan a billion rows instead of a million.
"A few seconds is fine" doesn't mean OLAP is the slow tier of database. It means the latency budget for this kind of question is different from a checkout click — a database built specifically for this job can scan billions of rows in that time. OLAP isn't slow; it's optimized for a different shape of question than OLTP is.
Here's the part that isn't just industry trivia: these two jobs pull database design in opposite directions. A structure that makes OLTP fast — an index that lets you jump straight to one specific row — does nothing to help OLAP, which needs to sweep across huge swaths of data at once. And a structure optimized for those huge aggregate scans usually makes single-row updates more expensive to maintain. This is a genuine engineering trade-off, not an arbitrary naming convention.
For decades, most teams didn't think about this distinction at all — they just used one general-purpose relational database (tuned, by default, for OLTP-style access) for everything, analytics included. That works fine at small scale. It buckles as data grows: the analytical queries start competing with the transactional workload for the same memory, disk I/O, and locks, and neither job gets served well. That collision is exactly what motivates a whole category of databases — data warehouses, and OLAP engines like ClickHouse — built specifically for the analytical side of this divide. You'll see how they pull it off starting in the next lesson.
A worked example
Consider an online store handling both jobs on the same underlying order data.
"Has this customer already used this promo code?" is OLTP — one targeted lookup, has to return in milliseconds, and it's blocking the checkout button while it runs.
"What was our conversion rate by traffic source last month?" is OLAP — it scans a month of order history, joins it against session data, and produces one table of numbers for a dashboard that nobody is standing in front of, tapping their foot, waiting on a spinner.
Same underlying data. Completely different access pattern, completely different latency expectation, completely different amount of data touched per query.
Common Misconception
"If my OLTP database is too slow for analytics, I just need more indexes — or more hardware."
This feels reasonable, but it gets the trade-off backwards. Indexes that make single-row lookups fast do so by adding structure that has to be updated on every write — so more indexes means slower checkouts, not just faster reports. More hardware buys you time, not a fix: it doesn't change what kind of structure your data is stored in, so the same tension resurfaces the moment you outgrow whatever headroom you just bought. This isn't a resourcing problem you can spend your way out of forever — it's a genuine design tension between two different access patterns, and the next lesson gets into exactly why.
Predict, Then Verify
For each of these, predict: is it OLTP or OLAP?
- Loading a user's shopping cart when they open the app.
- Computing average order value by month, for the last two years.
- Recording that a payment just succeeded.
- Finding the top 10 best-selling products across all regions this quarter.
Reveal the answer →
- OLTP — one user, one row, has to be instant.
- OLAP — scans two years of orders, produces one summarized table.
- OLTP — one row, one write, has to be durable and fast.
- OLAP — scans everything, ranks by an aggregate across all of it.
Summary
A database workload is OLTP when it's made of many small, independent operations that each touch a handful of rows and need to complete instantly. It's OLAP when it's made of queries that scan large volumes of data to produce a small number of summarized answers. These two access patterns pull storage design in opposite directions, which is why a single general-purpose database rarely serves both well at scale. You can now look at a described workload and classify it as one or the other — and predict what breaks when a team tries to force one database to do both jobs at once.
Quiz
1. Your team's database handles checkout smoothly, but a new sales-dashboard query — which scans two years of orders to compute monthly totals — makes checkout latency spike whenever someone loads the dashboard. What's the most likely underlying cause?
2. Which is the best one-sentence description of an OLAP workload?
3. Suppose you added a dozen new indexes to your OLTP database — one for every column your analysts might want to filter on — hoping to make the monthly sales report faster. What's the most likely side effect on checkout?
4. A startup's analytics team wants to answer 'which of our 50 columns of user data best predicts churn?' — scanning millions of user rows but touching only a handful of columns per query. Which workload is this, and why does it matter for how the data should be stored?
5. Why can't one storage design be simultaneously ideal for fast single-row lookups AND fast full-table aggregate scans?