PostgreSQL 與 MySQL 是開源關聯式資料庫的雙雄。雖同屬 SQL 資料庫，但架構哲學、功能特性、效能特徵、運維生態差異巨大。本文從 15 個維度深度對比，提供架構師選型決策參考。
| 維度 | MySQL | PostgreSQL |
|---|---|---|
| 進程模型 | 單進程多執行緒 (mysqld) | 多進程 (postmaster + 多 backend 進程) |
| 連線處理 | 執行緒池 (thread pool plugin/企業版) | 每連線一進程 (fork)，記憶體隔離性好 |
| 共享記憶體 | 全域：Buffer Pool、Log Buffer、Query Cache (5.7) | 共享緩衝區、WAL Buffer、Lock Table |
| 儲存引擎 | **可插拔**：InnoDB (預設)、MyISAM、Memory、RocksDB、TokuDB... | **單一核心引擎** (Heap/TOAST)，支援 FDW 外部資料包裝器 |
| 表空間管理 | InnoDB 系統/獨立表空間、General Tablespace | Tablespace (預設 pg_default)，支援多表空間掛載不同磁碟 |
| 概念 | MySQL | PostgreSQL |
|---|---|---|
| 實例啟動 | mysqld 進程 | postmaster (pg_ctl start) 啟動叢集 |
| 叢集定義 | 單實例管理多個 Database | 一個 Data Directory = 一個叢集，包含多個 Database |
| 初始化 | mysqld --initialize | initdb 建立叢集與 template1/template0 |
| Database 隔離 | 各庫獨立目錄、共享系統表 | 各庫共享系統編目 (pg_catalog)，但資料物理隔離 |
| 切換資料庫 | USE db; / CONNECT db; | \c db (psql) / SET search_path / 連線字串指定 |
| 特性 | MySQL | PostgreSQL |
|---|---|---|
| 布林型態 | TINYINT(1) 模擬 | 原生 BOOLEAN |
| 陣列型態 | 不支援 (JSON 替代) | 原生任意維度陣列 [] |
| 幾何/空間 | GIS (InnoDB 5.7+) | PostGIS 擴充套件** (業界標準) |
| 全文搜尋 | InnoDB Fulltext (中文需 ngram) | tsvector/tsquery + 字典，中文需 zhparser/jieba |
| JSON 支援 | 5.7+ 原生 JSONB (二進位) | 9.4+ JSONB (二進位)，功能更豐富 (jsonb_path_query, 索引更靈活) |
| UUID | CHAR(36) 或 BINARY(16) | 原生 UUID 型態，gen_random_uuid() |
| 枚舉/範圍 | ENUM/SET (修改需 DDL) | ENUM 型態 + 範圍型態 (int4range 等) |
| 檢查約束 | 8.0.16+ 支援 | 早期完整支援 CHECK CONSTRAINT |
| 窗口函數 | 8.0+ 支援 | 早期完整支援 (RANGE/ROWS 框架更完整) |
| CTE / 遞歸 | 8.0+ 支援 | 早期完整支援 |
| 側向連接 | 8.0.14+ LATERAL | 早期支援 LATERAL JOIN |
| 索引類型 | MySQL (InnoDB) | PostgreSQL |
|---|---|---|
| B-Tree | 預設、主鍵、二級索引 | 預設、支援 ASC/DESC、NULLS FIRST/LAST 指定 |
| Hash | Memory 引擎、InnoDB 自適應 Hash | 显式 HASH 索引 (等值查詢極快) |
| GiST | 不支援 | 通用搜尋樹：PostGIS、全文、範圍、IP 網段 |
| GIN | 不支援 | 倒排索引：JSONB、陣列、全文、tsvector |
| BRIN | 不支援 | 塊級範圍索引：超大表時間序列、極小索引體積 |
| 覆蓋索引 | 支援 (Using index) | 支援 (INCLUDE 非鍵欄位，11+) |
| 部分索引 | 不支援 (WHERE 條件需 8.0 函數索引) | 原生部分索引 WHERE condition |
| 運算式索引 | 8.0+ 函數索引 | 早期支援 INDEX ON (lower(email)) |
| 特性 | MySQL | PostgreSQL |
|---|---|---|
| 原生分區 | Range/List/Hash/Key/Column (8.0+) | Range/List/Hash (11+)，底層為表繼承 + 規則/觸發器 |
| 分區剪枝 | 優化器自動 | 優化器自動 (Constraint Exclusion) |
| 子分區 | 支援 | 不直接支援 (可用繼承模擬) |
| 分區維護 | ALTER TABLE ... REORGANIZE PARTITION | ALTER TABLE ... DETACH/ATTACH PARTITION (瞬間元數據操作) |
| 全域索引 | 不支援 (各分區獨立索引) | 不支援 |
| 特性 | MySQL (InnoDB) | PostgreSQL |
|---|---|---|
| 隔離級別 | READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (預設), SERIALIZABLE | READ COMMITTED (預設), REPEATABLE READ, SERIALIZABLE |
| MVCC 實現 | Undo Log 版本鏈 + Read View | 多版本元組 (xmin/xmax) + 快照 (xid/xip) |
| 鎖升級 | 行鎖 → 表鎖 (無显式升級) | 行鎖 (Tuple Lock) + 頁面鎖 (較少) |
| 間隙鎖 | 有 (Next-Key Lock 防幻讀) | 無 (用 Serializable 級別防幻讀) |
| 死鎖偵測 | 主動偵測 (wait-for graph) | 主動偵測 (Lock Manager) |
| 長事務風險 | Undo Log 膨脹、History List 增長、Purge 延遲 | 舊版本元組堆積、Vacuum 無法回收、膨脹 |
| 特性 | MySQL | PostgreSQL |
|---|---|---|
| 原生複寫 | 非同步/半同步 (rpl_semi_sync) / 群組複寫 (MGR) | 非同步/同步 (Synchronous Replication 10+)，邏輯複寫 (10+) |
| 複寫格式 | Binlog (STATEMENT/ROW/MIXED) | WAL (物理流式) + 邏輯解碼 (Logical Decoding) |
| 故障轉移 | MGR 自動、Orchestrator、MHA、GTID | Patroni、repmgr、pg_auto_failover、Stolon |
| 多主寫入 | MGR (單主/多主模式) | BDR (Bi-Directional Replication) 擴充套件、Citus 分布式 |
| 讀寫分離 | ProxySQL、MaxScale、MySQL Router | PgBouncer (連線池) + Pgpool-II / HAProxy + 應用層路由 |
| 特性 | MySQL | PostgreSQL |
|---|---|---|
| 邏輯備份 | mysqldump, mysqlpump | pg_dump (單庫), pg_dumpall (全叢集) |
| 物理熱備份 | Xtrabackup (免費開源) | pg_basebackup (原生) + WAL-G / Barman / pgBackRest (增量、壓縮、S3、並行) |
| 時間點恢復 (PITR) | Binlog + 全量備份 | WAL 歸檔 + 基礎備份，原生支援、工具成熟 |
| 表級恢復 | mysqldump 導出單表 / Xtrabackup --export | pg_restore -t table 從 pg_dump 恢復單表 |
postgres_fdw (跨庫查詢)、mysql_fdw、mongo_fdw、redis_fdw、file_fdw (CSV)、hdfs_fdw 等，**標準 SQL/MED 實現**，查詢計劃可下推。| 特性 | MySQL | PostgreSQL |
|---|---|---|
| 預存程序語言 | SQL/PSM (簡單)、C++ UDF | PL/pgSQL (完整過程語言)、PL/Python、PL/Perl、PL/Tcl、PL/Java、PL/v8、C 語言 |
| 觸發器 | BEFORE/AFTER ROW/STATEMENT | BEFORE/AFTER/INSTEAD OF ROW/STATEMENT，**支援 INSTEAD OF (視圖觸發)** |
| 事件排程器 | EVENT SCHEDULER | pg_cron 擴充套件 (排程在資料庫內) |
| 擴充套件機制 | Plugin (較少) | Extension 系統極豐富** (PostGIS, Citus, TimescaleDB, pg_partman, pg_stat_statements, pgAudit, wal2json...) |
| 場景 | MySQL 優勢 | PostgreSQL 優勢 |
|---|---|---|
| 高併發 OLTP (簡單查詢、點查) | **執行緒模型輕量、InnoDB 成熟、生態工具豐富** | 進程模型上下文切換開銷大，但 14+ 版本改善顯著 |
| 複雜分析查詢 (OLAP、多表 JOIN、窗口函數) | 8.0+ 有改善，但優化器仍弱於 PG | **優化器極強、並行查詢 (9.6+)、JIT 編譯 (11+)、豐富索引類型** |
| GIS/空間資料 | 基礎支援 | **PostGIS 業界標準、功能最全** |
| 時間序列/物聯網 | 原生分區 + TTL | **TimescaleDB (PG 擴充套件) 專用優化**、壓縮、連續聚合 |
| 全文搜尋 (中文) | ngram parser | zhparser/jieba + GIN 索引，效果更好 |
| JSON 文檔存儲 | JSONB 索引 (虛擬欄位/函數索引) | JSONB GIN 索引、路徑查詢、部分更新更強 |
| 嚴格 ACID、複雜約束 | 8.0+ 完善 | **歷史積累更深、標準相容性最好** |
| 類別 | MySQL | PostgreSQL |
|---|---|---|
| 命令列客戶端 | mysql, mysqlsh | psql (功能極強、\d 元數據瀏覽) |
| GUI 管理 | MySQL Workbench, DBeaver, Navicat, DataGrip | pgAdmin 4, DBeaver, DataGrip, Navicat |
| 效能診斷 | sys schema, performance_schema, PMM, Grafana | pg_stat_statements, pg_stat_activity, pgBadger, pgWatch2, Grafana |
| 連線池 | ProxySQL (功能強)、MySQL Router | PgBouncer (輕量、高效、標配) |
| Schema 遷移 | Flyway, Liquibase, gh-ost, pt-osc | Flyway, Liquibase, pg_migrate, sqitch |
| 方向 | 主要挑戰 | 工具/策略 |
|---|---|---|
| MySQL → PostgreSQL | 型態映射 (ENUM→CHECK/新型態、TINYINT→SMALLINT、JSON→JSONB)、自增→IDENTITY/SEQUENCE、函數語法、鎖行為、大小寫敏感 | pgloader (自動化)、ora2pg (支援 MySQL)、手動改寫、應用層雙寫驗證 |
| PostgreSQL → MySQL | 陣列/範圍/幾何型態無對應、FDW 外部表、PL/pgSQL 複雜邏輯、部分索引、INSTEAD OF 觸發器 | 通常不建議逆向遷移，功能回退風險大 |
請根據專案核心需求打分 (1-5 分)，總分高者優先：
| 決策因子 | 權重 | 傾向 MySQL 场景 | 傾向 PostgreSQL 场景 |
|---|---|---|---|
| 團隊熟悉度/生態 | ★★★★★ | 團隊深耕 MySQL、現有生產全棧 MySQL | 團隊熟 PostgreSQL、新專案技術選型自由 |
| 核心業務類型 | ★★★★★ | 高併發簡單 OLTP (電商訂單、用戶中心、Session) | 複雜查詢、報表、OLAP、GIS、時間序列、文檔混合 |
| SQL 標準相容性要求 | ★★★★ | 一般相容即可 | 嚴格標準、遷移自 Oracle/SQL Server、合規審計 |
| 擴充套件需求 | ★★★★ | 基本功能足夠 | 需要 PostGIS、TimescaleDB、Citus、FDW、自定義型態 |
| 高可用架構成熟度 | ★★★★ | MGR/Orchestrator 生產驗證久 | Patroni/pg_auto_failover 近年快速成熟 |
| 備份恢復工具鏈 | ★★★★ | Xtrabackup 免費熱備份標桿 | WAL-G/Barman/pgBackRest 企業級功能免費 |
| 授權成本敏感度 | ★★★ | 接受 Oracle 商業授權風險 | **BSD 授權零成本、零廠商鎖定** |
| 雲廠商託管服務 | ★★★ | RDS for MySQL、Aurora MySQL、CloudSQL MySQL | RDS for PG、Aurora PG、CloudSQL PG、Azure PG、AlloyDB |
無絕對優越，只有場景契合。MySQL 在高併發 OLTP、成熟生態、免費熱備份工具上佔優；PostgreSQL 在複雜查詢、標準相容、擴充性、GIS/時間序列/JSON、授權自由度上佔優。建議：新專案以 PostgreSQL 為首選評估，除非團隊強綁 MySQL 生態或極簡 OLTP 場景；存量 MySQL 系統穩定則勿盲目遷移，投入 ROI 需嚴謹評估。
