運用中の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があります。

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

安全に無停止で行うには

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

制約を追加する

起こりうる問題

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

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

安全に無停止で行うには

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

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

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

起こりうる問題

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

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

安全に無停止で行うには

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

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

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

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

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

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

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

起こりうる問題

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

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

書込み頻度の高いテーブルの場合、このエラーが発生する可能性が高まります。Typetalkの場合ユーザーがメッセージの投稿に失敗するなどの影響が考えられるため、可能なかぎり避けたい事象になります。ここからはPostgreSQL JDBC Driver(以降pgjdbcとします)の話になりますが、pgjdbcでは同一トランザクション内で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のエラーに関わる場合だけ再実行してくれるのでこちらを利用します。

CREATE INDEXを実行する

起こりうる問題

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

安全に無停止で行うには

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

大量のレコードに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が終わるまで次のトランザクションを開始しないようにしています。

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

最後に

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

参考


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

開発メンバー募集中

より良いチームワークを生み出す

チームの創造力を高めるコラボレーションツール

製品をみる