このページは Cloud Translation API によって翻訳されました。

整合性のない B-tree インデックスの検出と修正

データベースインデックスの不整合は、ソフトウェア欠陥、ハードウェアの問題、動作の根本的な変更（並べ替え順序の変更など）といったさまざまな理由で発生する可能性があります。

PostgreSQL のコミュニティでは、このような問題を特定して修正するためのツールが開発されています。これには amcheck などのツールが含まれます。これは、以前のバージョンの PostgreSQL 14 で明らかになった問題を含め、整合性の問題を特定するために PostgreSQL コミュニティが推奨するツールです。

このハンドブックは、このような問題が発生した場合の AlloyDB for PostgreSQL ユーザー向けリファレンスとして作成されています。このページの情報が、他の PostgreSQL ユーザーが整合性のない B-tree インデックスを特定して修正する際に役立つことを願っています。Google は、より幅広いオープンソースコミュニティ向けのリソースとして、このドキュメントを継続的に改善することを目指しています。フィードバックがありましたら、このページの上部と下部にある [フィードバックを送信] ボタンを使用してお送りください。

インデックスの不整合の解決には、次の手順が含まれます。

始める前に

インデックスの再作成を開始する前に、データベースをバックアップし、適切な権限を設定し、psql クライアントのバージョンを確認し、amcheck 拡張機能を有効にする必要があります。
整合性のない B-tree インデックスを確認する。

不整合を修正する必要があるインデックスを特定するには、不整合のあるすべての B-tree インデックスを特定し、一意のキー違反と主キー違反をすべて特定する必要があります。
インデックスの不整合を修正する。

インデックスを再作成すると、すべての不整合が修正されます。パフォーマンスを改善するには、インスタンスのメモリ設定の調整が必要になる場合があります。
インデックス再作成オペレーションをモニタリングする。

インデックス再作成オペレーションの進行状況をモニタリングして、オペレーションの進行中にブロックされないようにすることをおすすめします。
インデックスに整合性があることを確認する。

インデックスが正常に再作成されたら、インデックスに不整合がないか確認することをおすすめします。

始める前に

AlloyDB クラスタのデータをバックアップする

インデックスの再作成中にデータが失われないようにするため、クラスタのデータをバックアップすることをおすすめします。詳しくは、オンデマンドバックアップの作成をご覧ください。

`alloydbsuperuser` 権限を設定します。

このページの手順を完了するには、alloydbsuperuser 権限が必要です。詳細については、AlloyDB の事前定義 PostgreSQL ロールをご覧ください。

`psql` クライアントのバージョンが 9.6 以降であることを確認する

このページの手順を完了するには、psql クライアントのバージョンが 9.6 以降である必要があります。現在の psql クライアントバージョンを確認するには、psql --version コマンドを実行します。

amcheck 拡張機能を有効にする

インデックスの不整合を確認するには、amcheck 拡張機能を有効にする必要があります。

コードサンプル

  CREATE EXTENSION amcheck;

整合性のない B-tree インデックスを確認する

以降のセクションでは、インデックスの不整合、一意のキー違反、主キー違反を確認することで、整合性のない B-tree インデックスを確認する方法について説明します。

不整合を確認する

各データベースで次のステートメントを実行して、すべての B-tree インデックスの不整合を確認します。

コードサンプル

DO $$
DECLARE
  r RECORD;
  version varchar(100);
BEGIN
  RAISE NOTICE 'Started amcheck on database: %', current_database();
  SHOW server_version into version;
  SELECT split_part(version, '.', 1) into version;
  FOR r IN
    SELECT c.oid, c.oid::regclass relname, i.indisunique
      FROM pg_index i
      JOIN pg_opclass op ON i.indclass[0] = op.oid
      JOIN pg_am am ON op.opcmethod = am.oid
      JOIN pg_class c ON i.indexrelid = c.oid
      JOIN pg_namespace n ON c.relnamespace = n.oid
     WHERE am.amname = 'btree'
       AND c.relpersistence != 't'
       AND c.relkind = 'i'
       AND i.indisready AND i.indisvalid LOOP
    BEGIN
      RAISE NOTICE 'Checking index %:', r.relname;
      PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
    EXCEPTION
      WHEN undefined_function THEN
        RAISE EXCEPTION 'Failed to find the amcheck extension';
      WHEN OTHERS THEN
        RAISE LOG 'Failed to check index %: %', r.relname, sqlerrm;
        RAISE WARNING 'Failed to check index %: %', r.relname, sqlerrm;
        END;
  END LOOP;
  RAISE NOTICE 'Finished amcheck on database: %', current_database();
END $$;

出力は次のようになります。

コードサンプル

  NOTICE:  Checking index t_pkey:
  NOTICE:  Checking index t_i_key:
  WARNING:  Failed to check index t_i_key: item order invariant violated for index "t_i_key"
  NOTICE:  Checking index t_j_key:
  WARNING:  Failed to check index t_j_key: item order invariant violated for index "t_j_key"
  NOTICE:  Checking index ij:
  WARNING:  Failed to check index ij: item order invariant violated for index "ij"

ログの表示の詳細については、ログエクスプローラを使用してログを表示するをご覧ください。

一意のキー違反と主キー違反を特定して修正する

このセクションでは、インデックスで一意のキー違反と主キー違反を確認する方法と、違反が存在する場合の修正方法について説明します。

一意のキー違反を特定する

インデックスを再作成する前に、一意のキー違反を修正する必要があります。一意のキー違反をすべて確認するには、各データベースで次のコマンドを実行します。

コードサンプル

WITH q AS (
    /* this gets info for all UNIQUE indexes */
    SELECT indexrelid::regclass as idxname,
           indrelid::regclass as tblname,
           indcollation,
           pg_get_indexdef(indexrelid),
           format('(%s)',(select string_agg(quote_ident(attname), ', ')
              from pg_attribute a
              join unnest(indkey) ia(nr) on ia.nr = a.attnum
             where attrelid = indrelid)) as idxfields,
           COALESCE(substring(pg_get_indexdef(indexrelid) FROM '[)] (WHERE .*)$'), '') as whereclause
      FROM pg_index
     WHERE indisunique
     /* next line excludes indexes not affected by collation changes */
       AND trim(replace(indcollation::text, '0', '')) != ''
)
SELECT
 /* the format constructs the query to execute for each index */
 format(
$sql$
DO $$ BEGIN RAISE NOTICE 'checking index %3$I%2$I on table %1$I %4$I'; END;$$;
SELECT this,
       prev,
       /* we detect both reversed ordering or just not unique */
       (CASE WHEN this = prev THEN 'DUPLICATE' ELSE 'BACKWARDS' END) as violation_type
  FROM (SELECT %2$s AS this,
               lag(%2$s) OVER (ORDER BY %2$s) AS prev
          FROM %1$s %4$s
        ) s
 WHERE this <= prev and this IS NOT NULL and prev IS NOT NULL;  /* change to just '<' if looking for reverse order in index */
$sql$, tblname, idxfields, idxname, whereclause
)
  FROM q
-- LIMIT 20 /* may use limit for testing */
-- the next line tells psql to executes this query and then execute each returned line separately
\gexec

スクリプトの出力は、次のようになります。

Output

  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
  NOTICE:  checking index=games_title_key on table=games  key_columns="(title)"
          this        |        prev        | violation_type
  --------------------+--------------------+----------------
  Game #16 $soccer 2  | Game #16 $soccer 2 | DUPLICATE
  Game #18 $soccer 2  | Game #18 $soccer 2 | DUPLICATE
  Game #2 $soccer 2   | Game #2 $soccer 2  | DUPLICATE
  Game #5 $soccer 2   | Game #5 $soccer 2  | DUPLICATE

この出力では、テーブルヘッダー NOTICE に、その下に表示される値のインデックス、列、テーブルが示されています。出力に DUPLICATE または BACKWARDS を示す行が含まれている場合は、インデックスが破損していることを示しているため、修正が必要です。BACKWARDS を示す行は、非表示になっている可能性のある重複値があることを示しています。テーブルにこれらのエントリのいずれかが表示されている場合は、重複キー違反を修正するをご覧ください。

重複キー違反を修正する

重複する一意のインデックスを特定した場合や、重複キー違反エラーが原因でインデックスの再作成オペレーションが失敗した場合は、次の手順で重複キーを見つけて削除します。

上記のサンプル出力に示すように、NOTICE テーブルヘッダーから key_columns を抽出します。次の例では、キー列は email です。
出力
```
  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
```
これらの値は、ステップ 3 のクエリの KEY_COLUMNS で使用します。
テーブルのスキーマを探します。psql を使用してデータベースに接続し、次のコマンドを実行します。
コードサンプル
```
\dt TABLE_NAME
```
schema 列の値は、手順 3 のクエリで SCHEMA_NAME に使用する値です。

たとえば、次のクエリの場合:
```
 \dt games
 
```
出力は次のようになります。
```
 List of relations
 Schema  | Name  | Type  | Owner
 --------+-------+-------+----------
 public  | games | table | postgres
 (1 row)
 
```
次のステートメントを実行して、テーブルのフルスキャンを強制的に実行し、重複するキーを取得します。
コードの例
```
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;

SELECT KEY_COLUMNS, count(*)
  FROM SCHEMA_NAME.TABLE_NAME
GROUP BY KEY_COLUMNS
HAVING count(*) > 1;
```
注: データベースが大きい場合、このステートメントが完了するまでに時間がかかることがあります。
上記のステートメントの KEY_COLUMNS は、チェックするテーブルの一意のインデックスまたは主キーの対象となる 1 つ以上の列です。これらは、一意のキー違反をチェックしたときに特定されました。このステートメントは、重複したキーと、各キーの重複の数を返します。

たとえば、次のクエリの場合:
```
  SELECT name,count(*)
    FROM public.TEST_NAMES
  GROUP BY name
  HAVING count(*) > 1;
  
```
出力は次のようになります。
```
  name                | count
  --------------------+-------
  Johnny              |     2
  Peter               |     2
(2 rows)
```
この場合は、次のステップに進んで重複するキーを削除します。

KEY_COLUMNS のいずれかの列が null の場合、NULL 列には一意の制約が適用されないため、無視できます。

重複するキーが見つからない場合は、整合性のないインデックスを修正するに進みます。
（省略可、ただし推奨）重複するキーを含むレコードのバックアップを作成します。次のステートメントを実行して、バックアップレコードを作成します。
コードサンプル
```
CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
WHERE (KEY_COLUMNS)
IN (KEY_VALUES);
```
このステートメントの KEY_VALUES は、前のステップの結果からコピーされた値のリストです。次に例を示します。
コードサンプル
```
  CREATE TABLE public.TEST_NAMES_bak
  AS SELECT * FROM public.TEST_NAMES
  WHERE (name) IN (('Johnny'),('Peter'))
  
```
行数が多い場合は、IN ステートメントの ((KEY_VALUES)) パラメータを、count パラメータを省略した手順 2 の SELECT ステートメントに置き換えます。次に例を示します。
コードの例
```
  CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
  AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
  WHERE (KEY_COLUMNS)
  IN ( SELECT (KEY_COLUMNS)
  FROM SCHEMA_NAME.TABLE_NAME
  GROUP BY (KEY_COLUMNS)
  HAVING count(*) > 1);
```
トリガーを無効にするため、ユーザーにレプリケーションロールを追加します。
コードの例
```
ALTER USER CURRENT_USER with REPLICATION;
SET session_replication_role = replica;
```

重複するキーを削除するには、次のステートメントを実行します。

コードの例

BEGIN;

DELETE FROM  SCHEMA_NAME.TABLE_NAME a
      USING  (
              SELECT   min(ctid) AS ctid,
                      KEY_COLUMNS
              FROM     SCHEMA_NAME.TABLE_NAME
              GROUP BY KEY_COLUMNS
                      HAVING count(*) > 1 ) b
      WHERE a.KEY_COLUMNS = b.KEY_COLUMNS
      AND   a.ctid <> b.ctid;

たとえば、複数列の KEY_COLUMNS の場合は次のようになります。

コードサンプル

  DELETE FROM public.test_random a
      USING (
             SELECT min(ctid) AS ctid,
             day, rnum
      FROM public.test_random
      GROUP BY day, rnum
             HAVING count(*) > 1 ) b
      WHERE a.day=b.day and a.rnum = b.rnum
      AND a.ctid <> b.ctid;

ここで、day と rnum は KEY_COLUMNS です。

このステートメントを実行すると、重複する行のセットごとに 1 つの行が保持され、その他の行は削除されます。削除される行のバージョンを制御するには、delete ステートメントで次のフィルタを実行します。

コードの例

DELETE FROM  SCHEMA_NAME.TABLE_NAME
WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);

次の手順に沿って、DELETE コマンドがエラーなしで想定された数の行を返したことを確認します。
1. 次のステートメントを実行して、テーブルが変更された行を特定します。
  コードサンプル
  SELECT schemaname, relname, n_tup_del, n_tup_upd FROM pg_stat_xact_all_tables WHERE n_tup_del+n_tup_upd > 0;
2. すべての行が正しい場合は、DELETE トランザクションを commit します。
  コードサンプル
  END;
3. エラーが発生した場合は、変更をロールバックしてエラーを修正します。
  コードサンプル
  ROLLBACK;
重複するキーを削除したら、インデックスを再作成できます。

整合性のないインデックスを修正する

以降のセクションでは、インスタンスで検出されたインデックスの不整合を修正する方法について説明します。

データベースの構成によっては、前の手順で特定したインデックスごとに次の手順を行う必要があります。

インデックスの再作成に向けて準備する。
インデックスを再作成する。
外部キー違反が原因でインデックス再作成オペレーションが失敗した場合は、これらの違反を見つけて修正する必要があります。
インデックス再作成オペレーションをもう一度実行します。

インデックスの再作成に向けて準備する

インデックスサイズを確認する

大規模なデータベースにインデックスを作成するには、小規模なデータベースの場合よりも時間がかかります。大規模なデータベースのインデックスオペレーションとインデックス再作成オペレーションの速度を改善するには、これらのオペレーションにより多くのメモリと CPU 性能を割り当てることができます。これはインデックス再作成オペレーションを計画する際の重要なステップです。インデックスサイズを確認したら、インデックス再作成オペレーションで使用されるメモリサイズを設定し、並列ワーカーの数を設定できます。

次のステートメントを実行して、修正するインデックスのインデックスサイズ（キロバイト単位）を確認します。

コードの例

SELECT i.relname                                      AS index_name,
       pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size
FROM   pg_index x
       JOIN pg_class i
         ON i.oid = x.indexrelid
WHERE  i.relname = 'INDEX_NAME';

このステートメントの出力は、次のようになります。

Output

 index_name | index_size
------------+------------
 my_index   | 16 kB
(1 row)

インデックスの再作成に使用するメモリサイズを設定する

前のセクションで決定したインデックスのサイズに基づいて、maintenance_work_mem 構成パラメータに適切な値を設定することが重要です。このパラメータは、インデックス再作成オペレーションに使用するメモリ量を指定します。たとえば、インデックスサイズが 15 GB を超える場合は、メンテナンスメモリを調整することをおすすめします。

次の例は、maintenance_work_mem を設定する方法を示しています。

コードサンプル

SET maintenance_work_mem TO "1GB";

大規模なデータベースにインデックスを作成するには、小規模なデータベースの場合よりも時間がかかります。インデックス作成オペレーションとインデックス再作成オペレーションの速度を改善するには、メモリが 4 GB 以上のインスタンスで、このインデックス再作成オペレーションの実行中に maintenance_work_mem をインスタンスメモリの 2% 以上に設定することをおすすめします。

並列ワーカーの数を設定する

インデックス再作成のための並列ワーカーの数を増やすには、データベースで max_parallel_maintenance_workers 構成パラメータを設定します。このパラメータのデフォルト値は 2 ですが、これよりも大きい値を設定することで、インデックス再作成のためのワーカーの数を増やすことができます。vCPU コアの数が 8 個以上のインスタンスでは、max_parallel_maintenance_workers フラグの値を 4 に設定することをおすすめします。

これらのパラメータに設定されている値を確認する方法は次のとおりです。

コードサンプル

SHOW max_parallel_maintenance_workers;
SHOW max_worker_processes;
SHOW max_parallel_workers;

max_parallel_maintenance_workers パラメータは max_worker_processes のサブセットであり、max_parallel_workers によって制限されます。より多くの並列ワーカーが必要な場合は、max_worker_processes と max_parallel_workers の値を増やします。

次の例は、max_parallel_maintenance_workers を設定する方法を示しています。

コードサンプル

SET max_parallel_maintenance_workers TO 4;

max_parallel_maintenance_workers パラメータはワーカーの割り当てを保証するものではありません。インデックス再作成によって複数の並列ワーカーが開始されたことを確認するには、インデックス再作成を開始した後、別のセッションから次のクエリを実行します。

コードサンプル

SELECT
leader.leader_pid,leader.pid "worker_pid",leader.query
FROM
pg_stat_activity leader, pg_stat_activity worker
WHERE leader.leader_pid = worker.pid;

インデックスを再作成する

pg_repack ユーティリティを使用すると、本番環境ワークロードをブロックせずにインデックスを再作成できます。このユーティリティでは、同時インデックス再作成プロセスが自動化および簡素化されます。これにより、ダウンタイムなしでインデックスを再作成できます。この手順では、pg_repack バージョン 1.4.7 を使用します。

pg_repack を使用してインデックスを再作成するには、次の手順を行います。

pg_repack ページから pg_repack ユーティリティをダウンロードし、コンパイルしてインストールします。
pg_repack　拡張機能を作成します。
コードの例
```
CREATE EXTENSION pg_repack;
```
次のコマンドを実行して、同時にインデックス再作成を行います。
コードの例
```
  pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
```
注: pg_repack では、マテリアライズドビューでインデックスを再作成することはできません。
このコマンドの出力は次のようになります。
Output
```
INFO: repacking index "public.t_i_key"
```
pg_repack の実行中にエラーが発生した場合は、エラーを修正してからもう一度お試しください。すべての一意キーインデックスと主キーインデックスを修正したら、外部キー違反を確認し、検出されたすべての違反を修正する必要があります。

外部キー違反を検出して修正する

外部キー違反を検出して修正する方法については、外部キー違反を検出して修正するをご覧ください。

インデックス再作成オペレーションをモニタリングする

インデックス再作成オペレーションは他のセッションによってブロックされることがあります。4 時間ごとに確認することをおすすめします。インデックス再作成オペレーションがブロックされている場合は、ブロックしているセッションをキャンセルできます。これで、インデックス再作成オペレーションを完了できます。

ブロックしているセッションと待機中のセッションを特定して、INDEX オペレーションでキャンセルするには、次の手順を行います。

ブロックしているセッションを特定するため、次のクエリを実行します。

コードの例

SELECT pid,
      usename,
      pg_blocking_pids(pid) AS blocked_by,
      query                 AS blocked_query
FROM   pg_stat_activity
WHERE  cardinality(pg_blocking_pids(pid)) > 0;

セッションをキャンセルするには、前のクエリで取得したブロックしているセッションの PID を使用して、次のクエリを実行します。
コードの例
```
SELECT pg_cancel_backend(PID);
```

インデックスに整合性があることを確認する

整合性のない各インデックスについて、インデックスの不整合を継続的にチェックする必要があります。インスタンスの整合性がないインデックスとキー違反をすべて修正したら、前のセクションの手順に沿って問題が存在しないことを確認できます。

インデックスの不整合を確認する
一意のキー違反を確認する

整合性のない B-tree インデックスの検出と修正

始める前に

AlloyDB クラスタのデータをバックアップする

alloydbsuperuser 権限を設定します。

psql クライアントのバージョンが 9.6 以降であることを確認する

amcheck 拡張機能を有効にする

コードサンプル

整合性のない B-tree インデックスを確認する

不整合を確認する

コードサンプル

コードサンプル

一意のキー違反と主キー違反を特定して修正する

一意のキー違反を特定する

コードサンプル

Output

重複キー違反を修正する

出力

コードサンプル

コードの例

コードサンプル

コードサンプル

コードの例

コードの例

コードの例

コードサンプル

コードの例

コードサンプル

コードサンプル

コードサンプル

整合性のないインデックスを修正する

インデックスの再作成に向けて準備する

インデックス サイズを確認する

コードの例

Output

インデックスの再作成に使用するメモリサイズを設定する

コードサンプル

並列ワーカーの数を設定する

コードサンプル

コードサンプル

コードサンプル

インデックスを再作成する

コードの例

コードの例

Output

外部キー違反を検出して修正する

インデックス再作成オペレーションをモニタリングする

コードの例

コードの例

インデックスに整合性があることを確認する

`alloydbsuperuser` 権限を設定します。

`psql` クライアントのバージョンが 9.6 以降であることを確認する

インデックスサイズを確認する