PostgreSQL 遷移
核心原則。 每個遷移是一對 up/down，且 down 必須完全復原 up。遷移在其自己的交易中執行。只有一個例外：對於已有資料且正在寫入的資料表建立索引時，使用 CREATE INDEX CONCURRENTLY 在無交易的獨立遷移中進行，每個檔案只放一條命令。
斷言檢查 — CHECKS.md。
檔案格式
migrations/
├── 20250524120000_create_users.up.sql
├── 20250524120000_create_users.down.sql
├── 20250525090000_add_users_phone.up.sql
└── 20250525090000_add_users_phone.down.sql
- 前綴為 UTC 時間
YYYYMMDDHHmmss，名稱為 snake_case 的簡短描述。 *.up.sql用於套用，*.down.sql用於復原。兩個檔案都必須存在。- 已套用的遷移不要修改 — 請撰寫新的遷移。
類型與限制
| 避免使用 | 建議使用 | 原因 |
|---|---|---|
bigserial | bigint GENERATED ALWAYS AS IDENTITY | SQL 標準；若需要插入自訂 id（例如匯入），則使用 BY DEFAULT |
varchar(n) | text NOT NULL CHECK (length(x) <= n) | 限制是資料規則，變更限制更容易 |
timestamp | timestamptz | 儲存時間點，而非「掛鐘時間」 |
integer 旗標 | boolean | |
json | jsonb | 二進位格式，支援索引與運算子 |
| 來自擴充的 uuid | uuid DEFAULT gen_random_uuid() | 自 PostgreSQL 13 起內建 |
文字欄位的長度請一律使用 CHECK 限制，除非是確定為長文字（例如描述、評論）。若最大長度不明確，請詢問使用者。
| 資料 | 限制 |
|---|---|
CHECK (length(email) <= 320) | |
| 姓名 | CHECK (length(name) <= 255) |
| 電話 | CHECK (length(phone) <= 50) |
| slug、代碼 | CHECK (length(code) <= 100) |
| URL | CHECK (length(url) <= 2048) |
| 描述 | 無限制或 CHECK (length(bio) <= 10000) |
資料表範本：
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL CHECK (length(name) <= 255),
email text NOT NULL CHECK (length(email) <= 320),
is_active boolean NOT NULL DEFAULT true,
metadata jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ix_users_email ON users (email);
復原
| up | down |
|---|---|
CREATE TABLE t | DROP TABLE IF EXISTS t |
ALTER TABLE t ADD COLUMN c | ALTER TABLE t DROP COLUMN IF EXISTS c |
CREATE INDEX ix | DROP INDEX IF EXISTS ix |
CREATE INDEX CONCURRENTLY ix | DROP INDEX CONCURRENTLY IF EXISTS ix — 同樣無交易 |
CREATE TYPE t AS ENUM (...) | DROP TYPE IF EXISTS t |
ALTER TYPE t ADD VALUE 'v' | PostgreSQL 沒有 DROP VALUE — 重新建立類型或使用空的 down |
ALTER TABLE t ALTER COLUMN c SET NOT NULL | ALTER TABLE t ALTER COLUMN c DROP NOT NULL |
ALTER TABLE t ADD CONSTRAINT c | ALTER TABLE t DROP CONSTRAINT IF EXISTS c |
IF EXISTS 用於 down 中，確保復原操作是冪等的。
常見變更
含預設值的欄位。 使用常數 DEFAULT 時，資料表不會被重寫（PostgreSQL 11+），命令瞬間完成。使用 volatile 預設值（如 gen_random_uuid()、clock_timestamp()）時，資料表會在獨占鎖定下完整重寫 — 對於大型活躍資料表，應先新增不含預設值的欄位，然後分批填充。
ALTER TABLE users ADD COLUMN role text NOT NULL DEFAULT 'user' CHECK (length(role) <= 50);
外部鍵 — 應立即為參考欄位建立索引：PostgreSQL 不會自動建立，若無索引，刪除父記錄和 JOIN 操作將進行全表掃描。
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users (id),
total numeric NOT NULL
);
CREATE INDEX ix_orders_user_id ON orders (user_id);
列舉值。 ALTER TYPE ... ADD VALUE 可在交易中執行，但在提交前不能使用新值（55P04 unsafe use of new value）。如果遷移工具在交易中執行遷移，請將新增值和使用該值（如 UPDATE、DEFAULT、CHECK）放在不同的遷移中。
ALTER TYPE payment_method ADD VALUE 'bank_transfer';
down：沒有 DROP VALUE。如果該值無害，請保留空的 down 並加上註解；否則重新建立類型（新類型、ALTER COLUMN ... TYPE ... USING、刪除舊類型）。
在活躍資料表上建立索引
資料表已有資料且正在寫入時，請使用 CREATE INDEX CONCURRENTLY (CIC)。一般 CREATE INDEX 會在整個建立期間封鎖寫入。如果索引與新資料表在同一個遷移中，則使用一般 CREATE INDEX。資料庫大小不是救星：掛起的交易會破壞任何大小的新索引（如下所述）。
up：
CREATE INDEX CONCURRENTLY ix_orders_customer_id ON orders (customer_id);
down：
DROP INDEX CONCURRENTLY IF EXISTS ix_orders_customer_id;
- 獨立的無交易遷移，每個檔案只放一條命令。 在交易區塊內禁止 CIC（「cannot run inside a transaction block」）。若在同一個請求中發送多條命令，PostgreSQL 會以隱含交易執行，CIC 會因相同錯誤而失敗。
- 如果遷移工具不支援無交易模式，請勿將 CIC 放入一般遷移中。 請告知使用者需要此模式（參閱「對遷移工具的要求」一節），或手動單獨執行 CIC 並記錄遷移。
- CIC 會等待所有持有舊快照的交易結束，即使這些交易僅讀取其他資料表。遷移可能需要很長時間。
- 套用後請驗證索引。 CIC 不具交易性：若失敗，會留下 INVALID 索引，而遷移本身不會顯示此情況。
SELECT c.relname, i.indisvalid, i.indcheckxmin
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'ix_orders_customer_id';
indisvalid = false— 執行DROP INDEX CONCURRENTLY IF EXISTS並重新建立，或執行REINDEX INDEX CONCURRENTLY。- 重試失敗的 CIC 遷移。 若不使用
IF NOT EXISTS，它會在 INVALID 索引上失敗，並顯示「relation already exists」。使用IF NOT EXISTS更糟：該命令會默默地跳過 INVALID 索引，導致遷移「通過」。因此，請勿在 CIC 中使用IF NOT EXISTS：先移除無效索引，然後再重試。
索引存在，但計畫顯示 Seq Scan
索引有效但最佳化器不使用它 — 請檢查 indcheckxmin（執行上述查詢）。若為 true：在索引建立前開始的交易存活期間，查詢不會使用該索引。原因為 UPDATE：資料表中留下了索引未參考的 HOT 版本鏈。僅 INSERT 不會導致此情況。請勿重建索引或從統計資料中尋找原因。請找出舊交易：
SELECT pid, xact_start, backend_xmin, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start;
等待它們完成或終止它們（pg_terminate_backend(pid)）— 但必須先與使用者協商。在發布前重現此情況：在一個工作階段中開啟一個 repeatable read 交易並讀取資料表，然後在另一個工作階段中執行 UPDATE 行和 CREATE INDEX；indcheckxmin 會變成 true。
對遷移工具的要求
- 每個遷移在自己的交易中執行，同時記錄套用日誌：失敗的遷移會整個復原，已套用的則保持不變。
- 防止並行執行的鎖定機制（例如 advisory lock）：同時啟動的複本不會重複套用相同的遷移。
- 為特定遷移支援無交易模式 — 在檔案中標記（例如，第一行為
-- migrate:no-transaction）。此類檔案以單條命令發送，跳過交易。代價：套用和寫入日誌不再具有原子性。若在兩者之間進程崩潰，重試會因「already exists」而失敗；請驗證indisvalid，若索引有效，則手動在日誌中記錄該遷移。 - 無需連接資料庫即可建立成對的檔案。