20-6 · 章 20 · 2 分で読めます

インデックス追加でアプリケーションが停止する理由

インデックス追加が本番インシデントを引き起こす仕組みと、PostgreSQLのCONCURRENTLYやMySQLのALGORITHM=INPLACEなど、書き込みをブロックしない安全な移行手法を解説。外部キー制約のNOT VALID活用や移行計画の分離もカバー。

インデックス追加でアプリケーションが停止する理由

チームは何週間も低速クエリを追跡してきた。usersテーブルは数百万行に成長し、メールアドレスでの検索がミリ秒ではなく秒単位でかかるようになっている。誰かがインデックス追加を提案する。修正は単純に見える。CREATE INDEXコマンド一つで問題解決だ。

クエリがすでにユーザーに影響を与えているため、あなたは営業時間中にマイグレーションを実行する。インデックスの構築が始まる。そしてアラートが届く。アプリケーションのタイムアウト、トランザクションの失敗、サポートチケットの山積み。パフォーマンス改善のために追加したインデックスが、本番インシデントを引き起こしたのだ。

このシナリオは、ほとんどのチームが想定するよりも頻繁に発生する。インデックスと制約は単純なスキーマ変更に見えるが、負荷がかかって初めて顕在化する隠れたコストを伴う。

インデックス作成時に何が起こるか

CREATE INDEX idx_users_email ON users(email) を実行すると、データベースはテーブルのすべての行を読み取り、バランス木構造を構築し、ディスクに書き込む必要がある。この処理中、データベースは通常、インデックス構造を破損させるデータ変更を防ぐためにテーブルをロックする。

ロックの種類はデータベースに依存する。PostgreSQLでは、通常のCREATE INDEXはSHARE LOCKを取得する。読み取りは引き続き可能だが、挿入、更新、削除といったすべての書き込み操作は待機しなければならない。MySQL InnoDBの場合、ロックの動作は操作の種類とストレージエンジンのバージョンによって異なる。

数千人のユーザーが利用するテーブルでは、数秒の書き込みロックでもバックログが発生する。アプリケーションの接続がキューイングされ、タイムアウトが連鎖し、パフォーマンス改善のはずがダウンタイムイベントに変わる。

ほとんどのチームが忘れている同時実行オプション

主要なデータベースのほとんどは、書き込みをブロックせずにインデックスを構築する方法を提供している。PostgreSQLにはCREATE INDEX CONCURRENTLYがある。MySQLは互換性のあるバージョンでALGORITHM=INPLACEとLOCK=NONEをサポートしている。

-- PostgreSQL: 書き込みをブロックせずにインデックスを構築
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL: 書き込みをブロックせずにインデックスを構築（InnoDB）
ALTER TABLE users ADD INDEX idx_users_email (email), ALGORITHM=INPLACE, LOCK=NONE;

同時実行アプローチは時間がかかる。PostgreSQLのCREATE INDEX CONCURRENTLYは、進行中のトランザクションが完了するのを待ち、さらに構築中に行われた変更を捕捉するための追加パスを実行する必要がある。しかしトレードオフは明確だ。インデックス構築中もアプリケーションは稼働し続ける。

よくある間違いは、同時実行インデックス作成をオプションまたは高度な機能として扱うことだ。稼働中のテーブルに対する本番マイグレーションでは、これをデフォルトにするべきである。マイグレーションスクリプトに明示的に記述し、デフォルトの動作に依存してはならない。

制約もロックする

外部キー制約と一意制約も、ロックに関する驚きをもたらす。外部キーを追加するとき、データベースは既存のすべての行がリレーションシップを満たしていることを検証する。この検証はテーブル全体を読み取り、その間テーブルをロックする。

PostgreSQLは実用的な回避策を提供している。NOT VALIDだ。これにより、既存の行の検証をスキップし、新しいデータに対してのみ制約を追加する。その後、VALIDATE CONSTRAINTをオフピーク時に別途実行できる。

-- 既存データを検証せずに外部キーを追加
ALTER TABLE orders ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- トラフィックが少ないときに後で検証
ALTER TABLE orders VALIDATE CONSTRAINT fk_user_id;

一意制約も同様だ。カラムにUNIQUEを追加するには、テーブル全体で重複をチェックする必要がある。大規模なテーブルでは、このスキャンが不快な時間にわたって書き込みをロックする可能性がある。

インデックスマイグレーションは分離して計画する

多くのチームは、すべてのスキーマ変更を1つのマイグレーションファイルにまとめている。カラム追加、インデックス作成、外部キー追加。小規模なテーブルではこれで問題ない。しかし数百万行の本番テーブルでは、単一障害点が生まれる。

インデックスと制約の変更は、テーブル構造の変更とは別のマイグレーションファイルにし、別々にスケジュールする必要がある。さらに踏み込んで、自動化パイプラインの外で手動でインデックスマイグレーションを実行し、タイミングを厳密に制御するチームもある。

実践的なアプローチ：

テーブル構造の変更（カラム追加、型変更）は1つのマイグレーションで実行
インデックスと制約の変更は別のマイグレーションで実行
インデックスマイグレーションはトラフィックが少ない時間帯にスケジュール
すべてのマイグレーションスクリプトで同時実行オプションを明示的に使用
制約の追加と検証は分離して実行

本当の課題

インデックスと制約が配置されたら、それらが実行中のアプリケーションを壊さないことを確認する必要がある。クエリプランを変更するインデックスは、予期しないパフォーマンス変動を引き起こす可能性がある。削除カスケードをブロックする外部キーは、バックグラウンドジョブを停止させる可能性がある。

これはより広範な問題につながる。アプリケーションの新旧バージョンが同じデータベースにアクセスする場合、マイグレーションをどのように安全に保つのか。これは次回の議論のトピックだが、原則はここから始まる。すべてのスキーマ変更を、単なる実行スクリプトではなく、潜在的な本番イベントとして扱うことだ。

インデックスと制約のマイグレーションチェックリスト

アクティブな書き込みがあるテーブルでは、同時実行インデックス作成（CREATE INDEX CONCURRENTLYまたは同等のもの）を使用する
可能な場合はNOT VALIDで外部キーを追加し、別途検証する
インデックスマイグレーションはテーブル構造変更とは別のファイルで実行する
大規模なインデックス構築はトラフィックが少ない時間帯にスケジュールする
マイグレーション中およびマイグレーション後に、ロック期間とクエリパフォーマンスを監視する
本番で実行する前に、本番データのコピーでマイグレーションをテストする

まとめ

インデックスは単なるパフォーマンスツールではない。テーブルをロックし、書き込みをキューイングし、ユーザーをタイムアウトさせる可能性のあるスキーマ変更だ。他の本番デプロイと同様の注意をもって扱うこと。同時実行オプションを使用し、マイグレーションを分離し、安全性のためにスケジュールを組むこと。マイグレーション自体がアプリケーションを壊してしまうなら、得られるクエリ速度には価値がない。