Postgres - 常見的 schema change 問題

發表於 2023-12-31 更新於 2024-04-28 分類於 Postgres Disqus：文章字數： 8.3k 所需閱讀時間 ≈ 15 分鐘

幾年前我發了 Postgres - zero-downtime migration 該注意的細節，而這幾天又看到了這篇文章，讓我多學習到 schema change 需要注意的細節，因此另外開一篇新文章寫一下筆記。

Case 1. Schema mismatch

通常在 migration 的時候不外乎是要建立新 table 或是建立新 index，有時候會遇到 already exists 的錯誤，例如：

1	create table t1 (id INTEGER PRIMARY KEY);

出現該錯誤：

1	ERROR: relation "t1" already exists

會遇到這個問題通常是有人在 db 直接操作而不是透過 migration file 去做版本管理才會遇到。這種情況也會遇到像是 stage 跟 prod 的環境發現 index 不一致的問題。

最好的解決辦法是盡量都透過 migration file 去做版本管理並跟著 release flow 去變動，才不會遇到這種問題。

Case 2. Misuse of `IF [NOT] EXISTS`

由 Case 1 衍伸的問題是，該不該使用 IF NOT EXISTS 的方式來避免 migration 出錯？

作者提出的論點是如果不是測試用途的話，不應該用 IF NOT EXISTS 來掩蓋錯誤，否則有可能出現 prod 環境跟 migration file 的 schema 定義不同，例如同個 table 但是欄位不一致，或是 index 名稱一樣但是下 index 的 column 不一樣。

我個人也同意的確不應該掩蓋錯誤，但是因為 test 跟 prod 環境的 migration file 通常都會是一樣的，又不希望 test 跑 migration 因為這個問題而跑失敗，我認為最好的情況是 schema change 都是透過 migration file 而不是人工去連 db 處理，就算緊急狀況要這樣做，也盡量在事後做 migration file 的處理確保版本一樣。

Case 3. Hitting `statement_timeout`

通常 db 沒設定 statement_timeout 預設是不會 timeout 的，這個 case 指的是 migration file 有設定 statement_timeout，會這樣做是為了避免 DDL migration 過久導致 lock 影響 read /write 因此設定 timeout。

要考量的點是 stage 跟 prod 環境因為資料大小的不同，所以 statement_timeout 就不應該設定一樣。

但實際上 migration file 在 stage 跟 prod 通常會用同一份，所以也許 statement_timeout 就是以 prod 需要跑的時間為準。另外在上 migration 之前可以先 clone 一台 prod db 來測試實際資料大小其 migration 大約會跑多久才知道對應的 statement_timeout 應該要設定多少，這樣的話是最保險的。

Case 4. Unlimited massive change

這個 Case 指的是大量的 update 或是 delete 的情境。我個人是覺得量級有 10 萬以上就需要特別注意以下情況：

最好是採取批次更新
注意大量 dead tuple，以及 wal log 也會暴增的問題，另外 checkpoint 也會變頻繁，可以考慮調整 checkpoint 的頻率
如果 update 的欄位有許多 index 參考到的話這些 index 也需要跟著處理
update 或是 delete 結束之後可以跑 vacuum analyze 來清 dead tuple 及調整 statistics

Case 5. Acquire an exclusive lock + wait in transaction

要知道 Postgres 大部分的 DDL change 都會阻擋讀寫，例如常見的添加欄位：alter table t1 add column c123 int8;

這個會拿 AccessExclusiveLock 並且與 select 及 DML 都會 conflict。但因為 Postgres 大部分的 DDL 操作都是改 system table 所以其實是很快的，真正要擔心是遇到以下的狀況：

--tx1
begin             -- tx2
select * from t;  begin;
                  alter table t add column e int not null default 1000; -- tx3
                  -- blocked                                            begin;
                                                                        select * from t;
                                                                        -- blocked

tx 2 跑 DDL change 的時候前面已經有 long running tx 1，因此 tx 2 就必須等待 tx 1 結束，而後續的 tx 也需要在 tx 2 之後排隊。這樣就會大幅影響 read /write 了。

保持好習慣是每一次的 tx 裡面的 DDL change 量盡可能的少，如果要做大量的 DDL change 分散在不同的 tx 慢慢去做比較好。

Case 6. A transaction with DDL + massive DML

衍伸前一個 case，如果一個 migration 包含 DDL + 大量 DML change 導致跑很久，就會影響後續的 tx 的 read /write。例如：

begin;
alter table t1 add column c123 int8;
copy ... -- load a lot of data, taking some time
commit;

好的做法：

DDL 與 DML change 拆開不同的 migration file，除非是針對新的 table 做 DML change
DML change 最好是批次處理，如同前面的 case 所提的做法

Case 7. Waiting to acquire an exclusive lock for long ⇒ blocking others

衍伸 Case 5 提到 DDL change blocking others tx 的問題，要怎麼解決？

可以用 concurrently 的就採用，例如 creat index 可以用 concurrently 的方式，因為只需要拿 ShareUpdateExclusiveLock 是不會跟 select 及 DML 衝突的

使用 statement_timeout 或是 lock_timeout 並且可以搭配 retry + jitter 機制來處理，盡可能降低拿 lock 的時間。

作者這邊有提供一個 function：

do $do$
declare
  lock_timeout constant text := '50ms';
  max_attempts constant int := 30;
  ddl_completed boolean := false;
  cap_ms bigint := 60000;
  base_ms bigint := 10;
  delay_ms bigint := NULL;
begin
  perform set_config('lock_timeout', lock_timeout, false);

  for i in 1..max_attempts loop
    begin
      alter table test add column whatever2 int4;

      ddl_completed := true;

      exit;
    exception when lock_not_available then
      raise warning 'attempt %/% to lock table "test" failed', i, max_attempts;

      delay_ms := round(random() * least(cap_ms, base_ms * 2 ^ i));
      raise debug 'delay %/%: % ms', i, max_attempts, delay_ms;

      perform pg_sleep(delay_ms::numeric / 1000);
    end;
  end loop;

  if ddl_completed then
    raise info 'table "test" successfully altered';
  else
    raise exception 'failed to alter table "test"';
  end if;
end $do$;

也寫了一篇文章特地說明，值得看一下。

在上 migration 之前也可以先檢查當前有沒有 long running tx 遲遲不 commit 或是 rollback 的。

Case 8. Careless creation of an FK

這個 Case 是如果要加入 fk 的話要注意的點是：

兩邊的 table 都要拿到 lock
當引入 fk 的時候需要 reference check

1	alter table orders add constraint fk_orders_customers foreign key (customer_id) references customers (id);

最好是先看一下這兩個 table 是否平常 query loading 很重且很頻繁，並考慮分成兩個 tx 來優化處理：

加入 fk 並帶上 not valid option
1
alter table orders add constraint fk_orders_customers foreign key (customer_id) references customers (id) not valid;
not valid 可以讓 pg 跳過檢查，只讓這個 constraint 對未來的 new row 進行檢查
alter table … validate constraint …;
1
alter table orders validate constraint fk_orders_customers;
因為 validate 這段只會針對現有的值，因此拿的 lock 是 ShareUpdateExclusiveLock 是不會阻擋 select / DML 的。

但如果是 fk 的話其 referenced table 要拿 RowShare Lock 也是不會阻擋 select / DML

可以不跑 validate constraint 嗎？假設你確保你的舊資料是正確的，是可以不跑 validate constraint，但是這個 constraint 會被 PG 一直認為是 not valid，在 query planner 對 join 產生 plan 的時候有可能 estimate row count 的量會出錯，導致 query plan 選到不好的，因此還是建議要跑 validate constraint。

如果不 validate 的話， hack 的做法是直接去改 system table 將其 constraint 設定為 valid：

UPDATE pg_constraint
SET    convalidated = true
WHERE  conrelid = 'public.orders'::regclass
AND    conname = 'fk_orders_customers';

參考來自：https://dba.stackexchange.com/questions/279960/do-i-need-to-validate-constraint

Case 9. Careless removal of an FK

drop fk 不需要做 validate 的檢查，所以只需要注意要拿兩個 table 的 lock 的問題，因此一樣可以考慮設定 lock_timeout。

Case 10. Careless addition of a `CHECK` constraint

check constraint 主要是針對單一個 table 做欄位檢查之類的，而當加入之後一樣會需要 table scan 來確保不違反 constraint。但是它因為也是 constraint 的一種所以一樣可以採用 not valid + validate constraint 的兩步驟作法來優化。

Case 11. Careless addition of `NOT NULL`

當你想對欄位做 NOT NULL 的限制的時候，一般來說有兩種方式：

alter table … alter column c1 set not null
alter table … add constraint … (col1 is not null)

根據官方的說法第一種方式檢查的效能會比第二種好，但根據這篇文章的 benchmark 看起來才差 1%，所以看起來沒什麼差別。

而第一種最大缺點就是需要 table scan 檢查是否符合 not null，第二種使用 check 就能用我們前面提到的 not valid + validate constraint 的做法。

但是 check constraint not null 是不能取代 primary key not null 的，所以如果 table 想增加 primary key 欄位是需要 table scan 檢查 not null 的。

遇到這種情況，可以根據根據不同的 Postgres 版本來處理：

從 Postgres 11 之前的版本沒什麼好解法，只能接受 down time
從 Postgres 11 開始因為 default value 不需要在 rewrite table，所以可以利用以下的方式處理：
1. 新增一個欄位是 not null default，這樣 pg 會幫你快速建立 default value
2. 再對新的欄位進行回填
3. 接著將 default drop 掉，讓 not null constraint 保留下來
以上三步驟只有第二步驟會花費較多時間，但是可以在離峰的時間進行批次處理。

從 Postgres 12 開始：

先新增 check constraint 來檢查 not null in not valid option
validate constraint
接著加入 not null constraint，這時候 PG 會因為先前的 check constraint 已經檢查過了，這邊 not null constraint 就不會做二次檢查，因此少了 table scan 速度就很快
將既有的 check constraint drop 掉

給個例子：

-- short-time exclusive lock
alter table foos 
  add constraint foos_not_null 
  check (bar1 is not null) not valid;

-- seqscan, but without exclusive lock, concurrent sessions can read/write
alter table foos validate constraint foos_not_null;

-- exclusive lock, but foos_not_null proves: there is no NULL in this column
-- so this alter table would be fast
alter table foos alter column bar1 set not null;
-- not needed anymore
alter table foos drop constraint foos_not_null;

Case 12. Careless change of column’s data type

修改 column type 除了要考慮既有的 type 能不能直接轉到新 type，例如 int to bigint，但如果是 varchar to int 就需要透過 using 來告訴 pg 怎麼轉換：

1	ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);

另外一個點要考慮的是會需要 rewrite table，所以如果資料量很大會建議這樣做：

開 new column with new type
將 old column 的值透過 trigger 的方式或是批次處理回填到 new column
將 old column drop 掉
將 new column rename old column name

另外如果 column 上有 index 的話最好是採用 CONCURRENTLY 的方式來處理。

Case 13. Careless `CREATE INDEX`

這個在我之前 migration 文章就提過了，資料量大一率採用 CONCURRENTLY 來 create index 才不會阻擋 write。

因為使用 CONCURRENTLY 事後需要檢查 index 是否有建立成功，失敗的話要記得砍掉重建，不然留著會繼續影響 write performance。

Case 14. Careless `DROP INDEX`

DROP INDEX 是會阻擋 read /write 所以最好還是使用 CONCURRENTLY 來處理，而另外在 Postgres 12 支援 REINDEX CONCURRENTLY。

這邊順便說一下如果沒加 CONCURRENTLY 的話 REINDEX VS DROP + CREATE 差別在哪：

REINDEX 類似於刪除並重新建立 index，但是會阻擋 write，此外還會對該 index 進行 ACCESS EXCLUSIVE 而 query planner 會對 index 拿 ACCESS SHARE 因此還是會阻擋 read，如果該 query 需要拿該 index 的話，除非有 prepared statement 且不需要拿該 index 就不會被阻擋
DROP INDEX 會拿 ACCESS EXCLUSIVE 因此阻擋 read /write 而 CREATE INDEX 只會阻擋 write 因為是新的 index，自然不會有任何 read 會使用這個新的 index

Case 15. Renaming objects

rename 的話因為只改 metadata 不需要 table scan 所以速度很快，比較要考慮的點是舊版本 client 的問題，常見的解法是類似於 Case 12：

開一個新的 column
回填新 column by old value from old column
讓所有 client 都改用新版也就是拿新 column
將 old column drop 掉並 rename 新 column

這邊提供另外一個解法是透過 view 來控制 client 端：

begin;
    alter table my_table rename column old_column_name to new_column_name;

    alter table my_table rename to my_table_tmp;

    create view my_table as
        select *, new_column_name as old_column_name
        from my_table_tmp;

commit;

讓舊版的 client 仍然有 old_column 可以讀取，接著 migrate 所有 client 用 new_column

就可以將 view drop 掉並重新 rename table

Case 16. Add a column with `DEFAULT`

前面有提到在 PG 11 以前 default 是需要 rewrite table 的，但現在 PG 11 開始就不需要了，因此就沒有持有 lock 太久的問題存在了。

Case 17. Leftovers of `CREATE INDEX CONCURRENTLY`

因為 CONCURRENTLY 是無法在 tx 執行的，而且 index 有可能建立失敗並遺留下來，因此事後需要做檢查的：

1
2
3

select indexrelid, indexrelid::regclass as indexname, indisvalid
from pg_index
where not indisvalid and indexrelid::regclass::text = 'test_idx';

另外從 Postgres 12 開始有提供 pg_stat_progress_create_index view 來確認當前 create index 的進度。

Case 18. 4-byte integer primary keys for large tables

不管是訂 primary key 還是一般的 column 可能會遇到要選 int 類型還是選 big int ，會想要省空間而選用 4 byte integer，但還要考慮到 Column Tetris 的問題，所以可能並不會真的省空間，這個要探討起來值得另外開文章來說明，不管怎樣我個人認為是統一都用 big int 是沒什麼太大差別，還能避免未來的轉換 type 的問題。

總結

這篇文章主要是整理 migration 要跑 schema change 要注意的點，基本上就是先看會拿怎樣的 lock，而這個 lock 是否會阻擋 read /write，有 concurrently 就用比較好，另外就是 DDL 跟 DML 能分開跑就分開跑會比較理想。

最後我覺得是當 migration 要跑在資料量的 db 的時候，最好的辦法是模擬一台 db 並實際在上面跑 migration 看會花多少時間最準，當然因為很難模擬 prod db 的 client 進來的流量，但至少可以模擬同樣的配備下單跑 migration 會花多少時間而去決定要怎麼設定 timeout 避免 lock 拿太久的問題。

Case 1. Schema mismatch

Case 2. Misuse of IF [NOT] EXISTS

Case 3. Hitting statement_timeout