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

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

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

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

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

始める前に

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

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

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

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

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

始める前に

データベースをバックアップする

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

`cloudsqlsuperuser` 権限を設定する

このページの手順を完了するには、cloudsqlsuperuser 権限が必要です。詳細については、session_replication_role をご覧ください。

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

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

amcheck 拡張機能をインストールする

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

PostgreSQL 9.6

注: amcheck_next をインストールする前に、POSTGRES_9_6_24.R20220710.01_12 がインストール済みであることを確認する必要があります。新しいターゲットメンテナンスバージョンのインストールの詳細については、セルフサービスのメンテナンスをご覧ください。

amcheck for PostgreSQL 9.6 をインストールするには、次のステートメントを実行します。

  CREATE EXTENSION amcheck_next;

「Could not open extensions control file...」というエラーが表示された場合は、正しいターゲットメンテナンスバージョン（POSTGRES_9_6_24.R20220710.01_12）を実行していることを確認してください。

PostgreSQL 10 以降

amcheck for PostgreSQL 10 以降をインストールするには、次のステートメントを実行します。

  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;
        IF version = '10' THEN
          PERFORM bt_index_check(index => r.oid);
        ELSE
          PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
        END IF;
      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"

PostgreSQL ログの表示については、インスタンスログを表示するをご覧ください。

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

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

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

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

コードサンプル

  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    on   table=%1$I      key_columns=%2$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

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

出力

  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';

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

出力

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

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

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

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

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

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

詳細については、データベースフラグを設定するをご覧ください。

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

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

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

pg_repack ページから pg_repack ユーティリティをダウンロードし、コンパイルしてインストールします。

Debian GNU/Linux 11

Debian Linux ユーザーは、Linux x86_64 プラットフォーム用のビルド済み実行バイナリをダウンロードしてインストールすることをおすすめします。

このバイナリの sha256 チェックサムハッシュは次のとおりです。

ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

Linux のバージョンが Debian GNU/Linux 11 であることを確認するには、コマンド hostnamectl を実行します。

セルフコンパイル

注: この手順では、動作中の GNU ツールチェーンがある Linux または Unix のターミナルでコマンドを実行する必要があります。

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 では、マテリアライズドビューでインデックスを再作成することはできません。
このコマンドの出力は次のようになります。
出力
```
  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 インデックスの検出と修正

始める前に

データベースをバックアップする

cloudsqlsuperuser 権限を設定する

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

amcheck 拡張機能をインストールする

PostgreSQL 9.6

PostgreSQL 10 以降

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

不整合を確認する

コードサンプル

出力

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

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

コードサンプル

出力

重複キー違反を修正する

コードサンプル

コードサンプル

コードサンプル

コードサンプル

コードサンプル

コードサンプル

コードサンプル

コードサンプル

コードサンプル

コードサンプル

コードサンプル

コードサンプル

コードサンプル

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

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

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

コードサンプル

出力

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

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

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

Debian GNU/Linux 11

セルフ コンパイル

コードサンプル

コードサンプル

出力

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

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

コードサンプル

コードサンプル

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

`cloudsqlsuperuser` 権限を設定する

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

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

セルフコンパイル