OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) are the two fundamental database patterns — one optimised for recording individual transactions, the other for querying and analysing large volumes of historical data. This guide explains the difference, why it matters for system design, and how modern data architectures handle both.
The Two Fundamental Patterns
Every database system is designed around one of two primary access patterns: recording individual transactions quickly, or analysing large volumes of historical data. These patterns have different requirements, and optimising for one means accepting trade-offs for the other.
OLTP — Online Transaction Processing — optimises for individual record operations: insert, update, delete, and point lookups. The currency is the transaction.
OLAP — Online Analytical Processing — optimises for large-scale aggregation across many rows and columns. The currency is the query.
Understanding the distinction is foundational to data architecture. Most data architectures involve both: an OLTP system recording operational events, and an OLAP system (or data warehouse) analysing the history of those events.
OLTP: Optimised for Transactions
An OLTP database is the operational system of record — the database your application writes to when a user places an order, creates an account, sends a message, or makes a payment.
**Design characteristics**:
- **Normalised schema**: Data is split across many tables with foreign key relationships to eliminate redundancy. An order record stores customer_id (not the customer's name), product_id (not the product's description). This normalisation minimises storage and prevents update anomalies.
- **Row-oriented storage**: Data is stored row by row on disk. Reading one row is fast because the entire row is in one location. Analytical queries that read one column across millions of rows must read every row to access that column.
- **Indexes**: B-tree indexes on frequently queried columns enable fast point lookups and small range scans. A query for one customer's recent orders runs in milliseconds with the right index.
- **ACID transactions**: Atomicity, Consistency, Isolation, Durability. The database guarantees that a payment either succeeds completely or fails completely — no partial writes that leave data in an inconsistent state.
- **Low latency, small result sets**: Queries return a few rows quickly. Writing one new order takes sub-millisecond. Reading one customer's history takes milliseconds.
- **High concurrency**: Hundreds or thousands of simultaneous writes and reads from application users, handled with locking and transaction isolation.
**Examples**: PostgreSQL, MySQL, Oracle, SQL Server, Aurora — the relational databases that application backends write to.
**What OLTP is bad at**: Analytical queries that scan millions of rows. A query that calculates monthly revenue by product category across two years of orders requires reading the entire orders table and joining to products — potentially hundreds of millions of rows. On a normalised row-store with application load, this query is slow and competes with transaction processing.
OLAP: Optimised for Analysis
An OLAP system (data warehouse) is built for querying large volumes of data efficiently — aggregating millions of rows, joining many tables, and computing metrics across multiple dimensions.
**Design characteristics**:
- **Denormalised / dimensional schema**: Star and snowflake schemas store data in a flattened, redundant form optimised for querying. The product description is stored in the orders table directly, not retrieved via a foreign key join.
- **Columnar storage**: Data is stored column by column. A query reading the order_amount column across 100 million rows reads only that column's data — not all the other columns in each row. For analytical queries that use 5 of 50 columns, columnar storage reads 10% of the data compared to row storage.
- **Massive parallel processing (MPP)**: Queries are split across many compute nodes, processing partitions of the data in parallel. Snowflake, BigQuery, and Redshift all use MPP to execute analytical queries quickly on large datasets.
- **Batch optimised, not transactional**: OLAP systems are not designed for frequent small writes. Data is loaded in batches — hourly or daily — from operational systems. No row-level locking for concurrent writes.
- **High throughput, large result sets**: Queries scan millions of rows and return aggregated results. A monthly revenue report may scan 500 million rows and return 24 aggregated numbers.
- **No application load**: The OLAP system serves analysts and BI tools, not customer-facing application requests.
**Examples**: Snowflake, Google BigQuery, Amazon Redshift, Databricks SQL, Teradata — the analytical data platforms that BI tools and analysts query.
Why You Need Both
Most organisations run OLTP systems for operational processes and a separate OLAP system (data warehouse) for analytics. Combining both in one system means accepting the worst of both:
- Running analytical queries on an OLTP database competes with transaction processing, slowing application response times
- Running transactions on an OLAP database is inefficient because the columnar storage and MPP architecture is not designed for individual row writes
The data architecture pattern: operational systems (OLTP) record events; ETL/ELT pipelines (Fivetran, Airbyte, dbt) copy and transform that data into the data warehouse (OLAP); analysts and BI tools query the warehouse, not the production database.
Hybrid Approaches
**HTAP (Hybrid Transactional and Analytical Processing)**: Modern systems like Google Spanner and CockroachDB attempt to support both patterns in one system. Practical trade-offs remain — analytical query performance still lags purpose-built OLAP systems for complex aggregations.
**Operational analytics**: Some use cases require near-real-time analytics on operational data with low latency — fraud detection, real-time recommendation, inventory management. These require streaming architectures (Kafka + Flink/Spark Streaming) rather than batch OLAP, or purpose-built systems like Apache Druid or ClickHouse that support high-throughput analytical queries with sub-second latency on streaming data.
**Time-series databases**: A specialised class (InfluxDB, TimescaleDB) optimised for sequential timestamped events — sensor data, infrastructure metrics, financial ticks. Neither OLTP nor OLAP in the traditional sense, but closer to OLAP for write patterns and OLAP for query patterns at high time granularity.
The Implications for Data Architecture
Understanding OLTP vs OLAP shapes every data architecture decision:
- Do not query production databases for analytical reports — this creates operational risk and produces slow queries
- Design the data warehouse schema (dimensional model, star schema) for analytical access patterns, not operational ones
- Separate the concerns: operational systems own real-time accuracy; the data warehouse owns historical analysis
- Accept that the data warehouse is not the system of record — it is a copy of operational data optimised for querying
Our data architecture practice designs analytical data architectures that correctly separate operational and analytical systems — contact us to discuss your data architecture requirements.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →