OLTP versus OLAP

OLTP (Online Transaction Processing) serves many short read/write requests with strong row-level consistency — orders, balances, inventory. OLAP (Online Analytical Processing) serves fewer heavy scans and aggregations over large historical datasets — dashboards, funnels, finance reports.

| | OLTP | OLAP | |---|------|------| | Queries | Point lookups, small joins | Scans, GROUP BY, windows | | Schema | Normalized, 3NF typical | Star/snowflake, wide denormalized | | Latency | Milliseconds | Seconds to minutes acceptable | | Store | Postgres, MySQL | Warehouse, lakehouse, columnar |

Running analytics directly on production OLTP without guardrails risks lock contention, replica lag, and accidental table scans. Common pattern: CDC or ETL into a warehouse; OLTP stays the system of record.

On interviews: classify a given query workload; explain why you would not run a revenue-by-region report on the primary OLTP unless isolated.

Common pitfalls: one database for everything; BI tools hitting primary; star schema in OLTP "for convenience"; ignoring late-arriving facts in analytics.

The trade-off is correctness and low-latency serving (OLTP) versus scan throughput and historical breadth (OLAP)—usually separate systems linked by pipelines.

Checklist:

• Define OLTP vs OLAP workloads.
• Contrast schema and query shapes.
• Name sync path: ETL, ELT, CDC.
• Protect OLTP from analytic scans.
• State freshness SLA for dashboards.