LCL Engineers' Blog

バス比較なび・格安移動・バスとりっぷを運営する LCLの開発者ブログ

PostgreSQLで安全にテーブル定義を変更する

PostgreSQL

Webエンジニアの森脇です。

PostgreSQLで、サービス稼働中に安易にALTER TABLE等を実行すると、ダウンタイムに繋がることがあります。安全にテーブル定義を変更するために、弊社で気をつけている点を紹介します。

なお、本記事の内容は PostgreSQL 9.5.4 環境で確認しています。

PostgreSQLのロックについて

参照のみのテーブルに対して、ALTER TABLEを実行した場合でもダウンタイムに繋がることがあります。原因について理解するために、PostgreSQLのロックについて簡単に紹介します。

PostgreSQLでは、SELECTでも「ACCESS SHARE」というロックを獲得します。最も弱いロックですが、ALTER TABLE等で獲得される「ACCESS EXCLUSIVE」というロックと競合します。

これは、他のトランザクションでSELECTしているテーブルに対しては、ALTER TABLEが実行できないということを意味します。

セッションA セッションB
begin; begin;
select * from A;
alter table A add foo varchar; → 待ち状態になる
commit;
→　alter tableが実行される

また、ALTER TABLE中のテーブルに対して、SELECTを行うこともできません。

セッションA セッションB
begin; begin;
alter table A add foo varchar;
select * from A; → 待ち状態になる
commit;
→ selectが実行される

ALTER TABLEを実行すると該当のテーブルに対しては、更新だけではなく参照も不可となり、サービス提供不能状態に陥る可能性があります。

安全にテーブル定義を変更するための注意点

「ACCESS SHARE」「ACCESS EXCLUSIVE」等のロックの時間を極力短くすることが、サービスの稼働に影響与えないために重要です。

トランザクションの範囲を限定する

前述したように、SELECTでもロックを取得します。バッチ処理であまり考えずにトランザクションを張ってしまうと、意図せず長時間「ACCESS SHARE」ロックを獲得してしまう可能性があります。必要な箇所のみに、トランザクションを限定できないか検討しましょう。

どうしても長時間ロックを獲得してしまう処理がある場合は、その処理が動いていない時にALTER TALBEを実行するようにします。

同一トランザクション内で、複数テーブルに対してALTER TABLEを実行しない

一つのトランザクション内で、複数のテーブルに対してALTER TABLEを実行すると、以下のようなケースでデッドロックが発生する可能性があります。

セッションA セッションB
begin; begin;
select * from A;
alter table B add foo varchar;
select * from B;
alter table A add bar varchar; 
ERROR:  deadlock detected
DETAIL:  Process 1734 waits for AccessExclusiveLock on relation 5116933 of database 5116060; blocked by process 23.
Process 23 waits for AccessShareLock on relation 5116941 of

※ Railsのmigrationを利用している場合に、一つmigrationファイルに複数のテーブルに対する変更を書いてしまうと、デッドロックが発生してしまう可能性があります。

実行コマンド別の注意点

いくつかのコマンドは、レコード数が大きいテーブルに対して実行すると長時間ロックとなってしまいます。主要コマンドについて、回避策を紹介します。

default制約付きのカラム追加

カラム追加は一瞬で完了しますが、default制約を設定すると、カラム追加時に全レコードの更新が発生します。ALTER TABLEの実行時間が長くなるため、default制約を設定したい場合は、コマンドを2回に分けて実行します。

  • 一度 default制約なしでカラムを追加
  • その後にdefault制約を付与する

CHECK制約付きのカラム追加

CHECK制約を設定すると、同じく実行時間が長くなります。NOT VALIDオプションを指定すると、検証をスキップできるため安全にカラムを追加できます。

NOT NULL制約の追加

既存カラムに対するNOT NULL制約の追加時にもテーブルスキャンが発生し、長時間となります。回避策としては、以下のように少し複雑な手順が必要です。

  • 既存テーブルをコピーし、新規テーブルを作成
  • 新規テーブルのカラムに対してNOT NULL制約を追加
  • テーブルをRENAMEし、新旧のテーブルを入れ替える

※ 常に書き込みが発生しているテーブルの場合は、データの整合性をとる仕組みが必要です。

Index追加

Indexの追加は書き込みをブロックしてしまいますが、CONCURRENTLYオプションを付与すると書き込みをブロックせずに追加可能です。

※ ただし、Index追加にかかる実行時間は長くなります。

VACUUM FULL / REINDEX

pg_repackを利用すると、安全に実行できます。

その他

以下の記事では他パターンも詳しく書かれていますので、是非御覧ください。

まとめ

弊社ではPostgreSQLで複数のサービスを運営していますが、今回紹介した方法を利用することで基本的には無停止でサービス稼働ができています。皆様の参考になれば幸いです