找出並修正不一致的索引

資料庫索引不一致的原因有很多,包括軟體瑕疵、硬體問題,或排序順序變更等行為的基礎異動。

PostgreSQL 社群已建構相關工具,可找出並修正這類問題。這包括 amcheck 等工具,PostgreSQL 社群建議使用這類工具找出一致性問題,包括先前版本的 PostgreSQL 14 曾出現的問題。

如果 Cloud SQL 使用者遇到這些一致性問題,可以參考這份劇本。這份教戰手冊提供相關資訊,協助 PostgreSQL 使用者找出並修正不一致的索引。

如要解決索引不一致的問題,請按照下列步驟操作:

  1. 事前準備

    開始重新建立索引前,請先備份資料庫、設定正確的權限、確認 psql 用戶端版本,並下載 amcheck 擴充功能。

  2. 檢查索引是否不一致

    其中一個陳述式會找出違反唯一和主鍵的項目,另一個陳述式則會偵測各種其他不一致的項目。

  3. 修正索引的不一致問題

    重新建立索引可修正所有不一致的問題。您可能需要調整執行個體的記憶體設定,才能提升效能。

  4. 監控重新建立索引作業

    建議您監控重新建立索引作業的進度,確保作業順利進行,不會遭到封鎖。

  5. 確認索引一致

    成功重新建立索引後,建議您確認索引是否含有任何不一致之處。

事前準備

備份資料庫

為確保重新建立索引期間不會遺失資料,建議您備份資料庫。詳情請參閱建立按照需求執行的備份

設定 cloudsqlsuperuser 權限

如要完成這個頁面的步驟,您必須具備cloudsqlsuperuser權限。詳情請參閱 session_replication_role

確認 psql 用戶端版本為 9.6 以上

如要完成本頁面的步驟,請務必確認您的 psql 用戶端版本為 9.6 以上。執行 psql --version 指令,驗證目前的 psql 用戶端版本。

安裝 amcheck 擴充功能

如要檢查索引不一致情形,請啟用「amcheck」擴充功能。

PostgreSQL 9.6

如要安裝 PostgreSQL 9.6 的 amcheck,請執行下列陳述式:

  CREATE EXTENSION amcheck_next;
  

如果收到 `Could not open extension control file...` 錯誤訊息,請確認您執行的目標維護版本正確無誤 (POSTGRES_9_6_24.R20220710.01_12)。

PostgreSQL 10 以上版本

如要安裝 PostgreSQL 10 以上版本的 amcheck,請執行下列陳述式:

  CREATE EXTENSION amcheck;
  

檢查索引是否不一致

以下各節說明如何檢查索引不一致的情形,包括檢查索引的不一致性,以及違反唯一鍵和主鍵的情形。

檢查不一致之處

在每個資料庫中,執行下列陳述式來檢查不一致情形:

程式碼範例

  DO $$
  DECLARE
    r RECORD;
    version varchar(100);
  BEGIN
    RAISE NOTICE 'Started relhasindex validation on database: %', current_database();
    FOR r IN
      SELECT indexrelid::regclass relname
        FROM pg_index
      WHERE indrelid NOT IN (SELECT oid FROM pg_class WHERE relhasindex) LOOP
        RAISE LOG 'Failed to check index %: %', r.relname, 'relhasindex is false, want true';
        RAISE WARNING 'Failed to check index %: %', r.relname, 'relhasindex is false, want true';
    END LOOP;
    RAISE NOTICE 'Finished relhasindex validation on database: %', current_database();

    RAISE NOTICE 'Started b-tree 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 b-tree amcheck on database: %', current_database();
  END $$;
  

您會看到類似下方的輸出內容:

輸出

  WARNING:  Failed to check index t_i_key: relhasindex is false, want true
  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 會顯示索引、資料欄和表格,如果輸出內容包含顯示 DUPLICATEBACKWARDS 的資料列,表示索引已損毀,可能需要修正。資料列中的 BACKWARDS 表示可能隱藏重複值。如果表格中出現這兩項其中之一,請參閱「修正重複鍵違規問題」。

修正重複鍵違規問題

如果發現重複的專屬索引,或重新建立索引作業因重複鍵違規錯誤而失敗,請完成下列步驟,找出並移除重複的鍵。

  1. NOTICE 表格標題中擷取 key_columns,如上述範例輸出內容所示。在以下範例中,鍵列為 email

    程式碼範例

      NOTICE:  checking index=users_email_key on table=users key_columns="(email)"

    在步驟 3 的查詢中,使用 KEY_COLUMNS 中的這些值。

  2. 找出資料表的結構定義。使用 psql 連線至資料庫,然後執行下列指令:

    程式碼範例

    \dt TABLE_NAME
    schema」欄中的值是您在步驟 3 的查詢中,為「SCHEMA_NAME」使用的值。

    舉例來說,如果查詢如下:

     \dt games
     

    輸出結果會與下列內容相似:

     List of relations
     Schema  | Name  | Type  | Owner
     --------+-------+-------+----------
     public  | games | table | postgres
     (1 row)
     

  3. 執行下列陳述式,強制進行完整資料表掃描,並取得重複的鍵。

    程式碼範例

    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 是您要檢查的資料表中,由不重複索引或主鍵涵蓋的一或多個資料欄。您在檢查違反唯一鍵限制的項目時,會發現這些項目。這項陳述式會傳回重複的鍵,以及每個鍵的重複次數。

    舉例來說,如果查詢如下:

      SELECT name,count(*)
        FROM public.TEST_NAMES
      GROUP BY name
      HAVING count(*) > 1;
      

    輸出結果會與下列內容相似:

      name                | count
      --------------------+-------
      Johnny              |     2
      Peter               |     2
      (2 rows)
      

    在這種情況下,請繼續下一個步驟,移除重複的鍵。

    如果 KEY_COLUMNS 中的任何資料欄為空值,您可以忽略這些資料欄,因為唯一限制不適用於空值資料欄。

    如果沒有重複的鍵,請前往「修正不一致的索引」。

  4. 建議您執行以下非強制步驟:備份含有重複鍵的記錄。執行下列陳述式來建立備份記錄:

    程式碼範例

      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)) 參數替換為步驟 2 中的 SELECT 陳述式 (不含 count 參數),例如:

    程式碼範例

      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);
      
  5. 為使用者新增複寫角色,停用觸發程序:

    程式碼範例

      ALTER USER CURRENT_USER with REPLICATION;
      SET session_replication_role = replica;
      
  6. 執行下列陳述式,刪除重複的鍵:

    程式碼範例

      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;
      
    其中 dayrnumKEY_COLUMNS

    執行這項陳述式後,系統會保留一列,並刪除每組重複資料列中的其他資料列。如要控管要刪除的資料列版本,請在刪除陳述式中執行下列篩選器:

    程式碼範例

      DELETE FROM  SCHEMA_NAME.TABLE_NAME
      WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);
      
  7. 請完成下列步驟,確認 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 交易:

      程式碼範例

        END;
        
    3. 如有錯誤,請還原變更來修正錯誤:

      程式碼範例

        ROLLBACK;
        
  8. 刪除重複的鍵後,您可以重新為索引建立索引。

修正不一致的索引

下列各節說明如何修正執行個體中發現的索引不一致問題。

視資料庫的設定而定,您可能需要針對上一個步驟中識別的每個索引執行下列操作:

  1. 準備重新建立索引

  2. 重新建立索引

  3. 如果重新建立索引作業因外鍵違規而失敗,請務必找出並修正這些違規事項

  4. 再次執行重新建立索引作業。

準備重新建立索引

找出索引大小

建立大型資料庫的索引所需時間,會比小型資料庫更長。如要加快大型資料庫的索引和重新建立索引作業,可以為這些作業分配更多記憶體和 CPU 效能。這是規劃重新建立索引作業的重要步驟。瞭解索引大小後,您可以設定重新建立索引作業使用的記憶體大小,以及設定平行工作站數量

執行下列陳述式,找出要修正的索引大小 (以 KB 為單位):

程式碼範例

  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% 以上。

設定平行工作站數量

如要增加重新建立索引的平行工作站數量,請在資料庫中設定 max_parallel_maintenance_workers 參數 (使用 PostgreSQL 11 以上版本)。這個參數的預設值為 2,但可以設為較高的值,增加重新建立索引的工作站數量。對於具備 8 顆以上 vCPU 核心的執行個體,建議將 max_parallel_maintenance_workers 旗標值設為 4。

詳情請參閱「設定資料庫標記」。

重新建立索引

您可以使用 pg_repack 公用程式重新建立索引,不必封鎖實際運作的工作負載。這項公用程式可自動執行並簡化並行重新建立索引程序,讓您重新建立索引時不必停機,特別是對於沒有 REINDEX CONCURRENTLY 作業的 PostgreSQL 11 和更早版本。請使用 pg_repack 1.4.7 版執行此程序。

如要使用 pg_repack 重新建立索引,請完成下列步驟:

  1. pg_repack 頁面下載、編譯及安裝 pg_repack 公用程式。

    Debian GNU/Linux 11

    為方便起見,建議 Debian Linux 使用者下載並安裝這個適用於 Linux x86_64 平台的預先建構可執行二進位檔

    二進位檔的 sha256 總和檢查碼雜湊如下:

    ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

    如要確認 Linux 版本為 Debian GNU/Linux 11,請執行 hostnamectl 指令。

    自行編譯

    pg_repack頁面下載、編譯及安裝 pg_repack 公用程式。

  2. 建立 pg_repack 擴充功能:

    程式碼範例

      CREATE EXTENSION pg_repack;
      
  3. 執行下列指令,同時重新建立索引:

    程式碼範例

      pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
      

    這個指令會輸出類似以下的結果:

    輸出

      INFO: repacking index "public.t_i_key"
      

    如果執行 pg_repack 時發生任何錯誤,請修正錯誤並重試。修正所有不重複鍵索引和主鍵索引後,請檢查是否有違反外部鍵的行為,並修正所有發現的違規事項。

找出並修正外鍵違規

如要瞭解如何找出並修正外鍵違規事項,請參閱「找出並修正外鍵違規事項」。

監控重新建立索引作業

有時,重新建立索引作業可能會遭到其他工作階段封鎖。建議您每 4 小時檢查一次。如果重新建立索引作業遭到封鎖,您可以取消封鎖工作階段,讓重新建立索引作業完成。

請完成下列步驟,找出封鎖和等待中的工作階段,然後在 INDEX 作業中取消這些工作階段:

  1. 如要找出造成阻礙的工作階段,請執行下列查詢:

    程式碼範例

      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;
      
  2. 如要取消工作階段,請使用先前查詢中的封鎖工作階段 PID 執行下列查詢:

    程式碼範例

      SELECT pg_cancel_backend(PID);
      

確認索引一致

您必須繼續檢查每個不一致的索引,確認索引不一致。修正所有執行個體的不一致索引和鍵違規事項後,請按照先前章節的步驟檢查是否仍有問題: