PostgreSQL vs MySQL 實戰比較 — 從後端工程師角度

前言

「該選 PostgreSQL 還是 MySQL？」這大概是後端工程師最常被問到的資料庫選擇題之一。網路上的比較文章很多，但多半停留在「PostgreSQL 功能比較豐富」、「MySQL 比較容易上手」這種泛泛之談。

我在職涯中兩套都用過不少，踩過的坑也不算少。這篇文章不打算做一個面面俱到的特性比較表——那種東西查文件就好。我想從後端工程師日常開發會遇到的實際場景出發，聊聊這兩套資料庫在架構設計、查詢能力、JSON 支援、效能特性等方面的差異，最後給出我的選擇建議。

先講結論：如果你的團隊沒有特別的歷史包袱，2024 年我會推薦 PostgreSQL。但 MySQL 絕對不是一個壞選擇，在某些場景下它甚至更適合。

架構差異

程序模型 vs 線程模型

PostgreSQL 採用 process-per-connection 模型：每個連線都是一個獨立的作業系統程序（process）。MySQL（InnoDB）則是 thread-per-connection：每個連線是一個線程（thread）。

-- PostgreSQL：查看目前的連線程序
SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity
WHERE state = 'active';

-- MySQL：查看目前的連線
SHOW PROCESSLIST;
-- 或更詳細的：
SELECT * FROM information_schema.processlist WHERE command != 'Sleep';

這個差異在實務上意味著：

• PostgreSQL：程序隔離性好，一個連線 crash 不會影響其他連線；但記憶體開銷較大（每個程序約 5-10MB）
• MySQL：線程共享記憶體空間，單機可以承受更多連線數；但一個 bug 可能影響整個服務

MVCC 實作差異

兩者都支援 MVCC（多版本並行控制），但實作方式完全不同：

PostgreSQL 的 MVCC：

直接在 table 中存放多個版本的 tuple
每個 tuple 帶有 xmin（建立的 transaction ID）和 xmax（刪除的 transaction ID）
需要 VACUUM 來清理過期的 tuple
優點：更新操作語意清晰
缺點：table bloat，需要定期 VACUUM


MySQL InnoDB 的 MVCC：

使用 undo log 來儲存舊版本
當前版本存在 table 中，舊版本在 undo tablespace
undo log 會自動 purge
優點：主表不會膨脹
缺點：長事務會導致 undo log 暴增

-- PostgreSQL：監控 table bloat
SELECT
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
    n_dead_tup,                          -- 死 tuple 數量
    n_live_tup,                          -- 活 tuple 數量
    round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 2) as dead_ratio_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- MySQL：監控 undo log 大小
SELECT
    name, subsystem, count, comment
FROM information_schema.innodb_metrics
WHERE name LIKE '%undo%' OR name LIKE '%purge%';

JSON 支援

這是兩者差異最明顯的領域之一。PostgreSQL 的 JSON 支援遠勝 MySQL。

PostgreSQL 的 JSONB

-- PostgreSQL：建立含 JSONB 欄位的表
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入資料
INSERT INTO events (event_type, payload) VALUES
('user.signup', '{"user_id": 123, "email": "test@example.com", "plan": "pro"}'),
('order.created', '{"order_id": 456, "items": [{"sku": "A001", "qty": 2}, {"sku": "B002", "qty": 1}], "total": 1500}');

-- 查詢 JSON 內的欄位
SELECT
    event_type,
    payload->>'user_id' AS user_id,      -- 取出文字值
    payload->'items' AS items,             -- 取出 JSON 物件
    payload#>'{items,0,sku}' AS first_sku  -- 巢狀路徑存取
FROM events
WHERE payload->>'plan' = 'pro';

-- JSONB 支援 GIN 索引，查詢飛快
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- 可以用 @> 運算子做包含查詢（走索引）
SELECT * FROM events
WHERE payload @> '{"plan": "pro"}';

-- JSONB 路徑查詢（PostgreSQL 12+）
SELECT * FROM events
WHERE payload @? '$.items[*] ? (@.qty > 1)';

-- 聚合 JSON
SELECT
    event_type,
    jsonb_agg(payload ORDER BY created_at) AS all_payloads
FROM events
GROUP BY event_type;

MySQL 的 JSON

-- MySQL：建立含 JSON 欄位的表
CREATE TABLE events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查詢 JSON 內的欄位
SELECT
    event_type,
    JSON_EXTRACT(payload, '$.user_id') AS user_id,
    payload->'$.items' AS items,
    payload->'$.items[0].sku' AS first_sku
FROM events
WHERE JSON_EXTRACT(payload, '$.plan') = 'pro';
-- 簡寫：WHERE payload->>'$.plan' = 'pro';  (MySQL 8.0.21+)

-- MySQL 的 JSON 索引需要用 generated column
ALTER TABLE events
ADD COLUMN plan VARCHAR(50)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.plan'))) STORED;
CREATE INDEX idx_plan ON events (plan);

-- 不像 PostgreSQL 可以直接對整個 JSON 建 GIN 索引
-- MySQL 必須為每個需要查詢的 JSON path 建立 virtual/stored column

重點差異：

• PostgreSQL 的 GIN 索引可以對整個 JSONB 建索引，任何 key 的查詢都能走索引
• MySQL 必須為每個要查的 JSON path 建立 generated column + 索引
• PostgreSQL 有更多 JSON 運算子和函式
• 如果你的應用大量使用 JSON 欄位，PostgreSQL 是明顯的贏家

CTE 與 Window Function

Common Table Expression (CTE)

-- 遞迴 CTE：組織架構樹
-- PostgreSQL 和 MySQL 8.0+ 語法一樣
WITH RECURSIVE org_tree AS (
    -- 基礎查詢：根節點
    SELECT id, name, manager_id, 0 AS depth, name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL

UNION ALL

-- 遞迴查詢
    SELECT e.id, e.name, e.manager_id, t.depth + 1,
           t.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY path;

PostgreSQL 在 CTE 上有個重要的差異：

-- PostgreSQL 12+ 可以自動把 CTE inline（展開）
-- 預設行為：如果 CTE 只被引用一次且不是遞迴的，會自動 inline
-- 可以用 MATERIALIZED / NOT MATERIALIZED 控制

WITH monthly_stats AS MATERIALIZED (
    -- 強制物化（不要 inline），適用於被多次引用的 CTE
    SELECT date_trunc('month', created_at) AS month,
           COUNT(*) AS total_orders,
           SUM(amount) AS total_amount
    FROM orders
    GROUP BY 1
)
SELECT * FROM monthly_stats WHERE total_orders > 100;

-- MySQL 8.0 的 CTE 永遠是 materialized 的
-- 在 MySQL 中，CTE 主要是提升可讀性，效能不一定比子查詢好

Window Function

-- 兩者語法一致，但 PostgreSQL 支援更多 window function
-- 共同支援：ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG...

-- 計算每個客戶的訂單排名和累計金額
SELECT
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
    LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
    amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS diff_from_prev
FROM orders;

-- PostgreSQL 獨有的好用功能：

-- 1. FILTER 子句（MySQL 不支援）
SELECT
    date_trunc('month', created_at) AS month,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
    AVG(amount) FILTER (WHERE status = 'completed') AS avg_completed_amount
FROM orders
GROUP BY 1;

-- MySQL 替代方案（用 CASE WHEN）：
SELECT
    DATE_FORMAT(created_at, '%Y-%m-01') AS month,
    COUNT(*) AS total,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    AVG(CASE WHEN status = 'completed' THEN amount END) AS avg_completed_amount
FROM orders
GROUP BY 1;

-- 2. PostgreSQL 的 DISTINCT ON（超好用！）
-- 取每個客戶最近一筆訂單
SELECT DISTINCT ON (customer_id)
    customer_id, order_date, amount, status
FROM orders
ORDER BY customer_id, order_date DESC;

-- MySQL 替代方案（需要子查詢）：
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT customer_id, MAX(order_date) AS max_date
    FROM orders
    GROUP BY customer_id
) latest ON o.customer_id = latest.customer_id AND o.order_date = latest.max_date;

效能比較

寫入效能

-- 批量寫入測試（插入 100 萬筆資料）

-- PostgreSQL：使用 COPY 命令（最快）
COPY orders FROM '/tmp/orders.csv' WITH CSV HEADER;
-- 速度：大約 10-20 萬筆/秒

-- PostgreSQL：使用 INSERT ... VALUES
INSERT INTO orders (customer_id, amount, created_at)
SELECT
    (random() * 10000)::int,
    (random() * 1000)::numeric(10,2),
    NOW() - (random() * interval '365 days')
FROM generate_series(1, 1000000);

-- MySQL：使用 LOAD DATA（最快）
LOAD DATA INFILE '/tmp/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
-- 速度：大約 15-25 萬筆/秒（MySQL 在簡單寫入上通常略快）

讀取效能

-- 複雜分析查詢：PostgreSQL 通常較快
-- 原因：更優秀的查詢優化器、並行查詢支援、更多的 join 策略

-- PostgreSQL 支援的 join 策略：
-- Nested Loop, Hash Join, Merge Join, Parallel Hash Join
-- 優化器會根據統計資訊自動選擇

-- MySQL InnoDB 的 join 策略：
-- 主要是 Nested Loop Join 和 Block Nested Loop（8.0.18+ 支援 Hash Join）

-- PostgreSQL 的並行查詢
SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE
SELECT customer_id, SUM(amount), COUNT(*)
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;
-- 可能的計畫：Parallel Seq Scan -> Partial GroupAggregate -> Gather Merge

選擇建議

經過多年的使用，這是我的建議：

選 PostgreSQL 的場景

• 複雜的分析查詢（大量 JOIN、子查詢、CTE）
• 需要 JSONB 儲存和查詢
• 全文搜尋（內建 tsvector，不需要額外裝 Elasticsearch）
• 地理空間資料（PostGIS 是業界標準）
• 需要自訂型別、自訂函式
• 嚴格的資料一致性要求

選 MySQL 的場景

• 簡單的 CRUD 操作為主
• 讀多寫少、高併發的 Web 應用
• 團隊對 MySQL 較熟悉
• 需要最廣泛的雲端服務支援（AWS Aurora、GCP Cloud SQL 等都優先支援 MySQL）
• 既有系統已經是 MySQL，遷移成本太高

兩者都行的場景

• 一般的 Web 後端（REST API + 關聯式資料）
• 中小規模的應用（單機或簡單的主從架構）
• 微服務架構中的個別服務

# 我的經驗法則（Python 虛擬碼）
def choose_database(project):
    if project.needs_jsonb or project.has_complex_queries:
        return "PostgreSQL"
    if project.is_legacy_mysql and project.migration_budget == 0:
        return "MySQL"
    if project.team_expertise == "MySQL" and project.deadline < "2 months":
        return "MySQL"
    if project.is_new_project:
        return "PostgreSQL"  # 2024 年的預設選擇
    return "either_is_fine"

小結

PostgreSQL 和 MySQL 都是優秀的關聯式資料庫，但它們的設計哲學不同：

• PostgreSQL 追求功能完整性和標準合規性，是一把瑞士刀
• MySQL 追求簡單、快速，是一把鋒利的菜刀

2024 年的趨勢是 PostgreSQL 的採用率持續上升，主要原因是 JSONB、進階查詢功能、以及生態系統的成熟。但 MySQL 依然有龐大的用戶基礎和成熟的工具鏈。

最重要的是：選擇你的團隊最熟悉的那個。資料庫的效能差異，往往遠小於「會不會正確使用它」帶來的差異。一個用錯索引的 PostgreSQL，跑得比正確調校的 MySQL 慢得多。

延伸閱讀建議：

• PostgreSQL 官方文件 — 寫得非常好，值得細讀
• MySQL 官方文件 — 特別是 InnoDB 儲存引擎章節
• Use The Index, Luke — 跨資料庫的索引最佳實踐
• Postgres Wiki: Don’t Do This — PostgreSQL 常見反模式