PostgreSQL 架構總覽
介紹 PostgreSQL 的 Process 架構（Postmaster、Backend、Background Workers）、Shared Memory 結構、以及與 MySQL 在架構上的根本差異
與 MySQL 最根本的差異：Process vs Thread
MySQL 是一個程序、多執行緒（multi-threaded）；PostgreSQL 是多程序（multi-process）。每個客戶端連線在 PostgreSQL 裡對應一個獨立的 OS process（Backend Process），不是 thread。
這個設計決定了兩者在記憶體管理、穩定性、併發模型上的大量差異。Process 模型的好處是一個 backend crash 不會拖垮整個資料庫（只斷那一條連線），壞處是 process 比 thread 重、context switch 成本高、連線數受限於 OS process 上限。
程序架構
上圖展示 PostgreSQL 的三層架構：最上層的 Postmaster 主程序負責監聽連線並 fork() 子程序；中間層的 Backend Process 每個連線對應一個獨立 OS process，各自執行 SQL（Parser → Planner → Executor）；底層的 Shared Memory 是所有 process 共用的記憶體區域（Shared Buffers、WAL Buffers、Lock Table）。右側的 Background Workers 由 Postmaster 啟動，負責 WAL 刷盤、Checkpoint、Autovacuum 等背景任務。
Postmaster
PostgreSQL 的主程序（postgres），負責：
- 監聽客戶端連線（預設 port 5432）
- 為每個新連線
fork()一個 Backend Process - 管理所有 Background Worker 的生命週期
- 當任何 Backend crash 時，Postmaster 會終止所有 Backend 並啟動 crash recovery
Postmaster 自己不處理 SQL，只負責管理。
Backend Process
每個客戶端連線對應一個 Backend Process。它負責：
- 解析 SQL（Parser → Rewriter → Planner → Executor）
- 存取 Shared Buffers 讀寫資料頁
- 寫 WAL（Write-Ahead Log）
- 持有鎖、管理交易狀態
Backend Process 之間透過 Shared Memory 通訊，不直接互相呼叫。
Background Workers
Postmaster 啟動時會 fork 一系列 background worker：
| Worker | 職責 |
|---|---|
| WAL Writer | 定期把 WAL Buffer flush 到磁碟 |
| Checkpointer | 定期做 checkpoint，把 dirty page 刷回磁碟 |
| Autovacuum Launcher | 管理 autovacuum worker 的啟動與排程 |
| Autovacuum Worker | 執行 VACUUM 回收 dead tuple（見 Vacuum 與維運） |
| BGWriter | 背景刷髒頁，減輕 checkpoint 的 IO 壓力 |
| Stats Collector | 收集表、索引的統計資訊供 planner 使用 |
| WAL Sender | Streaming Replication 時把 WAL 送給 Standby |
| WAL Receiver | Standby 端接收 WAL |
| Logical Replication Worker | 處理 logical decoding 與 replication |
Shared Memory
所有 Backend Process 共用的記憶體區域，PostgreSQL 的資料存取核心。
Shared Buffers
最重要的記憶體結構，類似 InnoDB 的 Buffer Pool。所有資料頁（8KB，MySQL 是 16KB）的讀寫都先經過 Shared Buffers。
-- 預設 128MB，生產環境建議設為實體記憶體的 25%
-- （不像 MySQL 建議 50-80%，因為 PostgreSQL 還仰賴 OS page cache）
shared_buffers = '4GB'
為什麼不設太大？PostgreSQL 依賴雙層快取：Shared Buffers + OS Page Cache。如果 Shared Buffers 吃掉太多記憶體，OS Page Cache 就不夠用，反而影響 sequential scan 等需要大量 IO 的操作。經驗值是實體記憶體的 25%，剩下留給 OS。
頁面淘汰使用時鐘掃描演算法（Clock Sweep），比 LRU 簡單但在 PostgreSQL 的 workload 下效果相近。
WAL Buffers
WAL（Write-Ahead Log）的記憶體 buffer。Backend Process 把 WAL record 先寫到 WAL Buffers，再由 WAL Writer 或 COMMIT 時 flush 到磁碟。預設大小為 Shared Buffers 的 1/32，通常 16MB 足夠。
Lock Table
全域鎖表，記錄所有行鎖、表鎖、Advisory Lock 的狀態。所有 Backend 透過這個共享結構協調併發。
一條 SQL 的執行流程
SELECT * FROM orders WHERE id = 42;
- Parser：詞法 + 語法分析，產出 parse tree
- Rewriter：套用 rule（例如 view 展開）。這是 PostgreSQL 獨有的——view 在 PostgreSQL 裡是用 rule system 實作的
- Planner / Optimizer：根據統計資訊（
pg_statistic）產出查詢計畫。PostgreSQL 的 planner 比 MySQL 強很多，支援 hash join、merge join、parallel query 等 - Executor：按計畫存取 Shared Buffers 取資料，不在 buffer 則從磁碟讀入
寫入操作多兩步：修改前先寫 WAL（Write-Ahead Log），修改後頁面變成 dirty page，等 checkpoint 或 BGWriter 刷回磁碟。
資料儲存結構
PostgreSQL 的資料存在 $PGDATA 目錄下，每個資料庫是一個子目錄，每張表是一到多個檔案（每個檔案最大 1GB，超過自動分段）。
每一行資料（tuple）有幾個隱藏欄位：
| 欄位 | 用途 |
|---|---|
xmin | 建立此 tuple 的交易 ID |
xmax | 刪除/更新此 tuple 的交易 ID（0 = 未刪除） |
ctid | 此 tuple 在頁面中的物理位置 (page, offset) |
t_infomask | 各種狀態 flag（committed、aborted 等） |
這些欄位是 PostgreSQL MVCC 的基礎——與 MySQL 把舊版本存在 undo log 不同，PostgreSQL 把新舊版本都存在同一張表裡，靠 xmin/xmax 判斷可見性。詳見 MVCC 原理。
連線管理
因為每個連線 = 一個 process，PostgreSQL 對連線數很敏感：
max_connections預設 100（MySQL 預設 151）- 每個 backend 約佔 5-10MB 記憶體
- 超過幾百個 active connection 效能就開始下降（context switch + lock contention）
生產環境必須用連線池：
| 方案 | 特點 |
|---|---|
| PgBouncer | 最常用，輕量，支援 transaction pooling |
| Pgpool-II | 功能多（連線池 + 負載均衡 + 複製），但較重 |
| 應用層連線池 | HikariCP 等，控制在應用端 |
PgBouncer 的 transaction pooling 模式：一個 backend 在交易結束後立即釋放給其他客戶端，200 個客戶端可以共用 20 個 backend。但代價是不能使用 session-level 功能（prepared statements、temp tables、SET 命令）。
100+ 應用實例 → PgBouncer (20 connections) → PostgreSQL (20 backends)
與 MySQL 架構的關鍵差異
| 面向 | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| 程序模型 | Multi-process（fork） | Multi-thread |
| 資料頁大小 | 8KB | 16KB |
| 記憶體快取 | Shared Buffers + OS Page Cache | Buffer Pool（自己管理） |
| MVCC 實作 | 同表存多版本（heap tuple） | Undo Log 版本鏈 |
| 舊版本清理 | VACUUM | Purge Thread 自動回收 Undo |
| WAL | 一套（WAL） | 兩套（Redo Log + Binlog） |
| 連線池 | 必須外掛（PgBouncer） | 內建 thread pool（Enterprise） |
| Planner | 強（hash join、parallel） | 較弱（8.0 有改善） |
更詳細的比較見 PostgreSQL vs MySQL。