OLTP vs OLAP?

Detailed explanation

OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) describe two different database workloads. OLTP supports day-to-day operations such as placing an order, transferring money, or updating inventory. OLAP supports analysis, such as comparing quarterly revenue across regions. The main distinction is not simply writes versus reads: OLTP also performs many reads, but usually retrieves a small, targeted set of records, while OLAP examines large datasets to produce summaries and insights.

OLTP systems optimise for short response times, high concurrency, and reliable transactions. A checkout transaction might insert an order, create order items, and reduce stock, with related changes committed together or rolled back on failure. ACID properties, constraints, and concurrency-control mechanisms help preserve correctness when many users act simultaneously. Schemas are commonly normalised to reduce duplication and prevent update anomalies. Row-oriented storage and selective indexes often suit this workload because applications frequently access individual records through primary keys or other indexed predicates. However, every additional index adds maintenance work to writes.

OLAP systems optimise for analytical query performance over substantial historical data. For example, SELECT region, SUM(total) FROM fact_sales GROUP BY region aggregates sales rather than processing a customer's purchase. Data warehouses often use star schemas: a central fact table stores measures and dimension keys, while dimension tables describe entities such as products, customers, and dates. Column-oriented storage, compression, partition pruning, and parallel execution can reduce the cost of scanning and aggregating large datasets. These are common design choices, not mandatory features of every OLAP system.

Organisations often move operational data into an analytical platform using batch ETL/ELT or change data capture, so expensive reporting does not compete with checkout transactions for resources. This separation introduces trade-offs, including data freshness, pipeline complexity, and additional storage. In interviews, explain the workload first, then connect it to schema design, storage, concurrency, and performance goals. Avoid presenting the categories as rigid product labels: one database can support both, and hybrid transactional/analytical processing systems aim to serve both workloads while managing resource contention.

Key points to remember

• OLTP serves operational transactions; OLAP serves analytical queries and reporting.
• OLTP typically accesses few rows per request; OLAP often scans and aggregates many rows.
• OLTP commonly uses normalised schemas; OLAP commonly uses fact-and-dimension star schemas.
• OLTP prioritises transaction latency, concurrency, and correctness; OLAP prioritises efficient large-scale analysis.
• Separate analytical systems reduce operational contention but introduce data freshness and pipeline trade-offs.

Common mistakes candidates make

• Calling OLTP write-only and OLAP read-only instead of distinguishing their typical access patterns.
• Treating normalisation, denormalisation, or storage orientation as strict requirements rather than common design choices.
• Assuming an aggregate SQL query automatically requires a separate OLAP database, regardless of data volume or operational impact.

Likely follow-up questions