Postgresql

PostgreSQL 是否優化添加具有非 NULL DEFAULT 的列?

  • August 29, 2018

添加NOT NULL具有DEFAULT值的列時 - PostgreSQL 是否優化此操作?

如果表有 n 行,則未優化的 alter-table-add-column 將產生 n 次寫入預設值 - 這顯然會非常痛苦。通過優化,數據庫將立即創建新列,只儲存一份預設值的副本,當在合適的索引資料結構中找不到該列的非預設值時,該副本將返回。

比如Oracle 11g就有這樣的優化

PostgreSQL 中沒有這樣的機制。

但是,您仍然可以避免此類表更改的過度影響。

以下語句在語句/事務期間獲取表上的訪問排他鎖:

ALTER TABLE your_table
   ADD COLUMN new_column integer NOT NULL DEFAULT 0;

此語句更改目錄,然後重寫整個表,以便新列包含所有行中的預設值。如果表有很多行並且被頻繁訪問,這將導致一些臨時問題。

為避免這種情況,請嘗試盡可能短地持有獨占鎖:

ALTER TABLE your_table
   ADD COLUMN new_column integer;
ALTER TABLE your_table
   ALTER COLUMN new_column SET DEFAULT 0;

由於這基本上只是對目錄的一次(實際上是兩次)更改(沒有發生數據更改),因此它將很快完成。然後根據您的需要和表的使用情況,您可以一步或分批將新列更新為預設值,完成後將列設置為NOT NULL.

關於願望成真的更新: PostgreSQL 11 將具有此功能。有關更多資訊,請參見https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/

引用自:https://dba.stackexchange.com/questions/60965