0Pricing
SQL Academy · レッスン

インデックスのメンテナンスと肥大化

インデックスの肥大化を診断し、REINDEX CONCURRENTLYで再構築して、未使用のインデックスを安全に削除します。

「インデックスのメンテナンスと肥大化」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

インデックスが肥大化する理由

PostgreSQLはMVCCを使用します。UPDATEによって新しい行バージョンが書き込まれ、既存のトランザクションから見えている古いバージョンは残ります。両方のバージョンに対するインデックスエントリが存在するため、時間の経過とともに次のようになります。

  • 更新の多いテーブルでは、不要になったインデックスエントリが蓄積する
  • インデックスが必要以上に大きくなる
  • B-treeが深くなり、検索が遅くなる

肥大化の診断

肥大化を確認するクエリは単純ではありません。一般的なツールには次のものがあります。

  • pgstattuple拡張
  • pg_repackのレポート
  • check_postgresや監視ツールが提供する肥大化確認クエリ
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

REINDEX

インデックスを再構築します。従来の形式ではACCESS EXCLUSIVEロックを取得するため、本番環境には適していません。

REINDEX INDEX orders_user_id_idx;       -- blocks writes!

REINDEX CONCURRENTLY (PG 12以降)

ブロックを抑えた方式です。再構築中も読み取りと書き込みを継続できます。

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

未使用のインデックス

未使用のインデックスは、すべての書き込みを遅くする一方で、読み取りを1回も高速化しません。次の方法で見つけられます。

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

未使用のインデックスを削除する

削除する前に、すべての環境と時間帯をまたいで確認してください。たまにしか実行しないレポート用のインデックスは、ほとんどの時間帯で未使用に見えます。

DROP INDEX CONCURRENTLY old_unused_idx;

重複するインデックス

制約と手動のCREATE INDEXによって、同じインデックスが作成されていることがあります。pg_indexesで重複を確認し、不要な方を削除してください。

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

インデックスによる書き込み増幅

INSERT/UPDATE/DELETEを実行するたびに、関連するすべてのインデックスが更新されます。更新の多いテーブルにインデックスが3つあると、書き込みコストは3倍になります。効果のあるインデックスだけを追加してください。

GINの保留リスト

GINインデックスは、保留リストに更新をまとめて保存します。手動でフラッシュするか、autovacuumに任せます。

SELECT gin_clean_pending_list('events_data_gin');

VACUUMによるインデックスエントリのクリーンアップ

VACUUM（MVCCのレッスンで説明します）は、ヒープページから不要になったインデックスエントリを削除します。autovacuumがなければ、インデックスは際限なく大きくなります。

インデックスサイズの監視

時間の経過に伴うインデックスサイズを追跡します。

SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

pg_repack: テーブルをオンラインで書き換える

肥大化が深刻な場合、pg_repackはテーブルとインデックスをオンラインで書き換えます。テーブル全体をロックする必要はありません。OSパッケージとPostgreSQL拡張としてインストールします。

まとめ

インデックスにはメンテナンスが必要です。

  • MVCCによる肥大化は正常な現象です。VACUUMとREINDEX CONCURRENTLYで管理してください
  • 未使用のインデックスを削除する
  • 重複を避ける
  • 新しいインデックスを追加するたびに書き込みは遅くなるため、慎重に判断する

理解度チェック

書き込みをブロックせずにインデックスを再構築するPostgreSQLコマンドはどれですか。

よくある質問

「インデックスのメンテナンスと肥大化」レッスンは無料ですか？

はい。「インデックスのメンテナンスと肥大化」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「インデックスのメンテナンスと肥大化」で何を学びますか？

インデックスの肥大化を診断し、REINDEX CONCURRENTLYで再構築して、未使用のインデックスを安全に削除します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。

「インデックスのメンテナンスと肥大化」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. B-tree、Hash、GiST、GINインデックス
  2. 複合インデックスと列の順序
  3. 部分インデックスと式インデックス
  4. インデックスのメンテナンスと肥大化
← SQL Academyに戻る