PostgreSQL12ではreindexコマンドをオンラインで実行することができるconcurrentlyオプションに対応しました。
以下のように実行します。reindex index concurrently idx_testa01;
12以下はpg_repackを使用
テーブルやインデックスで必要となるメンテナンス処理についての備忘。
調べた結論だけまとめておくと、下記といった感じでした。
- auto vacuumを有効にしておく（デフォルトで有効）
- かつ、適当な頻度でauto vacuumされていることを、定期的に確認する
- indexの断片化状態を定期的に確認する
- 必要であれば、indexを再構築してあげる
PostgreSQLの、テーブル・インデックスのメンテナンスについて - goodbyegangsterのブログ 2019
使用していないインデックスを見つける - matsuou1の日記 2009
PostgreSQLアンチパターン：運用DBのREINDEX #PostgreSQL - Qiita
https://www.postgresql.jp/document/current/html/routine-reindex.html
https://www.postgresql.jp/document/current/html/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY
pgstattuple
https://www.postgresql.jp/document/current/html/pgstattuple.html
postgres=# CREATE EXTENSION pgstattuple;
CREATE EXTENSION
postgres=# SELECT * FROM pgstatindex('tbl_i_idx');
-[ RECORD 1 ]------+------
version | 4
tree_level | 1
index_size | 40960
root_block_no | 3
internal_pages | 1
leaf_pages | 3
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 41.1
leaf_fragmentation | 33.33
leaf_fragmentation の単位は%？
Then you can examine index bloat like this:
SELECT * FROM pgstatindex('spatial_ref_sys_pkey'); -[ RECORD 1 ]------+------- version | 2 tree_level | 1 index_size | 196608 root_block_no | 3 internal_pages | 1 leaf_pages | 22 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 64.48 leaf_fragmentation | 13.64This index is in excellent shape (never used): It has only 14% bloat.
Mind that indexes are by default created with a fillfactor of 90, that is, index blocks are not filled to more than 90% by INSERT.
It is hard to say when an index is bloated, but if leaf_fragmentation exceeds 50-60, it's not so pretty.
To reorganize an index, use REINDEX.
