PostgreSQL 実践講座 #1 スキーマ・マイグレーション — サービスを止めずにテーブルを変える

インフラ PostgreSQL データベース
読了 4分

基礎編が「DB を理解する開発者」を作ったなら、実践編は「稼働中の DB を扱える開発者」が目標です。最初の主題は、一番頻繁にやる運用作業、スキーマ変更です。開発環境ではどんな ALTER TABLE も即座に終わりますが、毎秒数百クエリが流れる本番のテーブルでは、同じ文がサービスを止めることがあります。この差を知ることが実践編の入り口です。

マイグレーションはコードです #

前提から立てておきます。スキーマ変更は psql に手で打つものではなく、バージョン管理されるマイグレーションファイルでやります(Flyway、dbmate、Django・Rails・Alembic など何でも)。すべての環境(ローカル・ステージング・本番)が同じ順序で同じ変更を踏んでこそ、「ステージングでは動いたのに」が消えます。そして PostgreSQL の大きな強みがここで効きます。DDL もトランザクションになります。 マイグレーション 1 つ(テーブル作成 + インデックス + 権限)を BEGIN/COMMIT で束ねれば、途中で失敗しても半分だけ適用された状態が残りません。これができない DB は多く、そこから来た方がうらやましがるところです。

危険な変更と安全な変更 #

ALTER TABLE の危険度は 2 つの質問で決まります。どれだけ強いロックを、どれだけ長く握るか。 ほとんどの ALTER TABLE はテーブル全体の排他ロック(ACCESS EXCLUSIVE)を取ります。ロックを取ること自体は避けられず、要は「握ったままやる仕事が即座に終わるか」です。

変更危険度理由
カラム追加(DEFAULT 付きでも)安全メタデータの修正だけで即終了(PostgreSQL 11 から DEFAULT も書き換え不要)
CREATE INDEX危険ビルドの間ずっと書き込みを止める
NOT NULL 追加危険全行の検証の間ロックを保持
カラム型変更(書き換えが必要)非常に危険テーブル全体を書き換える

ここに罠がもう 1 つあります。ロック待ちそのものが障害になります。 長い SELECT が 1 本流れている間に ALTER TABLE が待つと、その後ろのすべてのクエリ(普通の SELECT まで)が ALTER の後ろに並びます。だからマイグレーションのセッションには SET lock_timeout = '3s' のような上限をかけて、ロックが取れなければサービスを止める代わりにマイグレーションが失敗するようにするのが定石です(ロックの原理は実践第 6 回で掘り下げます)。

安全な形に変える方法 #

インデックスは CONCURRENTLY で作ります。

CONCURRENTLYでインデックス作成 
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

書き込みを止めずにインデックスを作ります。代わりにルールが 2 つ付いてきます。トランザクションの中では使えないのでマイグレーションツールの「トランザクションなしで実行」オプションが必要で、失敗すると INVALID 状態のインデックスが残るので、確認して DROP して再試行します。

NOT NULL は段階に分けます。 本番のテーブルに ALTER TABLE ... SET NOT NULL を直接打つと、全行の検証の間ロックを握ります。安全な手順はこうです。

NOT NULLを3段階で追加 
-- 1) 検証しない制約を先に追加(即終了)
ALTER TABLE orders ADD CONSTRAINT orders_email_not_null
    CHECK (email IS NOT NULL) NOT VALID;

-- 2) 既存の行を検証(弱いロックで進行。時間がかかってもサービスは動きます)
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_not_null;

-- 3) SET NOT NULL は検証済みの CHECK を根拠に即座に終わります
ALTER TABLE orders ALTER COLUMN email SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_email_not_null;

核心のアイデアは「時間のかかる仕事(検証)を弱いロックの区間へ追いやり、強いロックの区間には即座に終わる仕事だけを残す」です。外部キーの追加も同じ形(ADD CONSTRAINT ... NOT VALID → VALIDATE)で解きます。

拡張-移行-縮小: 巻き戻せる変更 #

カラム名の変更や型変更のように「一発でやるとアプリと DB が同時に変わらなければならない」変更は、expand-contract パターンで解きます。① 新しいカラムを追加し(拡張)、② アプリが両方に書きながらバックフィルを回し(移行)、③ アプリが新カラムだけを使うようになってから旧カラムを消します(縮小)。段階ごとにデプロイとロールバックができるのが、このパターンの価値です。バックフィルの UPDATE は一発ではなく数千行単位のバッチに分けて回します(一発の巨大な UPDATE は、基礎第 6 回の MVCC のとおりテーブル全体の新バージョン、つまり dead tuple の爆弾でもあります。この後始末が実践第 5 回 VACUUM の主題です)。

まとめ #

  • マイグレーションはバージョン管理されるコードです。PostgreSQL は DDL トランザクションが効くので、半端に適用された状態を残さずに済みます。
  • 危険度は「どれだけ強いロックをどれだけ長く」で判断します。カラム追加は安全、インデックス・NOT NULL・型変更は要注意です。
  • マイグレーションのセッションには lock_timeout をかけます。ロック待ちの行列こそが障害の実際の姿です。
  • インデックスは CONCURRENTLY、NOT NULL・FK は NOT VALID → VALIDATE の 2 段階が定石です。
  • 名前変更・型変更は expand-contract で、バックフィルはバッチに分けてやります。次回はコネクションプールです。
X