PostgreSQL 13でのreindexの活用方法をまとめてみた

はじめに

データベースのメンテナンスは、パフォーマンスと安定性の維持に欠かせません。その中でも、PostgreSQLではreindexという強力なツールが提供されています。
本記事では、reindexの役割と重要性について解説し、具体的な手順とコード例を通じて、PostgreSQL 13におけるreindexの活用方法とパフォーマンス向上効果についてざっくり紹介します。

reindexの実行前の準備

reindexを実行する前に、対象となるテーブルやインデックスの状態を確認することが重要です。以下のコマンドで確認できます。

sql

SELECT schemaname, tablename, relname, relkind, relpages
FROM pg_stat_all_tables;

sql

SELECT schemaname, indexname, relname, idx_scan
FROM pg_stat_all_indexes;

テーブルのreindex

テーブルの再構築によるパフォーマンス向上
テーブルのreindexは、テーブル全体のデータを再構築し、インデックスや統計情報を最新の状態に更新します。
次のコマンドを使用して、テーブルのreindexを行います。

sql

REINDEX TABLE <テーブル名>;

パフォーマンス向上効果の具体例を追記します。
以下の効果が期待できます。

インデックスの効率化: テーブルの再構築により、インデックスが最適な状態になります。これにより、クエリの実行速度が向上します。
統計情報の最新化: テーブルの再構築により、統計情報が更新されます。これにより、クエリプランナーがより正確な実行計画を立てることができ、クエリのパフォーマンスが向上します。

インデックスのreindex

インデックス再作成によるパフォーマンスの最適化
インデックスの再構築は、インデックスを削除して再作成することで、インデックスの効率性を向上させます。
次のコマンドを使用して、インデックスのreindexを行います。

sql

REINDEX INDEX <インデックス名>;

パフォーマンス向上効果の具体例を追記します。
以下の効果が期待できます。

インデックスの再構築: インデックスの再作成により、インデックスのフラグメンテーションが解消され、データのアクセス効率が向上します。
インデックスの最適化: インデックスの再構築により、クエリのパフォーマンスが向上します。特に、頻繁に更新されるテーブルのインデックスは定期的なreindexが推奨されます。

システムカタログのreindex

システムカタログの再構築によるメンテナンス性向上
システムカタログの再構築は、データベースのメタデータを再作成し、クエリの最適化やデータの整合性の確保に役立ちます。
次のコマンドを使用して、システムカタログのreindexを行います。

sql

REINDEX SYSTEM <カタログ名>;

パフォーマンス向上効果の具体例を追記します。
以下の効果が期待できます。

クエリの最適化: システムカタログの再構築により、クエリプランナーが正確な統計情報を利用して最適な実行計画を立てることができます。
データの整合性の確保: システムカタログの再構築により、データベースのメタデータが正常な状態に保たれます。これにより、データの整合性が確保され、安定性が向上します。

REINDEX DATABASEの使用

データベース全体の再構築によるメンテナンス性向上
REINDEX DATABASEコマンドを使用すると、データベース全体の再構築が行われます。これにより、テーブルやインデックスの再構築が一括して行われ、データベース全体のパフォーマンスが向上します。
次のコマンドを使用して、REINDEX DATABASEを実行します。

sql

REINDEX DATABASE <データベース名>;

REINDEX DATABASEの注意点とメリットを追記します。
★注意点
REINDEX DATABASEはデータベース全体の再構築なので、実行には時間とリソースがかかります。

メリット:
テーブルやインデックスの個別の再構築ではなく、一括して行うことで、メンテナンスの手間を削減できます。
データベース全体のパフォーマンス向上が期待できます。
(本番環境でも影響の少ない時間を考慮さえすれば、一番手軽にパフォーマンス向上が測れます。)

CONCURRENTLYオプションの使用

インデックス再構築中のデータアクセスを許可する
CONCURRENTLYオプションを使用すると、インデックスの再構築中にテーブルへのデータアクセスを許可します。これにより、データベースの稼働中にもインデックスの再構築が行えます。以下のコマンドでCONCURRENTLYオプションを使用したreindexを行います。

sql

REINDEX DATABASE CONCURRENTLY <データベース名>;

CONCURRENTLYオプションのメリットと制限事項を追記します。

メリット:
インデックスの再構築中でもデータベースへのアクセスが可能なので、生産環境での停止時間を最小限に抑えることができます。
非常に大きなテーブルや複数のインデックスがある場合でも、効率的に再構築を行えます。
(弊社では夜間にCONCURRENTLYオプションを使用してDATABSE単位で実行しています。)

制限事項:
CONCURRENTLYオプションを使用する場合、一部の制約があります。たとえば、一意制約や主キー制約を持つインデックスは再構築できません。詳細な制約については公式ドキュメントを参照してください。

★REINDEX DATABASE CONCRRENTLY実行時間例

テーブル数：492
レコード数(最大)：1587027
上記の環境で、2時間程度でした。
※データマップによるのであくまで参考程度でお願いします。

★落とし穴

REINDEXは対象のDBにログインした状態で行う必要があります。
シェルスクリプトなどで大量のDBのREINDEXを回す場合は以下をテンプレートにして頂くといいかもしれません。

psql

psql -h <ホスト名> -d <データベース名> -c 'REINDEX DATABASE CONCURRENTLY "'<データベース名>'";'

結論

reindexを適切に実行することで、テーブルやインデックスの再構築が行われ、データベースのメンテナンス性とパフォーマンスが向上します。また、REINDEX DATABASEやCONCURRENTLYオプションの使用により、データベース全体の再構築や稼働中のメンテナンスを効率的に行うことができます。データベースの安定性とパフォーマンスを維持するために、定期的なreindexの実行を検討しましょう。