What is the difference between OLTP and OLAP databases?

Q: What is the difference between OLTP and OLAP databases?

**Target Audience:** Beginners who hear "OLTP" and "OLAP" in architecture discussions, and candidates who need to explain why companies run a separate data warehouse. **Difficulty:** Medium **Engines:** The SQL examples run on SQLite 3.45.0 (sql.js 1.10.2, the engine this page uses) and returned the same rows on PostgreSQL 16.15; the plan comparison ran on PostgreSQL 16.15. Facts about warehouse products are cited from their documentation, not run here. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and plan shown was produced by running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A supermarket has two kinds of work. At the checkout, hundreds of cashiers each scan a few items, take a payment and print a receipt, all day, every few seconds. Each sale must be fast, must never be lost,

The problem

Question presented to candidate: "People talk about OLTP databases and OLAP databases as if they were different things. What is the difference, why can't one database just do both well, and how would a typical company set them up?"

What a strong answer should cover:

OLTP (online transaction processing): many small, short read/write transactions touching a few rows by key — placing an order, updating stock; needs low latency, high concurrency, ACID, a normalised schema and B-tree indexes
OLAP (online analytical processing): fewer, large read-mostly queries scanning and aggregating millions of rows — revenue by month and region; needs throughput, a denormalised star schema, and usually columnar storage with compression
Why the storage layout differs: row stores read whole rows (good for "fetch order 7"), column stores read only the columns a query uses (good for "sum one column over a billion rows")
How they connect: data is copied from OLTP systems into a warehouse by ETL/ELT or change data capture, so analytics do not slow down user-facing transactions
Examples of each kind (PostgreSQL, MySQL, SQL Server for OLTP; Snowflake, BigQuery, Redshift, ClickHouse for OLAP) and the HTAP middle ground

Code / implementation expected: Optional — an OLTP transaction and point lookup next to an OLAP aggregation, or a small fact/dimension example.

Schema used by the examples:

design
analytics

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who hear "OLTP" and "OLAP" in architecture discussions, and candidates who need to explain why companies run a separate data warehouse. Difficulty: Medium Engines: The SQL examples run on SQLite 3.45.0 (sql.js 1.10.2, the engine this page uses) and returned the same rows on PostgreSQL 16.15; the plan comparison ran on PostgreSQL 16.15. Facts about warehouse products are cited from their documentation, not run here.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and plan shown was produced by running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A supermarket has two kinds of work.

At the checkout, hundreds of cashiers each scan a few items, take a

The solution is hidden so you can try first

13 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLOLTP: place one order in a short transaction, then fetch it by key

SQLOLAP: revenue per month and category over all orders

SQLETL into a tiny star schema (fact_sales + dim_product), then an analyst query

PostgreSQL: on 200,000 rows the OLTP lookup reads 1 row by index, the OLAP report scans all 200,000

SET max_parallel_workers_per_gather = 0;
CREATE TABLE sales (
  id int PRIMARY KEY,
  region text NOT NULL,
  product_id int NOT NULL,
  sold_on date NOT NULL,
  amount numeric NOT NULL
) WITH (autovacuum_enabled = off);
INSERT INTO sales
SELECT g, (ARRAY['APAC', 'EU', 'NA'])[g % 3 + 1], g % 50 + 1,
       DATE '2024-01-01' + g % 366, g % 200 + 1
FROM generate_series(1, 200000) AS g;
ANALYZE sales;
CREATE FUNCTION plan_analyze(q text) RETURNS SETOF text LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) ' || q LOOP
    RETURN NEXT r."QUERY PLAN";
  END LOOP;
END $$;
CREATE TABLE steps (n serial, workload text, plan_line text);

INSERT INTO steps (workload, plan_line)
SELECT 'OLTP lookup', plan_analyze('SELECT * FROM sales WHERE id = 4242');

INSERT INTO steps (workload, plan_line)
SELECT 'OLAP report', plan_analyze('SELECT region, SUM(amount) FROM sales GROUP BY region');

SELECT workload, plan_line FROM steps ORDER BY n;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.