Part 166: Modern Cloud Data Warehousing: OLTP vs OLAP & Vectorized Execution
Why modern analytical warehouses process billions of rows in seconds where traditional relational databases choke.
1. OLTP vs OLAP: Fundamental Architecture
| Feature | OLTP (Postgres, MySQL, SQL Server) | OLAP (Snowflake, BigQuery, ClickHouse) |
|---|---|---|
| Primary Goal | High-concurrency, low-latency single-row CRUD | Aggregating billions of rows across multiple dimensions |
| Storage Format | Row-oriented (Pages store full records) | Column-oriented (Pages store single columns) |
| Transaction Model | Strict ACID, row locks, MVCC | Micro-partitions, immutable immutable blocks, append-mostly |
| Typical Query | SELECT * FROM users WHERE id = 123; |
SELECT region, SUM(revenue) FROM sales GROUP BY 1; |
| Latency Expectation | 1 – 10 milliseconds | 500ms – 10 seconds (over petabytes!) |
2. Columnar Storage Efficiency
In an analytical query querying only 2 columns out of 50: - Row-Oriented: Must read 100% of the raw data from disk to extract the 2 columns. - Column-Oriented: Reads only the 4% of disk data containing the required 2 columns! - In addition, identical data types stored contiguously compress dramatically higher (Run-Length Encoding, Dictionary Encoding, Bit-Packing).
3. Vectorized Execution
Instead of traditional Volcano-style "tuple-at-a-time" execution: - Vectorized query engines pass arrays (vectors) of thousands of column values through CPU cache registers. - Leverages modern SIMD (Single Instruction, Multiple Data) CPU hardware instructions.
Key Takeaways
- Never run heavy multi-year analytics on your operational OLTP database; replicate to an OLAP warehouse.
In Part 167, we explore Snowflake SQL Essentials: Virtual Warehouses & Micro-Partitions!