DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

破損したインデックスのトラブルシューティング：PostgreSQLインデックスの再構築と修復方法

この包括的なガイドで、PostgreSQLインデックスのトラブルシューティングと修復の技術を習得しましょう。`pg_stat_user_indexes`や`EXPLAIN ANALYZE`などの組み込みツールを使用して、肥大化または破損したインデックスを特定する方法を学びます。この記事では、`REINDEX`コマンド（`CONCURRENTLY`オプションを含む）を使用して、ダウンタイムを最小限に抑えながらインデックスを効率的に再構築する手順を段階的に説明します。関連するメンテナンスコマンド、プロアクティブな保守のベストプラクティス、および最適なクエリパフォーマンスとデータベースの健全性を確保するための重要な警告についても紹介します。

破損したインデックスのトラブルシューティング：PostgreSQLインデックスの再構築と修復方法

インデックスは通常、PostgreSQLがテーブル全体を読み取る代わりにミリ秒単位でクエリに応答できる理由です。また、インデックスが肥大化したり、無効になったり、破損が疑われるまで忘れられがちです。その場合、最初は通常のパフォーマンスの問題のように見えます。あるクエリが遅くなり、ディスク読み取りが増加し、以前は静かだったテーブルが高コストになり、クエリプランが意味をなさなくなります。

インデックスの再構築は難しくありません。いつ再構築するべきかを知ることの方が難しいです。肥大化したインデックスはREINDEXで修正できるかもしれませんが、根本的な原因は弱いautovacuum設定や、一日中同じ行を更新するワークロードである可能性があります。破損したインデックスは緊急の修復が必要かもしれませんが、なぜ破損が発生したのかを問うべきです：ストレージ、メモリ、カーネルエラー、安全でないハードウェア設定、またはまれなソフトウェアバグ。

このガイドでは、実用的なPostgreSQLコマンドに焦点を当てます：疑わしいインデックスを見つける方法、ダウンタイムありとなしで再構築する方法、そして本番データベースでメンテナンスを実行する前に確認すべきこと。

PostgreSQLインデックスとその一般的な問題の理解

PostgreSQLインデックス、最も一般的にはB-treeインデックスは、プランナーがすべての行をスキャンするのを避けるためのルックアップ構造です。インデックスが健全で選択的である場合、PostgreSQLは必要なテーブルの小さな部分にジャンプできます。インデックスが肥大化または無効である場合、プランナーはそれを使用するかもしれませんが、データベースは同じ結果を得るために余分な作業を行います。

インデックスは主に2つの理由で問題になる可能性があります：肥大化と破損。

インデックスの肥大化

インデックスの肥大化とは、インデックス構造内に「デッドタプル」（古いデータバージョン）が蓄積されることを指します。PostgreSQLでは、行が更新または削除されると、古いバージョンのデータ（およびそれに対応するインデックスエントリ）はすぐには削除されません。代わりに、「デッド」としてマークされ、最終的にVACUUMプロセスによって再利用されます。VACUUMが十分な頻度または効果的に実行されない場合、または更新/削除の割合が高い場合、これらのデッドタプルが蓄積し、インデックスが必要以上に大きくなる可能性があります。肥大化したインデックスは、より多くのディスク容量を占有し、スキャンにより多くのI/O操作を必要とし、クエリの高速化において効果が低下することさえあります。

インデックスの破損

インデックスの破損はより深刻な問題で、インデックスの内部構造が論理的に矛盾したり、物理的に損傷したりするものです。これはさまざまな要因によって引き起こされる可能性があります：

  • ハードウェア障害：ディスクエラー、メモリ問題、または停電。
  • ソフトウェアバグ：PostgreSQL自体または基盤となるオペレーティングシステムコンポーネントにおけるまれですが可能性のある欠陥。
  • 突然のシステムクラッシュ：適切なシャットダウン手順なしでのPostgreSQLサーバーの突然の終了。

破損したインデックスは、誤ったクエリ結果、「インデックスに予期しないデータが含まれています」などのエラー、またはクエリの完了を妨げることさえあります。破損の特定と修正は、データの整合性とデータベースの安定性にとって重要です。

問題のあるインデックスの症状には、特定のクエリの突然の速度低下、明らかな理由のないI/Oアクティビティの増加、またはインデックススキャンに関連するエラーメッセージが含まれます。

問題のあるインデックスの特定

インデックスを修復する前に、どのインデックスが問題を引き起こしているかを特定する必要があります。PostgreSQLはこれを行うためのいくつかの方法を提供します。

未使用または非効率的なインデックスの確認

pg_stat_user_indexesビューは、インデックス使用状況に関する統計を提供します。これを使用して、ほとんどまたはまったく使用されていないインデックスを見つけることができます。これらは削除または再評価の候補となる可能性があります。

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0 -- 一度もスキャンされていないインデックス
    AND schemaname = 'public'
ORDER BY
    pg_relation_size(indexrelid) DESC;

idx_scanが0の場合は未使用のインデックスを示している可能性がありますが、一部のインデックスは制約（例：UNIQUE、PRIMARY KEY）や頻繁にアクセスされないレポートに使用されることを考慮することが重要です。削除する前に必ず調査してください。

インデックスの肥大化の検出

肥大化を直接検出するのは難しいですが、テーブルと比較して不釣り合いに大きいインデックスサイズや、対応するデータの増加なしに過度に成長するインデックスは、肥大化を示している可能性があります。テーブルとそのインデックスのサイズを比較できます：

SELECT
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_stat_user_tables
ORDER BY
    pg_total_relation_size(relid) DESC;

より高度な肥大化検出のために、コミュニティ提供のスクリプトやpg_repackやpgstattuple（タプル密度を調べることで肥大化を推定できる）などの拡張機能の使用を検討してもよいでしょう。

EXPLAIN ANALYZEを使用した低速クエリの特定

特定のクエリが遅くなった場合、EXPLAIN ANALYZEが最良のツールです。これは、インデックスがどのように使用されているか（または使用されていないか）を含む、クエリ実行計画と実際の実行時統計を示します。

EXPLAIN ANALYZE
SELECT * FROM your_table WHERE your_column = 'some_value';

計画がインデックススキャンを期待していた場所でシーケンシャルスキャンを示している場合、またはインデックススキャンに異常に長い時間がかかっている場合、それは非効率的または問題のあるインデックスを示している可能性があります。

インデックス破損の確認

インデックスの破損は、多くの場合、PostgreSQLログのエラーや、クエリが予期せず失敗したときに現れます。corruption、unexpected data、invalid page、bad blockなどのフレーズを含むメッセージを探してください。データベース内のすべてのインデックスが健全であることを証明する単一の組み込みSQLコマンドはありません。より深いチェックのために、チームはメンテナンスウィンドウ中にPostgreSQLのamcheck拡張機能、特にB-treeインデックス用のbt_index_checkとbt_index_parent_checkを使用することがよくあります。

CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT bt_index_check('public.idx_products_name'::regclass);

amcheckは診断ツールであり、修復ツールではありません。問題を報告した場合は、まだ最近のバックアップがない場合はバックアップを取り、PostgreSQLおよびシステムログを検査し、再構築を計画してください。

ヒント： PostgreSQLログのエラーメッセージを定期的に監視してください。破損の早期発見は、より大きな問題を防ぐことができます。

REINDEXコマンド：主要なツール

REINDEXコマンドは、PostgreSQLインデックスを再構築するための主要なツールです。インデックスをゼロから再構築し、デッドタプルを削除して肥大化を効果的に修正し、テーブルの現在のデータに基づいて新しい有効な構造を構築することで破損を修復します。

REINDEXの仕組み

REINDEXが実行されると、PostgreSQLは現在のテーブルデータからインデックスを再構築します。結果は、新しいコンパクトなインデックス構造になります。肥大化の場合、インデックス内のデッドスペースが削除されます。多くのインデックスレベルの破損ケースでは、テーブルから構築された新しい構造をPostgreSQLに提供します。

REINDEXの構文と使用法

REINDEXは異なる粒度で適用できます：

  1. 特定のインデックスを再インデックス：

    REINDEX INDEX index_name;

    これは最も一般的な使用例で、単一の問題のあるインデックスを対象とします。

  2. テーブル上のすべてのインデックスを再インデックス：

    REINDEX TABLE table_name;

    テーブルに複数の肥大化または破損したインデックスがある場合に便利です。

  3. データベース内のすべてのインデックスを再インデックス：

    REINDEX DATABASE database_name;

    これはより抜本的な手段であり、通常、広範囲にわたる破損や肥大化が疑われる状況で使用されます。これにより、重大なダウンタイムが発生する可能性があります。

  4. データベース内のシステムカタログを再インデックス：

    REINDEX SYSTEM database_name;

    これにより、指定されたデータベース内のシステムカタログテーブルのすべてのインデックスが再構築されます。これは細心の注意を払って、システムカタログインデックスに問題があると疑われる場合にのみ使用する必要があります。データベース全体の機能に影響を与え、排他的アクセスを必要とするためです。

警告： CONCURRENTLYなしでREINDEXを実行すると、より強力なロックがかかり、影響を受けるオブジェクトに対する通常のアプリケーショントラフィックをブロックする可能性があります。PostgreSQLのバージョンとオブジェクトタイプに対して正確なコマンドとロック動作をテストしない限り、ダウンタイム操作として扱ってください。

REINDEX CONCURRENTLYによるダウンタイムの最小化

ダウンタイムが許容されない本番システムでは、REINDEX CONCURRENTLYは非常に貴重なオプションです。これにより、テーブルに対する同時読み取りおよび書き込み操作をブロックせずにインデックスを再構築できます。

REINDEX CONCURRENTLYの仕組み：

  1. 通常の操作と同時に新しいインデックス定義を構築します。
  2. テーブルに短いSHARE UPDATE EXCLUSIVEロックを取得します。これはDDL（ALTER TABLEなど）をブロックしますが、DML（INSERT、UPDATE、DELETE）およびSELECTステートメントは許可します。
  3. 次に、テーブルをスキャンして新しいインデックスを構築します。
  4. 初期構築後、別の非常に短いSHARE UPDATE EXCLUSIVEロックを取得して、構築プロセス中に発生した変更を適用します。
  5. 最後に、古いインデックスを新しいものに置き換え、古いインデックスを削除します。

構文：

REINDEX INDEX CONCURRENTLY index_name;

REINDEX CONCURRENTLYに関する重要な考慮事項：

  • 実行速度の低下：同時変更を処理する必要があるため、REINDEX CONCURRENTLYは通常、非同時のREINDEXよりも低速です。
  • ディスク容量：古いインデックスと新しいインデックスの両方の構造を一時的に保持するためのディスク容量が必要です。
  • トランザクションサポートなし：REINDEX CONCURRENTLYはトランザクションブロック内で実行できません。
  • エラー処理：REINDEX CONCURRENTLYが失敗した場合（例：一意インデックスの一意制約違反による）、無効なインデックスが残ります。この無効なインデックスをDROPしてから、REINDEX CONCURRENTLYコマンドを再実行する必要があります。

再インデックスの実践例

テーブルproductsにインデックスidx_products_nameがあると仮定しましょう。

単一インデックスの再構築（ダウンタイムあり）

影響を受けるインデックスに対して短い停止が許容される場合：

REINDEX INDEX idx_products_name;

単一インデックスの再構築（同時、最小ダウンタイム）

productsテーブルがアクセス可能である必要がある本番システムの場合：

-- B-treeインデックスの場合：
REINDEX INDEX CONCURRENTLY idx_products_name;

-- 主キーまたは一意制約インデックスの場合（多くの場合、特別な処理が必要ですが、REINDEX CONCURRENTLYで処理されます）：
-- 主キーまたは一意制約インデックスを再構築する必要がある場合、通常は基になるインデックスを再構築します。
-- たとえば、'products_pkey'が主キーインデックスの場合：
REINDEX INDEX CONCURRENTLY products_pkey;

テーブル上のすべてのインデックスの再構築

productsテーブル上の複数のインデックスに問題があると疑われる場合：

-- これにより、'products'テーブルにACCESS EXCLUSIVEロックが取得されます。
REINDEX TABLE products;

最新のPostgreSQLバージョンは、同時テーブル再インデックスをサポートしています：

REINDEX TABLE CONCURRENTLY products;

これは通常、各インデックスを手動で再構築するよりも簡単ですが、それでもI/O、CPU、および一時ディスク容量を消費します。この構文をサポートしていない古いPostgreSQLリリースでは、テーブルのインデックスを特定し、REINDEX INDEX CONCURRENTLYでそれぞれを再構築します。

まず、テーブルのすべてのインデックスを特定します：

SELECT indexname FROM pg_indexes WHERE tablename = 'products';

手動制御のために、最初にインデックスをリストします：

SELECT indexname
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'products'
ORDER BY indexname;

データベース内のすべてのインデックスの再構築

これは最後の手段であり、かなりのダウンタイムが必要です。スケジュールされたメンテナンスウィンドウ中にのみ実行する必要があります。

REINDEX DATABASE your_database_name;

または、サポートされているPostgreSQLバージョンでは、REINDEX DATABASE CONCURRENTLY your_database_name;を使用できます。最悪のブロック動作を回避しますが、それでも主要なメンテナンス操作であり、トランザクションブロック内では実行できません。

関連するメンテナンスコマンドとベストプラクティス

再インデックスは、多くの場合、より広範なメンテナンス戦略の一部です。他のコマンドは、インデックスの問題を防ぐ上で重要な役割を果たします。

VACUUMとVACUUM FULL

  • VACUUM：デッドタプルが占有する領域を再利用可能にします。テーブルやインデックスファイルをディスク上で縮小しませんが、肥大化を防ぐために重要です。autovacuumデーモンは通常これを自動的に処理します。
    VACUUM your_table;
  • VACUUM FULL：テーブル全体とそれに関連するインデックスを新しいディスクファイルに書き換え、最大限の領域を再利用し、肥大化を排除します。ただし、テーブルにACCESS EXCLUSIVEロックを取得し、すべての操作をブロックするため、細心の注意を払って使用する必要があります。インデックスの肥大化にはREINDEXがよく好まれます。
    VACUUM FULL your_table;

ANALYZE

ANALYZEコマンドは、データベース内のテーブルの内容に関する統計を収集し、pg_statisticに保存します。PostgreSQLクエリプランナーはこれらの統計を使用して、インデックスを使用するかどうかを含め、クエリの実行方法についてインテリジェントな決定を行います。重要なデータ変更後（または再インデックス後）にANALYZEを実行すると、プランナーが最新の情報を持つことが保証されます。

ANALYZE your_table;
-- またはデータベース全体を分析：
ANALYZE;

Auto-Vacuumの監視

autovacuumデーモンが実行され、正しく設定されていることを確認してください。これは、VACUUMおよびANALYZE操作を自動的に実行する責任があり、肥大化を防ぎ、統計を最新に保つために重要です。設定が誤っているautovacuumは、パフォーマンス低下の一般的な原因です。

定期的なメンテナンススケジュール

プロアクティブなインデックスメンテナンスは、事後対応型のトラブルシューティングよりも優れています。以下のスケジュールを確立します：

  • インデックスの使用状況とサイズの監視：潜在的な肥大化や未使用のインデックスを特定します。
  • REINDEX CONCURRENTLYの実行：頻繁に更新または削除されるテーブル、または重要なデータ移行後。
  • autovacuumログと設定の確認：データベースアクティビティに対応していることを確認します。

テストとバックアップ

  • 常にテスト：本番データベースで主要なメンテナンス操作を実行する前に、本番設定をミラーリングしたステージングまたは開発環境で徹底的にテストしてください。
  • 常にバックアップ：REINDEX操作、特に非同時のものやテーブル全体/データベースを対象とするものを開始する前に、データベースの最近の信頼できるバックアップがあることを確認してください。REINDEXは一般的に安全ですが、破損したデータベースのバックアップは役に立ちません。

トラブルシューティングのヒントと警告

  • ディスク容量：REINDEX操作（特にCONCURRENTLY）は、かなりの一時ディスク容量を必要とします – 再構築されるインデックスのサイズの最大2倍になる可能性があります。データベースサーバーに十分な空き容量があることを確認してください。
  • パフォーマンスへの影響：REINDEX CONCURRENTLYでさえ、その操作中にCPUとI/Oリソースを消費します。実行中はシステムのパフォーマンスを注意深く監視してください。
  • 根本原因の特定：インデックスがなぜ肥大化または破損しているのかを理解せずに、繰り返し再インデックスしないでください。非効率的なVACUUM設定、高いトランザクションレート、またはハードウェアの問題などの根本的な問題を調査してください。
  • インデックス作成と再インデックス：CREATE INDEX CONCURRENTLYは、ブロックせずに新しいインデックスを作成するためのREINDEX INDEX CONCURRENTLYと同等です。同様の原則に従い、同様の制限があります。

優れたインデックスメンテナンスは、部分的にはコマンドの知識であり、部分的には抑制です。REINDEX CONCURRENTLYは便利な修復ツールですが、ワークロードを理解せずに繰り返し再インデックスしても、通常は同じ肥大化が戻ってきます。上記のコマンドを使用して問題を確認し、可能な限り最小の影響を受けるオブジェクトを再構築し、autovacuum、更新パターン、ディスクの健全性、およびクエリプランを確認して、来月も同じ緊急修復を行わないようにしてください。