Case 01 · Two databases
Why your app’s database
is bad at answering
questions.
A shop’s database can take an order in four milliseconds. Ask it what the shop earned last year and it may sit there for ten minutes, or fall over. Same data. Same machine. The reason is not size, or tuning, or a missing index.
It is the shape the data was saved in. You can see it, and once you do, this whole topic is finished.
Two jobs
One database takes orders. Another answers questions.
Most companies end up with one of each, built to face opposite ways.
The one the app writes to
It sits behind the product. Somebody taps a button and this database records it.
- Add one order
- Change one address
- Read one customer
- Thousands of people doing that at once, each on their own few rows
The one people ask questions of
Nobody is tapping a button. Somebody wants a number that covers years.
- Revenue per city since 2019
- Which items sell together
- Millions of rows per question
- A handful of analysts and models
Neither is better. A warehouse would be a terrible place to take a payment, and an app database is a terrible place to add up five years of them. The interesting question is why — and the answer is physical.
The mechanism
Watch which cells a query actually touches.
Eight orders, four fields each — thirty-two values. Below is how those values are physically laid out. Pick a layout, pick a question, and press run. Cells the disk must read light up.
Rows win when you want one whole order. Columns win when you want one field across all of them.
That is the entire difference. Not the brand, not the price, not the cloud. Just which values were written next to each other.
The T in OLTP
What a “transaction” really means
It is not just “something happened”. A transaction is a group of changes that must all succeed together, or none of them may happen at all.
Move ₹500 from one account to another and two things change: one balance goes down, one goes up. If the machine loses power between them, you must not be left with money taken out and never put in. So the database keeps the pair together, and if anything fails it puts everything back the way it was.
That promise is expensive to keep, and it is the reason app databases are built the way they are: whole records in one place, so one change touches one spot. Warehouses barely need this. Nobody’s money moves when you run a report, so a warehouse can drop that guarantee and spend everything on reading fast instead.
The bit nobody mentions
Columns also make the data smaller.
Storing the same field together does something beyond fast reading. Look at a single column and you notice values repeat. A million orders have maybe forty cities between them.
The trick is dull and enormous: write each distinct value once, then store a small number pointing at it. Because identical values sit next to each other in a column store, this works brilliantly. In a row store the cities are scattered between order ids and amounts, so there is nothing to squash.
Real warehouses often hold five to ten times more data than it looks like they should, for this reason alone. It is also why scanning is cheap: less on disk means less to pick up.
Finding the rows
It answers a question by ticking boxes.
Ask the app database to find one order and it walks a lookup structure straight to that record. A warehouse usually does something that looks dumber and turns out faster. It reads a whole column and writes a 1 next to every row that matches and a 0 next to every row that does not. That line of ones and zeros is the entire answer to that one condition.
amount column · what actually gets opened
Two conditions give you two lines of ticks. Put one line on top of the other and keep only the positions where both say 1. Everything else drops out. Then, and only then, does the engine open the amount column — and only at the positions still ticked.
The reason this is quick is worth saying plainly. A computer does not check those ticks one by one. Ones and zeros pack together very tightly, and the processor can line up dozens of positions and settle them all in a single instruction. Comparing the text “Bengaluru” against “Bengaluru” eight million times is slow, fiddly work. Comparing bits is about as close to free as computing gets.
Note what never happened here: nobody looked up a row by its id, and nobody touched the order id column at all.
A second squash
When the same value repeats in a row, it almost disappears.
Dictionary encoding replaces long values with small numbers. There is a second trick that stacks on top of it, and it only works when identical values happen to sit next to each other.
Six entries became three notes. Stretch that to a million orders where most say Delivered and the whole column collapses to a handful of lines.
This is why you will hear people say sort your data before you write it, and why it sounds like fussy advice until now. Sorting does not change what the data means. It changes whether identical values end up neighbours, which decides whether this trick can fire at all. Scattered values squash a little. Grouped values squash enormously.
The cheapest read
The fastest data to read is the data it never opens.
A warehouse does not keep one endless column. It keeps it in blocks, and beside every block it writes a two-line note while saving: smallest value in here, largest value in here. Nobody asks for those notes. They are made automatically.
Before opening anything, the engine reads the notes. A block whose largest amount is ₹486 cannot possibly contain an order of ₹500 or more, so it is never touched. Not scanned quickly — not opened at all.
Now the point. Notice how much the skipping depends on how the rows were arranged when they were written. If every block happens to hold a mixture of tiny and huge amounts, then every note says “anything could be in here” and nothing can be skipped. If similar rows were written together, the notes get sharp and most blocks fall away before any real work starts.
If you have written a Parquet file, you have already made these blocks. Parquet calls them row groups, and it writes the min and max of every column in every row group automatically, whether or not anyone ever uses them.
That is the whole reason loading data well matters. You are not just moving rows across. You are deciding, months in advance, how much of it a query will be allowed to ignore.
One at a time, or a handful
It also stops asking the same question row by row.
An app database is built to handle one record properly: check it, lock it, write it, confirm it. That care costs a little time per record, and for one order that is invisible. Run it eight million times and the small cost is the whole bill.
A warehouse hands the processor a batch of values and a single instruction — add all of these up — instead of walking a loop and asking the same question once per row. Same arithmetic, far less overhead around it.
Put the three tricks together and the picture is complete: store less, skip most of what is stored, and process the rest in batches.
A distinction worth keeping straight
Squashing and searching are not the same job.
Two things were shown a moment ago and it is easy to mash them together. They do different work.
Dictionary and runs
How the value is written
Bengaluru → 0 Mumbai → 1 Delhi → 2
This is about size. It makes the same data take less room, so less has to be picked up. It does not, by itself, tell anyone where a matching row is.
Bitmap
Which rows match
Bengaluru → 1 0 1 0 0 1 0 0
This is about location. It answers “which rows”, which is exactly what an index does in an app database. The two stack neatly: squash the values, then tick the matches.
Keep them apart in your head and a lot of confusing writing suddenly reads clearly. Compression makes the pile smaller. Ticking finds things in the pile.
Coming from Postgres
There, you build the index. Here, you mostly do not.
This is the part that confuses everyone arriving from an app database, so it is worth being exact about.
App database
You declare it
CREATE INDEX ix_cust ON orders(customer_id);
You decide which column deserves one. You maintain it. Every new order now has to update the table and the index, so writes get a little slower. Forget to make one and the query crawls.
Warehouse
It is a side effect of saving
-- load the data -- that is the whole step
While writing the file, the engine builds the dictionary, squashes the runs and records each block’s smallest and largest value. There is nothing to declare because these are not optional extras — they are how the file is written.
So the honest answer to “do I need to create an index?” is usually no, and that is not because warehouses are magic. It is because the work an index does for you in an app database — narrowing down where to look — has been folded into the storage format itself.
But you are not off the hook. You do not choose the encoding, and you rarely choose an index. What you almost always choose is how the rows are arranged when they arrive — what the data is sorted by, how it is split into partitions, how big the files are. That is the warehouse version of tuning, and it is done at load time, by you, not at query time by whoever is waiting for the report.
One last caution, because it is the kind of sentence people repeat without meaning. Engines differ. Some let you declare extra structures; some sort and re-pack data in the background; the names for all of this change every couple of years. Do not memorise the names.
If you remember one picture
The pile shrinks four times before any adding happens.
Say the table holds a hundred million orders and fifty columns, and somebody asks for the total spent in Bengaluru on delivered orders. Watch how little survives to the last step.
Everything that is stored
100%A hundred million rows, fifty columns. Nobody is going to read this.
Only the columns in the question
about 6%City, status, amount. The other forty-seven columns are never opened, because they are stored somewhere else entirely.
this is columnar storageOnly the blocks whose notes could match
a fraction of thatEvery block carries its smallest and largest value. Blocks that cannot possibly contain a match are skipped without being opened.
people call this data skipping, or pruningOnly the rows both conditions kept
smaller againTwo lines of ones and zeros laid on top of each other. Whatever is still ticked is the answer set.
bitmaps, combined with a bitwise andThen the adding, in batches
the resultThe surviving amounts go to the processor in handfuls, with one instruction, instead of one row at a time.
people call this vectorised executionThe percentages are made up for the sake of the picture. The shape is not.
This is why the honest one-line answer to “why is a warehouse fast?” is not the name of any single trick. It is that a warehouse is built, at every level, to avoid work. And it is why the same warehouse is miserable at fetching one customer’s record — none of that machinery helps when the answer is a single row you already know the id of.
Names you will meet
The vocabulary, with the jargon removed.
You now understand all of these. The words are just what people say in interviews and documentation. Do not memorise them — recognise them.
| Columnar storage | Each field stored together instead of each record stored together. Everything else here follows from it. |
| Dictionary encoding | Write each distinct value once, then store a small number pointing at it. |
| Run-length encoding | When identical values sit next to each other, store the value once and how many times it repeats. |
| Bitmap | One tick per row saying whether that row matches a condition. Two conditions, two lines of ticks, laid on top of each other. |
| Min / max statistics Zone maps | The two-line note stored beside every block: smallest value in here, largest value in here. |
| Data skipping Predicate pushdown Partition pruning | Three names for deciding not to open something. Skipping uses the block notes; pruning usually means skipping whole folders of files because of how they were split up. |
| Bloom filter | A small note that can say “this block definitely does not contain that value”, used when min and max are not enough. |
| Vectorised execution | Handing the processor a batch of values and one instruction, rather than looping one row at a time. |
| Parquet Row group | Parquet is the most common file format that stores data this way. The blocks inside it are called row groups, and each one carries the notes described above. If you have ever written a Parquet file, you were creating all of this without being asked to. |
The trap. It is tempting to answer “why is OLAP fast?” with “bitmap indexes”. That will sound learned and will be wrong often enough to hurt. Engines differ, and many do not use bitmaps at all. The answer that holds everywhere is the boring one: it stores less, opens less, and works in batches. Every named trick is a way of doing one of those three.
One idea, four consequences
Everything follows from “what sits next to what”
That last one explains a lot of what you will meet later. Warehouses would rather you append history than update it, which is why you keep a new row each time something changes rather than overwriting the old one. It looks wasteful until you know why.
A second difference
One avoids repeating itself. The other repeats on purpose.
App databases try never to store the same fact twice. The customer’s name lives in one table, their orders in another, joined by an id. If they change their name, you edit one place.
Warehouses often do the opposite and paste the name onto every order row. It repeats, and they do not mind, because repeated values squash down anyway and a question that needs no joining is far quicker to answer.
App database
Kept separate
customers c_12 | Ananya | Bengaluru orders A-101 | c_12 | 486 A-107 | c_12 | 390
Change the name once. But every question needs a join.
Warehouse
Flattened together
orders A-101 | Ananya | Bengaluru | 486 A-107 | Ananya | Kolkata | 390
The name is written twice. Nothing needs joining, and the repeats cost almost nothing.
Neither is sloppy. Each is right for the job it has. Reshaping data from the first form into the second is a large part of what you will be doing.
The catch
The warehouse copy is always a little behind.
The moment you keep a second copy, you have to answer a new question: how old is it allowed to be? There is no free answer.
Copy it overnight and it is simple and cheap, but this morning’s dashboard shows yesterday. Fine for a monthly report. Not fine for someone watching today’s orders.
Watch the app database’s own change log and carry each change across as it happens, and the copy stays minutes or seconds behind. This is called change data capture. It costs more to build and more to run, and it breaks in more interesting ways.
Whichever you pick, be honest about it. A dashboard with no timestamp on it is a dashboard somebody will eventually misread.
The obvious question
So why not make a single database good at both?
Because the two layouts pull against each other. If you want to add one whole order in a single move, its values need to sit together. If you want to add up one field across millions of orders, that field needs to sit together instead. A value can only be in one place.
Everything else follows from that: the file formats differ, the indexes differ, the compression differs, even how the machine caches differ. Some newer systems try to keep both copies. That is a real option, and it costs what keeping two copies costs.
The mistake everyone makes once
Somebody needs a report, the app database already has the data, so they run the big query there. It reads far more than it looks like it will, the machine slows down, and real customers start seeing spinning wheels. The app database is not a reporting tool. It is a machine with people standing on it.
Where you come in
Somebody has to move it across.
Data is created on the left, and questions are asked on the right. In between, someone copies it over, fixes the mess, and keeps it current. That is what you are paid for.
The middle box has a name: extract, transform, load. Take the data out, reshape it so it makes sense, put it where it can be queried. Cleaning usually means dropping repeats, fixing dates that arrived in three formats, and making a column mean one thing.
Doing that once is a script. Doing it every night, catching the failures, and keeping it correct as the app changes is the profession.
Side by side
The short version
| App database | Warehouse | |
|---|---|---|
| Built for | running the product | answering questions |
| Touches | a handful of rows per request | millions per question |
| Saved as | whole records together | whole columns together |
| Good at | writing and single lookups | scanning and totalling |
| Used by | thousands of customers | a small number of analysts and models |
| Waiting time | milliseconds, someone is watching | seconds to minutes, nobody is |
| Names | Postgres, MySQL, Oracle | Snowflake, BigQuery, Redshift |
If you get asked
“We have Postgres. How do we give the business team reporting?”
The weak answer is to give them read access and hope. The good answer explains the shape: the app database saves whole records together, which is wrong for questions that read one field across everything. So copy the data into a warehouse built the other way round, and keep that copy fresh.
If you want to go further, say how fresh: a nightly copy is simple and cheap, and reading the database’s own change log lets you carry updates across within minutes. That log-reading approach is called change data capture, and naming it shows you have thought about freshness rather than just direction.
Two questions
Did it land?
Why is a warehouse fast at totalling one column?
Why not just run the report on the app database?
Your playbook · Case 01
Nine things to keep
- Two jobs, two machinesOne takes orders, one answers questions. Having both is normal, not a sign anybody designed it badly.
- The difference is physicalWhole records together, or whole columns together. A value can only sit in one of those places.
- Match the shape to the questionFetching a whole record wants rows. Adding up one field wants columns.
- Leave the app database aloneReal people are standing on it. Big reports go somewhere else.
- Columns squash, rows do notIdentical values sitting together compress to almost nothing. That is why warehouses are cheap to store and quick to scan.
- Warehouses would rather add than editChanging one value inside a packed block is expensive, so you append history instead of overwriting it.
- It skips more than it readsNotes kept beside each block let a warehouse ignore most of the data before opening any of it. How well that works depends on how the rows were arranged when you wrote them.
- You do not create the index — you create the layoutThe encoding and the block notes come free with saving. What you control is sort order, partitioning and file size, and you control it at load time.
- Moving the data between them is the jobCopy it out, clean it up, load it in, and keep doing that correctly while the app keeps changing — and know how far behind your copy is.
Your review
How was this case?
Loading ratings…
Next · Case 02
The two clocks.
Batch or streaming is not a speed question. It is a question about which clock you trust — and what happens to the events that arrive after you stopped counting.