本番環境でインデックスを張る「戦術」：CREATE INDEX CONCURRENTLYの深淵
PostgreSQLを長く触っていると、一度は冷や汗をかく瞬間があるはずだ。「数千万行のテーブルにインデックスを貼る必要がある。でも、数分間のロックすら許されない高負荷な本番環境だ」。
そんな時、僕たちが迷わず選ぶのが `CREATE INDEX CONCURRENTLY` だ。しかし、このオプションが「魔法の杖」ではないことは、経験を積んだエンジニアなら誰しも知っている。今日は、単なる「ロックを避ける構文」という理解を一歩超えて、その裏側で何が起きているのか、そしてなぜ時々僕たちを悩ませるのかを深掘りしてみよう。
1. なぜ「2回のスキャン」が必要なのか
通常、インデックスの作成はテーブル全体を一度ロックしてスキャンする。シンプルで高速だが、書き込みは完全に止まる。一方、`CONCURRENTLY` はこのロックを回避するために、非常に巧妙な、しかしコストのかかる二段階のプロセスを踏んでいる。
1. 第一段階： インデックスの定義をカタログに登録するが、まだ「読み取り不可」の状態。ここから並行してテーブルをスキャンし、インデックスを作成する。この間、既存のトランザクションは止まらない。
2. 第二段階： 最初のスキャンが終わった時点で、まだインデックスに反映されていない「隙間」を埋めるため、もう一度テーブルをスキャンする。
なぜ2回なのか？ それは、最初のスキャン中に発生したデータ更新を逃さないためだ。PostgreSQLのアーキテクチャ上、この二段階のステップを踏まない限り、データ整合性を保証しながら非ブロッキングを実現することはできない。つまり、このオプションを使うということは、「CPUとI/Oを余分に消費してでも、可用性を優先する」というエンジニアの意思表示に他ならないんだ。
2. パフォーマンストラブルの「死角」
`CONCURRENTLY` を使えばロックは回避できる。だが、代償として無視できないのが「システムリソースの飽和」だ。
- I/O負荷の増大： テーブルを2回フルスキャンするということは、キャッシュがインデックス作成の読み込みで溢れ返ることを意味する。バッファキャッシュが追い出され、本来のクエリがディスクI/Oを叩き始め、結果的にシステム全体のレイテンシが跳ね上がる。
- HOT (Heap Only Tuple) の恩恵が消える： これが意外と盲点だ。インデックスを作成することで、`HOT` 更新が効かなくなるケースがある。頻繁に更新されるカラムに不必要なインデックスを追加すると、書き込み性能が目に見えて低下する。
もし本番環境でこれを実行するなら、`work_mem` を一時的に広げつつ、`maintenance_work_mem` を調整し、可能であればピークタイムを避けるのが「大人の嗜み」だと言える。
3. 「無効なインデックス」という落とし穴
`CONCURRENTLY` で最も恐ろしいのは、何らかの理由（デッドロックやエラー）で作成が中断されたときだ。インデックスは残るが、ステータスが `indisvalid = false` になる。
この「無効なインデックス」は厄介だ。クエリには使われないくせに、テーブルの更新時には必ず「更新対象」として負荷をかけ続ける。いわば、働かないのに給料だけ泥棒する社員のようなものだ。
もし作成に失敗したら、必ず `DROP INDEX` で一度削除し、クリーンな状態からやり直すこと。中途半端な状態で放置するのは、運用の時限爆弾を埋めているのと同じだ。
4. 熟練者のための「運用のヒント」
最後に、現場で役立つTIPSを一つ。
もしデータ量が数十億行を超えるような超大規模テーブルであれば、単純に `CREATE INDEX CONCURRENTLY` を叩くのではなく、「時間帯を分けて小分けにする」か、あるいは「パーティショニングされたテーブルに対して、個別にインデックスを作成する」といったアプローチを検討してほしい。
PostgreSQLは強力なエンジンだが、物理的な限界点を超えようとすれば、必ずどこかで悲鳴を上げる。僕たちがやるべきなのは、構文を暗記することではなく、その背後にある「データベースが何をしようとしているのか」という呼吸を感じ取ることだ。
—
インデックス設計は、データベースチューニングの華だ。`CONCURRENTLY` を正しく使いこなすことは、単なる機能利用ではなく、安定稼働という責務を果たすための「技術的な矜持」だと僕は思っている。
さて、君のデータベースで、今日追加すべきインデックスはあるかな？
コメント