satoshi.inoue's blog

備忘録を兼ねているので、薄い内容の投稿もあります。

PostgreSQLでテーブルロックせずにindexを貼る

スポンサードリンク

通常、テーブルにindexを貼るときはCREATE INDEXを使うと思います。 私も基本的にはこれを使いますが、CREATE INDEXをオプションなしで使用する場合は注意が必要です。

www.postgresql.jp

注意点

PostgreSQLは、対象テーブルに対する書き込みをロックしてから、対象テーブル全体のインデックス作成を一度のスキャンで行います。
他のトランザクションはテーブルを読み取ることはできますが、対象テーブル内の行を挿入、更新、削除しようとすると、インデックス作成が完了するまでブロックされます。
ですので、運用状態にあるデータベースには重大な影響を与える可能性があります。
また大規模なテーブルに対してインデックスを作成する場合は何時間もかかることがあります。

CONCURRENTLYオプションとは

簡単に言うと、このオプションを使うことで 書き込みをロックしないインデックス作成 が行えます。
その為、インデックス作成中に通常の操作を行い続けることができるので、この方式は運用環境での新規インデックス作成に有用です。
ある時点のスナップショットを元にインデックスを作成し、インデックスへの挿入を有効化してから、改めて取ったスナップショットとの差分をインデックスに反映するというものです。

CONCURRENTLYオプションのデメリット

作業時間の増加

このオプションを使うと、PostgreSQLはテーブルを2回スキャンします。
さらに、潜在的にそのインデックスを更新または使用する可能性がある、実行中のすべてのトランザクションが終わるまで待機しなければなりません。
したがって、この方式は通常の方式よりも総作業時間がかかり、また、完了するまでの時間が非常に長くなります。
もちろん、インデックス作成によりCPUや入出力に余分に負荷がかかりますので、他の操作に影響を与える可能性があります。

無効なインデックスが残る可能性

デッドロックや一意性インデックスにおける一意性違反など、テーブルスキャン中に問題が発生すると、CREATE INDEXは失敗しますが、「無効な」インデックスが残ってしまいます。
こうしたインデックスは完全ではない可能性がありますので、問い合わせの際には無視されます。
しかし、更新時にオーバーヘッドがかかります。
psqlの\dコマンドでこうしたインデックスを検出できます。

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID

こうなった場合の推奨方法は「インデックスを削除し、再度CREATE INDEX CONCURRENTLYを実行することです。」と公式が言っているんですが、長時間かかった挙句、またやり直すのかと思うとゾッとしますね。

まとめ

運用状態にある本番に影響を与えないためにCONCURRENTLYは実装されたはずなので、上記で述べたことが問題にならないのであれば、どんどん使っていった方がいいと思います。 CONCURRENTLYはHeap Only Tupleの影響を受けるようなので、後日Heap Only Tupleについて調査しようと思います。