PostgreSQLにおいてALTER文実行前に留意すべき点

はじめに

PostgreSQLデータベースのスキーマ変更やインデックス追加を行う際は、ALTER文実行によるテーブルロックとその影響について事前考慮しておく必要があります。

2022年7月現在、MySQL 5.6以降では後述するオンラインDDLによってALTER文実行時でも多くの場合で内部ロックを獲得せずにスキーマ変更などが行えますが、PostegreSQL 14においては強いロックを獲得します。

ゆえに、マイグレーション時にテーブルレベルロックが獲得されることでアプリケーションが実行するクエリの結果が長時間返せず、ユーザーからアプリケーションが停止して見えたり、サーバーがタイムアウトしてしまう可能性があります。

本記事では、上記問題にできる限り対応すべく、PostgreSQLにおけるALTER文実行前に事前注意しておくべき点を列挙します。

オンラインDDLとはなにか

MySQL 5.1以前において、ALTER文実行時はテーブルの共有ロック（他のセッションからの参照を許可し変更をブロックするロック）を獲得していました。

ロックした上で古いテーブルのデータを新しいテーブルにコピーし、最終的に置き換えて古いテーブルを破棄する方法であり、テーブルのレコードが大きいほどテーブル変更をブロックする時間も長くなる点がネックでした（一応、MySQL5.5などではFast Index Creationという機能が追加されており、DDL実行時にテーブルコピーせずにセカンダリインデックスを変更できるようになったものの、依然変更時はテーブルロックを獲得していました）。

MySQL 5.6以降はオンラインDDL操作が可能になり、多くのDDL実行時にテーブルコピーおよびテーブルレベルロックが不要になりました。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.htmlの「表 14.5 DDL 操作のオンラインステータスのサマリー」におけるインプレース?列を参照すれば、一部のDDL（カラムのデータ型変更など）を除いてDDL実行時にテーブルロックが不要になったことがわかります。

問題は、PostgreSQLにこのオンラインDDLに該当する機能が存在しないことです。PostgreSQLにおいてはほとんどのALTER文実行時に、共有ロックよりも強いロックであるACCESS EXCLUSIVEロックという占有ロックを獲得して他セッションによるテーブル参照すらブロックしてしまいます。

PostgreSQL 14のロック種別については、PostgreSQL: Documentation: 14: 13.3. Explicit Lockingを参照してください。

ALTER文実行前に留意すること

PostgreSQLで実行可能なALTER文によって定義変更できる対象がいくつもあります。そのうち、ここでは比較的DDL操作機会が多いと思われる、テーブルのカラム定義更新とインデックス更新時における留意事項と回避策についてまとめました。

常に以下の回避策を採用する必要はありませんが、更新対象となるテーブルのレコード件数が多い場合は検討をおすすめします。

カラム定義更新

DEFAULT制約のあるカラムを追加する

DEFAULT制約を指定した場合、カラム追加時に既存データすべてに対して更新を行うためテーブルロックの時間が長くなってしまいます。この場合、以下のようにDDLを分割することでロックする時間を短縮できます。

-- まずDEFAULT制約なしでカラム追加
ALTER TABLE [テーブル名] ADD COLUMN [カラム名] [データ型];

-- DEFAULT制約を追加
ALTER TABLE [テーブル名] ALTER COLUMN [カラム名] SET DEFAULT [デフォルト値];

NOT NULL制約のあるカラムを追加する

下記のようにNOT NULL制約を付与してALTER COLUMNを実行してしまうとテーブルがフルスキャンされ、既存レコードに対してデフォルト値が設定可能かチェックしてしまいます（NOT NULL制約を付与するには既存レコードにデフォルト値を設定する必要があります）。

ALTER TABLE [テーブル名] ALTER COLUMN [カラム名] SET NOT NULL;

これを回避する方法として、まず既存テーブルをコピーして新規テーブルを作成し、新規テーブルのカラムに対してNOT NULL制約を追加します。その後、既存テーブルを削除して新規テーブルをリネームします。

この方法であればACCESS EXCLUSIVEロックを獲得せずにNOT NULL制約があるカラムを追加できますが、いかにデータ整合性を保証するか事前検討する必要があり、これは別種の課題をもたらします。

別の方法として、NOT NULL制約でなくCHECK CONSTRAINT制約を用いる方法もあります。PostgreSQL 14のドキュメントでは以下のように説明されています。

A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient.

つまりNOT NULL制約とCHECK CONSTRAINT制約は機能的に同一なようです（パフォーマンス観点など、一部差異は存在するので事前認識はしておいた方が良いです）。ゆえに、下記のようにDDLを分割実行し実質NOT NULL制約をカラムに付与できます。

-- 新しい制約をテーブルに追加する
-- NOT VALIDオプションを付与することで、既存レコードが制約を満たすかどうか判定するためのフルスキャンをスキップする
ALTER TABLE [テーブル名] ADD CONSTRAINT [制約名] CHECK ([カラム名] IS NOT NULL) NOT VALID;

-- 制約を有効化する
ALTER TABLE [テーブル名] VALIDATE CONSTRAINT [制約名];

インデックス更新

CONCURRENTLYオプションをつけることでロックを獲得せずインデックスを追加できますが、実行時間は長くなってしまいます。

おわりに

ビジネス向けかつ日本国内向けのサービスであればJSTの深夜帯などダウンタイムにデータベースのマイグレーションを実行できそうですが、24/365恒常的にアクセス量が安定しているサービスの場合、いかに短いロックでDDLを完了できるかがシビアな問題になるでしょう。

上記の方法を実験するため、本番と同等のデータ量を保存したDBを用意した開発環境を利用するのも良い方法だと思います。

参考文献

漢(オトコ)のコンピュータ道: ALTER TABLEを上手に使いこなそう。

PostgreSQLで安全にテーブル定義を変更する - LCL Engineers' Blog

Safe Operations For High Volume PostgreSQL

PostgreSQLのNOT NULL制約のロックを最小化して高速化する（翻訳）｜TechRacho by BPS株式会社