PostgreSQLアンチパターン：運用DBのREINDEX

最新版マニュアル(9.4/9.5)では、親切に書いてあるんですが、ググったときに、9.3以前のページが真っ先に目に入るかもしれないし、担当しているサービスが9.3のままだからマニュアルも9.3しか見ないという人もいるかもしれない。

B-tree の充填効率

生きてるTABLEについている INDEX の B-tree 各ページの充填率は、最密充填とはいきません。DELETEされたところ、UPDATEで値が変わったところが抜けていても、近いキー値でのINSERTがこないと、空いたところが埋まりません。
ページがあふれたときの新ページとの分割戦術と、その後に続いてINSERTされてくるキー値の変化傾向が合致しないと、スカスカのUnbalanced-treeになるケースもあります。ランダムケースなら、分割戦術50:50で全体充填率75%ですが、インクリメント傾向だったら、最後尾ばかりが分割していき全体充填率50%ということになります。

B-tree 再構築

充填効率は悪くても、キー順に並んではいるので、第1ページの空いてるところに、第2ページ以降から順序保存したまま詰められるだけ詰めて、という前へならえコンパクションを最後まで全部やりきれば、最密充填の B-treeに再構成できます。

ときどき、それをやれば、INDEXスキャン時間が早くなる、ディスクスペースの無駄がなくなるので、「定期的にREINDEXコマンドを使用してインデックスを再構築することが価値がある状況があります。」とマニュアルにあるわけです。

落とし穴

サービス稼働中のDBの巨大INDEXにREINDEXすると、TABLEの排他ロックをとられて、サービスの一部または全部がその間止まってしまいます。
もちろん、9.3以前のマニュアルにも、REINDEX自体のリファレンスページには、排他ロックのことは記述があります。「定常的なデータベース保守作業」の方に注意書きがなかったというだけのことですが。

回避

https://www.postgresql.jp/document/9.4/html/routine-reindex.html
から最後の追記部分をまるっとコピー。

このコマンド（REINDEX）はテーブルの排他ロックを要求しますので、生成と置き換えの処理を続けて行なうことでインデックスの再構築を実行する方が好ましい場合がしばしばあります。 CONCURRENTLYオプションの付いたCREATE INDEXをサポートする種類のインデックスでは代わりにそのように再構築できます。 それが成功し、結果のインデックスが有効ならば、ALTER INDEXとDROP INDEXを組み合わせて使って、元のインデックスを新しく構築されたものに置き換えることができます。 インデックスが一意性もしくはその他の制約を強制するために使われている場合には、既存の制約を新しいインデックスで強制されるものへ入れ替えるためにALTER TABLEが必要になるかもしれません。 インデックスをこのように再作成するのには制限がありますので、この複数の処理で再構築する代わりの方法を使う前に注意深く検討し、エラーを処理しなければなりません。

単純ケースのサンプル

CREATE INDEX CONCURRENTLY reports_user_id_idx_new
  ON reports
  USING tree (user_id);
DROP INDEX reports_user_id_idx;
ALTER INDEX reports_user_id_idx_new
  RENAME TO reports_user_id_idx;

エラーがないことを目視確認しながら、投入する必要があります。

indexの数が多すぎる pg_repackへ

REINDEX　の一単語を、上記のように長いSQLにするのも、indexが1本や2本ならいいんですが、実用サービスの全indexとなると、うんざりです。主キー制約、外部キー制約があると、上記の単純サンプルとはSQLが違ってくるので、単純なスクリプト技ではうまくいかないのも面倒くさい。

とても期待しているのが、最小限の排他ロックだけで、すぐに解放してくれて、負荷の低いときなら、運用中のまま保守できそうな pg_repack です。
bwtakacyさんの「運用で役立つツールpg_repackのご紹介」