運用中のPostgreSQLのスキーマを無停止で安全に変更する

初めに

こんにちは。ビジネスチャットサービスTypetalkを開発・運用している吉田です。Typetalkではデータの永続化ストレージとしてPostgreSQLを利用しています。扱うデータ量は多く、チャットというサービスの性質上書込み頻度はとても高いです。PostgreSQLを使い日々開発を進めていると、追加する機能によってはスキーマを変更する必要があります。メンテナンスを計画・告知してサービスを止めてしまえば、時間はかかるかもしれませんがスキーマの変更はそう難しくありません。しかしTypetalkはユーザーが日々の業務を進めるために利用しており、サービスが止まるとコミュニケーションが取れなくなります。業務を円滑に進めるためのビジネスチャットツールですので、極力サービスは止めたくありません（最後の計画メンテナンスは2019年6月30日で1年以上前となっています。）。本記事では以下の3つのケースについて、PostgreSQLのスキーマを無停止で安全に変更するため、日々実践しているオペレーションについて取り上げます。

• ALTER TABLEを実行する
• CREATE INDEXを実行する
• 大量のレコードにUPDATEを実行する

前提

環境

本記事で扱うPostgreSQLのバージョンは10となります。バージョン11以降では起こらない問題について記述している箇所があります。また、スキーマレスなDBについてはここでは取り上げません。なお、本記事に直接関係はありませんが、TypetalkではPostgreSQLをAmazon Auroraで使用しています。

ここでの無停止の定義

無停止とはサービス全体、またはその一部が停止することがないことと定義しています。

ここでの安全の定義

安全とはデータベースの負荷の高まりやロック、エラー等により、サービスのレスポンスが低下したりすることがなく、ユーザーが普段通りにTypetalkを使えることと定義しています。

ALTER TABLEを実行する

ALTER TABLEを実行したら起きること

スキーマを変更するALTER TABLE構文は基本的にテーブルレベルのACCESS EXCLUSIVEロックを取得します。これは他のどのロックとも競合するとても強いロックです。つまりALTER TABLEを実行してトランザクションが完了するまでは、INSERTやUPDATEはもちろんSELECTさえ待機することになります。待機が長くなるとDBコネクションも、それを利用するスレッド（TypetalkはJVM上で動作しています。）も解放されず、アプリケーションが応答を返せなくなります。このトランザクションを極力短くすることが、無停止で安全にALTER TABLEするための基本となります。単なるカラムの追加でも構文次第では長時間のロックを取得することになってしまいます。ここからは長時間のトランザクションになってしまうアンチパターンと、その対処について紹介していきます。

1トランザクションでALTER TABLEを複数実行する

起こりうる問題

以下のSQLがあります。

BEGIN;
ALTER TABLE todo ADD COLUMN title TEXT;
ALTER TABLE todo ADD COLUMN description TEXT;
ALTER TABLE user ADD COLUMN name TEXT;
COMMIT;

基本的なことになりますが、このように1トランザクションで複数のALTER TABLEやその他のSQLを実行してしまうと、その分トランザクションが長くなってしまいます。また上のSQLの場合、todoテーブルとは関係のないuserテーブルへのALTER TABLEも終わらないとtodoテーブルもロックを取得したままになり、影響範囲が大きくなってしまいます。

安全に無停止で行うには

こちらは単純に1トランザクションで1つのALTER TABLEを実行するようにします。これで1トランザクションの時間が短くなり、影響範囲も小さくすることができます。

ALTER TABLE todo ADD COLUMN title TEXT;
ALTER TABLE todo ADD COLUMN description TEXT;
ALTER TABLE user ADD COLUMN name TEXT;

制約を追加する

起こりうる問題

次のSQLではtodoテーブルのuser_idカラムにuserテーブルのidカラムへの外部キー制約をつけようとしています。

ALTER TABLE todo ADD CONSTRAINT user_id_fkey FOREIGN KEY(user_id) REFERENCES user(id);

この場合、既存のtodoレコードのuser_idがuserテーブルに正しく存在するか全てのレコードが検査されます。このため大量のレコードがある場合にトランザクションが長くなってしまいます。

安全に無停止で行うには

ADD CONSTRAINT構文のNOT VALIDオプションを利用します。このオプションをつけて制約をつけると新規のレコードに関しては制約が適用されますが、ALTER TABLEと同時に行われる既存レコードの制約検査をスキップさせることができます。このためトランザクションの時間を短くすることができます。

ALTER TABLE todo ADD CONSTRAINT user_id_fkey FOREIGN KEY(user_id) REFERENCES user(id) NOT VALID;

その後VALIDATE CONSTRAINT構文を実行して、制約の検査を別途行います。この構文はSHARE UPDATE EXCLUSIVEロックしか取得しません。このロックはALTER TABLEやVACUUMコマンドとしか競合しない弱いロックになり、通常のINSERT/UPDATE/SELECTは問題なく利用することができるため安心です。

ALTER TABLE TODO VALIDATE CONSTRAINT user_id_fkey;

NULLを不許可にしたカラムを追加する

起こりうる問題

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

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

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

安全に無停止で行うには

段階を踏んでALTERを行うと良いです。まずはNOT NULL制約、DEFAULTを使わずにカラムを追加します。

ALTER TABLE todo ADD COLUMN completed BOOLEAN;

この後、アプリケーションでは新レコードに対してcompletedカラムにfalseをセットするようにクエリを変更し一度リリースします。新しくINSERTされるレコードにはcompletedが入るようになります。

次に、既存レコードに値をセットするようにUPDATE文を実行します。これで全てのレコードがcompletedカラムに値を持つようになります。* こちらもケアすべきことがありますので、後述する「大量のレコードにUPDATEをかける」を参照してください。

最後にcompletedカラムをNULL不許可にしますが、ここでも注意すべきことがあります。以下のようにALTER COLUMNでNOT NULL制約を付けてしまうと、本当にそのカラムがNOT NULLにできるのかチェックするために全てのレコードが検査されてしまい、長時間のロックを引き起こす場合があります。

ALTER TABLE todo ALTER COLUMN completed SET NOT NULL;

これを回避するにはNOT NULL制約ではなくCHECK制約を前述の外部キー制約の追加時に紹介したNOT VALID/VALIDATE CONSTRAINT構文を利用して追加し、nullでないことを検査・保証するようにします。NOT NULL制約とCHECK制約は厳密には違うものですが、nullがないことを保証するという意味で同じことが実現できます。

ALTER TABLE todo ADD CONSTRAINT completed_not_null CHECK (completed is not null) NOT VALID;
ALTER TABLE todo VALIDATE CONSTRAINT completed_not_null;

なお検証はできていませんが、PostgreSQL 11以降ではALTER TABLE ~ NOT NULL DEFAULTを利用した場合でも、レコードのデフォルト値更新は遅延されるようになっているので、安全にALTERできるようです。

書き込み頻度の高いテーブルにALTER TABLEを行う

起こりうる問題

DBを用いたアプリケーションを開発する場合、Prepared Statementを利用することがほとんどかと思います。PostgreSQLもServer Prepared Statementsが用意されており、PREPARE構文などが用意されています。

# Prepared Statementを作成
db=# PREPARE find_todo (bigint) AS SELECT * FROM todo WHERE user_id = $1;
PREPARE

# Prepared Statementを実行
db=# EXECUTE find_todo(1);
...
(1 row)

しかし、PREPAREを実行しPrepared Statementを発行したあと、実際にそれを実行するまでの間にスキーマが変更された場合、生成されたPrepared Statementが利用できなくなるためcached plan must not change result typeエラーが起きて、以下のように実行に失敗してしまいます。

# Prepared Statementを作成
db=# PREPARE find_todo (bigint) AS SELECT * FROM todo WHERE user_id = $1;
PREPARE

# スキーマ変更
db=# ALTER TABLE todo ADD COLUMN created TIMESTAMP;
ALTER TABLE

# Prepared Statementを実行するとエラー
db=# EXECUTE find_todo(1);
ERROR: cached plan must not change result type

書込み頻度の高いテーブルの場合、このエラーが発生する可能性が高まります。Typetalkの場合ユーザーがメッセージの投稿に失敗するなどの影響が考えられるため、可能なかぎり避けたい事象になります。ここからはPostgreSQL JDBC Driver（以降pgjdbcとします）の話になりますが、pgjdbcでは同一トランザクション内でcached plan must not change result typeエラーが起きたクエリの前に別のクエリが実行されていない場合は、自動的にこのエラーとなったクエリを再実行してくれます。しかし、エラーが起きる前にすでに別のクエリが実行されていた場合、トランザクション全体をロールバックするしかなくなり、結果としてアプリケーション側は処理を正常に完了できなくなります。

# 自動的にPREPAREも含めて再実行してくれるパターン
- トランザクション開始
- クエリ1: 失敗（cached plan must not change result type）
- トランザクション終了

# トランザクション全体がロールバックされてしまうパターン
- トランザクション開始
- クエリ1: 成功
- クエリ2: 失敗（cached plan must not change result type）
- トランザクション終了

安全に無停止で行うには

pgjdbcにはautomatic savepointというオプションがあります。このオプションを利用するとクエリ毎にセーブポイントを生成することができ、失敗したクエリを再実行することができます。これを利用するにはアプリケーションのJDBC設定にautosaveオプションを指定します。常にセーブポイントを生成するautosave=alwaysなどがありますが、autosave=conservativeを利用するとcached plan must not change result typeなどのPrepared Statementのエラーに関わる場合だけ再実行してくれるのでこちらを利用します。

jdbc:postgresql://127.0.0.1/todo?autosave=conservative

CREATE INDEXを実行する

起こりうる問題

CREATE INDEX構文はSHAREロックを獲得します。そのため大量レコードがありインデックス作成に時間がかかる場合、ROW EXCLUSIVEロックが必要なINSERT/UPDATEが長時間待機してしまいます。

CREATE INDEX todo_user_id_idx ON todo(user_id);

安全に無停止で行うには

CONCURRENTLYオプションを利用し、SHAREロックを獲得しないようにします。潜在的にこのインデックスを利用する可能性のあるトランザクションが終わるまでインデックスの作成が待機されます。他のトランザクションを優先して少しずつインデックスを作成してくれます。

CREATE INDEX CONCURRENTLY todo_user_id_idx ON todo(user_id);

大量のレコードにUPDATEを実行する

起こりうる問題

これ自体はスキーマ変更ではありませんが、スキーマ変更のオペレーションの一部として大量のレコードをUPDATEしなければならない場合があります。いくつか起こりうる問題がありますが、特に2点注目したいものがあります。

ROW EXCLUSIVEロックの長時間化による、UPDATE/DELETE文の待機

基本的なことになりますが、単純に一度に大量のレコードをUPDATEするとトランザクションが終わるまでUPDATE対象となっている全てのレコードへの別トランザクションからの更新ができなくなります。100万件一気にUPDATEしようとすると、別トランザクションからはその100万件中の1レコードさえUPDATEできなくなります。

VACUUMが追いつかないことによる不要タプル領域の肥大化

PostgreSQLにはVACUUMと呼ばれる処理があり、更新によってできた不要タプル領域を回収する処理があります。autovacuum機能を有効にしている場合は自動でVACUUMを行ってくれますが、一気に大量のレコードを更新すると不要タプル領域が肥大化してしまいます。肥大化したタプル領域はVACUUMを実行する時や、通常のSELECT実行時にもI/Oトラフィックを増加させ、DBの性能を大きく低下させる可能性があります。

安全に無停止で行うには

まずはロックされるレコードの範囲を小さくするために、一度にUPDATEする件数を調整して、少しずつUPDATEすることを考えます。TypetalkチームではBETWEEN句などを利用しスクリプトを組んでUPDATEをかけるようにしています。スクリプトでは1トランザクションに許可する最大の時間を設定しておき、それ以上かかるトランザクションが発生した場合は性能に問題が起きつつあると判断しスクリプトを停止して、アプリケーションに影響が出ないようにケアしています。 また、スクリプトでは以下のようなクエリを使いautovacuumが現在実行中かどうかをチェックし、確実にautovacuumが終わるまで次のトランザクションを開始しないようにしています。

SELECT COUNT(*) FROM pg_stat_activity WHERE query LIKE '%autovacuum%todo' AND query NOT LIKE '%pg_stat_activity%'";

他にもautovacuumの設定を見直して頻度や不要タプル領域のしきい値を調整したりということも行っても良いかと思います。

最後に

いかがだったでしょうか？運用中のデータベースのスキーマ定義変更はとても神経を使いますが、PostgreSQLの場合はマニュアルがとても充実しているため、発行される構文のロックの種類や性質を押さえることで、安全に変更することも可能です。また上では触れませんでしたが、上記のような作業を行う際には本番と同じデータ量、同じ性能を持ったDBインスタンスを立て、十分に検証することも行っています。Typetalkでは引き続きサービスを止めず、ユーザーに安心して使い続けてもらえるようにしていきたいと思っています。

参考

• Safe and unsafe operations for high volume PostgreSQL
• PostgreSQLで安全にテーブル定義を変更する
• Adding a NOT NULL CONSTRAINT on PG Faster with Minimal Locking

弊社ヌーラボはユーザーに安心してサービスを使ってもらいたいと思っています。この思いに共感していただける方がいらっしゃいましたら、ぜひ【Typetalk】ソフトウェアエンジニアから応募をお願いします！