OLTP vs OLAP

The cash register and the accountant

A restaurant has two data jobs that look unrelated. The cash register processes individual orders all day — quick, small, frequent. That is OLTP (Online Transaction Processing). The accountant sits in the back office and analyses last month's receipts to find trends — slow, big, infrequent. That is OLAP (Online Analytical Processing).

Different shapes, different optimisations

OLTP	OLAP
Many concurrent users	Few analysts
Short queries (ms)	Long queries (seconds–minutes)
Read + write mix	Mostly read
Current data	Historical data
Row-oriented storage	Column-oriented storage
Example: place an order	Example: revenue by region by quarter

Where Postgres lives

Postgres is OLTP-first and excellent at it. It also handles moderate OLAP — millions to low-billions of rows — with window functions, materialised views, parallel queries, and JIT. For genuine warehouse-scale analytics (multi-billion-row scans), you replicate Postgres data to a columnar engine (ClickHouse, Snowflake, BigQuery) or use the Citus extension for column store + sharding inside Postgres itself.

Code

OLTP: tiny, indexed lookup·sql

SELECT id, status, total
FROM   orders
WHERE  id = 98712;
-- Hits a primary-key index, returns in under 1ms.

OLAP: aggregate over the year·sql

SELECT region,
       EXTRACT(QUARTER FROM order_date) AS quarter,
       SUM(total)                       AS revenue,
       COUNT(*)                         AS orders
FROM   orders
WHERE  order_date >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '1 year')
GROUP  BY 1, 2
ORDER  BY 1, 2;
-- Scans many rows; benefits from parallel workers and a covering index.

Materialised view: precomputed analytics·sql

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month,
       region,
       SUM(total) AS revenue
FROM   orders
GROUP  BY 1, 2;

-- Refresh on schedule; queries against the view are instant.
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

The cash register and the accountant

Different shapes, different optimisations

Where Postgres lives

Code

External links

Exercise

Progress

Comments 0