Two Brains for Two Jobs
Think of a busy restaurant. The waitstaff take orders, update the kitchen, and process payments — fast, small, individual actions happening constantly. That's OLTP. Meanwhile, the restaurant owner reviews monthly sales reports, compares revenue by dish category, and spots trends across thousands of orders. That's OLAP. Both are essential, but they require completely different tools.
In data engineering, we distinguish between two fundamental types of database workloads:
OLTP (Online Transaction Processing) is your application database — Postgres, MySQL, or similar. It is optimized for thousands of tiny, fast, concurrent transactions. Every time a user logs in, places an order, or updates their profile, that's an OLTP operation.
| Property | OLTP |
|---|---|
| Typical query | UPDATE users SET email = 'x' WHERE id = 123 |
| Goal | Speed, concurrency, ACID safety |
| Storage layout | Row-oriented |
| Dataset size | Gigabytes to low terabytes |
| Users | Application code, end users |
OLAP (Online Analytical Processing) is your Data Warehouse — Snowflake, Amazon Redshift, Google BigQuery, or similar. It is optimized for massive, heavy queries that scan billions of rows to produce aggregated insights.
| Property | OLAP |
|---|---|
| Typical query | SELECT country, AVG(order_value) FROM orders GROUP BY country |
| Goal | Aggregation speed, massive scale |
| Storage layout | Column-oriented |
| Dataset size | Terabytes to petabytes |
| Users | Analysts, BI tools, data scientists |
The Golden Rule: Never run heavy analytics queries on your production OLTP database. A full-table scan across hundreds of millions of rows will hold locks, starve application queries of I/O, spike CPU, and potentially crash the service for real users. The correct pattern is to replicate or export data into a dedicated OLAP warehouse and run analytics there.
This separation is not just a performance optimization — it is an architectural boundary. Your OLTP database is a system of record for live state. Your OLAP warehouse is a system of insight for historical analysis. Conflating the two creates operational risk in both directions: analytics queries degrade application performance, and application write patterns (frequent small updates, deletions) are hostile to the bulk-read patterns that warehouses are built for.