PostgreSQL schema migration evidence lab 的核心責任是把 schema change 轉成 release gate 可使用的 evidence。這篇承接 Online Schema Change 與 Database Migration Playbook。

本篇的範圍是在 local lab 的 accounts 上走一次 expand migration：加 nullable 欄位並觀察 lock、backfill 與 validation、用 NOT VALID 分開加 constraint、保存 query plan，最後整理 contract migration 的 fail-forward / rollback 判斷標準。

Expand Migration

Expand migration 的核心責任是先加入向後相容 schema。以下範例新增 accounts.email，先允許 null。

1psql "$DATABASE_URL" <<'SQL'
2\timing on
3BEGIN;
4ALTER TABLE accounts ADD COLUMN email text;
5COMMIT;
6SQL

新增 nullable column 通常是低風險操作，但仍要記錄 timing 與 lock。正式服務要在低流量窗口或 staging 上先測。

Lock Evidence

Lock evidence 的核心責任是讓 migration 的阻塞風險可見。上面的 expand migration 在幾百毫秒內就 commit，ALTER TABLE 拿的 lock 隨 commit 釋放，在它前後查 pg_locks 都是零列；要看到這個 lock，讓一次同類的 ALTER 停在 commit 之前。

在 migration terminal 用互動式 psql 執行：

1-- migration terminal：同類的 ALTER，先不 COMMIT
2BEGIN;
3ALTER TABLE accounts ADD COLUMN lock_probe text;
4-- 停在這裡，切到另一個 terminal 查 pg_locks；查完回來執行 ROLLBACK，lock_probe 不會留下

另一個 terminal 查：

1psql "$DATABASE_URL" <<'SQL'
2SELECT locktype, relation::regclass, mode, granted, pid
3FROM pg_locks
4WHERE relation IN ('accounts'::regclass, 'ledger_entries'::regclass)
5ORDER BY granted, mode;
6SQL
7# migration terminal 的 transaction 開著時：accounts 的 AccessExclusiveLock，granted = t
8# ROLLBACK 之後再查：0 rows

AccessExclusiveLock 擋住 accounts 上所有讀寫，包括單純的 SELECT；migration 的 transaction 開多久，這些查詢就等多久。

Release gate 要保存 lock mode、duration、blocked session 與 application impact。高風險 DDL 要先改成 expand / backfill / contract。

Backfill and Validation

Backfill and validation 的核心責任是把資料補齊並證明結果符合 domain。

1psql "$DATABASE_URL" <<'SQL'
2UPDATE accounts
3SET email = lower(owner_name) || '@example.test'
4WHERE email IS NULL;
5
6SELECT count(*) AS missing_email
7FROM accounts
8WHERE email IS NULL;
9SQL

大型表要分 batch backfill，避免 WAL、replica lag、autovacuum 與 lock 壓力。每個 batch 要記錄 row count、duration、error 與 lag。

Add Constraint Safely

Add constraint safely 的核心責任是把資料驗證和 constraint 生效拆開。

1psql "$DATABASE_URL" <<'SQL'
2ALTER TABLE accounts
3ADD CONSTRAINT accounts_email_present
4CHECK (email IS NOT NULL) NOT VALID;
5
6ALTER TABLE accounts
7VALIDATE CONSTRAINT accounts_email_present;
8SQL

NOT VALID 讓 constraint 先約束新資料，再用 validation 掃既有資料。這是 PostgreSQL online migration 常用技巧。

Query Plan Evidence

Query plan evidence 的核心責任是確認 migration 後 query 仍走正確路徑。

1psql "$DATABASE_URL" <<'SQL'
2EXPLAIN (ANALYZE, BUFFERS)
3SELECT *
4FROM accounts
5WHERE email = 'ada@example.test';
6SQL

若 email 查詢成為正式 path，要新增 index，並用 CREATE INDEX CONCURRENTLY 評估 lock 與時間。

Contract Migration

Contract migration 的核心責任是在 application 都改用新欄位後，收斂舊欄位或舊 constraint。Contract migration 要比 expand 更謹慎，因為 rollback 空間更小。

Contract release gate：

  1. 所有 app version 已停止讀舊欄位 / 舊行為。
  2. Backfill validation 為零缺口。
  3. Query plan 與 index evidence 已保存。
  4. Rollback path 是 fail-forward 或 restore，兩者擇一寫清楚。
  5. PITR / backup window 符合風險。

Release Gate Note

Release gate note 的核心責任是形成可交付 artifact。

1Migration: add accounts.email
2Expand DDL duration:
3Backfill rows:
4Validation query:
5Lock evidence:
6Query plan:
7Rollback / fail-forward:
8Owner:

完成本篇後，複雜 migration 回到 Online Schema Change；需要跨 DB 遷移則讀 Database Migration Playbook。