OLTP vs OLAP

Quick reference

OLTP (Many short reads and writes): The checkout database: many short transactions, one row at a time.

$pgbench -c 32 -T 60

OLAP (Few large scans, many columns): The warehouse: few long scans, many columns, one answer.

SELECT count(*) FROM events;

• OLTP → write fast, read one row, stay correct.
• OLAP → read everything, once, and forgive latency.

Explained

The workload OLTP is the database behind checkout: many short transactions, each reading or writing one row by primary key. Latency is the product, so it is measured in milliseconds and the schema is normalised to keep writes cheap. OLAP is the warehouse behind the dashboard: few queries, each scanning millions of rows and grouping them. Throughput matters more than per-query latency.

The storage A row store keeps a whole row together, so a point lookup reads one page. A column store keeps each column apart and compresses it, so a scan of three columns never touches the other thirty. That is why an OLTP index is narrow and many, and an OLAP table is wide, partitioned and columnar.

Where the line blurs Some managed warehouses now offer row-store tables for small dimensions, and PostgreSQL extensions add columnar storage to an OLTP database. The labels describe a workload, not a product.

OLTP writes the row. OLAP reads the world.