OLAP vs. OLTP
16 min
Database workloads split into two very different shapes. OLTP (Online Transaction Processing) is what a live application does constantly: fast, small, point lookups and writes — "get user 4821's profile," "insert this order." OLAP (Online Analytical Processing) is what a data warehouse does: large aggregate scans across huge swaths of history — "total revenue per region per month for the last two years." The access patterns are different enough that optimizing a database engine for one tends to actively hurt the other.
def classify_query(has_aggregation, is_point_lookup):
if is_point_lookup and not has_aggregation:
return "OLTP"
if has_aggregation:
return "OLAP"
return "OLTP"
This is exactly why running heavy analytics queries directly against a production app's OLTP database is a classic operational mistake -- a big GROUP BY aggregate scan competing for the same resources as live user-facing point lookups can slow down or even lock out the app's real traffic.
Write `classify_query(has_aggregation, is_point_lookup)`: return `'OLTP'` if it's a point lookup with no aggregation; `'OLAP'` if it involves aggregation (regardless of lookup type); otherwise `'OLTP'`.
Why does a single database engine rarely serve BOTH a production app's transactional traffic AND its analytics queries well?
You can classify a query by its access pattern, and understand why OLTP and OLAP workloads are usually served by separate, differently-optimized systems.