What is the difference between OLTP and OLAP workloads?
Quick Answer
OLTP — many small, fast, concurrent reads/writes; highly normalized; rowstore; tuned for integrity and concurrency (your app database). OLAP — few large aggregating scans; denormalized star schema; often columnstore; tuned for scan throughput (a warehouse or reporting store).
OLTP is the transactional side — an order system, lots of tiny operations, normalized tables, optimized for many users writing at once without stepping on each other. OLAP is analytical — “total revenue by region by month for three years” — where one query scans millions of rows, so the schema is denormalized into facts and dimensions and you use columnstore. The mistake teams make is running the analytics against the live OLTP database, where those scans block the app; you move that work to a read replica or a proper warehouse fed by an ETL job.
- Running big analytical queries on the OLTP database causes blocking and cache churn — offload to a replica, a columnstore index, or a separate warehouse.
- OLAP modelling: fact tables (measures) + dimension tables (attributes), pre-aggregated where possible.
- ETL/ELT (or CDC) moves and reshapes data from OLTP into OLAP on a schedule.