· 開発

PostgreSQL の CREATE INDEX CONCURRENTLY: 書き込みを止めない代わりに払うコスト

CREATE INDEX CONCURRENTLY が取るロック、2 回のテーブルスキャンとトランザクション待ち、失敗後に残る INVALID インデックスの扱い、トランザクションブロックとパーティションテーブルの制約を PostgreSQL 18 の文書に沿って整理します。

postgresqlindexlockingdatabasemigration

稼働中の PostgreSQL テーブルにインデックスを追加するとき、CONCURRENTLY を付けるのが定番です。このオプションで書き込みのブロックはなくなりますが、その代わり作成に時間がかかり、他のトランザクションを待つ必要があり、失敗すると後片付けが残ります。この記事では PostgreSQL 18 の文書(2026-10-13 時点で 18.6)をもとに、その動作を整理します。

通常の CREATE INDEX との違い

通常の CREATE INDEX はテーブルに SHARE ロックを取ります。このロックは ROW EXCLUSIVE と競合するため、読み取りは続けられますが、INSERT、UPDATE、DELETE はインデックス作成が終わるまで待たされます。テーブルのスキャンは 1 回です。

CREATE INDEX CONCURRENTLY は SHARE UPDATE EXCLUSIVE ロックを取ります。このロックは ROW EXCLUSIVE と競合しないので、書き込みは続行できます。その代わり、文書によると次のコストがかかります。

  • テーブルを 2 回スキャンする。
  • インデックスを変更または使用する可能性のある既存トランザクションがすべて終わるまで待つ。
  • そのため総作業量が多く、完了までかなり長くかかる。追加の CPU と I/O 負荷が他の処理を遅くすることもある。

SHARE UPDATE EXCLUSIVE は自分自身とも競合します。そのため、1 つのテーブルで同時に実行できる concurrent なインデックス作成は 1 つだけです。VACUUM(FULL なし)と ANALYZE も同じロックを取るので、競合表から読む限り、同じテーブルに対する手動の VACUUM や ANALYZE とも互いに待ち合うことになります(競合表からの推論です)。どちらの作成方法でも、インデックス作成中はテーブルのスキーマ変更はできません。

内部で起きていること

文書が説明する手順は次のとおりです。

  1. 1 つのトランザクションで、インデックスを “invalid” としてシステムカタログに登録する。
  2. 続く 2 つのトランザクションで、テーブルをそれぞれ 1 回ずつスキャンする。各スキャンの前に、そのテーブルを変更した既存トランザクションの終了を待つ。
  3. 2 回目のスキャンの後、それより前に取得されたスナップショットを持つトランザクションの終了を待つ。
  4. 最後にインデックスを “valid” にしてコマンドが終わる。

コマンドが終わっても、すぐにクエリで使われるとは限りません。最悪の場合、インデックス作成開始より古いトランザクションが残っている間は使われません。

並列インデックス作成は CONCURRENTLY でも使えますが、並列に実行されるのは 1 回目のテーブルスキャンだけです。

進捗を見る: pg_stat_progress_create_index

CREATE INDEX や REINDEX の実行中は、pg_stat_progress_create_index ビューにバックエンドごとに 1 行が表示されます。phase 列は上の手順に対応します。

phase 意味
waiting for writers before build 作成前、書き込みロックを持つトランザクションの終了待ち
building index 1 回目のスキャンとインデックス作成
waiting for writers before validation 検証前、書き込みロックを持つトランザクションの終了待ち
index validation: scanning index / sorting tuples / scanning table 2 段階目、作成中に入った変更の検証
waiting for old snapshots 古いスナップショットを持つトランザクションの終了待ち

待ちのフェーズでは lockers_total、lockers_done、current_locker_pid が埋まります。次のクエリは pg_stat_activity と結合して、今何を待っているかを確認します。

SELECT p.pid,
       p.phase,
       p.lockers_done,
       p.lockers_total,
       p.current_locker_pid,
       a.state,
       a.xact_start,
       left(a.query, 60) AS locker_query
FROM pg_stat_progress_create_index AS p
LEFT JOIN pg_stat_activity AS a
       ON a.pid = p.current_locker_pid;

current_locker_pid が指すセッションの state が idle in transaction で、xact_start がかなり前なら、何もクエリを実行していない開きっぱなしのトランザクションがインデックス作成を止めている可能性が高いです。これは文書の待ち条件と pg_stat_activity の列定義を組み合わせた解釈です。そのセッションを終了させるかどうかは、アプリケーションへの影響を見て判断してください。

失敗すると INVALID インデックスが残る

スキャン中にデッドロックや一意性違反などが起きると、コマンドは失敗しますが “invalid” なインデックスが残ります。不完全な可能性があるのでクエリには使われませんが、INSERT や、このインデックスに関わる UPDATE のたびに更新は続くので、そのコストはかかり続けます。psql の \d では INVALID と表示されます。

カタログでは pg_index.indisvalid を見ます。false は、クエリには安全に使えないが INSERT/UPDATE では引き続き更新が必要なインデックスであることを意味します。

SELECT i.indexrelid::regclass AS index_name,
       i.indrelid::regclass   AS table_name
FROM pg_index AS i
WHERE NOT i.indisvalid;

文書が勧める復旧方法は、インデックスを削除して CREATE INDEX CONCURRENTLY をやり直すことです。REINDEX INDEX CONCURRENTLY で作り直す方法もあります。

-- 名前は例です。この例は実行していません。
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;
CREATE INDEX CONCURRENTLY orders_customer_id_idx
    ON orders (customer_id);

DROP INDEX CONCURRENTLY はテーブルに ACCESS EXCLUSIVE ロックを取る代わりに、競合するトランザクションの終了を待ちます。ただし指定できるインデックス名は 1 つだけで、CASCADE は使えず、パーティションテーブルのインデックスには使えません。

REINDEX CONCURRENTLY が失敗した場合は名前の接尾辞を確認します。_ccnew で終わる invalid なインデックスは作成途中の一時インデックスなので、削除してやり直します。_ccold で終わる場合は再作成自体は成功しており、元のインデックスを削除できなかっただけなので、それを削除すれば済みます。_ccnew1 のように数字が付くこともあります。

再試行スクリプトの IF NOT EXISTS の落とし穴

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx ... は、同じ名前のリレーションがすでにあれば通知を出すだけで処理を進めます。文書は、既存のインデックスが作ろうとしたものと同じである保証はないと明記しています。そのため以前の試行で invalid なインデックスが残っていると、再試行は成功したように見えても、実際にはクエリで使われないインデックスしか残っていない、ということが起こりえます(文書の動作からの推論です)。再試行の前に上の indisvalid クエリで確認する方が安全です。

一意インデックスで気をつけること

一意インデックスを concurrent に作成すると、2 回目のスキャンが始まった時点で、他のトランザクションに対して一意制約がすでに適用されます。そのため、インデックスが使えるようになる前や、最終的に作成が失敗した場合でも、他のクエリで制約違反エラーが出ることがあります。2 回目のスキャンで失敗した場合、残った invalid なインデックスはその後も一意制約を強制し続けます。式インデックスや部分インデックスで式の評価エラーが起きた場合も、似たことが起こりえます。

pg_index.indisvalid の説明も同じ点に触れています。invalid な一意インデックスでは、一意性が成り立っている保証もありません。

実行できる場所と対象の制約

  • トランザクションブロック内では実行できません。 通常の CREATE INDEX は BEGIN ... COMMIT の中で実行できますが、CONCURRENTLY はできません。マイグレーションツールが各マイグレーションをトランザクションで包む方式なら、この文は別扱いにする必要があります(ツールごとの設定はこの記事の範囲外です)。DROP INDEX CONCURRENTLY と REINDEX CONCURRENTLY にも同じ制約があります。
  • パーティションテーブルには直接使えません。 文書が示す回避策は、各パーティションに concurrent にインデックスを作成し、最後にパーティションテーブルのインデックスを non-concurrent に作成する方法です。最後の手順はメタデータだけの操作になり、書き込みが止まる時間を短くできます。
  • 一時テーブルでは常に non-concurrent で動作します。 他のセッションからアクセスされないため、通常の方式の方が安いという理由です。
-- パーティションテーブルの例。この例は実行していません。
CREATE INDEX CONCURRENTLY events_2026_09_created_at_idx
    ON events_2026_09 (created_at);
CREATE INDEX CONCURRENTLY events_2026_10_created_at_idx
    ON events_2026_10 (created_at);
-- すべてのパーティションに同じインデックスができてから親のインデックスを作成します。
CREATE INDEX events_created_at_idx
    ON events (created_at);

バージョンに関する注意: 14.0-14.3

PostgreSQL 14.4(2022-06-16 リリース)のリリースノートによると、v14 で追加された最適化のために CREATE INDEX CONCURRENTLY と REINDEX CONCURRENTLY が作成中に更新された行をインデックスに入れ損なうことがあり、14.4 でその最適化が取り消されました。リリースノートは、14.4 を適用する前の PostgreSQL 14(つまり 14.0〜14.3)で CONCURRENTLY を使って作ったインデックスを、更新後に作り直すよう勧めています。

2026-10-13 に確認した公式のバージョン表では、サポート中のメジャーバージョンは 14 から 18 で、それぞれの最新マイナーは 18.6、17.11、16.15、15.19、14.24 です。この記事で引用した動作は PostgreSQL 18 の文書に基づきます。14 の文書の “Building Indexes Concurrently” 節にも、2 回のスキャン、invalid なインデックス、テーブルごとに 1 つの concurrent 作成、トランザクションブロックの制約、パーティションテーブル非対応が同じ内容で書かれています。15〜17 の文書は個別には開いていません。

関連記事: HOT 更新は、テーブルのインデックス(BRIN のような要約インデックスを除く)が参照する列を変更しない場合にだけ可能です。そのため新しいインデックスを追加すると、その列を変更する UPDATE は HOT の対象から外れます。条件は PostgreSQL の HOT 更新 にまとめています。

この記事の SQL の例は説明用に書いたもので、実際のデータベースでは実行していません。図は入れていません。手順のリストと phase の表で流れを説明できると判断しました。

出典

以下のリンクと内容は 2026-10-13 に確認しました。

ブログへ