Data & storage

OLTP vs OLAP

OLTP keeps the business running one transaction at a time; OLAP answers questions across millions of them — cramming both into one database means neither works well.

✗ The problem

One database, two conflicting jobs

A checkout writes one row. A finance report scans five years of rows for a regional total. Run both on the same engine and the analytical scan locks and slows the transactional path.

Checkout

INSERT order

→

Orders DB

one engine, one lock table

←

Finance report

SUM 5 years

-- 09:14:03  checkout (blocked, waiting on lock)
INSERT INTO orders (id, amount) VALUES (42, 340);

-- 09:14:03  finance report (running, holding locks)
SELECT region, SUM(amount)
FROM orders
WHERE ts > now() - interval '5 years'
GROUP BY region;

Analytical scans lock/slow the transactional workload — the customer at checkout feels a report someone else is running.

✓ How it works

Two shapes for two jobs

OLTP handles the write path: many tiny transactions, indexed by key. OLAP handles the analytics path: few huge aggregations scanning whole columns. Keep them separate; sync via ETL.

-- OLTP: row-store, normalized
CREATE TABLE orders (
  id      int PRIMARY KEY,
  cust_id int,
  amount  numeric
);
-- indexed by id → 1 fast lookup
SELECT * FROM orders WHERE id = 42;

OLTP

row-store · normalized · operational

↓ ETL

OLAP

column-store · denormalized · analytical

row-store keeps a record together

column-store keeps a field together

✓ See it live

Pick a workload — watch which layout wins

Same 4 orders, stored two ways. Point lookups favor row-store; full-column aggregates favor column-store.

row-store (OLTP)

—

column-store (OLAP)

—

✓ Takeaway

Separate stores for separate jobs

Separate the operational store (OLTP) from the analytical store (OLAP) — don't make one engine serve both.
Row vs column: row-store is fast for point reads/writes; column-store is fast for scans over one field across millions of rows.
Never run heavy analytics directly on your primary OLTP database — sync data out via ETL instead.
Related: CQRS separates read/write models the same way; a data warehouse is the usual OLAP destination, often modeled with a star schema.

Back to all topics →