0Pricing
SQL Academy · レッスン

並行インデックス作成（CREATE INDEX CONCURRENTLY）

CREATE INDEX CONCURRENTLYで書き込みをブロックせずにインデックスを作成し、中断した作成処理から復旧します。

「並行インデックス作成（CREATE INDEX CONCURRENTLY）」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

CREATE INDEX の問題

通常のCREATE INDEXはSHAREロックを取得するため、構築中は読み取りを妨げずに書き込みをブロックします。10億行のテーブルでは、数分間にわたって書き込みが停止することになります。

CONCURRENTLY：解決策

CREATE INDEX CONCURRENTLYは、書き込みをブロックせずにインデックスを構築します。

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

仕組み

テーブルを2回スキャンします。

  1. 1回目：現在の行からインデックスを構築します
  2. 2回目：1回目のスキャン中に変更された行を取り込みます

さらに、構築開始前に開始された並行トランザクションが完了するまで、短時間待機します。

トランザクション内では実行できない

CONCURRENTLYは2回コミットするため、明示的なBEGIN/COMMITブロック内では実行できません。

BEGIN;
CREATE INDEX CONCURRENTLY ...;  -- ERROR
COMMIT;

遅いがオンライン

CONCURRENTLYは、通常のCREATE INDEXより時間がかかります（2回のスキャンとロック待ちが発生するためです）。その代わり、ダウンタイムがゼロになります。

失敗からの復旧

CONCURRENTLYが中断やシステムエラーなどで失敗すると、インデックスはINVALID状態のまま残ります。

SELECT relname FROM pg_class
WHERE relkind = 'i' AND NOT pg_index.indisvalid;

-- Fix: drop and re-create:
DROP INDEX CONCURRENTLY my_idx;
CREATE INDEX CONCURRENTLY my_idx ON ...;

REINDEX CONCURRENTLY

PG 12以降では、ブロックせずに再構築できます。

REINDEX INDEX CONCURRENTLY orders_user_id_idx;
REINDEX TABLE CONCURRENTLY orders;

DROP INDEX CONCURRENTLY

これもブロックせずに実行できます。

DROP INDEX CONCURRENTLY old_idx;

ユニークインデックス

UNIQUEインデックスもCONCURRENTLYで構築できます。UNIQUE制約をオンラインで追加する場合に便利です。

CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON users(email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_uidx;

外部キーのインデックス

大きなテーブルにFKを追加する際の標準的な手順は次のとおりです。

-- 1. Build the index online:
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders(user_id);

-- 2. Add the FK with NOT VALID (instant):
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- 3. Validate in the background:
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

並行インデックス構築のコスト

テーブル全体のスキャン2回と管理処理が必要です。CPUとI/Oを大量に消費するため、可能であればトラフィックの少ない時間帯に実行します。

進行状況の監視

PG 12以降では、pg_stat_progress_create_indexで進行状況を確認できます。

SELECT phase,
       blocks_done, blocks_total,
       tuples_done, tuples_total
FROM pg_stat_progress_create_index;

まとめ

CONCURRENTLYは、本番環境でインデックスを管理するのに適した方法です。

  • CREATE/DROP/REINDEX CONCURRENTLYは書き込みをブロックしません
  • トランザクション内では実行できません
  • 失敗するとINVALIDインデックスが残るため、後で削除します
  • pg_stat_progress_create_indexで監視できます

クイックチェック

CREATE INDEX CONCURRENTLYを明示的なBEGIN/COMMITブロック内で実行できないのはなぜでしょうか。

よくある質問

「並行インデックス作成（CREATE INDEX CONCURRENTLY）」レッスンは無料ですか？

はい。「並行インデックス作成（CREATE INDEX CONCURRENTLY）」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「並行インデックス作成（CREATE INDEX CONCURRENTLY）」で何を学びますか？

CREATE INDEX CONCURRENTLYで書き込みをブロックせずにインデックスを作成し、中断した作成処理から復旧します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。

「並行インデックス作成（CREATE INDEX CONCURRENTLY）」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. オンラインマイグレーション：ALTER TABLEがロックする理由
  2. 並行インデックス作成（CREATE INDEX CONCURRENTLY）
  3. ダウンタイムゼロの列名変更
  4. ツール：Flyway、Liquibase、Sqitch
← SQL Academyに戻る