題目
線上 PostgreSQL 大表需要新增 CHECK 或外鍵約束。表中可能已有不合規資料，業務又不能接受長時間寫入阻塞。請設計從發現髒資料、加入 NOT VALID 約束、修復歷史資料到 VALIDATE CONSTRAINT 的完整流程，並說明鎖、並發、監控和失敗處理。
面試官考察點
- 能否區分加入約束時的鎖行為與後續驗證掃描。
- 能否解釋
NOT VALID只跳過歷史行掃描，新插入或更新的行仍會被檢查。 - 能否讓資料修復、驗證和應用發布互相協調，而不是直接執行阻塞 DDL。
- 能否在驗證失敗、長交易或回滾時保持約束狀態可見且可恢復。
參考答案
先用唯讀查詢估算違規行數量、索引可用性和長交易，再在低峰期執行 ADD CONSTRAINT ... NOT VALID。這一步不掃描既有表行，約束會立即約束之後的插入和更新；歷史行仍可能不符合約束，因此狀態必須標記為待驗證。
接著以批次修復歷史資料，每批有明確上限、提交邊界和進度記錄。修復邏輯必須與應用規則一致，必要時先部署相容程式碼。修復完成後執行 VALIDATE CONSTRAINT，觀察鎖等待、掃描耗時和資料庫負載。驗證成功後，約束目錄狀態變為有效，遷移才算完成。
外鍵還要確認被引用欄位有合適的唯一約束，並評估驗證期間的寫入和刪除。失敗時保留 NOT VALID 約束以繼續保護新寫入，修復剩餘歷史問題後重試；只有確認不再需要時才刪除約束。
遷移流程
-- 1. 先記錄違規行並建立修復任務
SELECT count(*) FROM orders WHERE total < 0;
-- 2. 快速加入約束；不掃描歷史行
ALTER TABLE orders
ADD CONSTRAINT orders_total_nonnegative
CHECK (total >= 0) NOT VALID;
-- 3. 分批修復歷史資料後再驗證
ALTER TABLE orders
VALIDATE CONSTRAINT orders_total_nonnegative;遷移工具應保存約束名、批次游標、開始與結束時間、驗證結果和操作者。發布前檢查所有應用寫入路徑都遵守同一規則，避免修復腳本與業務邏輯互相覆蓋。
NOT VALID 的價值是把昂貴的歷史掃描從加入約束步驟拆開；驗證階段仍會掃描表並取得相應鎖，不能把它當成零成本操作。先檢查長交易和複製延遲，設定 statement timeout 與鎖等待告警，在可控窗口執行驗證。
驗證過程中，新交易繼續受到約束檢查，歷史行修復則要避免與業務更新互相覆蓋。批次修復使用穩定索引範圍和短交易；必要時用 FOR UPDATE SKIP LOCKED 領取工作，但要評估跳過行是否使進度統計失真。
常見誤區
- 以為
NOT VALID代表約束完全不生效，繼續允許新的髒資料寫入。 - 直接執行
VALIDATE CONSTRAINT，沒有檢查長交易、鎖等待和複製容量。 - 用一次大交易修復所有歷史行，造成膨脹、長時間鎖和難以回滾。
- 只修復 CHECK，忽略外鍵驗證所需的引用索引和刪除路徑。
- 驗證失敗就刪除約束，失去對新寫入的保護和後續診斷線索。
失敗處理與回滾
把遷移狀態分為 planned、not_valid、backfilling、validating、validated 和 aborted。每次狀態變化寫入遷移表和稽核日誌。驗證發現違規行時記錄主鍵樣本和約束名，暫停驗證但保留約束；修復後可從進度點繼續。
如果應用發布需要回滾，相容程式碼仍應能處理舊資料和新約束。刪除 NOT VALID 約束是最後手段，並需要確認新寫入不會再次產生同類問題。任何 DROP CONSTRAINT 都應有審批、備份和重新加入計畫。
可觀測性
至少監控違規行數量、修復速率、剩餘估計時間、驗證掃描進度、鎖等待、長交易年齡、WAL 增長和複製延遲。告警要區分「新寫入違反約束」與「歷史資料尚未驗證」，兩者處理優先級不同。
驗證後查詢 pg_constraint.convalidated 與約束名，確認目錄狀態已更新；把結果、執行計畫和負載窗口寫入遷移記錄。對外報告只使用脫敏主鍵和聚合數，避免把業務資料寫入日誌。
- PostgreSQL 17
ALTER TABLE文件：NOT VALID、VALIDATE CONSTRAINT的鎖和並發語義。 - PostgreSQL 目前約束文件：CHECK、外鍵和歷史資料驗證規則。
- PostgreSQL 17
pg_constraint文件：convalidated等目錄欄位。
追問
為什麼新寫入會被檢查而舊行可以暫時不符合？
NOT VALID 省略的是建立時對既有行的掃描；約束定義仍立即作用於之後的 INSERT 和 UPDATE。這樣可以先阻止髒資料繼續增加，再安排歷史修復。
驗證期間能否繼續寫入？
可以繼續寫入，但驗證會持有相應鎖並讀取表，鎖等待和負載必須可控。新交易產生的行會被檢查，修復任務要避免覆蓋業務更新。
如何估算驗證會執行多久？
根據表大小、掃描計畫、快取命中率、並發讀寫和歷史窗口做壓測或抽樣估計，不能只用行數線性猜測。上線前設定逾時和取消策略。
外鍵遷移還要注意什麼？
確認被引用欄位有唯一約束、索引和穩定的刪除語義。驗證時既要檢查子表既有行，也要觀察並發刪除、更新及鎖衝突。
什麼時候應該放棄並刪除約束？
只有在約束需求被撤銷或設計錯誤，而且已有替代保護時才刪除。驗證失敗本身不是刪除理由；保留 NOT VALID 能繼續保護新資料並保留整改路徑。