PostgreSQL の ALTER TABLE でテーブルがロックされる理由：マイグレーションごとのロックと安全な実行方法

NULL を許容するカラムを 1 つ追加するマイグレーションは、1 ミリ秒もかからずに終わります。それでもアプリケーションを止めてしまうことがあります。実行前にロックを待つ必要があり、待っている間は、そのテーブルに対する他のクエリがすべてその後ろで待たされるからです。この記事では、3 つの psql セッションでロックキューが発生する様子を示し、よく使う ALTER TABLE がそれぞれどのロックを取り、テーブルを書き換えるかどうかを一覧にし、マイグレーションを安全にするパターンを順に説明します。リトライつきの lock_timeout、NOT VALID の後の VALIDATE、そして CREATE INDEX CONCURRENTLY です。以下のステートメントと出力はすべて、Relyt One が動かしているメジャーバージョンである PostgreSQL 17.11 で実行したものです（PostgreSQL 18 に関する 1 つの注記だけは 18.6 で確認しました）。

覚えておくべきことは次の 4 つです。

1. ほとんどの ALTER TABLE は ACCESS EXCLUSIVE ロックを取ります。このロックは書き込みだけでなく読み取りもブロックします。変更自体が一瞬で終わる場合でも、ステートメントはまずこのロックを待つキューに並びます。
2. キューで待っている間、同じテーブルへの新しいクエリはその後ろに並びます。長いトランザクションが 1 つとマイグレーションが 1 つあれば、そのテーブルに触れるリクエストをすべて止めるには十分です。
3. すべてのマイグレーションに lock_timeout を設定してください。そうすれば、トラフィックを無期限にブロックする代わりに数秒であきらめます。そしてリトライします。
4. 高コストなチェックをロックから切り離します。制約は NOT VALID で追加して後から VALIDATE し、インデックスは CONCURRENTLY で構築し、大きなテーブルを書き換えるステートメントは避けます。

この記事の残りでは、それぞれを再現可能な出力とともに示します。

テーブルは 2 つです。100 万件のアカウントと 200 万件の注文です。使い捨てのデータベースに対して psql で次を実行してください。

CREATE TABLE accounts (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  plan       varchar(20),
  score      integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO accounts (id, email, plan, score)
SELECT g, 'user' || g || '@example.com',
       (ARRAY['free', 'pro', 'team'])[1 + g % 3], g % 1000
FROM generate_series(1, 1000000) AS g;

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  account_id bigint NOT NULL,
  total      numeric(10, 2) NOT NULL
);
INSERT INTO orders
SELECT g, 1 + g % 1000000, (g % 10000) / 100.0
FROM generate_series(1, 2000000) AS g;
ANALYZE;

psql セッションを 3 つ開きます。1 つ目では、テーブルを読み取り、開いたままのトランザクションを開始します。実際のシステムでは、遅いレポート、バッチジョブ、あるいはコミットし忘れたアプリケーションが idle in transaction のまま放置した接続がこれに当たります。

-- セッション A
SET application_name = 'a_report';
BEGIN;
SELECT count(*) FROM accounts;
-- トランザクションは開いたままにする

2 つ目では、マイグレーションを実行します。NULL を許容するカラムの追加は、教科書どおりの「安全な」マイグレーションです。

-- セッション B
SET application_name = 'b_migration';
ALTER TABLE accounts ADD COLUMN note text;

これが止まります。3 つ目では、アプリケーションが 1 分間に何千回も実行するようなクエリを実行します。

-- セッション C
SET application_name = 'c_app';
SELECT email FROM accounts WHERE id = 42;

1 行を読むだけで、セッション A も読み取りしかしていないのに、これも止まります。4 つ目のセッションから、誰が誰を待っているのかを Postgres に問い合わせます。

SELECT pid, application_name, state, wait_event_type AS wait_type,
       pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE application_name IN ('a_report', 'b_migration', 'c_app')
ORDER BY application_name;

 pid | application_name |        state        | wait_type | blocked_by |                   query
-----+------------------+---------------------+-----------+------------+--------------------------------------------
 478 | a_report         | idle in transaction | Client    | {}         | SELECT count(*) FROM accounts;
 488 | b_migration      | active              | Lock      | {478}      | ALTER TABLE accounts ADD COLUMN note text;
 495 | c_app            | active              | Lock      | {488}      | SELECT email FROM accounts WHERE id = 42;
(3 rows)

pid は環境によって異なります。何が起きているかは blocked_by カラムが物語っています。C をブロックしているのは A ではありません。A は ACCESS SHARE ロックを保持しており、C も同じモードを求めているので、両者は競合しません。C をブロックしているのは B です。B はあらゆるモードと競合する ACCESS EXCLUSIVE を求めているため、A を待ちます。そして新しいロック要求は、すでに許可されたロックだけでなく、すでに待っている要求と競合する場合にも待たなければなりません。この規則は、読み取りが次々と来てマイグレーションが永遠に実行されない状態を防ぐためにありますが、その副作用として、キューで待つ ALTER TABLE はテーブルの前に立ちはだかる壁になります。

私たちの実行では、セッション A は開始から 8 秒後にコミットし、B はその直後に完了し、C の 1 行の検索には 6.2 秒 かかりました。実際のトラフィックの下では、その間に accounts に触れるリクエストがすべて積み上がり、コネクションプールが枯渇し、1 行のマイグレーションが障害を引き起こしたように見えます。実際そのとおりなのですが、原因はマイグレーションが遅かったことではありません。

lock_timeout は、ロックを上限より長く待ったステートメントをキャンセルします。同じ実験を、セッション B に 2 秒を与えて実行します。

-- セッション B
SET application_name = 'b_migration';
SET lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN note text;

ERROR:  canceling statement due to lock timeout

今回 C が待ったのは最大 2 秒です（私たちの実行では 6.2 秒ではなく 1.8 秒でした）。C は、B が上限に達してキューから抜けるまでしか待ちませんでした。マイグレーションは失敗しましたが、それが狙いです。テーブルを止める代わりに、低コストで失敗したのです。あとはリトライします。

実際にうまく機能させるには、2 つの点が重要です。

マイグレーションのトランザクション内で SET LOCAL を使います。 SET LOCAL は現在のトランザクションの終わりまで有効で、その後は元に戻るため、その接続で次に実行される処理に漏れ出すことがありません。

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

マイグレーションがトランザクションモードのプーラーを経由する場合、これが重要になります。トランザクションごとに別のサーバー接続に割り当てられる可能性があるため、前のトランザクションで実行した通常の SET が ALTER TABLE に適用される保証はありません。Relyt One のプーリング用接続文字列は、トランザクションレベルのプーリングを行う PgBouncer を経由します。マイグレーションは直接接続用の接続文字列で実行し、それでも設定はトランザクション内に置いてください。

バックオフつきでリトライします。ただしロックタイムアウトの場合だけです。 多くのマイグレーションツールはリトライできます。お使いのツールにその機能がなければ、小さなラッパーで十分です。次のラッパーは、マイグレーションがロック待ちをあきらめた場合だけリトライし、最初に成功した時点で終了し、5 回試して失敗します。SQL のタイプミスなど、それ以外のエラーではすぐに失敗します。英語のエラーメッセージと照合するため、マイグレーションのセッションでは lc_messages を英語のままにしてください。

#!/usr/bin/env bash
# 使い方: ./migrate.sh migration.sql
# マイグレーションがロック待ちをあきらめた場合だけリトライする。
set -u
for attempt in 1 2 3 4 5; do
  if output=$(psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 -f "$1" 2>&1); then
    echo "$output"
    exit 0
  fi
  echo "$output" >&2
  if ! grep -q "canceling statement due to lock timeout" <<<"$output"; then
    exit 1  # それ以外のエラー: リトライせずに修正する
  fi
  if [ "$attempt" -lt 5 ]; then
    echo "attempt $attempt hit the lock timeout; retrying in $((attempt * 5))s" >&2
    sleep $((attempt * 5))
  fi
done
echo "giving up after 5 attempts" >&2
exit 1

上の外部キーのマイグレーションを 0042_orders_account_fk.sql として保存し、別のセッションが accounts の行を更新したトランザクションを開いたままの状態で、このラッパーを実行しました。1 回目はタイムアウトになり、そのトランザクションがコミットされた後の 2 回目で成功しました。

BEGIN
SET
psql:0042_orders_account_fk.sql:5: ERROR:  canceling statement due to lock timeout
attempt 1 hit the lock timeout; retrying in 5s
BEGIN
SET
ALTER TABLE
COMMIT

ファイル全体が 1 つのトランザクションで囲まれているため、失敗した試行は完全にロールバックされ、リトライはきれいな状態から始まります。リトライするのは、このようなファイルだけにしてください。CREATE INDEX CONCURRENTLY のようにトランザクション内で実行できないステートメントは、失敗したときに何かを残すことがあり（後述します）、次の試行がそれにつまずきます。

負荷の高いテーブルでマイグレーションを行う前には、それをブロックしそうなトランザクションを探しておく価値もあります。

SELECT pid, state, now() - xact_start AS xact_age, left(query, 50) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 5;

何分も開いたままのトランザクション、特に idle in transaction 状態のものがあると、すべての試行がタイムアウトします。idle_in_transaction_session_timeout（デフォルトでは無効）を設定すると、そのようなセッションは自動的に終了されます。

これは自分で測定できます。ステートメントをトランザクション内で実行し、コミットする前に pg_locks を読み、ロールバックします。Postgres がテーブルを書き換えたかどうかを確認するには、前後でファイルノードを比較します。書き換えでは、テーブルの新しいコピーが新しいファイルに書き込まれるからです。

CREATE VIEW my_locks AS
SELECT relation::regclass AS table_name, mode
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype = 'relation'
  AND relation IN ('accounts'::regclass, 'orders'::regclass)
ORDER BY 1, 2;

BEGIN;
SELECT pg_relation_filenode('accounts') AS before \gset
ALTER TABLE accounts ALTER COLUMN score TYPE bigint;
SELECT * FROM my_locks;
SELECT pg_relation_filenode('accounts') <> :before AS rewritten;
ROLLBACK;

 table_name |        mode
------------+---------------------
 accounts   | AccessExclusiveLock
 accounts   | ShareLock
(2 rows)

 rewritten
-----------
 t
(1 row)

（\gset は結果を変数に格納する psql のコマンドです。）下の表のステートメントはすべて、この方法で 100 万行の accounts テーブルと 200 万行の orders テーブルに対して実行しました。「ブロック」の列については、各ロックを開いたトランザクションで保持し、別のセッションから 500 ms の lock_timeout を設定して 1 行の SELECT と 1 行の UPDATE を試しました。それ以外の影響は、PostgreSQL の競合表 のロックモードから導かれます。

外部キーは 2 つのテーブルをロックするため、別の表にしました。orders が参照する側、accounts が参照される側のテーブルです。

時間はローカルのコンテナで測定したもので、テーブルのサイズとハードウェアに比例して変わるため、絶対値ではなく相対値として読んでください。これらの表には、意外に思われる点がいくつかあります。

• ここに挙げた、カラムを追加・削除・名前変更・型変更するステートメントはすべて ACCESS EXCLUSIVE を取ります。 一瞬で終わるものも含めてです。「一瞬」とはロックを保持する時間のことであり、ロックが必要かどうかとは関係ありません。どれもロックキューを引き起こす可能性があります。
• デフォルト値があっても書き換えが起きるとは限りません。 PostgreSQL 11 以降、揮発性でないデフォルト値はカタログに 1 回だけ保存され、既存の行は読み取られるときにその値を受け取ります。ここでは now() は揮発性でないものとして扱われます。ALTER TABLE の実行時に 1 回だけ評価され、既存の行はすべて同じタイムスタンプになります。gen_random_uuid() や clock_timestamp() のような揮発性のデフォルト値は行ごとに異なる値が必要なので、テーブルが書き換えられます。
• varchar を広げるのは無料ですが、狭めるのはそうではありません。 varchar(20) から varchar(50) や text への変更はカタログを変えるだけです。varchar(10) への変更は、PostgreSQL 17 ではテーブルを書き換えました。
• integer から bigint への変更は必ず書き換えが発生します。 ディスク上の値のサイズが変わるからです。大きなテーブルでは、これが最も計画を必要とするマイグレーションです。新しい bigint カラムを追加し、バッチでバックフィルし、短いトランザクションで切り替えます。
• 外部キーは、参照先のテーブルへの書き込みもブロックします。 変更しているテーブルだけではありません。orders に外部キーを追加すると、accounts への UPDATE も止まりました。
• CREATE INDEX は読み取りを通しますが、書き込みをブロックします。 構築が続く間ずっとです。

ADD CONSTRAINT は通常、ロックを保持したまま既存の行をすべてチェックします。NOT VALID はこのチェックを省略します。制約は新しい行と更新された行にはすぐに適用され、既存の行は後から VALIDATE CONSTRAINT でチェックされます。VALIDATE CONSTRAINT が取るロックははるかに弱いものです。上の表では、外部キーを直接追加すると両方のテーブルへの書き込みが 1.8 秒ブロックされました。2 つに分けると、ブロックする部分は 3 ms で済み、1.2 秒の検証の間は読み取りも書き込みも通りました。

マイグレーション 1 は対策 1 のファイルです。そこで実行済みなら、残りはマイグレーション 2 だけです。

-- マイグレーション 1: すぐ終わるがロックは必要なので、lock_timeout は残す
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

-- マイグレーション 2: 読み取りも書き込みもブロックせずにテーブルをスキャンする
ALTER TABLE orders VALIDATE CONSTRAINT orders_account_fk;

VALIDATE が制約に違反する行を見つけると失敗し、制約は NOT VALID のまま残りますが、新しい書き込みには引き続き適用されます。データを修正してから、もう一度検証してください。

PostgreSQL 17 では、SET NOT NULL に NOT VALID の形式はなく、ACCESS EXCLUSIVE の下でテーブル全体をスキャンします。ただし、検証済みの CHECK 制約によってそのカラムに NULL がないことがすでに証明されている場合は、スキャンを省略します。この判断は client_min_messages = debug1 で確認できます。

ALTER TABLE accounts ADD CONSTRAINT plan_not_null CHECK (plan IS NOT NULL) NOT VALID;
ALTER TABLE accounts VALIDATE CONSTRAINT plan_not_null;

SET client_min_messages = debug1;
ALTER TABLE accounts ALTER COLUMN plan SET NOT NULL;
RESET client_min_messages;

ALTER TABLE accounts DROP CONSTRAINT plan_not_null;

DEBUG:  existing constraints on column "accounts.plan" are sufficient to prove that it does not contain nulls

CHECK 制約がない場合、同じステートメントは代わりに DEBUG: verifying table "accounts" を出力します。これがフルスキャンです。カラムが NOT NULL になれば CHECK 制約は冗長なので、削除してかまいません。

PostgreSQL 18 では、これがもっと短くなります。ALTER TABLE accounts ADD CONSTRAINT plan_nn NOT NULL plan NOT VALID を受け付け、後の VALIDATE CONSTRAINT が SHARE UPDATE EXCLUSIVE を取ることを 18.6 で確認しました。PostgreSQL 17 はこのステートメントを構文エラーで拒否するため、Relyt One では上の CHECK の方法を使ってください。

CREATE INDEX CONCURRENTLY は SHARE の代わりに SHARE UPDATE EXCLUSIVE を取るため、構築中も書き込みが流れ続けます。その代わり、ドキュメントにあるとおり、テーブルを 2 回スキャンし、インデックスを使用または変更する可能性のある既存のトランザクションを待つため、処理量が増え、時間も長くかかります。また、独自の規則が 2 つあります。

トランザクションブロック内では実行できません。

BEGIN;
CREATE INDEX CONCURRENTLY accounts_email_idx ON accounts (email);
ROLLBACK;

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

多くのマイグレーションツールは各ファイルをトランザクションで囲むため、1 つのマイグレーションだけそれを無効にする方法を確認してください。これもロックを待つので、タイムアウトを設定します。SET LOCAL はトランザクションの外では何もしない（Postgres は WARNING: SET LOCAL can only be used in transaction blocks と返します）ため、直接接続で、直前にセッションレベルの SET lock_timeout = '2s'; を実行してください。

そして失敗すると、無効なインデックスが残ります。重複のあるカラムに一意インデックスを作ると、簡単に確認できます。

CREATE UNIQUE INDEX CONCURRENTLY accounts_plan_key ON accounts (plan);
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index WHERE indrelid = 'accounts'::regclass;

ERROR:  could not create unique index "accounts_plan_key"
DETAIL:  Key (plan)=(free) is duplicated.

    index_name     | indisvalid
-------------------+------------
 accounts_pkey     | t
 accounts_plan_key | f
(2 rows)

（DETAIL 行の重複した値は実行ごとに異なる場合があります。）無効なインデックスがクエリに使われることはありません。構築のどのフェーズで失敗したかによっては、書き込みのたびにそのインデックスの維持が必要な場合があります（同じカタログの indisready を確認してください）。lock_timeout であきらめた並行構築も、無効なインデックスを残します。長時間実行中の書き込みトランザクションがあるテーブルに対して実行したところ、タイムアウトして indisvalid = f が残りました。原因が何であれ、DROP INDEX CONCURRENTLY accounts_plan_key; でインデックスを削除してから、もう一度試してください。

1. 上の表でステートメントを調べます。ACCESS EXCLUSIVE、または書き込みをブロックするロック（SHARE、SHARE ROW EXCLUSIVE）を取るなら、どれほど速くてもロックタイムアウトが必要です。
2. マイグレーションのトランザクションを SET LOCAL lock_timeout = '2s'（またはアプリケーションが許容できる停止時間）で始め、タイムアウトしたらリトライします。
3. 開始する前に、pg_stat_activity で長時間実行中のセッションと idle in transaction のセッションを確認します。
4. 外部キーと CHECK 制約は NOT VALID で追加し、別のステップで VALIDATE します。
5. PostgreSQL 17 では、NOT NULL は検証済みの CHECK 制約を経由して追加します。
6. インデックスの構築と削除は、トランザクションの外で、セッションレベルの SET lock_timeout を設定して CONCURRENTLY で行い、その後 indisvalid = false がないか確認します。
7. テーブルを書き換えるもの（integer から bigint、揮発性のデフォルト値、型を狭める変更）は、マイグレーションではなくプロジェクトとして扱います。
8. マイグレーションはトランザクションプーラーを経由せず、直接接続で実行します。

上の表からは、ステートメントがどのロックを取るかが分かります。しかし、お使いのデータでスキャンや書き換えにどれだけ時間がかかるか、データが VALIDATE のステップを通るかどうかは分かりません。それには本番規模のデータが必要で、通常はそのコピーを用意するのが難しいところです。

Relyt One では、すべてのブランチが親のデータ全体を持つコピーオンライトのフォークであり、独自のコンピュートを持っています。本番ブランチからブランチを作成し、そこで \timing on を付けてマイグレーションファイルを実行し、上のファイルノードのチェックでテーブルを書き換えるかどうかを確認し、本番で実行する前に VALIDATE が既存の行で失敗しないかを確かめます。その後ブランチを削除します。本番がこの実験の影響を受けることはありません。

ブランチでリハーサルできないものが 1 つあります。ロックキューそのものです。ブランチでは他に何も実行されていないからです。ブランチから分かるのは、ロックを取得した後、マイグレーションがそのロックをどれだけの時間保持するかです。本番でロックを待ち続けることから守ってくれるのは、やはり lock_timeout とリトライです。また、絶対的な時間ではなく相対的な時間を比較してください。ブランチのコンピュートは本番とサイズが異なる場合があります。

無料で始める