PostgreSQL vs MySQL
從架構、MVCC、索引、鎖、複製、型態系統等面向，全面比較 PostgreSQL 與 MySQL 的設計差異與適用場景
兩者都是成熟的開源關聯式資料庫，但設計哲學截然不同：MySQL 追求簡單高效、PostgreSQL 追求正確完整。
架構
| 面向 | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| 程序模型 | Multi-process（fork per conn） | Multi-thread |
| 連線開銷 | 重（需連線池） | 輕 |
| 資料頁大小 | 8 KB | 16 KB |
| 記憶體快取 | Shared Buffers + OS Page Cache | Buffer Pool（自管理） |
| Storage Engine | 只有一種（heap + WAL） | 可插拔（InnoDB 為主） |
| 系統目錄 | SQL 標準 information_schema + 自有 pg_catalog | information_schema + mysql schema |
PostgreSQL 的 multi-process 模型穩定性更好（一個 backend crash 不影響其他），但連線開銷大，生產環境必須搭配 PgBouncer。MySQL 的 thread 模型天生適合高連線數場景。
MVCC
這是兩者最大的架構差異。
| 面向 | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| 舊版本存放 | 同一張表（heap tuple） | Undo Log（獨立空間） |
| UPDATE 行為 | 插入新 tuple + 標記舊 tuple xmax | 原地修改 + 舊值存 Undo Log |
| 清理 | VACUUM（必須、需調校） | Purge Thread（自動、免維護） |
| 表膨脹 | 會（dead tuple 累積） | 不會（Undo 獨立空間） |
| Crash 回滾 | 不需要（標記 aborted） | 需要 Undo Log 逆向回復 |
| Transaction ID | 32-bit，需 freeze 防 wraparound | 隱藏欄位，無 wraparound 風險 |
PostgreSQL 的 MVCC 更簡單（不需要 undo log），但維運成本更高（VACUUM tuning 是必修課）。MySQL 的 MVCC 維運幾乎免費，但 Undo Log + 兩階段提交的架構更複雜。
日誌系統
| 面向 | PostgreSQL | MySQL |
|---|---|---|
| Crash Recovery | WAL | Redo Log |
| Replication | 同一套 WAL | Binlog（獨立於 Redo Log） |
| 日誌套數 | 一套 | 兩套（需兩階段提交保一致） |
| 邏輯複製 | Logical Decoding（解碼 WAL） | Binlog（天生邏輯） |
| PITR | WAL Archive | Binlog |
PostgreSQL 一套 WAL 打天下，架構乾淨；MySQL 兩套日誌職責分明但需要兩階段提交保持一致。
交易隔離
| 面向 | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| 預設隔離等級 | Read Committed | Repeatable Read |
| RR 實作 | Snapshot Isolation | MVCC + Next-Key Lock |
| 幻讀防護(RR) | SI 天生防（snapshot 機制） | 快照讀防、當前讀靠 Next-Key Lock |
| 寫衝突(RR) | 報錯（first-updater-wins） | 等鎖（在最新版本上修改） |
| Serializable | SSI（樂觀，高併發） | 所有 SELECT 加 S Lock（悲觀，低併發） |
| Gap Lock | 無 | 有 |
| 死鎖風險 | 較低 | 較高（gap lock 交叉） |
PostgreSQL 在高隔離等級下的併發度明顯優於 MySQL，特別是 Serializable（SSI vs 全鎖）。但 MySQL 的 RR 對應用層更「寬容」——寫衝突等鎖而非報錯，不需要 retry 邏輯。
索引
| 索引類型 | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| B-Tree | ✓ | ✓（B+ Tree） |
| Hash | ✓ | ✓（Adaptive Hash，自動） |
| GIN（倒排） | ✓ | ✗ |
| GiST（空間） | ✓ | ✗（靠 R-Tree） |
| BRIN | ✓ | ✗ |
| Full-Text | ✓（GIN + tsvector） | ✓（InnoDB FTS） |
| Partial | ✓ | ✗ |
| Expression | ✓ | ✓（8.0+） |
| Covering | ✓（INCLUDE） | ✓（天生在 Secondary Index） |
PostgreSQL 的索引種類遠多於 MySQL，特別是 GIN（JSONB、全文搜尋）和 BRIN（大表時序）這兩個在 MySQL 中沒有對應物。
但 MySQL 的 Clustered Index 讓主鍵查詢天生不需要回表（資料就在索引裡），這點 PostgreSQL 做不到——所有索引都是 secondary，都需要回 heap。
鎖
| 面向 | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| 行鎖實作 | 標記在 tuple header | 鎖在索引記錄上 |
| Gap Lock | 無 | 有（Next-Key Lock） |
| 行鎖粒度 | 4 種（FOR KEY SHARE/SHARE/NO KEY UPDATE/UPDATE） | 2 種（S/X） |
| Advisory Lock | 強（bigint key、xact-level） | 弱（字串 key、session-only） |
| DDL 風險 | 高（AccessExclusiveLock 阻塞一切） | 中（Online DDL 較成熟） |
PostgreSQL 的 FOR NO KEY UPDATE 讓外鍵檢查不阻塞普通 UPDATE，是比 MySQL 更精細的設計。但 MySQL 的 Online DDL 比 PostgreSQL 成熟（PostgreSQL 做 DDL 經常需要 pg_repack 或 lock_timeout 技巧）。
型態系統
PostgreSQL 的型態系統遠比 MySQL 豐富：
| 型態 | PostgreSQL | MySQL |
|---|---|---|
| JSON | json + jsonb | json（無 binary） |
| Array | ✓（原生） | ✗ |
| Range | ✓（int4range 等） | ✗ |
| Enum | ✓（可新增值） | ✓（改定義需 ALTER） |
| Composite | ✓（自訂型態） | ✗ |
| Domain | ✓（帶約束的型態） | ✗ |
| UUID | ✓（原生型態） | ✓（8.0 UUID 函數） |
| Network | inet cidr macaddr | ✗ |
| Geometric | ✓（原生） | ✗（需 spatial extension） |
JSONB 是 PostgreSQL 的殺手功能之一——binary 格式、可建 GIN 索引、支援豐富的操作符，效能遠勝 MySQL 的 JSON 型態。
SQL 功能
| 功能 | PostgreSQL | MySQL |
|---|---|---|
| Window Function | ✓（完整支援） | ✓（8.0+，較晚支援） |
| CTE | ✓（含 recursive + writable CTE） | ✓（8.0+，不支援 writable） |
| LATERAL JOIN | ✓ | ✓（8.0.14+） |
| UPSERT | INSERT ... ON CONFLICT | INSERT ... ON DUPLICATE KEY |
| RETURNING | ✓ | ✗ |
| Generate Series | ✓ | ✓（8.0.33+ recursive CTE） |
| Materialized View | ✓ | ✗ |
| Foreign Data Wrapper | ✓（查詢外部資料來源） | ✗ |
RETURNING 是 PostgreSQL 的一大便利——INSERT/UPDATE/DELETE 後直接回傳修改的行，不需要額外 SELECT：
INSERT INTO orders (customer_id, amount)
VALUES (1, 500)
RETURNING id, created_at;
-- 直接拿到自增 ID 和預設值，不需要 LAST_INSERT_ID()
擴展性
PostgreSQL 的擴展機制是另一個殺手功能：
| Extension | 用途 |
|---|---|
| PostGIS | 地理空間（業界標準） |
| pg_trgm | 模糊搜尋（similarity） |
| pgvector | 向量搜尋（AI embedding） |
| TimescaleDB | 時序資料庫 |
| Citus | 分散式 / sharding |
| pg_stat_statements | SQL 效能分析 |
MySQL 沒有等價的擴展機制。PostGIS 讓 PostgreSQL 在地理空間領域幾乎沒有對手；pgvector 讓它在 AI 應用中也很有競爭力。
適用場景
選 PostgreSQL 的理由：
- 資料型態複雜（JSONB、array、range、geometry）
- 需要進階 SQL（CTE、window function、LATERAL）
- 需要 GIN/GiST/BRIN 索引
- 需要 PostGIS（地理空間）或 pgvector（向量搜尋）
- 需要高隔離等級的高併發（SSI）
- 對 SQL 標準合規有要求
選 MySQL 的理由：
- 簡單的 OLTP workload（CRUD 為主）
- 超高連線數（不想管連線池）
- 不想花時間調 VACUUM
- 團隊更熟悉 MySQL 生態
- 需要成熟的 Online DDL
- 讀多寫少、需要大量 read replica
兩者都行的場景：
- 一般 web 應用的後端資料庫
- 微服務架構中的服務資料庫
- 中等規模的 OLTP
一句話總結
MySQL 是一把好用的螺絲起子——簡單、可靠、上手快。PostgreSQL 是一整套瑞士刀——功能強大、面面俱到，但需要更多學習和維運投入。選擇取決於你的場景複雜度和團隊能力。