MySQL 遷移到 PostgreSQL：完整注意事項與實務指南

隨著越來越多專案開始採用 PostgreSQL，許多原本使用 MySQL 的系統也開始進行資料庫遷移。雖然兩者同為關聯式資料庫，但在 SQL 語法、資料型別、索引機制與系統管理方式上存在不少差異。

本文整理 MySQL → PostgreSQL 遷移時最重要的注意事項，包括：

• SQL 語法差異

• 資料型別轉換

• 函式差異

• Laravel 專案調整

• 系統資源管理

• 遷移工具推薦

一、SQL 語法差異

1. 字串與引號

MySQL 與 PostgreSQL 在引號使用上有明顯不同：

注意事項：

• PostgreSQL 雙引號只用於識別符（table 或 column 名稱）

• MySQL 常用的 反引號 在 PostgreSQL 不合法

建議做法：

• 遷移時移除所有反引號

• 表名與欄位名稱統一使用 小寫

2. LIMIT / OFFSET

MySQL 支援兩種寫法：

SELECT * FROM users LIMIT 10 OFFSET 20;

或

SELECT * FROM users LIMIT 20,10;

但 PostgreSQL 只支援標準寫法：

SELECT * FROM users LIMIT 10 OFFSET 20;

MySQL 的 LIMIT x,y 在 PostgreSQL 無法使用。

3. GROUP BY 行為

MySQL 在預設情況下對 GROUP BY 比較寬鬆：

SELECT name, COUNT(*) 
FROM users 
GROUP BY name;

但 PostgreSQL 嚴格遵守 SQL 標準：

所有 非聚合欄位都必須出現在 GROUP BY 中。

錯誤範例：

SELECT name, email, COUNT(*)
FROM users
GROUP BY name; 

必須改為：

SELECT name, email, COUNT(*)
FROM users
GROUP BY name, email;

4. AUTO_INCREMENT

MySQL：

id INT AUTO_INCREMENT PRIMARY KEY

PostgreSQL 傳統寫法：

id SERIAL PRIMARY KEY

現代推薦寫法：

id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

二、資料型別差異

MySQL 與 PostgreSQL 的資料型別並非完全對應。

JSON vs JSONB

PostgreSQL 的 JSONB 是更推薦的選擇：

優點：

• 支援索引

• 查詢更快

• 支援更多運算子

三、大小寫敏感問題

PostgreSQL 會將 未加引號的識別符自動轉為小寫。

例如：

CREATE TABLE Users (...)

實際會變成： 

users

如果 MySQL 專案存在 大寫 table 或 column 名稱，遷移後可能造成錯誤。

建議：

• 全部統一使用 小寫命名

• 使用 snake_case

四、常見函式差異

MySQL 與 PostgreSQL 的函式並不完全一致。

五、索引與全文搜尋

MySQL 常見：

FULLTEXT INDEX 

PostgreSQL 不相容。

替代方案：

使用全文搜尋：

tsvector
tsquery

範例：

SELECT *
FROM articles
WHERE to_tsvector('english', content)
@@ to_tsquery('database');

如果是 中文搜尋：

需要額外處理 分詞系統（例如 jieba）。

六、Transaction 行為差異

PostgreSQL 的 transaction 行為比 MySQL 更嚴格。

DDL 支援 rollback

PostgreSQL：

BEGIN;

CREATE TABLE test (...);

ROLLBACK;

MySQL 通常 無法 rollback DDL。

Error 行為

PostgreSQL：

• 一旦 transaction 出錯

• 整個 transaction 會變成 aborted

必須執行：

ROLLBACK;

才能繼續執行 SQL。

七、Laravel 專案遷移注意

如果你的專案使用 Laravel，需要注意以下幾點。

1. Raw SQL

所有以下語句需要重新檢查：

DB::raw()
whereRaw()
orderByRaw()

可能包含 MySQL 專屬函式。

2. Migration

Laravel 的：

$table->increments('id');

在 PostgreSQL 會自動轉為 SERIAL。

但仍建議確認：

• 欄位型別

• unsigned 欄位

3. JSON 欄位

Laravel：

$table->json('data');

PostgreSQL 建議改為：

jsonb

查詢運算子：

4. Pagination

Laravel pagination 仍使用：

LIMIT + OFFSET

 但 PostgreSQL 在 大資料量 OFFSET 下效能可能下降。

可以考慮：

• keyset pagination

• cursor pagination

八、資料庫遷移工具推薦

1. pgloader（最推薦）

功能：

• 直接從 MySQL 讀取

• 自動轉換 schema

• 支援 transform rules

GitHub：

https://github.com/dimitri/pgloader

2. AWS Schema Conversion Tool

適合 AWS 環境：

• SCT + DMS

• 可自動轉換 schema

3. mysqldump + 手動轉換

適合小型資料庫：

流程：

1. mysqldump

2. 修改 SQL

3. 匯入 PostgreSQL

九、PostgreSQL 系統資源管理

PostgreSQL 的資源管理模式 與 MySQL 完全不同。

尤其是：

• 記憶體配置

• 連線管理

• VACUUM

• WAL

十、記憶體配置

核心設定在：

postgresql.conf

work_mem 陷阱

實際記憶體可能是：

work_mem × 連線數 × 查詢操作數

例如： 

work_mem = 64MB
connections = 100
sort operations = 3

可能使用：

64MB × 100 × 3 = 19GB

因此 高流量系統要特別注意。

十一、連線管理（最重要）

PostgreSQL：

每個連線都是一個 process

而 MySQL：

每個連線是 thread

因此 PostgreSQL 連線成本更高。

每個連線約：

5MB – 10MB

建議使用 Connection Pooler

架構：

App → PgBouncer → PostgreSQL

常見工具：

十二、VACUUM 與 Autovacuum

PostgreSQL 使用 MVCC（多版本並發控制）。

UPDATE / DELETE 不會覆蓋資料，而是留下：

 dead tuple

需要 VACUUM 清理。

如果不清理：

• table bloat

• transaction id wraparound

• database shutdown

Autovacuum 建議設定

autovacuum = on
autovacuum_max_workers = 3
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

高寫入表建議：

ALTER TABLE logs SET (
  autovacuum_vacuum_scale_factor = 0.01
);

十三、WAL 管理

PostgreSQL 使用 WAL（Write-Ahead Log）。

類似 MySQL 的 binlog。

重要設定：

wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
checkpoint_completion_target = 0.9

如果沒有設定清理：

pg_wal/

可能會 塞滿磁碟。 

十四、磁碟 I/O 建議

建議分離資料與 WAL：

/var/lib/postgresql/data
/var/lib/postgresql/wal

監控 table bloat：

SELECT schemaname, tablename,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables;

十五、AWS 環境建議

如果使用 AWS RDS PostgreSQL：

建議：

• 使用 RDS Proxy

• 設定 failover retry

• 監控 CloudWatch

監控指標：

• pg_stat_activity

• pg_stat_bgwriter

• pg_stat_user_tables

結論

MySQL 遷移到 PostgreSQL 最重要的三個新習慣是：

使用 connection pooler（PgBouncer / RDS Proxy）
持續監控 dead tuple 與 VACUUM
管理 WAL 空間

只要理解 PostgreSQL 的架構差異，遷移後通常可以獲得：

• 更強的 SQL 標準支援

• 更好的 JSON 能力

• 更強大的索引與全文搜尋

• 更穩定的 transaction 機制