OLTP vs OLAP: Transactional vs Analytical Databases

Applications store users, process orders and manage content. Organizations also need to analyze that data: revenue by region, products bought together, signups per month. These two jobs have opposite access patterns, so they use different databases. OLTP (online transaction processing) databases run the application; OLAP (online analytical processing) databases answer business questions.

Two different workloads

• OLTP serves the application in real time: insert an order, update inventory, delete a comment. Each operation touches a few rows, and thousands run per second.
• OLAP serves business intelligence: "total revenue by region?", "which products sell together?", "signups per month?". Each query scans millions of rows; far fewer queries run, but each is expensive.

Production systems use both. The OLTP database records transactions, and an ETL pipeline extracts that data, transforms it and loads it into the OLAP system for analysis.

Transactional (OLTP) databases

OLTP databases are built for high throughput of small operations. Each query touches a few rows by primary key or index: an e-commerce site looks up a user by ID, updates the cart and commits, thousands of times a second.

• Indexes are crucial. OLTP queries need millisecond latency, and B-tree indexes make key lookups fast; without them, queries scan whole tables and take seconds. See database indexing.
• Storage models vary. MySQL and PostgreSQL use B-trees and update data in place; Cassandra and HBase use LSM trees, appending writes and compacting later. Each balances read and write performance differently.
• Normalized data: each fact stored once so updates are cheap and consistent. See normalization.

-- Typical OLTP: touch one customer's rows by key, in milliseconds
BEGIN;
SELECT * FROM carts WHERE user_id = 42;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'tee-m-blue' AND stock > 0;
INSERT INTO orders (user_id, sku, amount) VALUES (42, 'tee-m-blue', 19.99);
COMMIT;

Analytical (OLAP) databases

OLAP databases are built for throughput of large scans. "Total sales by region" scans the whole orders table, groups by region and sums amounts.

-- Typical OLAP: scan millions of rows, read few columns, aggregate
SELECT region, date_trunc('month', order_date) AS month, SUM(amount) AS revenue
FROM fact_orders
GROUP BY region, month
ORDER BY month, revenue DESC;

• Indexes matter less. An index on region finds one region's rows, but this query needs every region, so the database reads most of the table anyway. When a query touches a large fraction of a table, a sequential scan beats index lookups (for selective filters matching few rows, an index still wins).
• Column-oriented storage is essential. Analytical queries read a few columns of wide tables: summing revenue doesn't need product names or descriptions. Column stores keep each column separately, so only the revenue column is read, which cuts disk I/O dramatically.
• Compression saves space and I/O. A column holds values of one type with similar values side by side, so it compresses far better than rows. Amazon Redshift and Google BigQuery are column stores for this reason.
• Parallel processing. MapReduce, Hadoop and Spark split a query across many servers, each processing a partition, then merge results; that's how petabytes get queried. See MapReduce.
• Denormalized models such as star and snowflake schemas: a central fact table (orders) surrounded by dimension tables (customers, products, dates), so queries need few joins.

OLTP vs OLAP compared

Don't run heavy analytics on the production OLTP database: one big report can saturate its disks and slow every user request. Point analytics at a replica at minimum, and at a warehouse once data grows.