What is the difference between OLTP and OLAP databases?
Quick Answer
OLTP (Online Transaction Processing) — many small, fast reads/writes, highly normalized, row-oriented, tuned for concurrency and integrity (your app's database). OLAP (Online Analytical Processing) — few huge aggregating queries, denormalized (star schema), often column-oriented, tuned for scan throughput (a data warehouse / reporting store).
OLTP is your application's transactional database — lots of small quick operations, normalized to avoid anomalies, optimized for concurrent writes and data integrity. OLAP is analytical — a reporting database or data warehouse where a query scans and aggregates millions of rows, so it's denormalized into a star schema and often uses columnstore. The mistake is running the heavy reporting queries against the OLTP database, where they block the app; you offload those to a read replica or a warehouse that's fed by an ETL pipeline.
- Running big analytical queries on the OLTP database causes blocking and hurts the app — offload to a read replica, a warehouse, or a columnstore index.
- OLAP design: fact tables (measures) + dimension tables (attributes), pre-aggregated where possible.
- ETL/ELT pipelines move and reshape data from OLTP into OLAP on a schedule or via CDC.