[PostgreSQL] 巨大なテーブルでカラム追加・削除する場合に気を付けるべきこと

2011/12/21

ALTER TABLE verybigtable ADD COLUMN col_1 integer DEFAULT 0;

これは要注意です。
このようなことをすると、テーブルの全行に対して更新処理がかかってしまい負荷が一気に上昇します。
サービス稼働中にカラム追加する場合は、DEFAULT制約はつけない方がよいでしょう。

どうしてもDEFAULT制約をつけたい場合は、
１．サービス停止してから上記のADD COLUMNを実行する
または、
２．いったんDEFAULTなしでカラム追加して、直後にDEFAULT制約をつける

ALTER TABLE verybigtable ADD   COLUMN col_1 integer;
ALTER TABLE verybigtable ALTER COLUMN col_1 SET DEFAULT 0;

という２つの方法があります。
方法２の場合は、負荷は全くかかりません。またテーブル内の既存行への変更は行われません。(NULLのまま)

カラム削除する場合

カラム削除の処理(DROP COLUMN)自体は一瞬で終わりますので負荷があがることはありません。
これは、内部的には「そのカラムが見えないようになる」だけだからです。

逆にいうと、不可視になるだけで、テーブルの物理的なディスクサイズが減ってくれません。

ディスクサイズを削減したい(PostgreSQLが占有している不要領域を解放する)場合は、列削除した後で、任意の列に対してALTER TABLEをしてあげましょう。

(負荷が上がるので注意)

ALTER TABLE verybigtable ALTER COLUMN hoge TYPE fuga;

ここでいうhogeは現存する任意の列、fugaはhogeの現在のデータ型です。
このALTER TABLE文は論理的には何の変更も行われませんが、DROP COLUMNで生じた不要ディスク領域を回収する効果があります。

(最新のドキュメントを見ると、VACUUM FULLまたはCLUSTERを実行しても不要領域の回収ができると書かれています。)

参考

明日は@L_starさんです。
よろしくお願いします。

カテゴリ: PostgreSQL