OLAP vs OLTP Workloads

OLTP (Online Transaction Processing) describes workloads dominated by short, atomic operations that read or write a small number of rows, typically identified by a primary key. A bank transfer, an e-commerce checkout, an airline seat reservation — each touches a handful of records and must complete with strict correctness guarantees (the ACID-style transaction properties: atomicity, consistency, isolation, durability). The defining metric is throughput of transactions per second combined with low per-transaction latency.

OLAP (Online Analytical Processing) describes workloads dominated by complex queries that read large fractions of a table (or many tables), aggregate over millions or billions of rows, and often join across several dimensions. A query like "average order value by month, region, and product category over five years" is typical. The defining metric is query throughput over huge data volumes, where a single query may legitimately take seconds to minutes, and the acceptable trade-off is that writes (data loading) happen in scheduled batches rather than continuously. The contrast is captured along three axes:

• Access pattern. OLTP access is point-oriented: "fetch the row where `order_id = 5231`."

OLAP access is set-oriented: "scan all rows where `year = 2023` and compute `SUM(revenue)` grouped by `region`." OLTP touches a tiny fraction of the table per operation; OLAP touches most or all of a (typically much larger) table per query, but usually only a few columns of it.

• Latency and concurrency. OLTP demands millisecond-scale response times under high

concurrency — thousands of transactions per second, each independent, each requiring strict isolation from the others. OLAP tolerates second-to-minute latencies for a handful of concurrent analytical queries, each one expensive but relatively rare compared to OLTP traffic.

• Schema design. OLTP schemas are usually normalized (third normal form or close to

it) to avoid update anomalies and keep single-row writes cheap and consistent. OLAP schemas are usually denormalized, often organized as a star schema with a central fact table and surrounding dimension tables, because normalization forces expensive joins during large scans — a cost analytical queries cannot afford. These three axes are not independent; they are causally linked. A workload of frequent small writes (access pattern) demands low tail latency (latency profile), which in turn favors a schema and storage layout optimized for locating and updating single rows cheaply (schema design). A workload of infrequent giant scans favors the opposite chain of decisions. The rest of this article traces exactly how each choice follows from the others.

The clearest way to see why OLTP and OLAP diverge is to trace what happens physically when each workload executes a representative operation, and to see how the underlying storage engine must be shaped to make that operation efficient.

OLTP execution path. A transaction such as `UPDATE accounts SET balance = balance - 100 WHERE account_id = 42` needs to: 1. Locate the exact row for `account_id = 42` — ideally via a B+-tree index in O(log n) time. 2. Acquire a lock (or use multi-version concurrency control) on just that row. 3. Read the current value, apply the update, write the new value plus a log record. 4. Commit: flush the write-ahead log entry so the change is durable, then release the lock. This path is optimized when data is stored row-wise — all columns of a given row placed contiguously on disk/in a page — because a single I/O fetches everything needed to read or update that one row. Row-store engines (as used by traditional OLTP databases) are built around this exact access pattern.

OLAP execution path. A query such as `SELECT region, SUM(revenue) FROM sales WHERE year = 2023 GROUP BY region` needs to: 1. Identify which columns are actually needed: `region`, `revenue`, `year`. Ignore every other column in the table (which may have dozens of columns: customer id, product id, payment method, shipping address, etc.). 2. Scan only those columns across potentially billions of rows. 3. Apply a filter (`year = 2023`), then aggregate (`SUM`, grouped by `region`). 4. Return a small, summarized result. This path is optimized when data is stored column-wise — all values of a single column placed contiguously — because the engine can skip every column not mentioned in the query entirely, dramatically reducing I/O, and can apply strong compression since values within one column tend to be similar (see Column-Family and Column Stores). The pseudocode below sketches the essential difference in a query planner's cost reasoning when it must decide how to physically execute each type of workload. ``` function EXECUTE_OLTP_TRANSACTION(table, key, update_fn):

# Step 1: point lookup via B+-tree index -- O(log n) row_ptr = BTREE_INDEX_LOOKUP(table.index, key) if row_ptr is NULL: abort("row not found")

# Step 2: acquire row-level lock (or begin MVCC snapshot) lock = ACQUIRE_ROW_LOCK(row_ptr)

# Step 3: read-modify-write on ONE row (row-store layout: all columns contiguous) row = READ_ROW(row_ptr) new_row = update_fn(row) WRITE_AHEAD_LOG(row_ptr, new_row) # durability before commit WRITE_ROW(row_ptr, new_row)

# Step 4: commit COMMIT_LOG_FLUSH() RELEASE_ROW_LOCK(lock) return SUCCESS

function EXECUTE_OLAP_QUERY(table, needed_columns, filter_fn, group_by_col, agg_fn):

# Step 1: column pruning -- only touch columns actually referenced column_stores = [ COLUMN_STORE(table, c) for c in needed_columns ]

# Step 2 (optimization): skip whole blocks using zone maps / min-max metadata relevant_blocks = [] for block in ZONE_MAP_BLOCKS(table, filter_fn.column): if block.min <= filter_fn.bound <= block.max or OVERLAPS(block, filter_fn): relevant_blocks.append(block)

# Step 3: vectorized scan + filter + partial aggregation, block by block partial_results = {} for block in relevant_blocks: decompressed = DECOMPRESS(block) # column encodings: RLE, dictionary, etc. filtered = FILTER(decompressed, filter_fn) for row in filtered: key = row[group_by_col] partial_results[key] = agg_fn(partial_results.get(key, agg_fn.identity), row)

# Step 4: merge partials across parallel workers (this scan is embarrassingly parallel) final_result = MERGE_PARTIALS(partial_results) return final_result ``` The key structural difference is visible in the pseudocode itself: `EXECUTE_OLTP_TRANSACTION` is dominated by a single indexed lookup and a locking protocol on one row, while `EXECUTE_OLAP_QUERY` is dominated by a block-parallel scan across an entire column, with column pruning and zone maps used specifically to avoid touching irrelevant data.

OLTP complexity. A single transaction on an indexed table costs `O(log n)` for the index traversal (B+-tree depth is logarithmic in the number of rows `n`), plus O(1) amortized for the row read/write itself, plus a constant amount of log-writing for durability. Under `k` concurrent transactions, a well-designed OLTP engine achieves throughput close to linear in available cores/disks, bounded by lock contention on hot rows. The correctness requirement is the strongest in the field: the ACID properties must hold under arbitrary interleavings of concurrent transactions. This is normally proved via serializability: a concurrency-control protocol (two-phase locking, or multi-version concurrency control) is correct if every allowed schedule is conflict-equivalent to some serial (one-at-a-time) execution of the same transactions. The proof sketch for two-phase locking is the classic one: if every transaction acquires all its locks before releasing any (the "growing" phase precedes the "shrinking" phase), then the precedence graph over transactions (an edge `Ti → Tj` whenever `Ti` releases a lock that `Tj` then acquires on a conflicting item) is guaranteed acyclic, and an acyclic precedence graph is exactly the condition for conflict-serializability.

OLAP complexity. A full-table aggregation query costs `O(n)` in the number of rows touched, but the constant factor is what matters practically: a column store touches only the `c` columns referenced by the query rather than all `C` columns in the table, giving an I/O reduction factor of roughly `C / c`. With zone maps (per-block min/max metadata) and predicate filters, entire blocks can be skipped without decompression, often reducing effective work far below `O(n)` in practice — though the worst case (no selective filter) remains `O(n)`. Aggregation across a partitioned/parallel column store parallelizes almost perfectly: with `p` workers scanning disjoint blocks, wall-clock time approaches `O(n/p)` plus a cheap `O(p)` merge step, because summation and grouping are associative and can be computed as partial aggregates and combined. Correctness here is simpler than OLTP's: OLAP queries are typically read-only against a (mostly) static snapshot loaded in batches, so there is no concurrent-write interleaving to reason about — the correctness question reduces to "does the query plan compute the mathematically correct aggregate over the scanned rows," which is a matter of query-plan equivalence (e.g., pushing filters below joins preserves the relational algebra's semantics) rather than a concurrency proof.

Honest tradeoffs. Row stores make single-row I/O cheap but waste I/O on wide scans (every column is dragged along even if unused). Column stores make scans and compression cheap but make single-row point updates expensive, since one logical row's values are scattered across many separate column files — appending or updating one row may require touching every column file. This is precisely why OLTP systems do not adopt column stores for their primary store, and why OLAP systems do not run their reporting queries against a live, row-oriented, highly-normalized production database: each storage layout is asymptotically and constant-factor optimized for one access pattern and penalized on the other.

Consider a simplified `orders` table with 4 columns (`order_id`, `customer_id`, `amount`, `order_date`) and suppose it holds 100 million rows, stored in 8 KB pages.

OLTP scenario: "Update the amount of order #58213901 due to a discount."

The row store wins decisively here — this is why OLTP engines use row-oriented layouts.

OLAP scenario: "Total order amount per month, for orders in 2023."

Here the column store wins by roughly an order of magnitude or more, which is exactly why data warehouses built for this scenario use columnar storage (foreshadowing the star-schema fact/dimension design covered in Star Schemas and Dimensional Modeling, where the fact table — like this `orders` table — is precisely the table subjected to these large scans). The two scenarios use the same table but demand opposite physical layouts — this is the concrete reason production systems typically maintain a row-oriented OLTP database and a separate column-oriented OLAP warehouse, connected by a batch or streaming pipeline that periodically copies and reshapes the data.

The OLTP/OLAP distinction crystallized as businesses' operational databases grew large enough that reporting queries against them became a performance liability, and as relational theory matured enough to support both transactional guarantees and large-scale analytical modeling as distinct, well-defined disciplines.

Every large e-commerce, banking, or telecommunications company runs this split in practice. The operational database — often a relational OLTP engine — processes purchases, transfers, and account changes with strict durability and low latency. Periodically (nightly, hourly, or via continuous streaming), that data is extracted, transformed, and loaded into a separate analytical store: a data warehouse or column-oriented OLAP engine, often organized with a star schema as covered in Star Schemas and Dimensional Modeling. Business intelligence dashboards, fraud-detection models, and executive reporting all query the OLAP side, never the live transactional system, precisely to avoid the resource contention described above. Modern cloud data platforms explicitly market themselves along this divide: managed relational databases advertise OLTP throughput and latency guarantees, while managed data warehouses advertise scan speed over petabytes and cost-per-query for analytics. Streaming platforms sit in between, feeding OLTP change events into OLAP stores in near-real time, narrowing (but never eliminating) the staleness gap between "what just happened" and "what the dashboard shows." Understanding this workload split is the foundation for designing any system that must serve both operational and analytical needs — the very problem Data Warehousing Foundations takes up next.

Ask the tutor: How would you design a system that supports both fast transactions and fast analytics with minimal staleness between them?