DATA & INDEXING
OLTP vs OLAP: two kinds of database work
Transactions and analysis want opposite things from a database. The split explains most slow reporting screens, and where an index server fits.
- Insert an order, update a status, read one customer by id
- Small, exact, constant: thousands a minute
- Locks held for a moment, then committed
- Scans and group-bys compete with all of it
- Every order this month, joined to customers, grouped by region
- Few queries, each touching millions of rows
- Reads only; a stated freshness is fine
- Row-by-row updates would be wasted on it
Two kinds of work
Every business system does two things with its data that have almost nothing in common. It records what happens - an order placed, a status changed, a payment received - one event at a time, exactly, thousands of times a day. And it asks what happened - how many orders, by region, against last month - a few times a day, across everything. The first is online transaction processing, OLTP. The second is online analytical processing, OLAP. The names are old; the split is as real as it ever was.
What OLTP needs
Correctness and speed on tiny units of work. Insert the order and its lines together or not at all. Read one customer by id in a millisecond. Hold a lock for the shortest possible moment and let the next transaction through. Row storage, B-tree indexes on keys, normalized tables so a fact is written once: the relational database is this job, refined over fifty years, and nothing beats it at it.
Notice what OLTP does not need: full-text search, counts per value, totals over a filtered set. A transaction never asks how many orders each region has. It asks for this order, now, exactly. The structures that make that fast - row storage, key indexes - are the structures that make the other kind of question slow, and no amount of tuning changes that, because the two are optimized in opposite directions.
What OLAP needs
Throughput on huge reads. Scan every order this month, join it to its customer and product, group by region, sum the value. The right layout stores a column's values together so a sum is one pass, denormalizes so the join was done at load time, and keeps counts per value ready so "how many per region" is a lookup. The data can be a little behind the source, as long as it says how far. Writes from readers are not just unnecessary; they are forbidden.
Why one database struggles to do both
Because the two want opposite things from the same machine. A long OLAP scan holds shared locks the writes queue behind, and evicts the hot transactional pages from memory, so the OLTP side slows down. Indexes added to speed the scans make every write slower, so the OLTP side slows down again. The database's own optimizer, tuned for key lookups, estimates badly on six-field predicates and falls back to scanning. None of this is a defect. It is what happens when one engine is asked to serve two workloads with opposite access patterns, and it is why the month-end report and the order-entry screen slow each other down. Why not query the production database directly? goes through the symptoms one by one.
Side by side
| Criterion | OLTP | OLAP |
|---|---|---|
| Unit of work | One transaction: a few rows, atomic | One question: millions of rows, read once |
| Typical query | SELECT … WHERE id = ?, INSERT, UPDATE | SELECT region, COUNT(*) … GROUP BY, joins across tables |
| Data layout | Row store, normalized, indexed by key | Column-oriented or inverted, denormalized per question |
| Latency expected | Milliseconds, thousands a minute | Milliseconds for a screen, seconds to minutes for an analyst |
| Consistency | Exact at commit | As of the last refreshStated on the screen |
| Writes | The whole point | None from readersLoaded one way from the source |
| Who runs it | The application, on every click | Screens, dashboards, assistants, analysts |
| What slows it | Long scans holding locks and evicting cache | Row-at-a-time access; wrong layout for the question |
Where an index server fits
The classical answer to the split is a data warehouse: a nightly copy, organized for analysts, queried in SQL over seconds. It is the right tool for analysts. It is the wrong tool behind an application screen, which needs an answer in milliseconds, on data that is hours rather than days old, for thousands of users who will never write SQL.
An index server is OLAP-shaped reading for application screens. It holds a copy organized per collection - orders, products, customers, invoices - refreshed hourly or daily, and answers search, filters, counts and totals in one request through an API. The database keeps OLTP. The warehouse, if there is one, keeps the analysts. The screens that used to be the slow middle move to the index. Data warehouse vs index server compares the two copies directly.
The practical test is the screen. If the question is designed in advance, repeated by many users, and answered from current records, it belongs on the index: 531 matching products with Polo 302 · Levis 144 · Nike 85 beside them, in one request. If the question is new, broad and historical, it belongs in the warehouse. If it is a write or a read of one exact row, it belongs on the database, where it always did.
When one database is enough
Small data, simple questions, few readers. A system with a few hundred thousand rows and a handful of reports can run both workloads on one well-indexed database for years, and should: adding a second copy is a second system to operate. The split becomes worth paying for when the reports start slowing the transactions, when a listing screen with filters and totals times out, or when the reporting indexes outnumber the transactional ones. Those are the tells, and they arrive on their own schedule.