後端面試：如何零停機遷移 PostgreSQL Schema？

題目與適用情境

一個使用者服務執行在 PostgreSQL 上，users 資料表有 2 億筆資料。username TEXT NOT NULL 具有唯一索引，所有線上請求、背景工作與事件消費者目前都會讀寫它。團隊準備把欄位改名為 handle，遷移期間兩個欄位表達完全相同的業務值。

服務有 80 個應用程式執行個體，一次滾動發布需要 30 分鐘；背景 worker 可能比 Web 執行個體更晚重新啟動。正常流量不能暫停，也沒有維護時段。現有 SLO 仍然有效：如果寫入 p99、鎖等待、複寫延遲或資料庫負載超過線上門檻，遷移必須能夠降速或中止。目標是完成改名，同時讓每個階段都有明確的不變條件、進入門檻、退出門檻與回滾路徑。

本題中的 2 億筆資料、80 個執行個體與 30 分鐘是面試假設。「零停機」是指不安排計畫性停機並保護既有 SLO，不代表可以忽略短暫鎖定、失敗重試或降速。核心考察能力是把資料庫變更拆成多個向前、向後相容的發布階段，因此歸入 backend。分庫分表、跨資料庫遷移與多主衝突不在第一輪範圍。

面試官考察重點

第一項訊號是候選人是否先辨識相容性問題。直接執行 RENAME COLUMN username TO handle 後，舊執行個體仍然存取 username，新執行個體只認得 handle；在 30 分鐘混合版本期間至少一方會失敗。資料庫陳述式執行得快，不代表應用程式發布就是零停機。

第二項訊號是能否區分 Schema 遷移、資料遷移與程式切換。安全方案通常採用 expand-and-contract：先增加舊程式可以忽略的新結構，再發布相容程式、分批回填與驗證，之後切換讀寫，最後才刪除舊結構。每一步都應獨立發布、可觀測、可重試，不能把 2 億筆資料更新與破壞性 DDL 塞進同一個發布交易。

第三項訊號是理解 PostgreSQL 的鎖與掃描邊界。ALTER TABLE 的不同子命令需要不同鎖，未特別說明時會取得 ACCESS EXCLUSIVE。增加一個沒有預設值的可空欄位不需要重寫整張資料表，但仍要取得鎖；若它排在長交易後等待，後續請求也可能繼續排隊。NOT VALID、VALIDATE CONSTRAINT 與 CREATE INDEX CONCURRENTLY 可以縮小對並行寫入的影響，但每一種都有自己的鎖、負載與失敗復原語意。

最後看候選人是否以不變條件與門禁推動發布。強回答需要說明所有 writer 何時相容、如何發現漏寫、回填如何保持冪等、何時允許只讀新欄位、每個階段可以回到哪裡，以及舊欄位何時真正失去復原價值；只列出「加欄位、雙寫、回填、刪欄位」還不夠。

回答前需要釐清的問題

• 所有 writer 在哪裡？ 除了 Web 服務，還要盤點 worker、排程工作、管理腳本、資料庫函式、觸發器、CDC、ETL、BI 查詢、view 與外部整合。一個未升級的 writer 就能持續製造不一致。
• 遷移期間兩欄是否必須完全相等？ 本題是純改名，所以答案是必須。若 handle 還要正規化、改變大小寫規則或允許使用者重新選擇，回填與衝突處理會成為另一類資料遷移。
• username 的唯一性如何實作？ 需要把等價的唯一索引或約束複製到 handle，並確認大小寫、collation、空值與 partial index 語意沒有改變。
• 應用程式與資料庫能否分開發布？ 必須可以。若 Schema 變更與應用程式只能在一個不可拆交易中部署，就無法跨多個相容階段推進。
• 允許多長的遷移週期？ 2 億筆資料的回填可能持續數小時或數天。方案應允許跨發布執行，並定義暫停、恢復、完成判定與舊欄位保留期間。
• 回滾目標是什麼？ 回滾應用程式版本、停止回填、恢復舊欄位寫入與復原已刪除資料是四件不同的事。刪除舊欄位後，一般應用程式回滾已經不再安全。

30 秒回答框架

「我會採用 expand-and-contract，原地 rename 不進入方案。先用短 lock_timeout 增加可空的 handle，拿不到鎖就退出重試。第一版相容應用程式仍讀 username，但在同一個資料庫交易裡同時寫兩欄；等 80 個執行個體與所有 worker 都升級後，再按主鍵範圍用小交易冪等回填 handle IS NULL 的資料列。整個過程監控空值數、不一致數、寫入錯誤、鎖等待、p99、複寫延遲、WAL 與 autovacuum。回填完成後並行建立新唯一索引，先加 NOT VALID 檢查約束再獨立驗證，最後設定 NOT NULL。第二版優先讀 handle、回退 username 並繼續雙寫；觀察通過後再只讀 handle，接著停止舊欄位寫入。舊欄位至少保留完整回滾期間，確認程式、工作、view 與消費者都不再引用後才刪除。任何門禁失敗都停在目前的相容階段，不把刪欄位當成一般回滾點。」

分步深入解答

先寫出遷移不變條件與階段門禁

執行 DDL 前先定義四條不變條件：

1. 混合版本期間，username 仍是可回滾的真實來源；handle 可以是空值，但非空時必須與它相等。
2. 相容版本部署到全部 writer 後，任何新寫入都必須在同一個交易中同時更新兩欄，不能出現一欄成功、一欄失敗。
3. 只有空值數與非空不一致數都為零、所有 writer 已相容，而且新索引與約束有效時，讀取路徑才可脫離舊欄位。
4. 只有線上版本、背景工作、view、報表、CDC 與回滾版本都不再需要 username 時，才允許收縮 Schema。

每個階段都記錄部署版本、開始時間、負責人、進度游標、監控連結與退出條件。遷移 runner 應持有互斥租約或使用資料庫 advisory lock，避免兩個執行者同時推進同一步；每一步也要有唯一版本號與持久化成功紀錄，重新啟動時讀取狀態並從斷點繼續。

階段一：只擴展 Schema，不改變現有行為

先在接近正式環境資料量的副本上演練 DDL，並檢查長交易、鎖佇列與磁碟餘量。線上增加一個沒有預設值的可空欄位：

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE users ADD COLUMN handle text;
COMMIT;

沒有預設值的可空欄位不需要重寫 2 億筆資料，但 ALTER TABLE 仍需要取得強鎖。lock_timeout 讓無法迅速取得鎖的嘗試及時失敗，再由發布系統稍後加入抖動重試。執行前要找出異常長交易；執行時監控誰正在等待誰。不能無限等待，因為排隊中的強鎖可能讓後續存取該表的請求一起堆積。

這一步完成後，舊應用程式完全忽略 handle，因此可以獨立回滾應用程式。不要同時加入 NOT NULL、易變預設值、唯一約束與全量更新；這會把 metadata 變更、資料表掃描、索引建立與資料寫入放大耦合在一次高風險操作中。

階段二：發布相容 writer，舊欄位暫時仍是讀取真源

發布版本 A：

• 讀取路徑繼續只讀 username，使用者行為保持不變。
• 建立與更新使用者時，在同一個 SQL 交易中把相同值寫入 username 與 handle。
• 寫入任一欄失敗就回滾整個交易，禁止把雙寫拆成兩個非同步請求。
• 記錄雙寫嘗試、失敗與兩欄不一致指標，但不記錄真實使用者名稱。

版本 A 滾動發布期間，尚未升級的執行個體仍只寫 username，所以此時出現新的 handle IS NULL 是允許的。必須等 80 個 Web 執行個體、worker、排程工作與獨立消費者都回報相容版本後，才關閉這個例外。若無法確認所有 writer，例如仍有第三方程式直接寫入資料庫，可以暫時使用資料庫觸發器維持兩欄一致；代價是隱藏寫入邏輯、額外寫入成本，以及複寫、CDC 與故障排查更複雜。觸發器應被視為有明確刪除日期的遷移設施。

階段三：按主鍵範圍冪等回填

所有 writer 相容後啟動背景回填。使用主鍵 keyset 範圍，不用 OFFSET，每批在短交易內執行：

UPDATE users
SET handle = username
WHERE id > $1
  AND id <= $2
  AND handle IS NULL;

handle IS NULL 讓這一步可以安全重試，也避免覆蓋已經由線上請求寫入的值。runner 持久化最後完成的主鍵範圍；程序可在任一批次後退出，重新啟動時從已確認範圍繼續。初始只開一個 worker，以實測批次大小與間隔推進，不事先承諾固定吞吐量。

每批提交後觀察寫入 p99、資料庫 CPU、鎖等待、複寫延遲、WAL、磁碟、dead tuples 與 autovacuum。任何指標接近線上門檻就縮小批次或暫停。回填以在 SLO 內穩定完成為目標。若資料表存在熱點資料列，可縮短交易或在離峰時段推進，不能用一個覆蓋 2 億筆資料的長交易換取腳本簡短。

同時執行兩個獨立驗證：

SELECT count(*) FROM users WHERE handle IS NULL;

SELECT count(*)
FROM users
WHERE handle IS DISTINCT FROM username;

第一個指標應持續下降到零。第二個利用 IS DISTINCT FROM 同時捕捉空值差異與非空值不一致；任何非零結果都要停止切換，依主鍵範圍定位來源。不能讓回填靜默覆蓋非空衝突，因為那可能掩蓋仍在執行的舊 writer 或錯誤的新邏輯。

階段四：補齊索引與約束

若 username 原本唯一，新欄位也需要等價的唯一索引。完成回填並確認沒有重複後，在交易區塊之外執行：

CREATE UNIQUE INDEX CONCURRENTLY users_handle_key
ON users (handle);

並行建立允許正常寫入繼續，但需要更多工作、兩次資料表掃描並等待相關交易；它仍會消耗 CPU 與 I/O。失敗後還可能留下 INVALID 索引，復原流程應先檢查 catalog，刪除失敗產物後再重試，不能只依命令結束碼假設資料庫已回到原狀。

非空約束也要分階段建立：

ALTER TABLE users
  ADD CONSTRAINT users_handle_not_null
  CHECK (handle IS NOT NULL) NOT VALID;

ALTER TABLE users
  VALIDATE CONSTRAINT users_handle_not_null;

ALTER TABLE users
  ALTER COLUMN handle SET NOT NULL;

ALTER TABLE users
  DROP CONSTRAINT users_handle_not_null;

NOT VALID 先阻止後續寫入製造新違規，但不立即掃描既有資料；VALIDATE CONSTRAINT 再以較低鎖級別檢查既有資料列。有效的 CHECK 可以證明沒有空值，讓後續 SET NOT NULL 跳過一般全表掃描。每條 DDL 仍應使用短鎖等待、獨立執行與線上監控，不能因為「並行」或「不需重寫」就當作沒有成本。

階段五：切換讀取，再停止舊欄位寫入

發布版本 B：優先讀取 handle，空值時回退 username，並繼續在同一個交易中雙寫。雖然門禁要求此時空值為零，回退仍替回滾與異常隔離保留一層相容性。先 canary，再逐步擴大；比較新舊欄位讀取結果、錯誤率與業務指標。

觀察期通過後發布版本 C：只讀 handle，仍雙寫兩欄。若讀取路徑發生問題，可以回滾到版本 B 或 A，因為 username 仍保持最新。再經過一個涵蓋背景工作、低頻 API 與完整發布週期的觀察期，才發布版本 D，停止寫入 username。

停止舊欄位寫入是重要的回滾分界點。此後若要回滾到只認得 username 的版本，必須先恢復雙寫，並把這段期間的新值反向補回舊欄位；直接回滾應用程式會讀到過期資料。發布系統應把這個條件寫進 runbook，確保故障處置不依賴值班工程師臨時記憶。

階段六：延後執行收縮

刪除前用程式碼搜尋、查詢日誌、相依 catalog 與消費者清單證明 username 不再被引用。先移除舊索引、約束、觸發器與 view 相依，再在獨立發布中刪除舊欄位：

ALTER TABLE users DROP COLUMN username;

刪欄位屬於破壞性邊界。即使 PostgreSQL 不立即重寫整張資料表，舊應用程式、快取的 Schema、view 與外部查詢仍可能立刻失敗；刪除的資料也無法透過一般應用程式回滾復原。因此它至少要晚於讀寫切換一個完整回滾保留期，並與「忽略舊欄位」的程式發布分開。備份用於災難復原，無法提供低延遲的發布回滾。

高品質示範回答

「這個需求的主要風險是 30 分鐘滾動發布期間舊程式與新 Schema 無法同時相容。我會把遷移拆成擴展、相容寫入、回填驗證、讀取切換與延後收縮。

第一步增加可空的 handle，使用短 lock_timeout；拿不到鎖就退出重試。可空且沒有預設值的欄位不會重寫整張資料表，但 DDL 仍會取得強鎖，所以執行前清理長交易並監控鎖佇列。接著發布版本 A，仍讀 username，所有 writer 在同一個資料庫交易中雙寫兩欄。只有 80 個執行個體、worker 與消費者全部升級後，才開始回填。

回填按主鍵範圍使用短交易執行 UPDATE ... WHERE handle IS NULL，持久化進度，失敗可重試。速率由線上 p99、複寫延遲、WAL、dead tuples 與 autovacuum 決定。空值數必須歸零；handle IS DISTINCT FROM username 必須維持為零，非空衝突不能被回填腳本覆蓋。

資料通過後，用 CREATE UNIQUE INDEX CONCURRENTLY 建立等價唯一索引，並處理失敗遺留的 invalid index。非空約束先 CHECK ... NOT VALID，再獨立 VALIDATE CONSTRAINT，最後設定 NOT NULL。版本 B 優先讀新欄位、回退舊欄位並繼續雙寫；版本 C 只讀新欄位但繼續雙寫；觀察完整週期後版本 D 才停止舊欄位寫入。

每個階段都保留回滾路徑：切換讀取前可直接回滾應用程式；只讀新欄位但仍雙寫時可以回到舊讀取；停止舊寫入後，要先恢復雙寫並反向補資料才能回到舊版本。最後確認程式、工作、view、CDC 與報表都不再引用 username，跨過回滾期間後才在獨立發布刪除它。任何門禁不滿足就停在相容狀態，不繼續破壞性收縮。」

常見錯誤

• 直接 RENAME COLUMN → 舊執行個體與新執行個體在滾動期間需要不同欄位名稱 → 透過新增欄位與多版本相容發布完成改名。
• 一次交易更新 2 億筆資料 → 長交易放大 WAL、鎖、複寫延遲、資料表膨脹與故障復原時間 → 依主鍵範圍使用短交易冪等回填。
• 應用程式剛開始雙寫就啟動回填 → 尚未升級的執行個體仍會製造新的空值 → 等所有 writer 相容後，再把空值歸零設為門禁。
• 雙寫使用兩個獨立請求 → 一次逾時就可能只更新一欄 → 在同一個資料庫交易中原子更新兩欄。
• 只檢查 handle IS NULL → 兩欄都非空但值不同仍會漏掉 → 同時檢查 IS DISTINCT FROM，並調查非空衝突。
• 把 ADD COLUMN 視為無鎖 → metadata 操作仍需取得資料表鎖，排隊中的 DDL 可能放大阻塞 → 使用短鎖等待、重試，並監控長交易與鎖佇列。
• 用一般方式建立唯一索引 → 大表索引建立會長時間阻塞寫入 → 使用 CONCURRENTLY，同時處理額外負載與 invalid index 復原。
• 回填完成後立刻刪除舊欄位 → 低頻 worker、view、Schema 快取或應用程式回滾仍可能依賴它 → 跨完整觀察與回滾期間延後收縮。
• 把備份當作回滾按鈕 → 復原 2 億筆資料的時間與影響遠大於應用程式回滾 → 在刪欄位前持續保留可線上回退的相容結構。
• 承諾「零影響遷移」 → DDL、索引與回填都有鎖或資源成本 → 承諾不安排停機、守住 SLO，並在越界前自動暫停。

追問與應對

追問一：為什麼不直接替 handle 加預設值？

PostgreSQL 對非易變常數預設值可以避免整表重寫，但常數無法表達「複製每列現有的 username」。即使某個預設值在物理上很快，它仍要取得 DDL 鎖，也不能解決舊執行個體只寫舊欄位的問題。本題仍需要相容 writer 與資料回填；是否替未來新增資料設定預設值，應由業務語意決定，不能拿來取代遷移流程。

追問二：如果 handle 需要轉小寫並重新保證唯一呢？

這已不再是純改名。先定義正規化函式與衝突策略，在唯讀副本或離線工作中統計 lower(username) 的碰撞，再決定保留、加後綴或要求使用者處理。回填要保存原始值與轉換狀態，唯一索引只能在衝突歸零後建立。雙寫路徑必須使用同一套正規化實作，不能讓線上寫入與回填腳本各自解讀規則。

追問三：所有 writer 無法同時升級怎麼辦？

若直接寫入資料庫的外部系統無法快速升級，可以在資料庫內增加暫時同步觸發器，讓任一舊欄位寫入補齊新欄位，並記錄觸發次數以找出遺留呼叫端。觸發器要處理同時提供兩欄但值不一致的請求，通常直接拒絕；也要評估遞迴、複寫與 CDC 語意。所有呼叫端遷移後，先停用並觀察，再刪除觸發器，避免永久保留隱藏業務邏輯。

追問四：回填期間主資料庫的複寫延遲持續升高怎麼辦？

先暫停新批次，讓副本追上；此時增加 worker 只會繼續放大 WAL。檢查批次大小、提交間隔、WAL 產生率、長查詢與 autovacuum，再以更小批次和更低並行度恢復。若讀取流量依賴副本，複寫延遲本身就是使用者可見風險；回填完成時間必須服從線上 SLO。

追問五：CREATE UNIQUE INDEX CONCURRENTLY 失敗後能直接重跑嗎？

不能盲目重跑。同名 invalid index 可能仍留在 catalog；並行唯一索引在失敗階段也可能已對其他交易執行唯一性檢查。先檢查索引是否有效、確認失敗原因與資料衝突，依 runbook 刪除失敗索引，再重新建立。這個命令不能放在一般交易區塊中，遷移工具也必須支援這種執行模式。

追問六：哪個階段最難回滾？

停止舊欄位寫入後，舊資料開始落後；刪除舊欄位後，資料與 Schema 都跨過破壞性邊界。前者需要恢復雙寫並反向補齊後，舊版本才安全；後者通常只能向前修復或從備份復原。因此發布計畫應把這兩個動作拆開，並讓舊欄位在足夠長的觀察期間持續保持最新。