Skip to content

OLTP vs OLAP

OLTP (online transaction processing) systems run the business one record at a time; OLAP (online analytical processing) systems answer questions across many records at once.

Why it matters

The database behind an application is built to handle many small reads and writes quickly and safely. Analytical questions, such as revenue per month for three years, need the opposite: few queries that each read millions of rows. Running them on the operational database makes both jobs worse. This difference is the reason data platforms exist as separate systems.

How it works

An OLTP database serves the application. When a customer places an order, it inserts one order row and a few order item rows, checks stock and commits, all in milliseconds. Many of these transactions run concurrently. The database is tuned for this: rows are stored together, indexes find a single record quickly, and locks keep concurrent writes consistent. The data model is normalised, so each fact is stored once and updates touch one place.

An OLAP system serves analysis. A typical query scans a large share of the data but reads only a few columns: the order date and amount of every order, grouped by month. Writes arrive in bulk, from loads or streams, rather than one row at a time. The system is tuned for scanning: data is stored by column, compressed heavily, and often processed in parallel, across cores or machines. The data model is often denormalised, with fewer joins, because reading speed matters more than avoiding duplicates.

Row storage keeps all columns of a record next to each other, which is ideal for reading or writing whole records. Column storage keeps all values of one column together, which is ideal for reading a few columns across many records. For a query that needs 2 columns of a 20-column table, column storage reads about a tenth of the data, and compression, which works better on similar values stored together, reduces it further.

flowchart LR
  subgraph OLTP[OLTP: the shop database]
    A[Place order<br/>insert 1 row] --> DB1[(orders<br/>stored by row)]
    B[Show order<br/>read 1 row] --> DB1
  end
  subgraph OLAP[OLAP: the analytical store]
    DB2[(orders<br/>stored by column)] --> Q[Monthly revenue<br/>scan millions of rows,<br/>2 columns]
  end
  DB1 -->|copy: scheduled<br/>or continuous| DB2

The two are connected by copying. Data moves from the operational database into the analytical store on a schedule or continuously. This copy is what the rest of a data platform manages: how it is moved, cleaned, modelled and served.

Key terms

  • Transaction: a group of reads and writes that succeeds or fails as a whole.
  • Normalised model: each fact stored once, across many related tables.
  • Denormalised model: related data combined into wider tables to avoid joins.
  • Row-oriented storage: all columns of a row stored together.
  • Column-oriented storage: all values of a column stored together.
  • Scan: reading a large part of a table rather than looking up single rows.

Example

The same orders table, used two ways.

-- OLTP: the checkout writes and reads single orders
INSERT INTO shop.orders (order_id, customer_id, amount, ordered_at)
VALUES ('o-1001', 'c-42', 59.90, now());

SELECT * FROM shop.orders WHERE order_id = 'o-1001';

-- OLAP: the monthly report reads every order, two columns
SELECT date_trunc('month', ordered_at) AS month, sum(amount) AS revenue
FROM shop.orders
GROUP BY 1
ORDER BY 1;

The first two statements touch one row each and must finish in milliseconds. The last reads every order the shop has ever taken and may take seconds; on the operational database it would compete with customers trying to check out.

Pitfalls

  • Reporting directly on the production database. It works while data is small, then slows down the application at the worst moment.
  • Read replicas are not an analytical store. They protect the primary database, but they are still row stores with an operational model.
  • Copying the operational model unchanged. Analytical queries on a highly normalised copy need many joins; modelling for analysis is a separate step.
  • Expecting the analytical store to be current to the second. Copies lag behind the source, from seconds with change data capture to hours with nightly loads.

Learn more

  • Martin Kleppmann, Designing Data-Intensive Applications (1st edition, 2017), chapter 3 (storage and retrieval). A 2nd edition appeared in 2026; chapter numbers differ.
  • Ralph Kimball and Margy Ross, The Data Warehouse Toolkit, chapter 1.

Data lake, warehouse and lakehouse · Columnar files and Parquet · Processing patterns: batch, micro-batch and streaming