主題: PostgreSQL

Schema migration 不是一條 SQL：用 expand、backfill、觀測證據安全地移除舊路徑

PostgreSQL 的 schema migration 要同時顧及既有資料、舊版 worker 與新程式。從 expand、可重跑 backfill 到 lock、index 與 constraint 的觀測證據，建立能安全收尾的發布順序。

把一個欄位改名、換型別，或補上 constraint，看起來都像是一條 ALTER TABLE。但正式環境裡，schema 不只屬於資料庫。已部署的網站、尚在跑的 worker、排隊中的工作，以及資料表裡的舊資料，都還在使用同一份契約。

因此 migration 的難處通常不是 SQL 語法，而是讓新舊契約短暫共存，並且知道何時真的可以收掉舊路徑。這篇用一個抽象的 orders table 說明：把舊的 delivery_state 逐步換成 delivery_state_v2，但不在第一個部署就賭所有執行中的程式都已更新。

先 expand：新增相容空間，不急著收掉舊欄位

第一步只新增欄位，讓舊版與新版程式都還能工作：

ALTER TABLE orders
ADD COLUMN delivery_state_v2 text;

沒有 DEFAULT 的新欄位，既有 row 會讀成 NULL；它不是把每一筆舊資料立刻改成新值。不過 ALTER TABLE 預設仍可能需要 ACCESS EXCLUSIVE lock，所以「SQL 看起來很小」不等於「可以在任意時刻執行」。先設定本次連線的 lock_timeout，等不到就失敗並排程重試，比悶著等住整個發布更容易處理。

SET lock_timeout = '3s';

接著部署相容的程式：新寫入同時寫兩個欄位，讀取時優先使用 delivery_state_v2，空值則退回 delivery_state。這段 dual write 與 fallback 的時間不一定短，因為它是在替舊 worker、延遲工作和慢慢換新的使用者請求留出口。

這也是 expand-contract pattern 的重點：先讓兩種版本都安全，再進行資料與程式的切換。較早版本的程式能繼續讀舊欄位，新版本則有一個可逐步填滿的新欄位。

再 backfill：小批、可重跑，而且不覆寫新資料

新版程式只照顧之後的寫入，舊資料仍要回填。回填工作最怕一口氣更新整張大表：transaction 很長、write load 拉高、autovacuum 壓力增加，失敗後也難從中間重來。

一個保守做法是每次只挑少量尚未回填的 row。假設 id 是主鍵，批次工作可以長成這樣：

WITH batch AS (
  SELECT id
  FROM orders
  WHERE delivery_state_v2 IS NULL
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
UPDATE orders
SET delivery_state_v2 = delivery_state
FROM batch
WHERE orders.id = batch.id;

這個範例刻意只更新 delivery_state_v2 IS NULL 的 row，因此可重跑；如果新版程式已寫入新值，回填不會蓋回舊值。SKIP LOCKED 讓多個回填 worker 遇到彼此鎖住的 row 時略過它們，而不是排隊等待。它適合這種「工作分配」情境，不適合作為一般查詢的一致性捷徑。

批次大小沒有通用答案。從小批開始，記錄每批影響的 row 數、耗時、重試與錯誤，再依實際 write load 調整。真正有用的 rollback，也不是急著反向改資料，而是保留舊讀取路徑，直到新的資料與寫入行為都被證明穩定。

Index 和 constraint 也要分階段處理

若新查詢需要 index，直接執行一般 CREATE INDEX 會阻擋 table 上的寫入。正式環境通常會用：

CREATE INDEX CONCURRENTLY orders_delivery_state_v2_idx
ON orders (delivery_state_v2);

CONCURRENTLY 讓 table 仍可寫入，但它通常更久、需要兩次掃描，也會等待既有 transaction；而且不能包在 transaction block 中。若建立失敗，還可能留下 INVALID index，所以發布流程必須把失敗狀態與後續清理列入檢查，而不是只看指令是否送出。

constraint 也可以拆開。先讓新的寫入遵守規則，再把舊資料的驗證排進可觀測的步驟：

ALTER TABLE orders
  ADD CONSTRAINT orders_delivery_state_v2_present
  CHECK (delivery_state_v2 IS NOT NULL) NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_delivery_state_v2_present;

NOT VALID 不會先掃完整張舊表，但新的 INSERT 和 UPDATE 仍會被這個 constraint 檢查。稍後的 VALIDATE CONSTRAINT 會驗證既有資料；它需要的 lock 比一開始就做強制驗證溫和，但仍然是一次需要觀察的 table scan。把「加規則」和「證明舊資料符合規則」分開，才能把風險放到合適的時間窗。

觀測的目標不是儀表板，是能否 contract 的證據

回填開始後，我會為 migration 保留一份小型證據清單：migration ID、開始與結束時間、每批處理數、失敗與重試次數，以及還有沒有程式讀取或寫入舊欄位。這些資料比「發布成功」四個字更有意義，因為它回答的是：我們能否安全移除 fallback？

資料庫端也有兩個很實際的觀測點：

• pg_stat_activity 可以用來確認 session 正在等什麼，以及是否有長 transaction 把 migration 卡住。
• pg_stat_progress_create_index 可以在 concurrent index build 進行中回報 phase 與進度，避免只能猜它是慢、等待，還是真的失敗。

若 lock 等太久，lock_timeout 的價值在於讓這次 statement 明確失敗，而不是默默把問題解決。此時該找出阻塞的 session、選擇較安靜的時間窗，或重新安排步驟。對大表而言，能失敗得清楚，通常比無限等待更安全。

最後才 contract，而且不必急著 drop

只有在這些訊號同時成立時，我才會移除舊路徑：

1. 新版程式已全面部署，並已跨過 worker 或排隊工作的最長存活時間。
2. 回填已完成，且新的寫入持續符合 constraint。
3. 沒有舊欄位的讀寫紀錄，相關 index 與 constraint 也已確認健康。
4. 觀察窗口內沒有 lock、錯誤率或延遲異常。

接著先移除程式中的 fallback 與 dual write，再選擇是否刪除 delivery_state。舊欄位多留一個發布週期，往往是便宜的保險；沒有規定 migration 結束當天一定要 DROP COLUMN。真正該避免的是尚未證明沒人使用，就把 rollback 路徑一併關掉。

把 schema migration 當成一個有階段、有觀測、有回退空間的發布，比記住更多 ALTER TABLE 選項更重要。SQL 是其中一段；相容性與證據，才決定它能不能安全上線。

我學到什麼

• 我會把 migration 拆成 expand、backfill、validate 與 contract，而不是期待一條 SQL 同時完成資料與程式的切換。
• lock_timeout 是讓等待明確失敗的安全欄杆，不是繞過 lock 的方法；失敗後仍要找出阻塞原因。
• CREATE INDEX CONCURRENTLY 與 NOT VALID constraint 都是在降低發布衝擊，但各自有 transaction、驗證與失敗狀態要觀察。
• 我會先用資料與程式的使用證據證明舊路徑已退場，再移除 fallback 或舊欄位。

外部參考資料

• PostgreSQL：Modifying Tables
• PostgreSQL：ALTER TABLE
• PostgreSQL：CREATE INDEX
• PostgreSQL：Progress Reporting
• PostgreSQL：Monitoring Database Activity
• PostgreSQL：Client Connection Defaults
• Matthew Palma：Zero Downtime Database Migrations - The Expand-Contract Pattern
• Reddit：如何在舊版 worker 仍可能執行時淘汰 PostgreSQL 欄位

延伸學習

• PostgreSQL：Modifying Tables：從新增、修改到移除欄位，建立 migration 的基本地圖。
• PostgreSQL：ALTER TABLE：查 lock、NOT VALID 與 VALIDATE CONSTRAINT 的精確行為。
• PostgreSQL：CREATE INDEX：理解 CONCURRENTLY 的限制、等待與失敗狀態。
• Matthew Palma：Zero Downtime Database Migrations：以圖解方式複習 expand-contract 的發布順序。