ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQL 11 での DEFAULT 指定した列追加時の処理速度改善は NOT NULL 制約との組み合わせに限らない

概要

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 制約の有無にかかわらず、この改善の恩恵を受けることができます。

検証

検証環境を以下に示します。

検証に用いたクエリを以下に示します。

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 の追加性能の追加改善