はじめに

以前、「【PostgreSQL】テーブルのインデックスが壊れた話」の記事を書きました、そして数日前に1ヶ月振りにインデックスが壊れました。今回で7度目になります。

再度発生した時にはバックアップ後にREINDEXコマンドを仕掛けるように検討しようと思います。

対応策として、1日1回実行しているバックアップの際にインデックスの再構築をすることにしました。これを仕掛けて数ヶ月発生しないか様子を見ることにします。

インデックスの再構築

インデックスの再構築するには、REINDEXコマンドがあります。

psql -U test -c "reindex INDEX pk_replicate_que;"

インデックスの更新確認

インデックスの再構築の確認ってPostgreSQLのログには何も残らないので、どうすればいいんだろうと考えた時に以前書いた記事「PostgreSQLテーブルの作成日時と更新日時の取得について」を思い出しました。

この時はテーブルに限定していましたが、インデックスでも同様に出来ると考えました。

※schemaname = 'public' は任意のスキーマに変更してください。
※全てのインデックス結果が出力されるので、対象のインデックスのみにしたい場合は抽出条件を追加してください。

SELECT
   indexname
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/PG_VERSION')) AS creation
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/' || filenode)) AS modification
FROM (
    SELECT
        indexname
       ,(SELECT MAX(pg_ls_dir::int)::text
         FROM pg_ls_dir('./base')
         WHERE pg_ls_dir <> 'pgsql_tmp'
           AND pg_ls_dir::int  <= 
        (SELECT relfilenode FROM pg_class cls
         INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
         WHERE nsp.nspname = schemaname AND relname ILIKE indexname)) AS folder 
       ,(SELECT relfilenode FROM pg_class cls
         INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
         WHERE nsp.nspname = schemaname AND relname ILIKE indexname) AS filenode
    FROM pg_indexes
    WHERE schemaname = 'public'
) CTE

結果

indexname	creation	modification
pk_replicate_que	2019-08-08 10:26:14+09	2020-04-28 09:31:47+09

※ファイル作成時刻(creation)は変更される可能性が最も低いPG_VERSIONの最終更新時刻です。
※更新日時(modification)は、元のテーブルに登録、更新、削除があった場合にも更新されます。

テーブルの更新が無い時に、REINDEXコマンドを実行して更新日時が更新されていれば実行されたんだなと考えます。
注意として、オートバキュームでも更新日時が変更されてしまいますので直後に確認しないと意味がないです。

最後に

今回、1日1回実行しているバックアップの際にインデックスの再構築を仕掛けました。これでインデックスが壊れる現象が発生しなくなることを祈るのみです。