概要
PostgreSQL 11 にて ALTER TABLE で列を追加する際に、NULL でないデフォルト値を指定しても全行の書き直しが不要となりました。これにより、PostgreSQL 10 以前では長時間かかることのあった処理がごく短時間で済むようになりました。
この改善を解説する記事では NOT NULL 制約と組み合わせているケースがありますが、実際には NOT NULL 制約無しでも同様の効果が得られます。
解説
PostgreSQL 11 にて ALTER TABLE で列を追加する際に、NULL でないデフォルト値を指定した場合の処理が変更されました。これにより処理時間の短縮が期待できます。
ALTER TABLE が列を追加するにあたって、全行の書き直しをすることなく非NULLの定数デフォルト値を付与することができるようになりました。 (Andrew Dunstan, Serge Rielau) (11)
PostgreSQL 11 に関する技術情報
ADD COLUMNで列が追加され、非変動性のDEFAULTが指定されたときには、デフォルトは宣言時に評価されてテーブルのメタデータに格納された結果です。 この値は全ての既存行の列に使われます。 DEFAULTが指定されなかった場合にはNULLが使われます。 どちらの場合もテーブルを書き直す必要はありません。
ALTER TABLE
内部処理についてはこちらの記事で解説されています。
A Missing Link in Postgres 11: Fast Column Creation with Defaults — brandur.org
この改善を解説する記事では NOT NULL 制約と組み合わせて検証しているものがいくつか見られました。ですが、実際には NOT NULL 制約の有無にかかわらず、この改善の恩恵を受けることができます。
検証
検証環境を以下に示します。
- CentOS Stream release 8
- PostgreSQL 10.16
- PostgreSQL 11.11
検証クエリを以下に示します。
DROP TABLE item; CREATE TABLE item(id INTEGER NOT NULL, name TEXT NOT NULL); INSERT INTO item SELECT val, 'aaa' FROM generate_series(1, 50000000) val; VACUUM ANALYZE; \timing -- DEFAULT 指定無しの場合 ALTER TABLE item add quantity INTEGER; -- DEFAULT 指定有り ＆ NOT NULL 制約無しの場合 ALTER TABLE item add quantity INTEGER DEFAULT 0; -- DEFAULT 指定有り ＆ NOT NULL 制約有りの場合 ALTER TABLE item add quantity INTEGER NOT NULL DEFAULT 0;
列追加の処理時間は3回測定した中央値を採用しました。
結果を下表に示します。
| 条件 | PostgreSQL 10 | PostgreSQL 11 |
|---|---|---|
| DEFAULT 指定無し | 6.889 ms | 6.872 ms |
| DEFAULT 指定有り ＆ NOT NULL 制約無し | 41246.829 ms | 7.212 ms |
| DEFAULT 指定有り ＆ NOT NULL 制約有り | 41068.293 ms | 8.244 ms |
PostgreSQL 11 では NOT NULL 制約の有無にかかわらず、DEFAULT 指定有りの処理速度改善が確認できました。
補足
今回の記事の主題と少しずれますが、列の追加や変更で依然として処理に時間のかかるケースがあります。
変動性のDEFAULT句を持つ列を追加したり、既存の列の型を変更するには、テーブルとインデックス全体の書き換えが必要になります。
ALTER TABLE
「変動性のDEFAULT句」の例として下記の説明が参考になります。
Due to the relative simplicity of attmissingval, this optimization only works for default values and function calls that are non-volatile. Using it with a volatile function like random() won’t set atthasmissing and adding the default will have to rewrite the table like it did before. Non-volatile function calls work fine though. For example, adding DEFAULT now() will put the transaction’s current value of now() into atthasmissing and all existing rows will inherit it, but any newly inserted rows will get a current value of now() as you’d expect.
A Missing Link in Postgres 11: Fast Column Creation with Defaults — brandur.org
SRA OSS の発行した「PostgreSQL 11 検証レポート」では、値の入った列の型変更で PostgreSQL 11 でも依然として時間を要していることを検証しています。
PostgreSQL 11 検証レポート
4.5. ALTER TABLE .. ADD COLUMN の追加性能の追加改善
