資料庫索引不一致的原因有很多，包括軟體缺陷、硬體問題，或行為的基礎變更 (例如排列順序變更)。
PostgreSQL 社群已建構工具，可找出並修正這類問題。這包括 PostgreSQL 社群建議使用的 amcheck 等工具，可用於找出一致性問題，包括舊版 PostgreSQL 14 出現的問題。
本教戰手冊可供遇到這類一致性問題的 AlloyDB for PostgreSQL 使用者參考。這份教戰手冊提供的資訊，可協助 PostgreSQL 使用者找出並修正不一致的索引。
解決索引不一致問題的步驟如下：
事前準備。
開始重新建立索引前，請先備份資料庫、設定正確權限、驗證
psql用戶端版本，並啟用amcheck擴充功能。-
其中一個陳述式會找出不重複和主鍵違規，另一個陳述式則會偵測各種其他不一致之處。
-
重新建立索引可修正所有不一致的問題。您可能需要調整執行個體的記憶體設定，才能提升效能。
-
建議您監控重新索引作業的進度，確保作業順利進行，未遭到封鎖。
-
成功重新建立索引後，建議您確認索引不含任何不一致之處。
事前準備
備份 AlloyDB 叢集的資料
為確保重新建立索引期間不會遺失任何資料，建議您備份叢集資料。詳情請參閱「建立隨選備份」。
設定 alloydbsuperuser 權限
您必須具備 alloydbsuperuser 權限，才能完成本頁步驟。詳情請參閱「AlloyDB 預先定義的 PostgreSQL 角色」。
確認 psql 用戶端版本為 9.6 以上
如要完成本頁步驟，請務必確認 psql 用戶端版本為 9.6 以上。執行 psql --version 指令，確認目前的 psql 用戶端版本。
啟用 amcheck 擴充功能
如要檢查索引不一致的情況，請啟用 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; 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 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"
如要進一步瞭解如何查看記錄，請參閱「使用 Logs Explorer 查看記錄」。
找出並修正不重複和主鍵違規
本節說明如何檢查索引是否有不重複和主鍵違規，以及如何修正這些問題。
找出不重複鍵違規
您必須先修正不重複鍵違規問題，才能重新建立索引。如要檢查所有不重複鍵違規，請在每個資料庫中執行下列指令：
程式碼範例
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
指令碼的輸出內容大致如下：
輸出內容
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
\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 是您要檢查的資料表中，不重複索引或主鍵涵蓋的一或多個資料欄。這些是您在檢查不重複鍵違規時發現的。陳述式會傳回重複的鍵，以及每個鍵的重複次數。
舉例來說，以下列查詢為例：
SELECT name,count(*) FROM public.TEST_NAMES GROUP BY name HAVING count(*) > 1;
輸出結果會與下列內容相似：
name | count --------------------+------- Johnny | 2 Peter | 2 (2 rows)
在這種情況下，請繼續執行下一個步驟，移除重複的金鑰。
如果 KEY_COLUMNS 中的任何資料欄為空值，您可以忽略這些資料欄，因為不重複限制不適用於空值資料欄。
如果找不到重複的金鑰，請前往「修正不一致的索引」。
選用，但建議執行：為含有重複鍵的記錄建立備份。執行下列陳述式來建立備份記錄：
在這個陳述式中，KEY_VALUES 是從上一個步驟結果複製的值清單。例如：程式碼範例
CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak AS SELECT * FROM SCHEMA_NAME.TABLE_NAME WHERE (KEY_COLUMNS) IN (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);
為使用者新增複製角色，以停用觸發條件：
程式碼範例
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;
執行這個陳述式後，系統會保留一個資料列，並刪除每組重複資料列中的其他資料列。如要控制要刪除的資料列版本，請在 delete 陳述式中執行下列篩選器：
程式碼範例
DELETE FROM SCHEMA_NAME.TABLE_NAME WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);
請完成下列步驟，確認
DELETE指令傳回預期的資料列數量，且未發生任何錯誤：執行下列陳述式，找出資料表變更的資料列：
程式碼範例
SELECT schemaname, relname, n_tup_del, n_tup_upd FROM pg_stat_xact_all_tables WHERE n_tup_del+n_tup_upd > 0;
如果所有資料列都正確無誤，請提交
DELETE交易：程式碼範例
END;
如有錯誤，請復原變更來修正錯誤：
程式碼範例
ROLLBACK;
刪除重複的鍵後，即可重新建立索引。
修正不一致的索引
以下各節說明如何修正執行個體中的索引不一致問題。
視資料庫設定而定，您可能需要針對上一個步驟中找到的每個索引，執行下列操作：
如果重新建立索引作業因外鍵違規而失敗，您必須找出並修正這些違規事項。
再次執行重新索引作業。
準備重新建立索引
找出索引大小
為大型資料庫建立索引所需的時間，會比小型資料庫更長。如要提高大型資料庫的索引和重新索引作業速度，可以為這些作業分配更多記憶體和 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，建議您調整維護記憶體。
以下範例說明如何設定 maintenance_work_mem：
程式碼範例
SET maintenance_work_mem TO '1GB';
為大型資料庫建立索引所需的時間，會比小型資料庫更長。如要提高索引和重新索引作業的速度，建議在重新索引作業期間，將 maintenance_work_mem 設為至少 2% 的執行個體記憶體 (適用於記憶體為 4 GB 以上的執行個體)。
設定平行 worker 數量
您可以在資料庫中設定 max_parallel_maintenance_workers 設定參數，增加重新建立索引的平行 worker 數量。這個參數的預設值為 2，但可以設為較高的值，增加重新建立索引的工作站數量。如果執行個體有 8 個以上的 vCPU 核心，建議將 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 限制。如需更多平行 worker，請提高 max_worker_processes 和 max_parallel_workers 的值。
以下範例說明如何設定 max_parallel_maintenance_workers：
程式碼範例
SET max_parallel_maintenance_workers TO 4;
max_parallel_maintenance_workers 參數不保證會分配 worker。如要確認重新建立索引作業已啟動多個平行 worker，請在啟動重新建立索引作業後，從另一個工作階段執行下列查詢：
程式碼範例
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.5.3 版。
請按照下列步驟，使用 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
這個指令會輸出類似以下的結果：
輸出內容
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);
確認索引一致
您必須繼續檢查每個不一致的索引，修正所有執行個體的不一致索引和鍵違規問題後，請按照前幾節的步驟操作，確認沒有任何問題：