はじめに
この記事は「NTTテクノクロス Advent Calendar 2024（シリーズ2）」の2日目の記事です。
本記事では、PostgreSQLデータベースのメンテナンスの一環として実施する「インデックス再編成」を行う際のコマンドの選び方について解説していきます。
インデックスの再編成の方法
インデックスの再編成はいくつかの方法で実施できます。
- DROP INDEX / CREATE INDEX CONCURRENTLY コマンドを用いてインデックスを作成しなおす。
- REINDEX、REINDEX CONCURRENTLY コマンドを用いてインデックスを再編成する。
- pg_repack 拡張機能を用いてインデックスを再編成する。
CREATE INDEX と REINDEX には同時作成オプション（CONCURRENTLY）があり、このオプションを使用すると、インデックスの作成時、および再編成時であっても、テーブルへの参照・更新が可能になります。ただし、インデックス作成中に生じた更新を最終的に反映する必要があるため、テーブルスキャンが2回必要になり、その分時間がかかることが注意点になります。
pg_repack もインデックスの再編成中にテーブルへの参照・更新が実行可能です。
※pg_repack 拡張機能は、「テーブル再編成」と「インデックス再編成」のオンライン実施を提供する拡張機能です。AWS（RDA、Aurora）、Azure PostgreSQL、GCP（CloudSQL, AlloyDB）でも利用可能です。 本記事では、インデックスのオンライン再編成機能のために利用します。
pg_repack は以下のドキュメントを参照ください。
再編成コマンドの比較
再編成コマンドの処理上の大きな違いは、再編成中のテーブル更新可否とインデックスの利用可否、そして、ロックの取り方です。
以下の通り比較してみました。
| 方式 | 再編成中のテーブルの利用 | 再編成中のインデックスの利用 | 挙動 |
|---|---|---|---|
| DROP INDEX / CREATE INDEX | 参照〇 更新× |
不可 | 処理中 SHARE レベルのロックをとり続ける |
| DROP INDEX / CREATE INDEX CONCURRENTLY | 参照〇 更新〇 |
不可 | 処理中 SHARE UPDATE EXCLUSIVE レベルのロックをとり続ける |
| REINDEX | 参照〇 更新× |
不可 | 処理中 ACCESS EXCLUSIVE レベルのロックをとり続ける |
| REINDEX CONCURRENTLY | 参照〇 更新〇 |
可能 | 処理中 SHARE UPDATE EXCLUSIVE レベルのロックをとり続ける。一時的に SHARE レベルのロックも必要とする |
| pg_repack | 参照〇 更新〇 |
可能 | 処理中 SHARE UPDATE EXCLUSIVE レベルのロックをとり続ける。処理の最後に ACCESS EXCLUSIVE レベルのロックを一時的に必要とする |
まず、運用を考慮すると、再編成中であってもインデックスを用いたデータの参照を実施したいケースがあり得るため、ここでは処理中にインデックスの利用ができない DROP INDEX / CREATE INDEX (CONCURRENTLYを含む) と、REINDEX による再編成は話題から除外します。
REINDEX CONCURRENTLY コマンドは、テーブルへの参照、更新を阻害せず、インデックスの利用も可能です。また、インデックス再作成中に発生した更新を反映するために、テーブルスキャンが2回必要になりますので、時間と、リソースを消費する点に注意が必要です。ただし、処理中に数回、SHARE レベルのロックが必要になります。
pg_repack も、テーブルへの参照、更新を阻害せず、インデックスの利用も可能です。この点については REINDEX CONCURRENTLY と同じです。また、pg_repack の場合もインデックス再作成中に発生した更新を反映するために、テーブルスキャンが2回必要になります。ただし、処理の最終段階で、作成したインデックスを既存のインデックスと交換する際に、ACCESS EXCLUSIVE レベルのロック（排他ロック）が必要となります。
ここだけを見ると、インデックス再編成に最適なのは排他ロックを必要としない REINDEX CONCURRENTLY のように見えますが、pg_repack と REINDEX CONCURRENTLY にはロック待ちの方法に特徴的な差異がありますのでその点について詳しく比較していきましょう。
pg_repack と REINDEX CONCURRENTLY のロック待ちの違い
■REINDEX CONCURRENTLY の場合
REINDEX CONCURRENTLY は処理中に SHARE UPDATE EXCLUSIVE レベルのロックを獲得します。SHARE UPDATE EXCLUSIVE レベルのロックは SELECT、INSERT、DELETE、UPDATEとは競合しませんので、インデックス再編成中でもテーブルの更新が可能です。ただし、裏でインデックスを作成して、最後に入れ替えるという処理のために、内部的に SHARE レベルのロックが数回発生します。
この内部的に数回発生する SHARE レベルのロックは、更新処理（DML）と競合します。この場合、ロックは待ち始めた時間が早い順に獲得しますので、インデックス再編成中に発生した、もしくはインデックス再編成前から実施されている、UPDATE、DELETE、INSERT処理を行ったトランザクションがある場合、REINDEX CONCURRENTLY は競合するトランザクションがコミットかロールバックされるのを待つ必要があります。
図：REINDEX CONCURRENLTY が更新処理（DML）と競合するケース
なお、REINDEX CONCURRENTLY コマンドがロック待ちの最中に、新たな別のトランザクションが再編成対象のテーブルに対して SHARE UPDATE EXCLUSIVE レベルと競合するロックを獲得しようとする処理を実行しようとした場合は、REINDEX CONCURRENTLY の待ち順序のほうが優先されます。（仮に、新たな別のトランザクションが DML処理を実施しようとしていた場合、DML処理のほうも待たされることになります）
■pg_repack の場合
pg_repack も、内部的に CREATE INDEX CONCURRENTLY を実施しており、REINDEX CONCURRENTLY と同じ SHARE UPDATE EXCLUSIVE レベルのロックを獲得します。そして、最後に既存のインデックスと入れ替えるという処理を行いますが、こちらはその瞬間にだけ ACCESS EXCLUSIVE レベルのロック（排他ロック）を獲得します。
ただし、pg_repack は、業務処理を妨げないようにするため、自身のロック待ちの順序は常に最下位を保ちます。これは1秒経過してもロックが獲得できなかったら、いったんロック待ちをキャンセルして再度ロック待ちをし直す、という方式で実現しています。これにより、途中でSELECT、INSERT、UPDATE、DELETEを行う処理が割り込むことを可能にしています。
以下の図のように、緑色の矢印の段階でインデックスの作成は終わっているものの、途中から発生したトランザクションによって pg_repack のインデックス入れ替えが待たされるイメージを示しています。通常の排他ロック待ちの場合、図中の「競合トランザクション3」は pg_repack の排他ロックの獲得が成功し、その後 pg_repack の排他ロックが解放されるまで待たされますが、pg_repack が排他ロックを1秒ごとに取り直すことで他のロック待ちの優先度を優先させています。
図：pg_repack のロック待ちの優先順位は常に最下位になる
さらに、pg_repack オプションとしてインデックス再編成の優先度を上げることも、場合によってはロック待ちをあきらめてインデックス再編成をキャンセルするような指定も可能です。ロックの待ち方については pg_repack のほうが柔軟に制御できます。
| pg_repack で 設定できる内容 |
挙動 | デフォルト |
|---|---|---|
| タイムアウト時間の指定 （--wait-timeout オプション） |
①指定した時間を経過すると競合しているSQLをキャンセルする。 ②指定した2倍の時間を経過すると、競合しているプロセスを停止する。 |
60秒 |
| タイムアウト時の挙動 （--no-kill-backend オプション） |
タイムアウトで指定した時間を経過しても、競合しているSQLをキャンセルしない。 かわりに対象のテーブルをスキップする。 |
スキップせずに競合しているSQLをキャンセルする |
- ①タイムアウト時は pg_cancel_backend() 関数でSQLをキャンセルします
- ②タイムアウトの2倍の時間を経過した際は pg_terminate_backend() 関数で競合しているプロセスを停止します
REINDEX CONCURRENTLY と pg_repack の使い分け
REINDEX CONCURRENTLY コマンドや、pg_repack を利用することで、インデックス再編成と並行してデータの更新処理を実施できることはメリットです。
しかし、いずれの方式もロック競合による処理待ちが生じる可能性があり、場合によっては待たされ続けることでインデックスの再編成がメンテナンススケジュールの枠内におさまらなくなってしまうといったケースも想定されます。
例えば、意図せず IDLE IN TRANSACTION 状態でロックをつかんだままの接続が残ってしまっていた場合や、やむを得ずメンテナンス時間帯にオペレーションを行う必要が発生した場合など、ということが考えられ得ます。
本来であればインデックス再編成を行うメンテナンス枠内での業務処理は行わないようにすることが望ましいですが、こういったケースを想定して、pg_repack のタイムアウト機能やキャンセル機能を利用することでリスクを避けるといったことも検討すると良いと考えます。
基本的には REINDEX CONCURRENTLY コマンドを使用する前提で検討し、以下のケースを必要とする場合に pg_repack を利用する、と考えると良いかもしれません。
■pg_repack を利用するべきケース
- データ更新処理が常にインデックス再編成よりも優先される必要があるケース
- 一定時間競合トランザクションが解放されない場合、インデックス再編成のほうををキャンセルしたほうがよいケース（メンテナンス枠内に処理を終了させたい場合など）
- 競合トランザクションが解放されない場合、インデックス再編成を最優先に完了させたいケース（この場合、キャンセルされたトランザクションは再実行によって回復させることが可能である必要があります）
まとめ
PostgreSQLでのインデックス再編成の方法の選択について解説しました。
pg_repack と REINDEX CONCURRENTLY のロックの取り方の違いを理解してインデックス再編成運用に活用していきましょう！
本記事は以上となります。
NTTテクノクロスのWEBサイトでも PostgreSQLの技術記事を掲載しておりますので、ぜひこちらもご覧ください。
引き続き、NTTテクノクロス Advent Calendar 2024 (シリーズ2) をお楽しみください。