[micro-service/32] 從 MySQL 遷移到 PostgreSQL：實戰踩坑指南

結論先講

遷移不難，難的是那些你不知道的差異。 MySQL 和 PG 的 SQL 看起來一樣，但細節處處不同——AUTO_INCREMENT vs SERIAL、TINYINT(1) vs BOOLEAN、GROUP BY 寬鬆 vs 嚴格、字串比較預設 case-sensitive vs case-insensitive。ORM 幫你抽象掉大部分差異，但總有漏網之魚。

遷移前的評估

不要遷移的情況

系統正在高速開發中（遷移會暫停功能開發 2-4 週）
團隊沒有任何 PG 經驗（先讓一兩個人學，在新專案試用）
現有系統用了大量 MySQL 特有功能（stored procedure、event scheduler）

適合遷移的情況

新的微服務可以直接用 PG（不影響舊系統）
要加 JSON 查詢功能（PG JSONB 比 MySQL JSON 快 3.2 倍，第 17 篇）
要做讀寫分離（PG streaming replication 設定更簡單）

資料型態對照表

MySQL	PostgreSQL	注意事項
`INT AUTO_INCREMENT`	`SERIAL` 或 `GENERATED ALWAYS AS IDENTITY`	PG 推薦用 IDENTITY
`TINYINT(1)`	`BOOLEAN`	MySQL 的 TINYINT(1) 常當 boolean 用
`TINYINT`	`SMALLINT`	PG 沒有 TINYINT
`DOUBLE`	`DOUBLE PRECISION`	一樣，只是名字不同
`DATETIME`	`TIMESTAMP`	PG 的 TIMESTAMP 精度更高
`TEXT`	`TEXT`	一樣，但 PG 不需要指定長度
`ENUM('a','b')`	`CREATE TYPE ... AS ENUM`	PG 的 ENUM 是獨立型態
`JSON`	`JSONB`	用 JSONB 不用 JSON（有索引）
`UNSIGNED INT`	`INT` + CHECK 約束	PG 沒有 UNSIGNED

最容易踩的坑：UNSIGNED

MySQL 的 INT UNSIGNED 範圍是 0~~4,294,967,295。PG 沒有 UNSIGNED——INT 的範圍是 -2,147,483,648~~2,147,483,647。

如果你的 MySQL 表有超過 21 億的 auto_increment ID，直接搬到 PG 的 INT 會溢位。解法：用 BIGINT。

SQL 差異

GROUP BY 嚴格度

-- MySQL（寬鬆模式）：這可以跑
SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;
 
-- PG：報錯！name 沒有在 GROUP BY 或聚合函式裡
SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;
 
-- PG 正確寫法
SELECT user_id, MAX(name), COUNT(*) FROM orders GROUP BY user_id;

如果你的程式碼有大量「隨便 GROUP BY」的查詢，遷到 PG 會噴一堆錯。

字串比較

-- MySQL 預設 case-insensitive
SELECT * FROM users WHERE name = 'Terry';  -- 也會找到 'terry'
 
-- PG 預設 case-sensitive
SELECT * FROM users WHERE name = 'Terry';  -- 不會找到 'terry'
 
-- PG 要用 ILIKE 或 LOWER()
SELECT * FROM users WHERE LOWER(name) = LOWER('Terry');

LIMIT 語法

-- MySQL
SELECT * FROM posts LIMIT 10, 20;  -- offset 10, limit 20
 
-- PG
SELECT * FROM posts LIMIT 20 OFFSET 10;  -- 順序不同

ORM 通常會處理這個差異，但手寫 SQL 要注意。

ORM 層的改動

Sequelize

// 改 dialect
const sequelize = new Sequelize(database, user, password, {
  dialect: 'postgres',  // 原本是 'mysql'
  // ... 其他不變
});

大部分情況改 dialect 就好。但如果用了 MySQL 特有的查詢（ON DUPLICATE KEY UPDATE），要改成 PG 的 ON CONFLICT ... DO UPDATE。

TypeORM

// 改 type
{
  type: 'postgres',  // 原本 'mysql'
  // ... 其他不變
}

TypeORM 的抽象層比 Sequelize 厚，大部分差異它會自動處理。

Django ORM

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',  # 原本 mysql
        # ...
    }
}

Django ORM 對 PG 的支援比 MySQL 更好——JSONField 在 PG 上直接映射到 JSONB，在 MySQL 上功能較受限。

遷移工具

pgloader（推薦）

pgloader 是專門做 MySQL → PG 遷移的工具：

pgloader mysql://user:pass@localhost/mydb \
         postgresql://user:pass@localhost/pgdb

它會自動：

轉換資料型態
搬移資料
建立索引
處理 AUTO_INCREMENT → SERIAL

但不會處理：

Stored Procedure（需要手動重寫）
MySQL Event Scheduler（改用 pg_cron）
特有的 SQL 語法差異

漸進式遷移（推薦做法）

不要一次全部搬。推薦做法：

新微服務直接用 PG（零風險）
舊服務雙寫：寫入同時寫 MySQL 和 PG，讀取仍從 MySQL
驗證一致性：比對兩邊資料一段時間
切換讀取：讀取改從 PG
移除 MySQL：確認穩定後關掉 MySQL 的寫入

遷移後的調校

跑一次 ANALYZE

PG 的 query planner 依賴統計資訊。剛搬完資料，統計是空的：

ANALYZE;  -- 更新所有表的統計

調 autovacuum

預設的 autovacuum 對小表夠用，但大表（> 100 萬筆）建議調積極一點：

ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- 預設 0.2
  autovacuum_analyze_scale_factor = 0.005
);

建 JSONB 的 GIN 索引

如果你遷移的原因之一是用 JSONB，別忘了建索引：

CREATE INDEX idx_products_specs ON products USING GIN (specs);

Cache 的正確用法：不是什麼都往 Redis 塞 — 什麼時候該 cache、什麼時候不該、cache invalidation 的三種策略、cache stampede 怎麼防。從壓測數據看 cache 的真實效果和隱藏成本。

本系列文章

完整 68 篇目錄見系列首頁

← 上一篇：PostgreSQL vs MySQL 完整選型：不只是效能，是生態和未來 → 下一篇：Cache 的正確用法：不是什麼都往 Redis 塞

Terry Yao's Blog

分類

目錄