OLTP vs OLAP（交易型 vs 分析型）
兩種完全相反的存取模式，推導出兩種截然不同的儲存引擎。看到「報表/統計/儀表板/歷史分析」的需求，就該主動說：把這類工作負載從 OLTP 分離到 OLAP。
為什麼需要兩種資料庫
| 需求 | OLTP 範例 | OLAP 範例 |
|---|---|---|
| 操作 | 下單：扣庫存+建訂單+記付款 | 報表：上月各地區×類別銷售額 |
| 頻率/範圍 | 每秒幾千次，每次動幾行 | 一天幾次，每次掃幾億行 |
| 一致性 | 必須 ACID、可回滾 | 不需即時一致 |
用同一個 DB 兩者都做會兩頭做不好：分析掃全表鎖住業務資料；業務寫入的隨機 I/O 又拖慢分析。
OLTP：為交易而生
設計哲學：對少量資料做快速精準讀寫 + 保證一致性。
Row-oriented 儲存
一筆訂單的所有欄位連續存在磁碟同一位置。
[order_id=1, user_id=101, amount=500, status="paid", created_at=...]
[order_id=2, user_id=203, amount=1200, status="pending", ...]
對點查詢和小範圍更新極高效：讀一筆訂單一次 I/O 拿全欄位；更新單欄定位該行；插入末尾附加。
三大支柱
- B-tree 索引：查詢從
O(n)全表掃變O(log n)直接定位 - ACID 事務：原子/一致/隔離/持久（靠 WAL）
- 代表：PostgreSQL、MySQL、Oracle（RDBMS）；DynamoDB、MongoDB（NoSQL 但交易模式仍屬 OLTP）
| 效能基準（以 PG） | 量級 |
|---|---|
| 主鍵查詢 | 數萬 QPS/核心 |
| 帶索引範圍查詢 | 數千 QPS/核心 |
| 複雜多表 JOIN | 數百 QPS/核心 |
| 最適資料量 | 單表幾千萬行以內 |
OLAP：為分析而生
設計哲學：對海量歷史資料做高效聚合，即使單次掃幾億行。
Column-oriented 儲存
所有訂單的 amount 存一起、所有 status 存一起。
[amount] : 500, 1200, 300, 800, 2100, ...
[status] : "paid", "pending", "paid", "cancelled", ...
[region] : ...
查 SELECT region, SUM(amount) ... GROUP BY region 只需 3 個欄位：
- Row-oriented：必須讀每行所有欄位，大量 I/O 浪費
- Column-oriented：只讀那 3 欄的資料塊，I/O 可少 90%+
為什麼 column 對分析快（三原因疊加）
| 原因 | 說明 |
|---|---|
| I/O 效率 | 只讀需要的欄位，跳過其他 |
| 壓縮 | 同欄位型別相同/值域相近，壓縮率高（status 字典編碼 → 每值 2 bit） |
| 向量化執行 | 一次處理一批（1024/4096 行），CPU SIMD 一條指令算幾十個值 |
星型 Schema（Star Schema）
OLTP 強調正規化（分析要 JOIN 幾十表，慢）。OLAP 用星型 schema：中心事實表（每筆業務事件，幾億~幾兆行）+ 周圍維度表（描述性屬性）。分析 = 事實表聚合 + 維度表篩選分組。
- 代表：BigQuery、Redshift、Snowflake（雲端倉儲）；ClickHouse、Apache Doris（自建）；DuckDB（嵌入式）
核心差異對照
| 維度 | OLTP | OLAP |
|---|---|---|
| 設計目標 | 快速個別交易 | 大規模聚合分析 |
| 查詢模式 | 點查詢、小範圍讀寫 | 全表掃描、聚合、GROUP BY |
| 資料量/次 | 幾行~幾百行 | 幾億~幾兆行 |
| 並發 | 高（數千用戶） | 低（幾十分析師） |
| 延遲 | 幾毫秒~幾十毫秒 | 幾秒~幾分鐘 |
| 儲存 | Row-oriented | Column-oriented |
| Schema | 高度正規化 | 星型 / 雪花型 |
| 資料新鮮度 | 即時（毫秒） | 有延遲（分鐘~小時） |
| 優化手段 | B-tree、事務、WAL | 壓縮、向量化、分區裁剪 |
| 代表 | PostgreSQL、MySQL、DynamoDB | BigQuery、Snowflake、ClickHouse |
連接 OLTP 與 OLAP 的管線
業務資料先進 OLTP，再搬到 OLAP。三種做法：
| 管線 | 機制 | 延遲 | 取捨 |
|---|---|---|---|
| ETL | 定期抽取→轉換→載入 | 小時~T+1 | 簡單可靠；不適合即時 |
| CDC | 監聽 WAL（Debezium）→ Kafka → OLAP | 幾秒~幾分 | 近即時；基礎設施複雜（Kafka） |
| ELT | 先載原始資料進倉儲，再用 SQL/dbt 在倉儲內轉換 | 視排程 | 雲端倉儲算力強，現代主流 |
ETL: PostgreSQL ──每日批次──→ BigQuery
CDC: PostgreSQL ──Debezium 讀 WAL──→ Kafka ──→ ClickHouse（近即時）
ELT: PostgreSQL ──載入原始──→ BigQuery ──dbt 倉儲內轉換──→ Mart
核心取捨 延遲 vs 複雜度。分析師接受看昨天資料 → ETL 幾乎永遠更好。需要近即時（詐欺偵測、即時儀表板）→ CDC。先用 ETL 起步，確認有即時需求再引入 CDC（過早引入 CDC 是過度工程）。
HTAP：混合方案
用一個系統同時支援兩種工作負載。
- TiDB：TiKV（row，OLTP）+ TiFlash（column，OLAP），同份資料兩格式，OLTP 寫入自動同步到 column 副本
- SingleStore：記憶體 rowstore 處理交易，磁碟 columnstore 處理分析
同時維護兩種儲存格式 → 資源開銷高、複雜度上升、難對兩邊都最佳化。適合：資料量不大（幾十 TB 內）、即時性要求高、團隊資源有限。大多數規模的公司分開建置 OLTP + OLAP 更穩健。
面試話術與情境
「PostgreSQL 處理日常業務，但財務報表要跑複雜聚合，在同一個 PG 上跑會影響線上效能。我會引入 BigQuery 作資料倉儲，透過 CDC + Kafka 近即時同步，讓分析查詢完全隔離。」
「行程寫入 PostgreSQL（OLTP）。透過 CDC 串流到 BigQuery（OLAP）跑收入報表、供需分析。需即時監控的指標（各城市在線司機數）走獨立 Redis 計數器，不依賴 OLAP。」
常見 Deep Dive
三原因疊加：I/O 效率（只讀需要的欄位）+ 壓縮（同型別值域壓縮率高）+ 向量化執行（SIMD 一條指令處理幾十個值）。
資料量幾十 TB 內、對分析即時性要求高、工程資源有限 → HTAP（免維護管線、無 ETL 延遲）。資料超過幾百 TB 或負載模式差異大 → 分開建置的效能與成本優勢明顯。
ETL 簡單可靠但有延遲（小時~天）；CDC 把延遲壓到幾秒，代價是 Debezium/Kafka 的基礎設施 + consumer lag、schema 變更、exactly-once 等故障點。先 ETL，確認即時需求再 CDC。
自我測驗重點
| 問題 | 重點 |
|---|---|
| OLTP vs OLAP 儲存差異 | Row-oriented vs Column-oriented |
| column 為何快 | I/O 效率 + 壓縮 + 向量化（SIMD） |
| OLAP 的 schema | 星型（事實表 + 維度表） |
| 三種管線 | ETL（簡單）/ CDC（近即時）/ ELT（dbt 倉儲內） |
| HTAP 代價 | 兩種格式並存，兩邊都妥協 |
| 看到「報表/分析」需求 | 主動分離到 OLAP |
Related Notes
- 06-Database-Tech/01-Database — OLTP/OLAP 是與資料模型正交的維度
- 06-Database-Tech/02-PostgreSQL — OLTP 代表 + WAL（CDC 來源）
- 06-Database-Tech/03-DynamoDB — OLTP NoSQL；Streams 即 CDC
- 04-Database-Indexing/02-B-Tree-Index — OLTP 的索引核心
- 09-Messaging-Coord/01-Data-Pipeline — Batch/Stream、ETL/ELT、CDC 管線
- 08-Search-and-AI/01-Search-System — CDC 同步搜尋索引的相同模式
- 06-Database-Tech/Practice-Database-Tech