PostgreSQL vs. MySQL：深入剖析內部架構與設計哲學

簡介：超越功能的比較

PostgreSQL 和 MySQL 都是當今開源關聯式資料庫領域的翹楚，各自擁有龐大的社群與廣泛的應用。然而，若僅停留在功能清單的比較，便會錯失兩者在核心設計上的根本性差異。這些深植於其內部架構的差異，直接導致了它們在不同應用場景下，於效能、穩定性與資源使用上的顯著不同。

本報告的目的並非簡單地羅列功能，而是從更宏觀的系統設計視角，深入剖析 PostgreSQL 與 MySQL 在核心架構、資料儲存、並行處理及 I/O 模型上的設計哲學與其背後的權衡取捨。透過理解這些底層的決策，我們才能真正洞察為何它們在某些工作負載下表現卓越，而在另一些場景下則可能面臨挑戰。

讓我們先從資料庫系統的門戶——連線管理的核心架構模型開始探討。

--------------------------------------------------------------------------------

1. 核心架構：程序模型 vs. 執行緒模型 (Process vs. Thread Model)

連線管理是資料庫系統的基礎，而 PostgreSQL 和 MySQL 在此採用了截然不同的模型。此選擇從根本上影響了系統的資源利用率、穩定性與擴展性。

PostgreSQL 的程序模型 (Process-per-Connection)

PostgreSQL 為每個客戶端的連線都會派生 (fork) 出一個獨立的作業系統程序。在 PostgreSQL 的術語中，這個獨立的程序被稱為 "backend"。

• 優勢：極佳的穩定性與隔離性 每個 backend 程序都擁有自己獨立的記憶體位址空間。這意味著，即使某一個連線因為異常查詢或程式錯誤而崩潰，也只會影響到其自身的程序，而不會波及到其他連線或整個資料庫主程序。這種設計提供了無與倫比的穩定性與故障隔離能力。

• 劣勢：較高的資源開銷 在高併發連線的場景下，此模型的缺點變得明顯。每建立一個新的連線，作業系統都需要創建一個新程序，這個過程涉及的資源開銷和上下文切換成本遠高於創建一個執行緒。此外，每個獨立的 backend 程序都會佔用一定的記憶體，這種類似 NGINX 的工作者 (worker) 模型還會導致資源孤島問題。例如，每個 backend 都維護著自己的連線池，即使其他 backend 的連線池中有空閒的「熱」連線，當前 backend 也無法使用，必須建立新連線，進一步加劇了資源浪費。

MySQL 的執行緒模型 (Thread-per-Connection)

與 PostgreSQL 相反，MySQL（特別是其主流的 InnoDB 儲存引擎）採用的是執行緒模型。所有的客戶端連線都作為獨立的執行緒在同一個主程序內運行，共享該程序的記憶體空間。

• 優勢：輕量級與高資源利用率 執行緒的創建與上下文切換成本遠低於程序。這使得 MySQL 在處理大量、短暫的併發連線時顯得更為輕量與高效，資源利用率也更高。這也是為什麼 MySQL 長期以來在 Web 應用領域備受青睞的原因之一。

• 劣勢：較差的隔離性與複雜的並行控制 所有執行緒共享同一個記憶體空間是一把雙面刃。一個不穩定的執行緒（例如，由於記憶體洩漏或錯誤）有可能污染共享記憶體，進而導致整個資料庫程序崩潰。此外，共享記憶體也為並行控制帶來了更大的挑戰，需要更精細和複雜的鎖定機制來確保資料的一致性。

綜合評估與影響

這兩種模型的選擇清晰地反映了它們的設計哲學。這是一個經典的工程權衡：MySQL 的架構師有意識地用 PostgreSQL 那種防彈級的程序隔離性，換取了處理 Web 應用典型的大量、短暫連線模式所需的高資源效率和低延遲。這是在連線層級上，將效率置於絕對穩定性之上的戰略性賭注。

• PostgreSQL 的設計選擇揭示了其對穩定性、資料完整性和嚴格隔離性的優先考量，即使犧牲一部分高併發下的資源效率。這使其非常適合需要少量但長時間、穩定連線的應用，如資料分析系統、複雜的交易處理系統。

• MySQL 則優先考慮在高併發場景下的資源效率和吞吐量。這使其成為需要處理成千上萬個短連線的典型 Web 應用的理想選擇。

從宏觀的程序與執行緒模型，我們將視角轉向更微觀的層面：資料在磁碟上究竟是如何被組織與儲存的。

--------------------------------------------------------------------------------

2. 資料儲存與索引策略：堆積表 vs. 叢集索引 (Heap Table vs. Clustered Index)

資料在磁碟上的物理佈局是決定查詢效能的關鍵因素。PostgreSQL 的堆積表結構與 MySQL (InnoDB) 的叢集索引結構，代表了兩種截然不同的儲存策略，並對讀寫操作產生了深遠的影響。

PostgreSQL 的堆積表 (Heap Table) 與指標

在 PostgreSQL 中，資料列（Tuples）被儲存在一個稱為「堆積」（Heap）的檔案集合中，其內部並沒有特定的物理順序。索引（如 B-Tree）是一個完全獨立於資料的結構。

• 工作方式：索引條目中包含一個指向堆積表中特定資料列物理位置的「元組指標」(Tuple Pointer)，即 ctid。當透過索引查找資料時，PostgreSQL 首先在索引中找到對應的 ctid，然後再根據這個 ctid 直接跳轉到堆積檔案的相應位置讀取完整的資料列。

• 寫入優勢：這種「索引與資料分離」的設計對 INSERT 操作特別友好。新的資料可以直接附加到堆積檔案的末尾，這個過程非常快，因為它避免了複雜的資料頁重組。

MySQL (InnoDB) 的叢集索引 (Clustered Index)

InnoDB 的儲存方式截然不同，它的表本身就是一個 B+ Tree 索引，這種結構被稱為「叢集索引」或「索引組織表」（Index-Organized Table）。

• 工作方式：資料表是圍繞主鍵 (Primary Key) 構建的一個 B+ Tree。這個 B+ Tree 的葉節點不再是指標，而是直接儲存了完整的資料列。這意味著資料的物理順序與主鍵的順序嚴格一致。

• 二級索引：InnoDB 的二級索引（Secondary Index）的葉節點儲存的不是資料的物理地址，而是對應資料列的主鍵值。因此，當使用二級索引進行查詢時，需要經過兩次 B-Tree 遍歷：第一次在二級索引中找到主鍵值，第二次再用這個主鍵值去叢集索引（主鍵索引）中查找完整的資料列。

效能權衡分析

這兩種儲存策略在不同操作下的效能表現有著明顯的權衡：

資料的儲存方式與其並行控制機制密切相關。接下來，我們將探討兩者在實現多版本並行控制（MVCC）上的核心差異。

--------------------------------------------------------------------------------

3. 多版本並行控制 (MVCC) 的實現差異

MVCC (Multi-Version Concurrency Control) 是現代資料庫實現高並行讀寫的核心機制，它允許多個交易同時存取資料而不會因讀寫鎖而相互阻塞。儘管 PostgreSQL 和 MySQL 都實現了 MVCC，但其底層機制截然不同，這直接影響了更新操作的效能、垃圾回收的機制以及長交易的行為。

PostgreSQL 的新舊版本共存模型

PostgreSQL 的 MVCC 實現非常直觀：它直接在資料頁中儲存資料列的多個版本。

• 運作機制：當一筆資料被 UPDATE 時，PostgreSQL 並不會在原地修改舊的資料列。相反，它會創建一個該資料列的「新版本」(new tuple)，並將舊版本標記為「死亡」(dead tuple)。系統透過 xmin 和 xmax 這兩個隱藏的系統欄位（分別記錄創建和刪除該版本的交易 ID）來判斷哪個版本的資料列對當前交易是可見的。

• 後果：資料庫膨脹 (Bloat)：這種設計的直接後果是，那些「死亡」但尚未被清理的資料列仍然佔用著磁碟空間，導致了所謂的「資料庫膨脹」。系統必須依賴一個名為 VACUUM 的背景程序來定期掃描資料表。值得注意的是，標準的 VACUUM 僅僅是將死亡元組佔用的空間標記為「可重用」，以便未來的 INSERT 操作可以利用這些空間，但它並不會將這些空間歸還給作業系統來縮小檔案大小。只有更具侵入性的 VACUUM FULL 操作才能實現這一點。

MySQL (InnoDB) 的原地更新與 Undo Log 模型

InnoDB 則採用了一種不同的策略，它結合了原地更新和一個稱為 "Undo Log" 的外部儲存區域。

• 運作機制：當一筆資料被 UPDATE 時，InnoDB 會直接在資料頁上「原地修改」該資料列。為了實現 MVCC，它會將修改前的舊版本資料寫入一個稱為「Undo Log」的獨立日誌區域。

• 讀取舊版本：當一個讀取交易需要存取一個較舊版本的資料時（例如，在一個長時間執行的可重複讀交易中），系統會從 Undo Log 中讀取歷史版本，並在記憶體中「重構」出該交易時間點應看到的資料狀態。

架構影響深度剖析

兩種 MVCC 實現方式帶來了顯著的效能和管理差異：

• 讀取效能：PostgreSQL 的讀取操作通常非常快，因為它直接從資料頁中讀取對當前交易可見的資料版本，無需任何額外的計算或重構。相比之下，MySQL 在處理需要讀取舊數據的長交易時，可能因需要遍歷一長串的 Undo Log 鏈來重構資料而導致效能下降。

• 更新效能：PostgreSQL 的 UPDATE 實質上是一個 INSERT（新版本）加上一個 DELETE（標記舊版本為死亡）的組合。這個架構是其寫入放大的根源。而 MySQL 的原地更新在某些情況下可能更高效，因為它直接修改資料頁。

• 垃圾回收：PostgreSQL 依賴於 VACUUM 程序，這是一個有其自身管理和效能開銷的背景任務，需要 DBA 進行適當的配置和監控。MySQL 的 Undo Log 清理雖然整合在系統內部，但如果管理不善，也可能導致 Undo Log 空間過度增長。

• 空間使用：PostgreSQL 的「膨脹」問題更為直觀，需要使用者主動監控和管理，以防止資料表變得過於臃腫和低效。

無論是何種 MVCC 模型，最終都依賴底層的 I/O 操作將變更持久化到磁碟上。這自然地引導我們進入下一個主題：I/O 處理與日誌機制。

--------------------------------------------------------------------------------

4. I/O 處理與日誌機制 (I/O Handling & Logging)

資料庫的持久性和效能最終取決於其與儲存系統的互動方式。在本節中，我們將探討 PostgreSQL 和 MySQL 在 I/O 層面的共通設計——預寫式日誌，以及資料庫架構演進的重要方向——非同步 I/O。

預寫式日誌 (Write-Ahead Logging - WAL) 的核心作用

預寫式日誌（在 MySQL 中常稱為 Redo Log）是確保資料庫 ACID 特性中「持久性」(Durability) 的關鍵技術，也是兩者共同採用的核心機制。

• 基本原則：其核心思想非常簡單但極為有效。當一個交易 COMMIT 時，資料庫系統不需要立即將變更後的資料頁（Data Pages）本身寫入磁碟。它唯一需要保證的是，將描述這些變更的日誌記錄（WAL record）安全地寫入到磁碟上的日誌檔案中。

• 巨大優勢：此機制將數千個潛在緩慢、遍布磁碟各處的隨機寫入 I/O 操作，轉換為對 WAL 檔案的單一、高效的順序寫入 I/O 操作。這種從隨機 I/O 到順序 I/O 的轉換，是現代資料庫最根本的效能優化之一。在系統崩潰後，資料庫可以透過重放 (replay) WAL 日誌來恢復到崩潰前的狀態。

緩衝區管理與 I/O 路徑

資料庫系統本身（如 PostgreSQL 的 Shared Buffers）和作業系統（OS Page Cache）都存在快取層。資料庫在讀寫資料時，需要與這兩層快取互動。這種「雙重快取」會帶來額外的 CPU 開銷，因為資料需要從 OS Page Cache 複製到用戶空間的資料庫緩衝區。為了避免這種開銷，資料庫有時會選擇使用「直接 I/O」(Direct I/O) 來繞過 OS Page Cache，直接與儲存設備交互。

架構的演進：非同步 I/O (Asynchronous I/O)

向非同步 I/O 的轉變，是對儲存硬體演進的直接架構回應。隨著能夠處理大規模並行 I/O 佇列的極速 NVMe SSD 的出現，傳統的同步、一次一請求的模型已成為一個顯著的軟體瓶頸。以 PostgreSQL 的演進為例，我們可以看到這一轉變的路徑：

1. 傳統同步 I/O 的瓶頸：在傳統模型中，當一個 backend process 需要從磁碟讀取資料時，它會發起一個阻塞式 (blocking) 系統呼叫，整個程序會被掛起，直到核心完成 I/O 操作。在此期間，CPU 資源被浪費。

2. 工作者池模型 (Worker Pool)：作為一個過渡方案，PostgreSQL 引入了 I/O 工作者池。Backend process 可以將 I/O 請求提交給一個共享的工作者程序。此時，是工作者程序被阻塞，而提交請求的 backend process 自身則可以繼續執行其他任務。

3. 真正的非同步 I/O (io_uring)：Linux 先進的 io_uring 介面徹底改變了這一模式。資料庫可以向核心提交一個 I/O 請求後，無需等待其完成，而是可以立即繼續執行其他計算任務。沒有任何程序會被阻塞，核心在後台完成 I/O 操作並通知資料庫。這種模式旨在消除軟體瓶頸，充分釋放現代硬體的潛能。

底層 I/O 的持續優化，是 PostgreSQL 和 MySQL 等主流資料庫不斷追求極致效能的共同方向。

--------------------------------------------------------------------------------

5. 結論：設計哲學的權衡與選擇

綜上所述，PostgreSQL 和 MySQL 之間的差異遠不止於技術細節，它們是兩種不同設計哲學的深刻體現，每種哲學都源於其最初的設計目標和目標應用場景。

• PostgreSQL 的哲學：可以歸納為優先考慮「資料完整性、可擴展性和標準符合性」。它的程序模型提供了無與倫比的穩定性；堆積表的設計簡化了寫入路徑；其 MVCC 實現則避免了讀取時的複雜重構，確保了資料視圖的一致性。這使得 PostgreSQL 成為一個極其穩健、功能強大且高度可信賴的資料庫系統，尤其適用於對資料正確性要求極高的複雜應用。

• MySQL 的哲學：可以歸納為優先考慮「高效能、易用性和高可用性」，特別是針對大規模的 Web 應用。它的執行緒模型對高併發連線更為友好；叢集索引結構極大地優化了最常見的主鍵查詢模式；其生態系統也圍繞著快速部署和水平擴展而構建。這使得 MySQL 成為需要快速處理海量請求的互聯網服務的基石。

最終，世界上沒有「最好」的資料庫，只有「最適合」的資料庫。對於技術選型者而言，深入理解這些內部架構的差異及其背後的權衡取捨，是根據自身業務的工作負載（Workload）特性，做出明智決策的根本依據。因此，任何架構師的關鍵任務不是問「哪個更好？」，而是問「哪一套權衡取捨——是 PostgreSQL 的穩健隔離性，還是 MySQL 的高併發效率——能為我們特定應用的生命週期提供一個更穩定、更高效能的基礎？」