OLTP vs OLAP: Transactional vs Analytical Workloads

Online Transaction Processing (OLTP) systems are engineered to power operational, user-facing applications where thousands to millions of concurrent clients perform fast, fine-grained create, read, update, and delete (CRUD) mutations.

Core Storage Mechanics:

• Row-Oriented Disk Pages: In databases like PostgreSQL and MySQL (InnoDB), data is organized into fixed-size physical pages (typically 8KB in PostgreSQL, 16KB in MySQL). Every row (tuple) is stored contiguously on the page along with its transaction metadata (xmin, xmax, roll pointers).
• Index Lookup Efficiency: When executing a point lookup such as SELECT * FROM orders WHERE order_id = 948123, the database traverses a balanced tree (B+ Tree) index in O(log N) time, navigates to the exact leaf page, and reads the entire contiguous order record with a single disk I/O seek (sim 0.1 - 1ms).
• ACID Guarantee Overheads: Strict adherence to Atomicity, Consistency, Isolation, and Durability requires Write-Ahead Logging (WAL) and Multi-Version Concurrency Control (MVCC) locking mechanisms. Row mutations append to the WAL sequentially before dirty buffer pool pages are flushed to NVMe SSD storage.

The Analytical Bottleneck in OLTP:

When a business intelligence analyst runs an aggregation query such as SELECT AVG(total_amount), country FROM orders GROUP BY country, a row-based engine must load every single column of every row into memory—including large text descriptions, customer addresses, and tax identifiers—only to discard 95\% of the bytes in CPU memory to sum a single numeric field. On a 100-million-row table, this causes catastrophic disk I/O saturation and memory eviction thrashing.

Online Analytical Processing (OLAP) systems (e.g., ClickHouse, Snowflake, Google BigQuery, DuckDB, Amazon Redshift) are purpose-built for high-throughput scans, analytical filters, and multidimensional aggregations spanning millions to billions of rows.

Columnar Storage Physics:

In columnar storage, each column's values across millions of rows are grouped and stored contiguously on disk and in memory blocks:

Row-Oriented: \quad [R_1(C_1, C_2, C_3)], [R_2(C_1, C_2, C_3)], [R_3(C_1, C_2, C_3)]

Column-Oriented: \quad [C_1(R_1, R_2, R_3)], [C_2(R_1, R_2, R_3)], [C_3(R_1, R_2, R_3)]

Key Columnar Superpowers:

1. Aggressive Compression Ratios (5× - 15×): Because all values in a column share the exact same data type and domain semantics, specialized compression algorithms achieve extraordinary density:
   • Run-Length Encoding (RLE): Collapses consecutive repeated values (e.g., ['US', 'US', 'US', 'US'] → ('US', 4)).
   • Dictionary Encoding: Replaces high-cardinality strings with 1-byte or 2-byte integer IDs.
   • Delta & Frame-of-Reference (FoR) Encoding: Replaces monotonically increasing timestamps or sequential IDs with difference offsets.
   • Bit-Packing & General Compression: Bit-packing integers followed by ZSTD or LZ4 compression algorithms.
2. SIMD (Single Instruction, Multiple Data) Vectorization: Modern x86 (AVX-512) and ARM (NEON) CPUs can load contiguous 512-bit registers containing sixteen 32-bit integers in a single clock cycle, executing parallel mathematical additions (SUM) or filter masks (WHERE age > 21) in sub-nanosecond instruction cycles.
3. Projection Pruning: A query selecting only 2 columns out of a 100-column wide table reads exactly 2\% of the data files from storage, eliminating 98\% of I/O transfer latency.

-- OLTP Point Query (PostgreSQL Index Seek - Fast ~0.4ms)
SELECT id, user_id, order_total, status, created_at 
FROM orders 
WHERE id = 8849201;

-- OLAP Aggregation (ClickHouse Vectorized Column Scan - Fast ~18ms over 200M rows)
SELECT 
    country,
    date_trunc('day', created_at) AS order_date,
    COUNT(*) AS total_orders,
    AVG(order_total) AS avg_basket_size,
    SUM(order_total) AS gross_merchandise_value
FROM orders_analytical
WHERE created_at >= '2026-01-01'
GROUP BY country, order_date
ORDER BY gross_merchandise_value DESC;

The internal query evaluation engine determines CPU instruction cache locality and branch prediction efficiency:

Organizations increasingly deploy Hybrid Transactional/Analytical Processing (HTAP) engines to minimize the latency gap between transaction commit and analytical visibility:

• Dual Engine Replicas (e.g., TiDB / TiFlash, AWS AlloyDB): The database maintains a row-oriented engine (TiKV / RocksDB) for real-time ACID updates, while asynchronously replicating Raft log commits to a dedicated columnar engine (TiFlash) for real-time reporting without impacting OLTP write throughput.
• Micro-Batch ETL/ELT Streaming: Change Data Capture (CDC) via Debezium and Kafka captures row-level WAL events from PostgreSQL and streams them into ClickHouse or Snowflake Iceberg tables within 1 to 5 seconds of transaction commit.