Phase 04 — Database Internals（Day 31–40）

What：一段 SQL 文字進到資料庫後，依序被四層元件處理：Parser把 SQL 文字解析成語法樹（確認語法正確、把 SELECT ... FROM ... WHERE... 拆解成可操作的結構）；Planner（也叫 Query Planner/Optimizer）根據 table 的統計資訊（列數、column 分布）決定「用哪一種方式執行這個查詢最省成本」，產出一份執行計畫（例如「先對 users 做 Index Scan 再跟 orders 做 Nested Loop Join」）；Executor 照著這份執行計畫實際跑，向下一層要資料、做 filter/join/sort 等運算；Storage 是最底層，負責把資料實際讀進記憶體或寫回磁碟——也就是接下來 Day 31–40 要展開的 Page/Buffer/B-Tree/Index 都活在這一層。

Failure Modes：最常見的是把「SQL 語法正確」跟「SQL 執行方式正確」混為一談——一句語法完全合法的 SELECT 也可能因為 Planner 選錯計畫（例如該用 Index Scan 卻用了 Sequential Scan）而在大表上跑到逾時；這種錯誤 Parser 抓不出來，因為語法沒有問題，只有 Planner 產出的執行計畫（用 EXPLAIN 才看得到）才會暴露問題，這正是後面幾天要建立「不能只看 SQL 文字本身」這個習慣的原因。

Backend Applications：Backend 工程師平常寫的 ORM（例如 Go 的sqlc/gorm）本質上是「幫你把程式碼組成 SQL 文字」，但 ORM 完全不介入 Parser 之後的流程——一句透過 ORM 產生的 SQL，一樣要經過 Planner 決定計畫、Executor 執行、Storage 層存取。這代表「用了 ORM」不等於「不用管資料庫內部怎麼跑」，效能問題（N+1 query、缺 index）一樣要靠理解這四層才能診斷。

What：Page 是資料庫在磁碟與記憶體之間讀寫的最小固定大小單位，PostgreSQL 預設是 8KB。不管你只想讀 1 個欄位還是整張表，資料庫底層的 I/O 永遠是「以 page 為單位」讀寫——要拿到某一筆資料的某個欄位，至少要把該筆資料所在的整個 page（8KB）讀進記憶體，不會有「只讀 8 bytes」這種操作。

Failure Modes：常見誤解是以為「資料庫可以只讀我要的那幾個 bytes，跳過其他部分」，因此以為「SELECT 少一點欄位就能省 I/O」——實際上只要那筆資料所在的 page 需要被讀，整個 8KB 的 page 都會被讀進記憶體，SELECT id 跟 SELECT *（同一筆 row）的 I/O 成本幾乎相同（差別只在 Executor 從記憶體裡的 page 取出欄位時，處理的資料量不同，但那已經是記憶體操作而不是 I/O 操作）；真正能省 I/O 的是「少讀幾個 page」，這才是 Index（T-026）與 Covering Index 存在的意義——用另一棵更小的 B-Tree，只存查詢真正需要的欄位，讓一次查詢碰到的 page 數量下降。

Backend Applications：Backend 工程師設計 table schema 時，「一筆 row 有多寬（多少 bytes）」直接影響「一個 Page 能塞幾筆」，進而影響「掃描同樣筆數需要讀幾個 page」。把一個很少被查詢、但很肥大的欄位（例如一段 JSON blob 或長文字）跟其他常查欄位放在同一張表、同一個 row 裡，會讓每個 page 能塞的「常查欄位」筆數變少，即使那個大欄位從來不在 WHERE/SELECT 裡出現，也會拖慢其他欄位的掃描效率——這是實務上把「冷欄位」拆到另一張表（或用 PostgreSQL 的 TOAST 機制對超大欄位另外存放）的直接理由。

陌生題示範：情境——一張 events 表，每筆 row 平均 200 bytes，目前有 4,000,000 筆資料，做一次不用 index 的全表掃描（SELECT count(*) FROM events WHERE payload LIKE '%error%'，payload 沒有 index，非得整表掃過一遍不可）。請推導這次掃描大約要讀幾個 Page。推導：可用 Page 空間約 8192 - 200(header/pointer 開銷) ≈ 7992bytes，每個 Page 能塞 7992 / 200 ≈ 39 筆；總共需要的 Page 數4,000,000 / 39 ≈ 102,564 個 page；若磁碟一次隨機 I/O 約 5ms（機械硬碟量級，SSD 會快很多但仍非 0），且 page 之間若不連續則接近隨機 I/O，理論上界約 102,564 × 5ms ≈ 512.8 秒——這個數字說明了「全表掃描的成本跟資料筆數與 row 大小直接相關，而且是 page 數量而不是 row 數量在決定 I/O 次數」，也解釋了為什麼 Day 33–35 要學的 B-Tree（能把「找到符合條件的資料」從「碰到幾乎所有 page」降到「碰到 log 量級的 page」）對這種查詢有數量級的改善空間。

What：Disk（或 SSD）是資料真正永久存放的地方，容量大但存取慢（相對 CPU/記憶體速度而言，隨機讀取一個 page 可能要數毫秒）；Memory（RAM）容量小但存取快（微秒甚至奈秒級）；Buffer Pool是資料庫程序在啟動時向作業系統要來的一塊記憶體區域，專門用來存放「最近被讀過或寫過的 Page」，扮演 Disk 與執行查詢的 Executor 之間的Cache——本質上跟 CPU 的 L1/L2 cache 是同一種設計哲學：把「最近可能還會再用到的資料」留在快速的地方，避免每次都付出慢速媒介的成本。

Failure Modes：一個常見的錯誤心智模型是以為「重開資料庫程序後，第一次查詢就會跟平常一樣快」——實際上重開後 Buffer Pool 是空的（cold cache），前幾次查詢會全部觸發 disk I/O 直到 Buffer Pool 重新被「熱身」（warm up）填滿熱門 page，這段期間查詢延遲會明顯比平常高，這也是為什麼線上資料庫重啟後常常會看到短暫的延遲尖峰，跟程式碼本身有沒有 bug 無關。另一個失效模式是「一次性的大範圍掃描（例如跑一個報表查詢，掃過整張很大的表）會把 Buffer Pool 裡原本的熱門 page 全部擠出去」——PostgreSQL 對大範圍循序掃描有專門的ring buffer 機制緩解這個問題（大掃描只佔用一小圈固定的 buffer slot 循環使用，不會佔滿整個 Buffer Pool），但如果你自己在應用層實作類似快取機制，沒有這種保護，一次背景報表查詢就可能拖垮線上其他查詢的 cache hit rate。

Backend Applications：EXPLAIN (ANALYZE, BUFFERS) 輸出裡的shared hit=X read=Y 就是在報告這次查詢命中 Buffer Pool 幾次（hit）、觸發真正 disk I/O 幾次（read）——這是診斷「這句查詢慢，是因為資料庫邏輯設計得差，還是純粹因為 cold cache」最直接的證據：同一句 SQL 第一次跑 read 很高、第二次馬上重跑變成幾乎全部hit，說明問題只是暫時的 cold cache，而不是查詢本身有結構性問題。Backend 工程師在做效能調查時，看到「同一句查詢時快時慢」，第一個該檢查的就是 Buffer Pool 的命中率，而不是急著改 SQL 邏輯。

陌生題示範：情境——資料庫伺服器有 4GB 記憶體分配給 Buffer Pool，但你的 orders 表加上其索引總大小是 20GB，且查詢分佈幾乎均勻（沒有明顯的熱門資料，每筆 order 被查到的機率差不多）。請推導這種情況下 Buffer Pool 能帶來多少幫助，以及你會建議哪個方向調查。推導：因為查詢均勻分佈、且工作集（20GB）遠大於 Buffer Pool（4GB），理論上任何時刻 Buffer Pool 只能留住整體資料的 4/20 = 20%，且因為沒有「熱門/冷門」之分，被換出去的 page 跟被查詢到的 page 出現機率相同，長期下來 cache hit rate 會趨近 20% 而不是更高——這代表「加大 Buffer Pool」在均勻分佈下的邊際效益是線性的（Buffer Pool 翻倍、hit rate 大致也翻倍，直到 Buffer Pool 大到能裝下整個工作集），不像有明顯熱門資料時「稍微加大就能大幅提升命中率」。建議調查方向：(1) 檢查記憶體是否還有餘裕加大 shared_buffers；(2) 檢查查詢是否真的需要均勻存取全部 20GB，或者可以透過分區/歸檔把很少被查的舊orders 移出主要查詢路徑，讓「實際常查的工作集」縮小到能被 4GB 完整涵蓋。

What：B-Tree 是一種自平衡的多叉排序樹：每個 node 不像 Binary Search Tree（Day 18）只存 1 個 key、最多 2 個子節點，而是存多個排序好的 key、對應多個子節點（子節點數量 = key 數量+ 1）。整棵樹分三種角色：Root（最上層，唯一的進入點）、Internal Node（中間層，只存 key 與指向子節點的指標，不存實際資料，作用是「導航」）、Leaf Node（最底層，所有實際資料或指向實際資料的指標都存在這裡，且所有 Leaf 都在同一深度——這正是「自平衡」的意思）。

Mechanism（node 大小與 Page 對齊）：B-Tree 的 node 被設計成大小貼齊一個 Page（8KB），這樣「讀進一個 node」剛好對應「讀進一個 Page」，一次 disk read 就能拿到一整個 node 的全部 key 與指標，不用分好幾次 I/O 才湊齊一個 node 的內容。假設每個 key+pointer 需要 16 bytes（8 bytes 的 bigint key + 8 bytes 的指標），扣掉約 100 bytes 的 page header 開銷，一個 8KB 的 Page 大約能存(8192-100)/16 ≈ 505 個 key+pointer——這就是前面 Why 段落「fanout 約 500」這個數字的來源，不是憑空假設，而是直接由 Page 大小、key 大小反推出來的。

Mechanism（Search）：從 Root 開始，把要找的 key 跟 Root 裡排序好的 key 陣列做比較（例如二分搜尋），找出「要找的 key 落在哪兩個 existing key 之間」，依此決定要往哪個子節點下降；到了子節點（一個 Internal Node），重複同樣的比較與下降；一路下降到 Leaf Node 後，在 Leaf 裡再做一次比較，確認找到（或確認不存在）。整個過程「每往下一層 = 一次 page read（若該 page 不在 Buffer Pool，見 Day 32）」，樹高即為最壞情況下需要的 I/O 次數上界。

Failure Modes：常見誤解是把 B-Tree 跟 Binary Search Tree 搞混，以為「B-Tree 的 B 是 Binary 的意思」——實際上 B-Tree 的 B 一般被理解為 Balanced（或其發明者 Rudolf Bayer 姓氏的字首，來源有爭議，但確定不是 Binary），核心差異就是多叉 vs. 二叉。另一個失效模式是誤以為「樹越高代表資料越亂」——B-Tree 是自平衡的，樹高只跟資料筆數與 fanout 有關，不會因為插入順序不同而長歪、變高（這點與一般未平衡的 BST 不同，未平衡 BST 若照排序好的順序依序插入，會退化成一條鏈，等同 O(n) 的 linked list）。

Backend Applications：PostgreSQL 的 primary key 與大多數CREATE INDEX 預設就是用 B-Tree（實作稱為 B+Tree，一種 Leaf 之間互相用指標串起來、方便做範圍掃描的 B-Tree 變形）；Backend 工程師每次執行 WHERE id = ? 或 WHERE created_at BETWEEN ? AND? 這類條件式查詢，只要對應欄位有 B-Tree index，就是在使用今天學的這套結構做 Search，而不是逐筆比對整張表。

陌生題示範：情境——一個 B-Tree index，fanout（分支因子）為 100，目前存了 8,000,000 筆資料。請推導樹高大約是多少，以及如果資料量成長到 8 億筆（成長 100 倍），樹高會變成多少。推導：樹高h 滿足 100^h ≈ n，即 h ≈ log₁₀₀(n)。n = 8,000,000時，log₁₀₀(8,000,000) = ln(8,000,000)/ln(100) ≈ 15.9/4.6 ≈ 3.46，無條件進位樹高約 4 層；n = 800,000,000 時，log₁₀₀(800,000,000) ≈ 20.5/4.6 ≈ 4.46，無條件進位樹高約 5 層。資料量成長 100 倍，樹高只多了 1 層——這正是 B-Tree「對數成長」的威力：搜尋成本（I/O 次數）幾乎不隨資料量線性成長，這是為什麼即使 table 成長到數十億筆，只要有合適的 index，單筆查詢的延遲仍能維持在毫秒級。

What：Insert 一筆新資料到 B-Tree，第一步跟 Search 完全一樣——從 Root 開始比較、逐層下降，找到這個新 key 應該落在哪一個 Leaf Node。找到目標 Leaf 後，把新 key 插入該 Leaf 內部正確的排序位置。如果這個 Leaf 插入後還沒滿，Insert 到此結束；如果插入後滿了（超過 node 能容納的 key 數量上限，這個上限由 Page 大小決定，見 Day 33），就會觸發 Split。

Mechanism（Split）：以一個已滿的 Leaf Node 為例——把這個 Leaf 裡所有 key（含新插入的那個）依序排好後，從正中間切成兩半，變成兩個各自約半滿的 Leaf Node；切開點的那個 key（或其副本，依 B-Tree 實作變形而定）被「往上推」給 parent node，成為 parent 裡新增的一個 key，同時 parent 也多了一個指向新 Leaf 的指標。如果 parent node 也因此被塞滿，Split 會繼續往上傳播，一路傳到 Root——如果連 Root 都滿了，會產生一個全新的 Root（原本的 Root 一分為二成為新 Root 的兩個子節點），這是 B-Tree 唯一會增加樹高的時刻，且因為是從最頂端往上加一層，所有原本的 Leaf 深度會同時增加 1，保證所有 Leaf 深度依然相同（自平衡不被破壞）。

Mechanism（Delete）：先跟 Search 一樣定位到 key 所在的 Leaf，移除該 key。如果移除後這個 Leaf 的 key 數量掉到「最低容量」以下（太空），會先嘗試向左右兄弟 node 借一個 key（如果兄弟 node key 數量夠多，可以借出一個而不會自己變太空）；如果兩邊兄弟都「借不起」，就會跟其中一個兄弟 node 合併（merge）成一個 node——這是 Split 的反向操作，同樣可能往上傳播（parent 因為少了一個指標與一個 key，也可能因此變太空，需要繼續借/合併）。

Failure Modes：一個常見的效能陷阱是大量隨機順序的 Insert（例如用 UUID 而非遞增整數當 primary key）——因為新 key 會均勻散落在整棵樹的各個角落，而不是集中在最右側（遞增 key 的情況下，新 key 永遠插在最後一個 Leaf），這會導致 Split 發生的頻率遠高於遞增 key 的情況，且新產生的 Page 在磁碟上的位置可能跟原本的 Page 不連續（page fragmentation），讓之後的循序掃描效率下降；這是為什麼許多系統設計上偏好「遞增但仍具唯一性」的 ID 生成策略（例如 Day 62 URL Shortener 會用到的 ID generation 設計），而不是直接對 primary key 使用純隨機 UUID。

Backend Applications：了解 Split/Merge 的成本，能解釋為什麼「批次 Insert（例如 COPY 或一次 INSERT 多筆）通常比逐筆 Insert 快很多」——批次操作能讓資料庫更有效率地規劃 Page 配置、減少 Split 傳播到多層的次數；也能解釋為什麼「大量匯入前先移除 index、匯完資料後再重建 index，通常比帶著 index 一邊持續 Insert/Delete、造成大量 Split/Merge 更有效率」，這是資料遷移/批次匯入時常見的操作建議（先停用或最後才建立 index）背後的原理。

陌生題示範：情境——一棵 B-Tree index，每個 node 最多容納 100 個 key（超過就 Split，低於 50 個就會嘗試跟兄弟借/合併）。目前某個 Leaf Node 剛好有 50 個 key（已在最低容量邊界），這時發生一次 Delete，移除其中 1 個 key。請推導接下來會發生什麼、以及這個影響會不會往上傳播到 parent。推導：Delete 後這個 Leaf 只剩 49 個 key，低於最低容量 50，觸發「太空」處理——先檢查左右兄弟 Leaf 是否 key 數量 > 50（借了一個之後仍不低於 50 才能借出）：若右兄弟有 70 個 key，可以借 1 個給這個 Leaf（借完後右兄弟剩 69 個，本身變 50 個，兩邊都不低於最低容量），這種情況不會往上傳播，parent 只需要更新一下分隔 key（因為兩個子節點之間的邊界 key 變了）；但若左右兄弟都剛好也在 50 個左右（借了就會讓對方也太空），就必須跟其中一個兄弟合併成一個 node（合併後約 99 個 key，未超過 100 的上限，不會立刻再觸發 Split），合併後 parent 少了 1 個指標與 1 個 key，這就會往上傳播——重複同樣的「parent 是否因此太空」判斷，最壞情況一路傳到 Root。這說明了 Delete 的攤銷成本雖然是O(log_m n)，但傳播與否高度依賴當下兄弟 node 的 key 數量分佈，無法從單一次 Delete 的操作本身預測，需要動態檢查——這正是 Day 34 開頭 Trade-off 段落強調「操作成本不均勻」的具體來源。

What：B-Tree 的高度 h 由資料筆數 n 與分支因子（fanout）m 決定：h ≈ log_m(n)（無條件進位，因為樹高必須是整數層）。Search、Insert、Delete 的時間複雜度都是 O(log_m n)——因為三種操作都需要先從 Root 走到目標 Leaf（Insert/Delete 多出的 Split/Merge 成本，在攤銷分析下也維持在 O(log_m n) 量級，見 Day 34）。空間複雜度是 O(n)（每筆資料在樹中恰好被存放/索引一次，加上 Internal Node 的額外指標開銷，量級仍是 O(n)）。

Mechanism（把 Big-O 換算成實際 I/O 次數）：O(log_m n) 這個記號本身沒有告訴你「所以到底要幾次 I/O」——要拿到具體數字，必須代入實際的 m（fanout，由 Page 大小與 key 大小決定，見 Day 33）與 n（實際資料筆數）。這正是把 Big-O 從「抽象記號」變成「可以拿來做效能預算」的關鍵一步：O(log n) 對 n = 10 跟 n = 10億在記號上「長得一樣」，但實際 I/O 次數差了好幾倍，工程決策必須看實際數字，不能只看記號。

核心問題推導：為什麼 B-Tree 適合 database index？

把 Day 31–35 學到的三件事串起來做完整推導：

1. Page（Day 31）決定了 I/O 是以固定大小區塊為單位——讀一個 node 等於讀一個 Page，這是 disk 的物理限制逼出來的設計。
2. fanout（Day 33）由 Page 大小除以 key+pointer 大小決定——8KB 的 Page、16 bytes 的 key+pointer，fanout 約 505；這代表 B-Tree 的分支因子不是憑空選的大數字，而是「一次 I/O 能帶進來多少個 key」這個物理限制直接算出來的。
3. 樹高 h ≈ log_m(n)，其中 m 是第 2 點算出來的大 fanout——把 m ≈ 500 代入：n = 1,000,000 時 h ≈ log₅₀₀(1,000,000) =ln(1,000,000)/ln(500) ≈ 13.8/6.2 ≈ 2.2，無條件進位約 3 層；n = 1,000,000,000（10 億筆）時h ≈ ln(1,000,000,000)/ln(500) ≈ 20.7/6.2 ≈ 3.3，無條件進位約 4 層。資料量從 100 萬暴增到 10 億（1000 倍），樹高只從 3 層變成 4 層。

對照：若改用 Day 31 算過的「全表掃描」（沒有 index），n = 1,000,000,000 筆、每筆 200 bytes，需要讀的 page 數約1,000,000,000 × 200 / 8192 ≈ 24,414,062 個 page；用 B-Tree index 找同一筆資料只需要約 4 次 page I/O。4 次 I/O 對 2400 萬次 I/O——這就是「為什麼 B-Tree 適合 database index」的具體答案：B-Tree 把「fanout 由 Page 大小決定」這個硬體限制，轉換成「用極少的 I/O 次數（樹高，對數量級）就能定位到任意一筆資料」的能力，而且這個能力隨資料量增長幾乎不衰退（樹高的對數成長遠慢於資料量的線性成長）。如果沒有 Page 這個「一次 I/O 能帶進大量 key」的前提，分支因子就無法做大，B-Tree 也就不會比其他排序結構（例如 Binary Search Tree，Day 33 提過的 log₂ n）在磁碟環境下更有優勢——這正是 Day 31（Page）必須先教、B-Tree 必須排在後面的完整理由。

Trade-off / Failure Modes：見 Day 33（fanout 越大、樹越矮，但單一 node 內比較次數越多）與 Day 34（Split/Merge 的攤銷成本、隨機 key 導致的 fragmentation）——今天不重複，而是強調：這些 trade-off 全部建立在同一個前提上（node 大小 = Page 大小），只要記住這個前提，B-Tree 每一個設計決策都能自己重新推導出來，不需要死背。

Backend Applications：當你在 PostgreSQL 執行 CREATE INDEX idx_orders_user_id ON orders(user_id)，資料庫實際上是在背景建立一整棵今天描述的 B-Tree（掃過 orders 現有全部資料、依 user_id排序後，依序插入這棵樹，過程中會觸發今天 Day 34 學過的 Split）。之後每一句 WHERE user_id = ? 的查詢，都是在對這棵已經建好的 B-Tree 做 Day 33 的 Search，I/O 成本是這棵樹的高度，而不是orders 表的總筆數。

What：Index 是資料庫額外維護的一份資料結構——絕大多數情況下就是 Day 33–35 學過的 B-Tree——把某個（或某幾個）column 的值排序後另外存一份，B-Tree 的每個 Leaf 除了 key 之外還存一個指回原表（PostgreSQL 內部稱為 heap）對應那筆資料實體位置的指標（PostgreSQL 叫CTID，即 (page number, item pointer)）。Single-column Index 是最基本的形式：只針對一個 column 建一棵 B-Tree。

Failure Modes：最常見的失效是「Index 建了但沒被用到」——例如WHERE LOWER(email) = '[email protected]'，即使 email 欄位有 Index，B-Tree 裡存的是原始值（不是小寫後的值），Planner 找不到「LOWER(email)的結果」對應到哪個 Index，只能整表掃描（除非另外建CREATE INDEX ... ON users (LOWER(email)) 這種 functional index）。這說明「欄位有沒有 Index」跟「查詢條件能不能用上那個 Index」是兩件事，WHERE 條件裡對欄位做任何函式運算，都可能讓既有 Index 直接失效。

Backend Applications：Backend 工程師在設計 schema 時，判斷該對哪個欄位建 Index 的第一原則是「這個欄位常出現在 WHERE/JOIN/ORDER BY 嗎？」而不是「這個欄位重不重要」——一個從不出現在查詢條件裡的欄位，建 Index 只有維護成本、沒有任何讀取收益。

陌生題示範：情境——orders 表有 500 萬筆資料，目前只有id（primary key）有 Index，應用程式最常見的查詢是SELECT * FROM orders WHERE user_id = ?，這句查詢目前跑一次要 2.3 秒。請推導加上 CREATE INDEX idx_orders_user_id ON orders(user_id); 前後，這句查詢的 I/O 量級大約會怎麼變化，以及為什麼「加這個 Index」比「把整張表搬到更快的 SSD」更該優先做。推導：加 Index 前，這句查詢必須做 Day 31 式的全表掃描，I/O 次數正比於 orders 的總 Page 數（跟資料筆數線性相關，500 萬筆量級可能是數萬到十幾萬個 Page）；加上 Index 後，變成先對一棵針對 500 萬筆user_id 建的 B-Tree 做 Search，I/O 次數約為樹高（Day 35 算過，fanout 500 時 500 萬筆的樹高僅約 3 層），再加上少量的 heap fetch——I/O 量級從「跟總筆數線性相關」變成「幾乎常數」。換 SSD 只能讓「每次 I/O」變快幾倍，但 Index 是讓「I/O 次數」從萬級降到個位數級，數量級的改善通常遠大於硬體升級能提供的倍數改善，這正是「先看有沒有用對的 Index，再考慮砸錢升級硬體」這個工程優先順序的具體理由。

What：Composite Index（也叫 Multi-column Index）是同時對多個 column 建一棵 B-Tree，key 是這些 column 依宣告順序組成的複合值。例如 CREATE INDEX idx_orders_status_created ON orders(status,created_at); 建出的 B-Tree，其 key 排序方式是「先依 status排序，status 相同的資料再依 created_at 排序」。

Mechanism（column 順序為什麼重要）：這直接沿用 Day 33 教過的 B-Tree Search 機制——比較 key 時從第一個 column 開始比，只有第一個 column 相等時，才會繼續比第二個 column。這代表 composite index (status, created_at)：- 能高效支援 WHERE status = ?（只用第一個 column）。- 能高效支援 WHERE status = ? AND created_at > ?（先用 status 定位到一段連續範圍，範圍內再依 created_at 排序，可以直接做範圍掃描）。- 無法高效支援單獨 WHERE created_at > ?（沒有 status 條件）——因為 B-Tree 裡的 key 是先依 status 分組排序，同一個 created_at 值可能散落在依 status 分好的各個區段裡，等於要掃過整棵樹，跟沒建 Index 幾乎一樣貴。

Failure Modes：常見誤解是「只要幫常查的欄位都建進同一個 composite index 就好，不用管順序」——已經在 Mechanism 段落證明，順序錯了會讓 Index 對某些查詢完全無效，跟沒建一樣，卻仍要付出寫入維護成本，是「兩頭都沒討到好處」的浪費。

Backend Applications：Backend 工程師寫 API 分頁/篩選功能時（例如「依 status 篩選 + 依時間排序」的訂單列表 API），composite index 的 column 順序應該直接對照 API 實際的查詢參數組合來設計，而不是憑直覺把「看起來重要」的欄位排前面。

陌生題示範：情境——一個 API 的查詢模式是：90% 的請求帶WHERE status = ? ORDER BY created_at DESC LIMIT 20（依狀態篩選、依時間排序取最新 20 筆），10% 的請求帶 WHERE status = ? AND user_id = ?（同時篩狀態與使用者）。目前只有一個 composite index(user_id, status)。請判斷這個 Index 對兩種查詢模式各自有沒有幫助，並給出更適合的設計。推導：現有 (user_id, status) 對 90% 的查詢（沒有 user_id 條件）完全用不上，因為 B-Tree 是先依 user_id 分組，沒有 user_id 條件等於要掃過所有分組；對 10% 的查詢（同時有 user_id 與 status）則可以正常使用。應該改成 (status, created_at)：90% 的查詢能靠 status 定位到一段連續範圍，範圍內的 key 已經依created_at 排序好，ORDER BY created_at DESC LIMIT 20 甚至可以不用額外排序（Index 本身順序就對）直接取前 20 筆；10% 的查詢（多了 user_id 條件）雖然這個新 Index 幫不上 user_id 這個條件，但可以先靠 status 縮小範圍，再讓 Executor 對縮小後的結果逐筆比對user_id——比原本「完全掃不到」好得多，且服務了佔比更高的 90% 查詢模式，是這個情境下更划算的取捨。

What：Covering Index 是「Index 本身就包含這次查詢需要的所有欄位，不需要再回頭去 heap 讀一次」的 Index。PostgreSQL 用 INCLUDE 語法把非搜尋用（不參與比較排序，只是「順路存起來」）的欄位一併存進 Index 的 Leaf。

Failure Modes：即使建了 Covering Index，Index Only Scan 仍可能在某些情況下退化回要查 heap——PostgreSQL 用 Visibility Map 追蹤「這個 Page 裡的資料是否所有 transaction 都看得到（沒有正在進行中的、尚未 vacuum 清理的舊版本）」，若對應 Page 的 visibility map 沒有標記為「all visible」（常見於這個 Page 剛被大量更新、還沒跑過VACUUM），即使 Index 裡有需要的欄位，Executor 仍必須去 heap 確認這筆資料在目前這個 transaction 的 MVCC snapshot（Day 38 展開）下是否可見，等於白白多存了一份欄位卻沒有拿到 Index Only Scan 的好處。

Backend Applications：列表類 API 常只回傳固定的少數幾個欄位（例如訂單列表只要 amount、created_at），而且查詢頻率高，這是 Covering Index 最適合的場景；反過來，如果查詢的欄位組合常常變動，或這張表寫入/更新頻繁（Visibility Map 常失效），INCLUDE 多存的欄位往往換不到 Index Only Scan，只是白白增加 Index 體積與維護成本，所以建完之後要用 EXPLAIN 確認計畫節點真的變成 Index Only Scan，再決定要不要保留。

What：Partial Index 是「只對符合某個 WHERE 條件的子集資料建 Index」，而不是對整張表全部資料建 Index。

Failure Modes：查詢條件寫法跟 Partial Index 的 WHERE子句「邏輯等價但字面不同」時，Planner 未必能推導出兩者等價，因而放棄使用該 Index，改成完整 Seq Scan 甚至報告找不到合適的 Index 可用——這是 Partial Index 比一般 Index 更容易踩到的陷阱，寫查詢時最好讓條件盡量貼近 Index 定義時的字面寫法。

Backend Applications：背景 worker 反覆輪詢「待處理」的資料（例如只撈 status = 'pending' 的訂單或任務）是 Partial Index 的典型用途：輪詢查詢只需要碰一棵很小的 Index，佔絕大多數的已完成歷史資料完全不用付出 Index 空間與寫入維護成本。實作時要記得，經由 ORM 產生的 SQL 也一樣要讓 WHERE 條件的字面寫法貼近 Index 定義，否則 Planner 用不上這個 Index。

What：Cardinality 是一個欄位有多少個不同的值（distinct values）；Selectivity 是某個查詢條件篩選後，預期剩下的資料佔全體的比例——Selectivity 越低（篩掉的越多），代表這個條件的篩選力越強。在資料分布大致均勻的假設下，Selectivity ≈ 1 / Cardinality（例如一個欄位有 10,000 個 distinct 值且分布均勻，篩「等於某個特定值」的條件，預期只剩 1/10000 的資料）。

Trade-off / Failure Modes：這裡的核心風險是統計過時——資料在大量 INSERT/UPDATE/DELETE 後，pg_stats 裡的數字若沒有重新 ANALYZE，會跟實際資料分布脫節。例如一個欄位原本值很分散（n_distinct 很大），但業務邏輯改變後大量新資料都集中在少數幾個值上，若統計沒更新，Planner 仍依照舊的（過度樂觀的）Selectivity 估算去選 Index Scan，實際執行時卻要對大量結果逐一做 heap fetch，造成「EXPLAIN 估計很快、實際跑起來很慢」的落差——這正是為什麼EXPLAIN ANALYZE（會真的執行一次並記錄實際 rows/time）比純EXPLAIN（只給估計值）更能診斷這類問題。

Backend Applications：Backend 工程師在大量批次匯入/刪除資料後，應該主動執行 ANALYZE table_name;（而不是被動等 autovacuum 排程觸發），確保接下來的查詢 Planner 拿到的是新鮮的統計資訊，尤其是資料分布劇烈改變（例如批次清空重灌）的場景。

陌生題示範：情境——products 表有 200 萬筆資料，category_id欄位 pg_stats 顯示 n_distinct = 50（只有 50 種分類），sku（商品編號）欄位 n_distinct 接近 2,000,000（幾乎每筆都不同）。請判斷 WHERE category_id = ? 與 WHERE sku = ? 這兩種查詢，Planner 各自比較可能選哪種 scan，並解釋為什麼即使兩個欄位都建了 Index，判斷結果會不一樣。推導：category_id 的 Selectivity 約1/50 = 2%，篩選後仍剩約 2,000,000 × 2% = 40,000 筆——這代表要做 4 萬次 heap fetch（大部分是隨機 I/O），成本可能高於直接 Seq Scan 一次循序讀完整張表；sku 的 Selectivity 約 1/2,000,000，篩選後幾乎只剩 1 筆，Index Scan 只需要 Day 35 算過的樹高次數 I/O 再加 1 次 heap fetch，遠比 Seq Scan 便宜。同一張表、都建了 Index，Planner 卻可能對 category_id 選 Seq Scan、對 sku 選 Index Scan——這說明「有沒有 Index」跟「這次查詢實際會不會用到 Index」是兩個獨立的問題，後者完全取決於 Selectivity。

What：Index Scan 是沿著 Day 33 學過的 B-Tree Search 找到符合條件的 CTID 再逐一 heap fetch；Sequential Scan 是 Day 31 學過的「把整張表的每個 Page 循序讀一遍」。Planner 對同一句查詢，會分別估算兩種（甚至更多種，例如 Bitmap Index Scan）計畫各自的 cost，選 cost 最低的那個執行。

Failure Modes：常見誤解是「EXPLAIN 顯示 Seq Scan 就代表 Index 沒生效/壞掉」——多數時候這是 Planner 依照 Selectivity 與 cost 模型做出的正確選擇（例如篩選力很差的條件，或整張表本來就很小、Seq Scan 幾頁就讀完，Index 反而多一層開銷）。診斷時應該先確認 Selectivity 與資料量，而不是預設「沒用到 Index 就是壞掉」。

Backend Applications：Backend 工程師若在同一台機器上把資料庫從機械硬碟遷移到 SSD，應該同時檢查並調整 random_page_cost（例如從預設 4.0 調到 1.1），否則 Planner 仍照舊的成本模型運作，可能在新硬體上做出並非最優的計畫選擇。

陌生題示範：情境——一張 100 萬筆資料的表，WHERE status = ?的 Selectivity 是 30%（篩選後剩 30 萬筆），已建 Index。請判斷 Planner 比較可能選 Seq Scan 還是 Index Scan，並說明如果把random_page_cost 從 4.0 調到 1.1（模擬換成 SSD），判斷會不會改變。推導：Selectivity 30% 代表要對 30 萬筆做 heap fetch，用random_page_cost = 4.0 計算，Index Scan 的 cost 大約是300,000 × 4.0 = 1,200,000（樹高部分相對很小可忽略），而 Seq Scan 掃完整張表（假設約 10,000 個 page）的 cost 大約是10,000 × 1.0 = 10,000——Index Scan 貴了 100 倍以上，Planner 會選 Seq Scan；把 random_page_cost 調到 1.1，Index Scan 的 cost 變成 300,000 × 1.1 = 330,000，仍遠高於 Seq Scan 的 10,000，判斷不會改變——因為問題根源是 Selectivity 太差（30% 太高），不是硬體隨機讀取成本，這說明「換 SSD」對 Selectivity 差的查詢幫助有限，真正該做的是重新設計查詢或索引（例如改用更能篩選的複合條件），而不是只調整成本參數。

What：EXPLAIN 顯示 Planner 估計的執行計畫（不會真的執行查詢），輸出每個計畫節點的 cost=startup_cost..total_cost（開始輸出第一筆結果的估計成本、輸出全部結果的估計成本）、rows（估計會輸出幾筆）、width（估計每筆平均多寬）。EXPLAIN ANALYZE 會真的執行這句查詢，額外印出 actual time=startup..total（實際耗時）與 actual rows（實際輸出筆數），讓你能對照「Planner 猜的」跟「實際發生的」差多少。加上 BUFFERS選項（EXPLAIN (ANALYZE, BUFFERS)）還會顯示 Buffers: shared hit=X read=Y（Day 32 學過的 Buffer Pool 命中/未命中次數）。

Mechanism（Planner Decision 的完整流程）：Planner 針對一句查詢，會先列出所有邏輯上可行的計畫（例如「Seq Scan」「Index Scan using idx_a」「Index Scan using idx_b」「Bitmap Index Scan 合併 idx_a 與 idx_b」……），依 pg_stats 的統計資訊（Selectivity/Cardinality）與 seq_page_cost/random_page_cost 等成本參數，分別估算每個計畫的 total_cost，選 total_cost 最低的那一個執行——這是一個窮舉＋比價的過程，不是規則式的「有 Index 就用」。

Failure Modes：rows（估計）與 actual rows（實際）差距過大，是「統計過時」或「Planner 對複雜條件的關聯性估算不準」最直接的證據——例如 WHERE a = 1 AND b = 2，若 Planner 假設 a 與 b互相獨立各自估算 Selectivity 再相乘，但實際上 a 與 b 高度相關（例如 a 決定了 b 的值），估計出來的筆數會遠低於實際筆數，導致 Planner 選錯計畫。PostgreSQL 較新版本支援CREATE STATISTICS 手動告訴 Planner「這兩個欄位有關聯，不要假設獨立」，緩解這類問題。

Backend Applications：Backend 工程師在做慢查詢診斷時，標準第一步永遠是 EXPLAIN (ANALYZE, BUFFERS)，比對 rows 與actual rows、Buffers: hit 與 read 的比例，先確認問題出在「Planner 估計錯誤」還是「cold cache（Day 32）」還是「Selectivity 本來就差、無法用 Index 解決」，再決定該加 Index、改寫查詢、還是重新設計 schema。

陌生題示範：情境——EXPLAIN ANALYZE 顯示某句查詢的計畫節點估計 rows=50，但 actual rows=48000，執行時間比 Planner 估計的多了 20 倍。請推導最可能的原因與該做的下一步。推導：估計筆數與實際筆數相差近 1000 倍，代表 Planner 對這個條件的 Selectivity 估算嚴重失準——最可能的原因是相關欄位的統計過時（大量資料變動後沒重跑 ANALYZE），或者查詢用了多個彼此相關但被當獨立估算的條件（前述 Failure Modes 的關聯性問題）。下一步應先執行ANALYZE table_name; 重新收集統計後再跑一次同樣的EXPLAIN ANALYZE，若估計值大幅貼近實際值，證實是統計過時；若仍差很多，則進一步用 CREATE STATISTICS 告訴 Planner 欄位間的關聯性，而不是直接調整成本參數（那治標不治本）。

What：當一句查詢需要合併兩張表（或兩個中繼結果）的資料時，Planner 在 Nested Loop Join、Hash Join、Merge Join三種演算法之間選擇一種來實際執行 join。三者的核心差異在於「怎麼找出兩邊符合 join 條件的配對」：Nested Loop 對外層（outer）的每一筆資料，逐一去內層（inner）找配對；Hash Join 先把其中一邊（通常是較小的一邊）在記憶體裡建成一個以 join key 為索引的雜湊表，再用另一邊逐筆探測（probe）；Merge Join 要求兩邊都已經依 join key 排序，像合併兩份排好序的清單一樣同步往前走，一次配對完成。

• Nested Loop Join：對 outer 端的每一列，掃一次 inner 端找符合 join 條件的列。若 inner 端在 join key 上有可用 index（Index Scan），每次 inner 查找的成本接近 Day 35 算過的 B-Tree 樹高 +heap fetch，總成本大致是 outer_rows × (單次 inner index lookup 成本)；若 inner 端沒有可用 index，每次 inner 查找都要整個 Seq Scan 一次 inner 表（除非 Planner 插入一個 Materialize 節點，把 inner 端結果快取起來避免對同一份資料重複做實體 I/O），成本趨近outer_rows × inner_table_size，outer 稍大就會非常昂貴。
• Hash Join：分兩階段。Build 階段：把兩邊裡 Planner 依統計判斷較小的一邊完整讀一次，依 join key 算雜湊值建成記憶體內的雜湊表。Probe 階段：讀另一邊（較大的一邊）每一列，算同樣的雜湊值去雜湊表裡查有沒有配對。兩階段各自只需要把對應的 relation 完整讀過一次，總成本大致是 build_rows + probe_rows（線性量級），不會因為 outer 筆數變多而重複觸發大量個別查找。Hash Join 只能用在 join 條件是等值比較（=）的情況，因為雜湊表只能做等值查找。
• Merge Join：前提是兩邊資料都已經按 join key 排序好——可能是本來就用 Index Scan 依序讀出（B-Tree index 本身依 key 排序），或者 Planner 額外插入一個 Sort 節點先排序。排序完成後，兩個指標各自從頭往後走：比較目前兩邊指標的 key，相等就輸出配對並視情況移動指標、不相等就移動 key 較小的那一邊的指標，像合併排序（merge sort）的合併步驟一樣，整個過程只需要各自掃過一次（若本來就排序好），成本同樣是線性量級；但如果任一邊需要額外 Sort，Sort 本身的成本是 O(n log n)（大量資料排序若超出可用記憶體，會退化成外部排序，牽涉暫存磁碟 I/O，這部分成本可能抵銷掉 Merge Join 本身的優勢）。

Failure Modes：常見誤解是「join 慢就是缺 index」，但如果 Planner 選的是 Hash Join，效能瓶頸可能出在可用記憶體太小導致雜湊表放不下、被迫多輪 batch（EXPLAIN ANALYZE 的 Hash 節點會顯示Batches 數字，大於 1 就代表發生了這種退化，需要更多磁碟 I/O 才能完成）；另一個常見錯誤是誤以為 Nested Loop 一定是最差的演算法而人為避免它——但當 outer 端經過前面的 WHERE 條件篩選後只剩極少筆、且 inner 端有可用 index 時，Nested Loop 反而是三者裡最便宜的，強迫 Planner 改用其他演算法在這種場景下反而更慢。

Backend Applications：Backend 工程師在診斷慢查詢時，看到EXPLAIN ANALYZE 顯示 Hash Join 且 actual time 遠高於預期，第一件事該檢查 Hash 節點的 Batches 是否 >1；看到 Nested Loop 但 outer 端 actual rows 意外地大（原本預期篩選後應該很少筆，但統計過時或條件寫錯導致篩不掉），代表 Planner 是基於錯誤的（過小的）rows 估計選了 Nested Loop，實際執行卻要做大量個別 inner 查找——這也呼應本天前段反覆強調的重點：join 演算法的選擇，跟 Index Scan vs Seq Scan 一樣，完全依賴 Selectivity/Cardinality 統計是否新鮮。

陌生題示範：情境——orders 表 500 萬筆，order_items 表 3,000 萬筆，兩表分別在 orders.id（primary key）與order_items.order_id 上有 B-Tree Index。查詢：

``sql SELECT * FROM orders o JOIN order_items oi ON o.id = oi.order_id WHERE o.status = 'pending';``

status = 'pending' 的 Selectivity 使篩選後 orders 只剩 200 筆，order_items 沒有其他過濾條件。請推導 Planner 較可能選哪種 join 演算法，並說明如果拿掉 WHERE o.status = 'pending'（篩選後的orders 變成全部 500 萬筆都要 join），Planner 的選擇會不會改變、為什麼。

推導：有 filter 時，outer 端（篩選後的 orders）只剩 200 筆，這正是 Nested Loop 的理想場景——對這 200 筆各自用 order_items.order_id的 index 做一次 Index Scan 查找配對列，總成本大致是200 × (樹高 + 少量 heap fetch)，遠低於把 3,000 萬筆的order_items 完整讀一次（Hash Join 至少要對較大的一邊做一次 Seq Scan）；Planner 會選 Nested Loop Join（inner 走 Index Scan）。拿掉 filter 後，outer 端變成全部 500 萬筆都要 join，若仍用 Nested Loop，代表要對 order_items 的 index 做 500 萬次個別 lookup——依本天前段的 Selectivity/cost 邏輯，這麼多次個別隨機 I/O 的總成本會遠超過「把兩表各自完整讀一次」；這時 Hash Join 更划算：用 orders（500 萬筆）當 build 端建雜湊表、order_items（3,000 萬筆）當 probe 端各自只需被完整讀取一次，總成本量級是O(5,000,000 + 30,000,000)，遠比 500 萬次個別 index lookup 便宜。因此拿掉 filter 後，Planner 的選擇會從 Nested Loop 換成 Hash Join——outer 端筆數越大，Nested Loop 的個別 index lookup 次數就越多，超過某個交叉點後，「兩邊各自完整讀一次再雜湊比對」的 Hash Join 反而更便宜，這跟 Selectivity 越差、Index Scan 越不划算是同一套「筆數推高單一策略的重複成本」的邏輯。

What：work_mem 是一個 PostgreSQL 設定值，限制單一Sort（例如 ORDER BY、DISTINCT、Merge Join 前置排序）或 Hash（例如 Hash Join 的 build 端、Hash Aggregate）操作可以使用的記憶體上限——注意是「每一個運算節點」各自的上限，不是整個查詢、更不是整個連線的總量。

Failure Modes：測試環境資料量小、Sort/Hash 都留在記憶體內、效能正常；上線後資料量成長，同一句查詢開始溢出到磁碟，actual time 明顯變慢但 Planner 選的計畫形狀（Seq Scan/Index Scan、Sort 節點）完全沒變——這種「計畫沒變、但突然變慢」的情況，work_mem不足導致 Disk Spill 是常被忽略的排查方向之一。另一個常見誤區是只看伺服器整體記憶體用量很低就排除 work_mem 問題，卻沒發現真正會被記憶體榨乾的情況只發生在流量尖峰、多個連線同時跑重查詢的瞬間，平常監控看到的是低估的平均值。

Backend Applications：Backend 工程師遇到單一句已知很吃記憶體的查詢（例如夜間報表的大排序），比起冒風險全域調高 work_mem，更安全的做法是只針對這個 session/這句查詢用SET work_mem = '256MB'; 提高上限，執行完這個連線結束後設定就失效，不影響其他一般查詢的記憶體配額。

可執行練習：沿用 Day 36 建立的 bench_orders2（50 萬筆），對一個沒有 index 可用、必須真的排序的欄位（user_id）跑排序，比較預設work_mem 與調高後的差異：

```sql SHOW work_mem; -- 確認目前設定（未特別調整過通常是預設 4MB）

EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM bench_orders2 ORDER BY user_id;-- user_id 沒建 index，Planner 必須先取出全部 50 萬筆再排序，-- 預期在預設 work_mem（4MB）下這筆資料量會超過上限，Sort 節點顯示-- Sort Method: external merge Disk: XXXkB

SET work_mem = '64MB'; -- 只在本 session 生效，調高到足以放下整批排序資料

EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM bench_orders2 ORDER BY user_id;-- 預期 Sort 節點改顯示-- Sort Method: quicksort Memory: XXXkB-- 對照兩次的 actual time 與 Buffers read 數字差異```

1. CTE Materialization（PostgreSQL 12+ 的行為改變）：12 之前，WITH 子句（CTE）永遠是一道最佳化屏障——Planner 一定先把 CTE 算完、暫存起來，外層查詢即使只用得到其中一小部分，也無法把外層的篩選條件推進 CTE 內部一起最佳化。12 起，非遞迴 CTE 預設改成像一般子查詢一樣可以被 inline（外層條件可以推進去），如果仍想保留舊行為（例如刻意只想讓一段昂貴運算只跑一次，不被展開重算），要明確加 MATERIALIZED 關鍵字：WITH cte AS MATERIALIZED (...)。踩坑例子：一段在 PostgreSQL 11 上用 CTE 包裝、只想算一次的昂貴子查詢，升級到 12+ 後可能被 Planner 展開成多次重算（預設不再自動 materialize），效能反而變差，要補上 MATERIALIZED 才能拿回原本的行為。

1. Parallel Workers（max_parallel_workers_per_gather）：對夠大的 Seq Scan/聚合等操作，Planner 可能選擇平行計畫，用多個背景 worker process 同時處理不同資料區塊，最後由 Gather（或Gather Merge）節點收攏結果；max_parallel_workers_per_gather決定單一查詢節點最多能配到幾個 worker。踩坑例子：調高這個值不保證查詢變快——資料量太小的表 Planner 根本不會選平行計畫（平行化本身有 worker 啟動與結果彙整的固定開銷，小資料量划不來），而且整個系統實際可動用的 worker 數還受 max_worker_processes/max_parallel_workers 這兩層更上面的全域上限節制，單一查詢的上限調再高也拿不到超過全域剩餘額度的 worker；設成 0 則是常見的除錯手法，用來確認某次效能差異是不是平行化造成的。

1. JOIN 語意改寫（LEFT JOIN + IS NULL vs NOT EXISTS → Anti Join）：「找出 A 表裡沒有出現在 B 表的資料」常見的兩種寫法——LEFT JOIN B ON ... WHERE B.id IS NULL 與WHERE NOT EXISTS (SELECT 1 FROM B WHERE ...)——邏輯上等價，PostgreSQL Planner 通常都能把兩者最佳化成同一種底層節點（Anti Join：outer 端每一列只要在 inner 端找到至少一個符合的列就整列排除，不需要真的列出所有符合的 inner 列）。踩坑例子：當 B 表的 join 欄位本身可能是 NULL 時，LEFT JOIN ... WHERE B.id IS NULL 在某些條件組合下會因為三值邏輯（NULL 比較結果既非真也非假）算出跟預期不同的結果；NOT EXISTS 對這種情況通常更直覺、不容易寫錯，這也是多數風格指南建議優先用 NOT EXISTS的原因，即使兩者常被 Planner 最佳化成同一種執行計畫。

在展開 MVCC 之前，先建立一個完全自足、不需要 Phase 05（Concurrency,Day 41–50）任何詞彙的情境：想像兩個完全獨立的應用程式請求，幾乎同時對同一張 accounts 表的同一筆資料（id = 1，balance = 100）發出操作——請求 A 要執行「查詢目前餘額，扣款 30」，請求 B 要執行「查詢目前餘額，扣款 50」。如果資料庫沒有任何機制保護，可能發生：A 讀到 balance = 100，還沒寫回，B 也讀到 balance = 100（因為 A 還沒寫回），B 算出 100 - 50 = 50 並寫回；接著 A 算出100 - 30 = 70 並寫回——最終 balance 變成 70，而不是正確的100 - 30 - 50 = 20。B 的扣款憑空消失了。Transaction（交易）是資料庫用來防止這類問題的抽象邊界：把「讀取餘額」到「寫回新餘額」包成一個不可分割的單位，資料庫保證同一時刻只有一個 transaction 真正「看得見」某個中間狀態，或至少提供明確、可調整的規則（Day 39 的 Isolation Level）去限制這種交錯可能造成的後果。這個「多個操作同時讀寫同一筆資料」的現象，本身不需要任何程式語言層級的執行緒/goroutine 知識就能理解——它是資料庫這個共享資源天生就會遇到的問題，跟 Phase 05 之後要學的「同一個程式內部多個 goroutine 互相搶記憶體」是同一個抽象問題在不同層級的重現，但這裡先只在資料庫這一層把它講清楚。

What：MVCC 是 PostgreSQL（以及多數現代資料庫）用來處理上述併發問題的核心機制：不是靠鎖住整筆資料擋住其他人，而是「每次UPDATE 不覆寫原本的資料，而是新增一個帶版本資訊的新版本（Version）」，每個 transaction 開始時拿到一份快照（Snapshot），只能看到快照當下「已經確定提交」的版本，藉由這種「各自看各自的版本」設計，讓讀取完全不需要等待寫入（也不會被寫入擋住），寫入也不需要等待讀取。

Mechanism（Version）：PostgreSQL 裡每一筆資料的實體列（heap tuple）都帶有兩個隱藏欄位：xmin（建立這個版本的 transaction ID）與 xmax（讓這個版本失效的 transaction ID，尚未被任何後續操作取代時為空/0）。UPDATE 在 PostgreSQL 的實作裡，實際上是「把舊版本的 xmax 設成目前這個 transaction 的 ID（標記舊版本失效），同時插入一個新版本，新版本的 xmin 是目前這個 transaction 的 ID」——舊版本並沒有被立刻刪除，只是被標記「對之後的 transaction 不再可見」，之後由 VACUUM（Day 36 Covering Index 段落提過的 visibility map 維護機制之一）背景清理真正回收空間。

Mechanism（Snapshot 與 Visibility）：每個 transaction 開始時（依 Isolation Level 不同，時機略有差異，Day 39 展開），會記下「目前哪些 transaction 已經提交、哪些還在進行中」這份資訊，成為它的 Snapshot。之後這個 transaction 讀任何一筆資料的任何一個版本時，都要做 Visibility 判斷：這個版本的 xmin 對應的 transaction，在我的 Snapshot 裡是否已經提交？如果還沒提交（或xmin 就是我自己這個 transaction 但操作發生在目前這個查詢之後），這個版本對我不可見；如果這個版本的 xmax 已經被設定且對應的 transaction 已提交，代表這個版本已經被取代，同樣不可見。只有「xmin 對應的 transaction 已提交、且 xmax 為空或對應的 transaction 尚未提交」的版本，才是這個 Snapshot 看得到的正確版本。

Failure Modes：最常見的營運問題是長時間開著不提交的 transaction（例如應用程式忘記 commit、或一個互動式 session 開了 transaction 後掛著不動）——因為 MVCC 必須保留「這個 transaction 的 Snapshot 可能還需要看到」的所有舊版本，這類長 transaction 會讓VACUUM 無法清理大量本該回收的舊版本，久了造成表與 Index 大幅膨脹、查詢變慢；診斷時可以查pg_stat_activity 裡 xact_start 很久以前、state 仍是idle in transaction 的連線，找出並終止它們。

Backend Applications：Backend 工程師寫應用程式碼時，若使用連線池（connection pool）搭配 ORM 的 transaction 管理，最容易犯的錯誤就是「開了 transaction 之後在裡面做一個很慢的外部 API 呼叫」——外部呼叫拖多久，這個 transaction 就開多久，直接影響上面提到的表膨脹問題；正確做法是把「跟資料庫無關的慢操作」移到 transaction 之外執行。

陌生題示範：情境——Transaction T1（ID=100）在時間點 A 讀取accounts 表 id=1 這筆資料，此時看到的版本是 xmin=90,xmax=NULL, balance=100。在 T1 讀取之後、還沒提交之前，另一個 Transaction T2（ID=101）執行 UPDATE accounts SET balance = 50 WHERE id = 1; 並且已經提交。請推導：如果 T1 在提交前，於同一個 transaction 內再讀一次同一筆資料，會看到 balance是 100 還是 50？（提示：這正是 Day 39 要展開的 Isolation Level 差異來源）推導：這個答案取決於 T1 使用的 Isolation Level——若是 Repeatable Read（或更嚴格的 Serializable），T1 的 Snapshot 是在 transaction 一開始就固定的，即使 T2 之後提交了新版本（xmin=101 的新版本 balance=50），這個新版本對 T1 的 Snapshot 而言「xmin 對應的 transaction（101）在我的 Snapshot 建立時還沒提交」，仍然不可見，T1 兩次都會讀到 balance=100；若是 Read Committed（PostgreSQL 預設），T1 的 Snapshot 是每一句語句（而非整個 transaction）開始時才重新建立，第二次讀取會拿到一個新的 Snapshot，這時 T2 已提交，T1 第二次讀取會看到balance=50——同一個 transaction 內兩次讀到不同結果，這正是 Day 39 要學的 Non-repeatable Read 現象，MVCC 的 Version/Snapshot/Visibility 機制正是讓「該不該看到別人已提交的新版本」這件事，可以透過切換 Isolation Level 精確控制，而不是寫死的行為。

What：Isolation Level 是資料庫提供的一組「可以選擇要多嚴格」的規則，決定一個 transaction 執行時能不能看到其他同時進行的 transaction 造成的中間狀態。ANSI SQL 標準定義四個等級，嚴格程度遞增：Read Uncommitted（最寬鬆，理論上能看到別人尚未提交的資料）→ Read Committed（只能看到已提交的資料，但同一個 transaction 內每句語句可能看到不同的已提交狀態，即 Day 38 陌生題展示的行為）→ Repeatable Read（整個 transaction 期間看到的是同一份固定的 Snapshot，同一筆資料兩次讀到的結果保證一致）→Serializable（最嚴格，保證多個 transaction 同時執行的最終結果，等同於它們依某種順序依序執行的結果，不會出現任何交錯導致的異常）。

Mechanism（PostgreSQL 實際行為，與 ANSI 標準的落差）：PostgreSQL 只實作了三種行為（雖然 SQL 語法上可以宣告READ UNCOMMITTED，但 PostgreSQL 內部會把它當成READ COMMITTED 處理——PostgreSQL 的 MVCC 設計本質上就不可能讀到未提交的資料，因為 Visibility 判斷（Day 38）明確要求xmin 對應的 transaction 必須已提交）：Read Committed（預設）、Repeatable Read、Serializable（PostgreSQL 用一種稱為Serializable Snapshot Isolation, SSI 的技術實作，用「偵測到可能違反 Serializable 保證的交錯」時讓其中一個 transaction 失敗、要求應用程式重試，而不是真的把所有 transaction 排成一個佇列依序執行）。

Failure Modes：常見誤解是「Isolation Level 設得越高，效能一定越差」——這不完全準確：Repeatable Read/Serializable 在低衝突的工作負載下（大部分 transaction 存取的資料範圍不重疊），額外成本很小；真正的效能代價集中在高衝突場景（大量 transaction 搶同一小批資料），這時 Serializable 的重試率會明顯上升。診斷效能問題時應該先確認實際衝突率，而不是先入為主認定「調低 Isolation Level 一定變快」。

Backend Applications：Backend 工程師選擇 Isolation Level 時，應該先問「這個操作牽涉幾筆資料、彼此有沒有關聯性、錯誤的中間狀態會造成多嚴重的業務後果」——單筆、無關聯的操作用預設的Read Committed 通常足夠；牽涉多筆資料且彼此有業務邏輯關聯（例如轉帳的兩個帳戶餘額）才需要考慮 Repeatable Read 或Serializable，並準備好處理重試邏輯。

陌生題示範：情境——Transaction T1 執行SELECT count(*) FROM orders WHERE status = 'pending';，得到 100 筆。在 T1 尚未提交前，Transaction T2 INSERT 了一筆新的 pending 訂單並提交。T1 在同一個 transaction 內再執行一次同樣的count(*) 查詢。請分別判斷在 Read Committed 與Repeatable Read 下，T1 第二次會不會看到 101 筆，並說出這對應哪一種 Concurrency Problem。推導：Read Committed 下，T1 的 Snapshot 每句語句都重新建立，第二次查詢會看到 T2 已提交的新增資料，得到 101 筆——這正是下一段要定義的 Phantom Read（同一個條件的查詢，兩次得到的筆數不同，多出/少了符合條件的整列，而不是同一筆資料的值改變）；Repeatable Read 下，T1 的 Snapshot 在 transaction 一開始就固定，即使 T2 已提交新資料，對 T1 而言那筆新資料的 xmin 對應的 transaction 在它的 Snapshot 建立時尚未提交，不可見，第二次查詢仍是 100 筆——PostgreSQL 的 Repeatable Read 藉由這個機制，實際上已經防止了 Phantom Read（這點跟 ANSI SQL 標準「Repeatable Read 允許 Phantom Read 發生」的定義不同，是 PostgreSQL 實作比標準更嚴格的一個具體例子）。

What：四種標準定義的異常現象——Dirty Read：讀到另一個 transaction 尚未提交、之後可能被 rollback 的資料（讀到「根本不算真的發生過」的資料）。Non-repeatable Read：同一個 transaction 內，對同一筆資料讀兩次，兩次的值不同（Day 38 陌生題展示過的現象）。Phantom Read：同一個 transaction 內，用同一個條件查詢兩次，兩次符合條件的筆數/整列不同（Day 39 前段陌生題展示過的現象）。Lost Update：兩個 transaction 各自基於同一份舊資料計算新值並寫回，其中一個的寫入結果被另一個覆蓋、憑空消失（Day 38 §0 併發存取情境定義展示的扣款消失問題正是這一種）。

Mechanism（各異常與 Isolation Level 的對應關係）：

| Isolation Level | Dirty Read | Non-repeatable Read | Phantom Read | Lost Update ||---|---|---|---|---|| Read Uncommitted（PostgreSQL 視為 Read Committed） | 標準允許，PostgreSQL 不會發生 | 允許 | 允許 | 允許 || Read Committed | 不會發生 | 允許（Day 38 陌生題） | 允許 | 允許 || Repeatable Read | 不會發生 | 不會發生 | 標準允許，PostgreSQL 實作上不會發生（Day 39 前段陌生題） | 不會發生（衝突時拋出 serialization error 要求重試，見下方 Mechanism 展開） || Serializable | 不會發生 | 不會發生 | 不會發生 | 不會發生 |

Repeatable Read 如何用 serialization error 擋下 Lost Update：T1、T2 各自的 Snapshot 都固定、互相看不到對方未提交的變更，兩者仍可能各自基於自己 Snapshot 裡的舊值算出新值，分別執行UPDATE ... SET balance = <算出來的新值>；但 PostgreSQL 在 Repeatable Read 下，若偵測到「我要更新的這一列，在我開始 transaction 之後已經被別人更新並提交」，並不會讓後到的那個 transaction 靜默地基於自己 Snapshot 裡的舊值覆蓋——而是先等前一個 transaction 完成，若它真的提交了對這一列的更動，就讓後到的 transaction 以ERROR: could not serialize access due to concurrent update 失敗，要求應用層抓住這個錯誤、重跑整個 transaction（重跑會用新的 Snapshot 讀到最新值，不會再基於過時資料計算）。這代表 Repeatable Read 實際上已經擋住了本節定義的 Lost Update（兩個 transaction 各自基於同一份舊值覆蓋同一列）；但這一層保護只到「同一列的直接寫入衝突」——如果換成跨多個列、彼此有業務邏輯關聯的 write skew（例如兩個 transaction 各自更新不同的列，兩者合起來卻違反了一個跨列的業務約束），Repeatable Read 不保證擋得住，需要真正的 Serializable（SSI）才有這一層更廣的保護。應用層自己用 SELECT ... FOR UPDATE顯式鎖定該列（見 Day 40），則是另一種完全不同的解法：讓後到的 transaction 等待前一個完成再讀取最新值，而不是走「失敗後重試」這條路。

Failure Modes：常見誤解是把 Non-repeatable Read 跟 Phantom Read 搞混——差異在於前者是「同一筆資料的值變了」，後者是「符合條件的整批資料筆數變了」；另一個常見錯誤反過來是誤以為「用了 Repeatable Read，程式完全不用額外處理 Lost Update」——上面 Mechanism 段落說明的是 PostgreSQL 靠讓後到的 transaction 失敗＋要求重試來擋下衝突，如果應用層沒有實作「捕捉 serialization error 並重跑整個 transaction」的邏輯（很多框架的預設行為是讓例外直接往上拋、請求就此失敗），使用者會看到一個原本該成功的操作報錯，只是從「資料悄悄算錯」換成「操作明確失敗」，仍然沒有真正處理好；另外，這層保護只到同一列的直接衝突，遇到跨列的 write skew 依然不保證安全，唯有 Serializable 才涵蓋這個更廣的情境（見上方 Mechanism 段落展開）。

Backend Applications：Backend 工程師實作「查詢餘額 → 計算 →寫回」這類讀取後再寫入（read-modify-write）的邏輯時，正確做法通常不是拉高 Isolation Level，而是用單一 SQL 語句完成整個操作（例如 UPDATE accounts SET balance = balance - 30 WHERE id = 1;，讓資料庫在單一原子操作內完成讀取現值與寫入，不給任何交錯空間），或明確用 SELECT ... FOR UPDATE 鎖住要更新的那一列。

陌生題示範：情境——電商系統的庫存扣減邏輯目前寫成：``sql-- Transaction 內 SELECT stock FROM products WHERE id = 1; -- 假設讀到 stock = 5-- 應用程式判斷 stock > 0，於是：UPDATE products SET stock = 4 WHERE id = 1; -- 寫回讀到的值減 1`在 Read Committed（PostgreSQL 預設）下，兩個並發請求幾乎同時執行這段邏輯，請推導可能發生的錯誤結果，並給出正確的修法。推導：兩個 transaction T1、T2 都各自讀到 stock = 5，都判斷「庫存足夠」，都各自算出「扣 1 之後應該是 4」並寫回 stock = 4——實際上應該賣出 2 件、stock 應為 3，但因為兩者都是基於同一份舊值計算，其中一個的扣減「憑空消失」，最終 stock 仍是 4，這正是 Lost Update，且會導致庫存被超賣（賣出 2 件但庫存只少了 1）。正確修法是把「讀取＋判斷＋寫回」壓縮成一句原子操作：UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0 RETURNING stock;——資料庫在單一語句內完成「檢查 stock > 0」與「扣減」，兩個並發的 UPDATE 會被資料庫序列化執行（後到的那個看到的是前一個已經寫回的新值），不會有兩者都基於同一份舊值計算的空間，若 RETURNING 沒有回傳任何列，代表 stock` 已經不足、扣減未發生，應用層可依此判斷並回報「庫存不足」。

What：Lock 是資料庫用來限制「多個 transaction 能否同時存取同一個資源」的機制，補足 MVCC（Day 38）處理不了的情況——MVCC 讓讀取不阻擋寫入、寫入不阻擋讀取，但兩個寫入互相衝突時（例如 Day 39 陌生題的庫存扣減），仍需要某種形式的互斥。Row Lock鎖定單一列，最常見的形式是 SELECT ... FOR UPDATE——明確告訴資料庫「我接下來要更新這一列，其他想對同一列做 FOR UPDATE 或UPDATE 的 transaction 請先等我」。Table Lock 鎖定整張表，範圍粗得多，通常用於 schema 變更（例如 ALTER TABLE）這類需要確保沒有其他 transaction 同時在讀寫整張表的操作。Advisory Lock 是 PostgreSQL 提供的一種「跟任何實際資料表無關、由應用程式自己定義語意」的鎖（例如 SELECT pg_advisory_lock(12345);），常用於「同一時間只允許一個背景排程/一個 worker 執行某個邏輯任務」這類跟特定資料列無關的互斥需求。

Mechanism（Deadlock，不依賴 Phase 05 定義的獨立說明）：Deadlock（死結）發生在兩個（或多個）transaction 互相等待對方已經持有的鎖、誰都無法繼續往下執行的情況。具體例子：Transaction T1 先鎖住 row A（例如對 A 執行 SELECT ... FOR UPDATE），接著想鎖 row B；同時 Transaction T2 先鎖住 row B，接著想鎖 row A——T1 在等 T2 釋放 B，T2 在等 T1 釋放 A，兩者永遠等不到對方釋放，形成循環等待。PostgreSQL 會定期執行 Deadlock Detection（檢查「誰在等誰持有的鎖」是否形成一個循環），一旦偵測到，會選擇其中一個 transaction（通常是造成循環的較晚那個）強制回滾（拋出deadlock detected 錯誤），讓另一個得以繼續執行，打破循環——這個定義完全建立在「鎖」與「等待」這兩個本節剛定義過的概念上，不需要引用 Phase 05 才會教的 Goroutine/OS thread 排程知識；後續 Phase 05 教到 Deadlock 時，會回頭引用這裡的 transaction 鎖案例作為「你已經看過一個 deadlock 實例」的複習起點<sup>1</sup>。

Failure Modes：常見錯誤是「鎖的順序不一致」——例如某段程式碼永遠先鎖 A 再鎖 B，另一段程式碼卻先鎖 B 再鎖 A，即使兩段程式碼各自看起來都合理，只要同時執行就可能形成上面 Mechanism 描述的循環等待。修正 Deadlock 問題最根本的方法通常是統一鎖的取得順序（例如永遠依 primary key 由小到大依序鎖），而不是單純依賴資料庫的 Deadlock Detection 事後補救（那只是避免系統整個卡死，transaction 仍然會失敗，需要應用層重試）。

Backend Applications：Backend 工程師實作「轉帳」這類需要同時鎖多筆資料的邏輯時，應該明確規定鎖的取得順序（例如永遠先鎖id 較小的帳戶），並且讓應用層在捕捉到 deadlock detected錯誤時自動重試（因為 Deadlock 是資料庫主動回滾其中一方造成的，重試通常能成功）；用 Advisory Lock 實作排程互斥時，也要確保所有會用到同一個 Advisory Lock key 的地方都遵循同樣的取得順序。

陌生題示範：情境——轉帳邏輯：transfer(from_id, to_id,amount) 依序執行 SELECT ... FOR UPDATE WHERE id = from_id再 SELECT ... FOR UPDATE WHERE id = to_id。應用程式同時收到兩個轉帳請求：請求 X 要「帳戶 1 轉給帳戶 2」，請求 Y 要「帳戶 2 轉給帳戶 1」，幾乎同時執行。請推導是否會發生 Deadlock，以及如何修正。推導：請求 X 先鎖帳戶 1、再嘗試鎖帳戶 2；請求 Y 先鎖帳戶 2、再嘗試鎖帳戶 1——這正是 Mechanism 段落定義的循環等待：X 等 Y 釋放帳戶 2，Y 等 X 釋放帳戶 1，形成 Deadlock，PostgreSQL 會偵測到並讓其中一個失敗。修正方法：不管轉帳方向是 1→2 還是 2→1，都先鎖 id 較小的帳戶——這樣請求 X 與請求 Y 都會先嘗試鎖帳戶 1，其中一個會先成功並繼續鎖帳戶 2，另一個會在鎖帳戶 1 這一步就排隊等待（而不是各自鎖住一個又去等另一個），不會形成循環等待，Deadlock 不會發生，只是其中一個請求會多等一下、依序完成。