PostgreSQL 關聯式資料庫 (PostgreSQL)
從 PostgreSQL 出發，再說明為何需要偏離它，比從小眾方案出發再證明它比 PG 好更有說服力。
面試官不是找 DBA —— 不要鑽 MVCC/WAL 內部，也別丟「NoSQL 擴展性比 PG 好」這種籠統論斷。聚焦：讀寫效能、一致性、複製四個取捨。
四大核心能力
| 能力 | 關鍵 |
|---|---|
| 讀取效能 | B-tree + 進階索引（GIN/PostGIS）+ 涵蓋/部分索引 |
| 寫入效能 | buffer + WAL + 背景寫；批次 / 分區 / sharding 擴展 |
| 一致性 | ACID 交易 + 隔離等級 + Row-Level Locking |
| 複製 | 同步/非同步 replica → 擴展讀取 + 高可用 |
讀取效能
讀取通常遠多於寫入。面試聚焦「不同索引何時說得通」，不需深入 query planner。
基礎索引（B-tree，預設）
適合：精確匹配（WHERE email = ...）、範圍查詢（created_at > ...）、排序（ORDER BY，欄位順序需符合索引）。
CREATE INDEX idx_users_email ON users(email);
-- 複合索引：針對常見查詢模式
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);
每個索引會：① 拖慢寫入（索引要跟著更新）② 佔磁碟空間 ③ 不一定被用到（planner 可能判定全表掃描更快）。只對高頻查詢 + 選擇性高的欄位建索引。
進階索引：常常省掉一個專用資料庫
| 索引 | 用途 | 取代了什麼 |
|---|---|---|
| GIN | 全文搜尋（tsvector） | 簡單搜尋不必上 Elasticsearch |
| JSONB + GIN | 靈活 metadata、半結構化 | 給你類 NoSQL 的彈性，不必上 MongoDB |
| PostGIS（GIST） | 地理空間查詢 | 不必上專用地理 DB（Uber 早期就用它） |
PG 內建全文搜尋支援詞幹處理、相關性排名、多語言、AND/OR/NOT。但若要更精細的相關性評分、faceted search、模糊匹配、超大規模分散式搜尋或進階聚合，才該引入 Elasticsearch。原則：架構越簡單越好。詳見 08-Search-and-AI/03-Elasticsearch。
查詢優化進階技巧
- 涵蓋索引（Covering Index）：
INCLUDE (title, created_at)把需要的欄位塞進索引 → 不必回表（index-only scan）。代價：索引更大、寫入稍慢。 - 部分索引（Partial Index）：
WHERE status = 'active'只索引子集 → 更小更快。適合大量「已刪除/非活躍」記錄不需索引時。
效能基準（粗估）
| 操作 | 量級 |
|---|---|
| 簡單索引查找 | 每核心每秒數萬次 |
| 複雜 JOIN | 每核心每秒數千次 |
| 開始吃力的規模 | 單表 > 1 千萬~1 億筆 |
工作集（頻繁存取資料）超過 RAM 後效能急劇下降。盡量讓工作集留在記憶體裡。
寫入效能
寫入路徑
1. 修改記憶體 buffer（標記為髒頁）+ 產生 WAL 記錄 ← 都在記憶體
2. commit 時：WAL 循序寫進磁碟（fsync）→ 交易即持久化（崩潰可重播 WAL 恢復）
3. 背景寫入程序：髒頁非同步刷進資料檔（可批次提升效能）
4. 索引更新：每個索引都要更新（→ 太多索引大幅拖慢寫入）
大部分工作在記憶體 + WAL 確保持久性 → 寫入可以很快。瓶頸通常卡在 WAL 的磁碟 I/O。
吞吐量（單節點、中等硬體）
| 操作 | 每核心每秒 |
|---|---|
| 簡單 INSERT | ~5,000 |
| 有索引更新的 UPDATE | ~1,000–2,000 |
| 複雜多表交易 | 幾百 |
| 批次操作 | 幾萬 |
需要 > 5,000 寫入/秒不代表 PG 不行，只代表要把資料分片到多節點。
寫入優化策略（由簡到繁）
- 垂直擴展：NVMe（改善 WAL）、更多 RAM、更多核心 —— 合理起點
- 批次處理：多筆寫入合成一個交易（風險：中途崩潰整批遺失）
- 寫入卸載（Write Offloading）：分析/日誌/聚合指標 → 丟 Kafka → 背景 worker 批次寫
- 表格分區（Partitioning）：常依時間
PARTITION BY RANGE (created_at)；並行寫不同分區、索引更新只在相關分區、讀只掃最近分區。可把舊分區移到便宜儲存 - 分片（Sharding）：按查詢最頻繁的欄位（如
user_id，讓同用戶資料同 shard，避免 scatter-gather）。PG 無內建 sharding，要自己做或用 Citus
資料一致性
要說清楚具體怎麼用 ACID 解決一致性需求。
交易
一組必須一起成功或一起失敗的操作（轉帳：扣款 + 入帳，不能只做一半）。
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
並發競態（拍賣出價）
預設隔離等級 Read Committed 下，即使包在交易裡，兩個用戶同時出價仍可能不一致：
1. A 讀到最高價 90
2. B 讀到最高價 90
3. A 出 100，commit
4. B 出 95，commit ← 不合法！95 被接受了，但已有 100
兩個解法：
| 解法 | 機制 | 適用 |
|---|---|---|
| Row-Level Locking | SELECT ... FOR UPDATE 鎖住該行 |
清楚知道要鎖哪幾行（首選） |
| 更高隔離等級 | SET TRANSACTION ISOLATION SERIALIZABLE |
複雜到難以預知要鎖什麼 |
更準確：「我們用交易加上對拍賣記錄的列層級加鎖（FOR UPDATE）」。
| 比較 | Serializable | Row-Level Locking |
|---|---|---|
| 並行度 | 較低（衝突需重試） | 較高（碰同一行才衝突） |
| 效能開銷 | 較高（追蹤所有讀寫依賴） | 較低（只追蹤特定鎖） |
| 錯誤處理 | 處理序列化失敗（重試） | 處理死鎖 |
| 典型例子 | 跨多表複雜財務計算 | 拍賣出價、庫存更新 |
隔離等級
| 等級 | Dirty | Non-repeat | Phantom | 備註 |
|---|---|---|---|---|
| Read Uncommitted | (PG 等同 RC) | 可能 | 可能 | PG 實際不允許 dirty read |
| Read Committed（預設） | ❌ | 可能 | 可能 | 效能好 |
| Repeatable Read | ❌ | ❌ | ❌（PG 特有） | PG 此級比 SQL 標準更強 |
| Serializable | ❌ | ❌ | ❌ | 防所有異常，需重試邏輯 |
它在交易開始建一致性快照，同時防 non-repeatable read 與 phantom read。很多在別的 DB 需要 Serializable 的情況，PG 用 Repeatable Read 就夠。對照 05-Database-Advanced/01-Transactions。
複製（Replication）
幾乎所有真實部署都用複製，兩個原因：擴展讀取 + 高可用。
| 模式 | 行為 | 取捨 |
|---|---|---|
| 同步 | primary 等 replica 確認才回成功 | 強一致，延遲高 |
| 非同步 | primary 立刻回成功，背景複製 | 效能好，replica 可能短暫落後 |
很多組織混用：少數同步 replica 保強一致 + 額外非同步 replica 擴展讀取。
擴展讀取 + Replication Lag
讀取分散到多 replica，寫入走 primary（大多應用讀多寫少）→ 讀取吞吐 ×N。
用戶剛寫完立刻讀，可能打到還沒跟上的 replica → 看不到自己的變更。解法：剛寫入的請求短暫路由到 primary，或在應用層處理。對照 05-Database-Advanced/03-Replication。
高可用 + Failover
primary 掛掉 → 偵測下線 → 提升一個 replica 為 primary → 更新連線資訊 → 應用指向新 primary。多數團隊用托管服務（RDS、Cloud SQL）自動處理。面試知道「failover 可行 + 大致流程」就夠。
什麼時候選 PostgreSQL（與不選）
✅ 選 PG：複雜關係、強一致需求、豐富查詢、結構化+非結構化混合（JSONB）、內建全文搜尋、地理空間查詢。
完美場景：電商（庫存/訂單）、金融（交易/稽核）、CMS、合理規模的分析平台。
❌ 考慮替代方案：
| 情況 | 問題 | 替代 |
|---|---|---|
| 極端寫入吞吐 | 每筆寫入要 WAL + 索引更新 → I/O 瓶頸 | Cassandra（事件流）、Redis（計數器） |
| 全球多區域多寫 | 單主架構，只能一個 Region 主寫 | CockroachDB、Cassandra、DynamoDB Global Tables |
| 純 Key-Value 存取 | MVCC/WAL/planner 是殺雞用牛刀 | Redis、DynamoDB |
設計得當的 PostgreSQL 能處理相當大的規模。
自我測驗重點
| 問題 | 重點 |
|---|---|
| 預設索引 | B-tree（精確/範圍/排序） |
| GIN / JSONB / PostGIS 各解什麼 | 全文搜尋 / 半結構化 / 地理空間 |
| 寫入為何快 | 大部分在記憶體 + WAL；瓶頸在 WAL I/O |
| 拍賣競態怎麼解 | SELECT FOR UPDATE（首選）或 Serializable |
| PG Repeatable Read 特別在哪 | 同時防 phantom read（比 SQL 標準強） |
| Read-Your-Writes 怎麼解 | 剛寫入請求路由到 primary |
| 何時棄用 PG | 極端寫入 / 全球多寫 / 純 KV |
Related Notes
- 06-Database-Tech/01-Database — 資料庫總覽與選型
- 06-Database-Tech/03-DynamoDB — 對照：托管 KV、原生水平擴展
- 06-Database-Tech/04-OLTP-vs-OLAP — PG 是 OLTP 代表
- 05-Database-Advanced/01-Transactions — ACID、隔離等級、Lost Update
- 05-Database-Advanced/02-Sharding — sharding 細節
- 05-Database-Advanced/03-Replication — 複製、lag、failover
- 04-Database-Indexing/02-B-Tree-Index — B-tree 索引原理
- 04-Database-Indexing/01-Indexing-Overview — 索引取捨
- 08-Search-and-AI/03-Elasticsearch — 何時 GIN 不夠、要上 ES
- 06-Database-Tech/Practice-Database-Tech