OLAP vs OLTP
OLTP systems run many small transactions on current records; OLAP systems run few large scans that aggregate history. Their designs pull in opposite directions.
Two workloads, two designs
Databases serve two very different jobs. OLTP (online transaction processing) handles the day-to-day operations of an application: create an order, update a status, read one record. OLAP (online analytical processing) answers questions over large histories: total throughput by month across all sites. Because the access patterns differ so sharply, the two are usually built on different systems.
Contrasting characteristics
| axis | OLTP | OLAP |
|---|---|---|
| query size | small | large |
| writes | frequent | batch |
| layout | row | columnar |
| target | latency | throughput |
OLTP touches few rows but many columns of those rows, so row storage and record-level indexes win. OLAP touches many rows but few columns, so columnar storage and scan efficiency win. OLTP optimizes for the latency of a single transaction; OLAP optimizes for the throughput of a big aggregation.
Normalization versus denormalization
OLTP schemas are normalized: data is split across many tables to avoid duplication and keep updates consistent. OLAP schemas are often denormalized into wide tables or star schemas, trading some redundancy for fewer joins at query time. The reason is that OLTP writes constantly, so avoiding duplicate updates matters, while OLAP mostly reads, so avoiding joins matters more.
Why not one system
Running heavy analytical scans on the transactional database competes for the same resources that serve users, and the row layout is wrong for scans anyway. The standard resolution is to keep OLTP as the source of truth and periodically move its data into an OLAP store shaped for analysis. Change data capture is the modern way to keep that copy fresh continuously rather than in nightly batches.
The blurring middle
So-called HTAP (hybrid) and translytical systems try to serve both from one engine, often by keeping a row store for writes and a column store for reads over the same data. They ease operational complexity but rarely match a specialized system on either axis. See columnar storage, dimensional modeling, and change data capture.