大きなインデックスを使った autovacuum の管理
操作の一環として、autovacuum はテーブル上で実行している間にいくつかのバキュームフェーズ
このプロセスでは、まずインデックス全体のサイズを確認します。次に、次の例に示すように、削除できるインデックスがあるかどうかを確認します。
テーブルとそのインデックスのサイズを確認するには
postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));pg_size_pretty 6404 MB (1 row)
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));pg_size_pretty 11 GB (1 row)
この例では、インデックスのサイズはテーブルよりも大きくなっています。この違いにより、インデックスが肥大化したり使用されなかったりするため、パフォーマンスの問題が発生し、自動バキュームや挿入オペレーションに影響する可能性があります。
未使用のインデックスを確認するには
pg_stat_user_indexesidx_scan 列でインデックスがどのくらいの頻度で使用されているかを確認できます。次の例では、未使用のインデックスに 0 の idx_scan 値があります
postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+------------------+-----------------------+----------+--------------+--------------- 16433 | 16454 | public | pgbench_accounts | index_f | 6 | 6 | 0 16433 | 16450 | public | pgbench_accounts | index_b | 3 | 199999 | 0 16433 | 16447 | public | pgbench_accounts | pgbench_accounts_pkey | 0 | 0 | 0 16433 | 16452 | public | pgbench_accounts | index_d | 0 | 0 | 0 16433 | 16453 | public | pgbench_accounts | index_e | 0 | 0 | 0 16433 | 16451 | public | pgbench_accounts | index_c | 0 | 0 | 0 16433 | 16449 | public | pgbench_accounts | index_a | 0 | 0 | 0 (7 rows)
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;schemaname | relname | indexrelname | idx_scan ------------+------------------+-----------------------+---------- public | pgbench_accounts | index_f | 6 public | pgbench_accounts | index_b | 3 public | pgbench_accounts | pgbench_accounts_pkey | 0 public | pgbench_accounts | index_d | 0 public | pgbench_accounts | index_e | 0 public | pgbench_accounts | index_c | 0 public | pgbench_accounts | index_a | 0 (7 rows)
注記
これらの統計情報は、統計がリセットされた時点から増加します。例えば、あるビジネス四半期末にのみ使用される、または特定のレポートにのみ使用されるインデックスがあるとします。統計がリセットされてから、このインデックスが使用されていない可能性があります。詳細については、「統計関数
データベースの統計が最後にリセットされた日時を確認するには、pg_stat_database
postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';datname | stats_reset ----------+------------------------------- postgres | 2022-11-17 08:58:11.427224+00 (1 row)
テーブルをできるだけ早くバキューム処理する
RDS for PostgreSQL 12 以上
大きなテーブルにインデックスが多すぎる場合、DB インスタンスがトランザクション ID ラップアラウンド (XID) に近づいている可能性があります。これは XID カウンターが 0 にラップアラウンドするタイミングです。チェックを外したままにすると、この状況では、データが失われる可能性があります。ただし、インデックスをクリーンアップせずにテーブルをすばやくバキューム処理できます。RDS for PostgreSQL 12 以上では、INDEX_CLEANUP
postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;INFO: vacuuming "public.pgbench_accounts" INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
自動バキュームセッションが既に実行されている場合、手動 VACUUM を開始するにはセッションを終了する必要があります。手動バキュームフリーズの実行については、「 手動バキュームフリーズの実行」を参照してください。
注記
インデックスのクリーンアップを定期的にスキップすると、インデックスが肥大化し、スキャン全体のパフォーマンスに影響する可能性があります。ベストプラクティスとして、前述の手順は、トランザクション ID の循環を防ぐためにのみ使用してください。
RDS for PostgreSQL 11 以降
ただし、RDS for PostgreSQL 11 以前のバージョンでは、バキューム処理をより速く完了させる唯一の方法は、テーブルのインデックス数を減らすことです。インデックスを削除すると、クエリプランに影響する可能性があります。未使用のインデックスを最初に削除し、XID の循環が間近になったらインデックスを削除することをお勧めします。バキューム処理が完了したら、これらのインデックスを再作成できます。
