PostgreSQL 11でALTER TABLE … NOT NULL DEFAULTが簡単になっていた

by yoshida

2020/12/23

Nulab

※このブログはヌーラバー Advent Calendar 2020の23日目の記事です。明日はChihiro Itoさんの記事です。

はじめに

こんにちは。ビジネスチャットサービス Typetalkを開発・運用している吉田です。先日PostgreSQLでのALTER文をいかに無停止で安全に行うかという記事を公開しました。その中で以下のようにNULLを不許可にしたカラムを追加する際の注意点について述べていました。（参照 NULLを不許可にしたカラムを追加する）

新しいカラムを追加する際、NULLを不許可にしたいことがあります。その場合よく使われるのがALTER TABLE構文で同時に追加できるNOT NULL制約です。このNOT NULL制約をつけるためには、既存レコードにデフォルト値を設定する必要があるため、DEFAULT構文を同時に使用することになります。

ALTER TABLE todo ADD COLUMN completed BOOLEAN NOT NULL DEFAULT false;

この構文は自動的に既存のレコードにも値をセットしてくれるので手軽なのですが、ALTER TABLEと同時に既存のレコード全てにUPDATEがかかります。これは大量のレコードがある場合にトランザクションを長くしていまいます。

PostgreSQL 10では、ALTER TABLEと同時に既存のレコード全てにUPDATEがかかるという挙動があるため、無停止で安全にカラムを追加するには、ALTERを複数回実行したり、アプリケーションのリリースを段階的に行ったりと、面倒な作業が必要でした。しかしこの挙動は PostgreSQL 11で改善されています。本記事ではその改善点と、実際にパフォーマンスを計測した結果を紹介します。

PostgresSQL 11での改善点

PostgreSQL 11のリリースノートにも以下のように書かれており、今はこの挙動が改善されているようです。

Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE … ADD COLUMN with a non-null column default

どのように改善されているかは A Missing Link in Postgres 11: Fast Column Creation with Defaults にとても詳しく書かれているので参考にしていただきたいですが、要約するとこのようなことが行なわれています。

ALTER時にはデフォルト値のUPDATEは行なわない
INSERT時にはもちろんNOT NULL成約がついているので値が必須
SELECT時などに値が入っていないレコードに関しては、デフォルト値を勝手に返してくれて実際にレコードに値が入ってるかのように見せてくれる

パフォーマンスの計測

リリースノートも読みましたし詳しい挙動についても理解はできました。しかし実際プロダクションのDBで実行するとなると、やはり事前にしっかり検証しておきたいです。次回必要になった際に安心して実行するため、パフォーマンスを計測してみます。

テストデータの準備

ローカルにPostgreSQL 10と11用のコンテナを立ち上げて、両方に同じテーブルを作成しました。

CREATE TABLE todo (
  title TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL
);

alter_test=# \d todo
                            Table "public.todo"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 title      | text                        |           | not null | 
 created_at | timestamp without time zone |           | not null |

次にテストデータをINSERTします。件数がパフォーマンスに大きく影響するので5000万件のテストデータを、ローカルのストレージが爆発しないか気をつけながら投入しました。

INSERT INTO todo(title, created_at)
SELECT RANDOM()::TEXT, NOW() - '1 year'::INTERVAL * ROUND(RANDOM() * 100)
FROM generate_series(1, 50000000);

PostgreSQL 10 で ALTER してみる

まずは、問題になる PostgreSQL 10でのALTERを実行してみます。

ALTER TABLE todo add completed BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE
Time: 44100.056 ms (00:44.100)

なかなか応答が返ってこず、約45000ミリ秒もかかってしまいました。これでは長時間 todoテーブルをロックすることになり、運用中に実行するのは到底厳しいです。

PostgreSQL 11 で ALTER してみる

さて、いよいよ PostgreSQL 11 で実行してみます。

ALTER TABLE todo add completed BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE
Time: 1.599 ms

結果はなんと約2ミリ秒！これだと運用中に実行しても問題ないです。アプリケーションの段階リリースなどの手順も踏まなくてよいので、リリースサイクルにも良い影響がありそうです！

最後に

いかがでしたか？すでに知っている方には簡単な内容だったと思いますが、実際に大量レコードでの検証もできましたので、次回から安心して作業できそうです。Typetalkでは引き続きサービスを止めず、より素早く改善をリリースし、ユーザーに安心して使い続けてもらえるようにしていきたいと思っています。

参考

advent-calendar-2020