Find and fix inconsistent indexes

Inconsistencies in database indexes can occur for a variety of reasons including software defects, hardware issues, or underlying changes in behavior such as sort order changes.

The PostgreSQL community has built tools to identify and remediate such issues. This includes tools like amcheck, which is recommended by the PostgreSQL community to identify consistency issues, including issues that earlier versions of PostgreSQL 14 exhibited.

This playbook is a reference for Cloud SQL users who experience these consistency issues. The playbook provides information that helps PostgreSQL users identify and remediate inconsistent indexes.

Resolving an index's inconsistencies involves the following steps:

1. Before you begin.

   Before you begin reindexing, you should back up your database, set the correct permissions, verify your psql client version, and download the amcheck extension.

2. Check for inconsistent indexes.

   One statement identifies unique and primary key violations, and another statement detects a variety of other inconsistencies.

3. Fix the index's inconsistencies.

   Reindexing an index fixes all its inconsistencies. You may need to adjust your instance's memory settings to improve performance.

4. Monitor reindexing operations.

   We recommend that you monitor the progress of the reindexing operation to ensure that the operation is progressing and is not blocked.

5. Verify that the indexes are consistent.

   After you have successfully reindexed your index, we recommend that you verify that your index does not contain any inconsistencies.

Before you begin

Backup your database

To ensure that no data is lost during reindexing, we recommend that you back up your database. For more information, see Create an on-demand backup.

Set the cloudsqlsuperuser permission

To complete the steps on this page, you must have cloudsqlsuperuser permissions. For more information, see session_replication_role.

Ensure that the psql client version is 9.6 or higher

To complete the steps on this page, you must ensure that your psql client version is 9.6 or higher. Run the command psql --version to verify your current psql client version.

Install the amcheck extension

To check for index inconsistencies, you must enable the amcheck extension.

CREATEEXTENSIONamcheck_next;

CREATEEXTENSIONamcheck;

Check for inconsistent indexes

The following sections describe how to check for inconsistent indexes by checking for an index's inconsistencies as well as unique and primary key violations.

Check for inconsistencies

In each database, run the following statement to check for inconsistencies:

DO$$
DECLARE
rRECORD;
versionvarchar(100);
BEGIN
RAISENOTICE'Started relhasindex validation on database: %',current_database();
FORrIN
SELECTindexrelid::regclassrelname
FROMpg_index
WHEREindrelidNOTIN(SELECToidFROMpg_classWHERErelhasindex)LOOP
RAISELOG'Failed to check index %: %',r.relname,'relhasindex is false, want true';
RAISEWARNING'Failed to check index %: %',r.relname,'relhasindex is false, want true';
ENDLOOP;
RAISENOTICE'Finished relhasindex validation on database: %',current_database();
RAISENOTICE'Started b-tree amcheck on database: %',current_database();
SHOWserver_versionintoversion;
SELECTsplit_part(version,'.',1)intoversion;
FORrIN
SELECTc.oid,c.oid::regclassrelname,i.indisunique
FROMpg_indexi
JOINpg_opclassopONi.indclass[0]=op.oid
JOINpg_amamONop.opcmethod=am.oid
JOINpg_classcONi.indexrelid=c.oid
JOINpg_namespacenONc.relnamespace=n.oid
WHEREam.amname='btree'
ANDc.relpersistence!='t'
ANDc.relkind='i'
ANDi.indisreadyANDi.indisvalidLOOP
BEGIN
RAISENOTICE'Checking index %:',r.relname;
IFversion='10'THEN
PERFORMbt_index_check(index=>r.oid);
ELSE
PERFORMbt_index_check(index=>r.oid,heapallindexed=>r.indisunique);
ENDIF;
EXCEPTION
WHENundefined_functionTHEN
RAISEEXCEPTION'Failed to find the amcheck extension';
WHENOTHERSTHEN
RAISELOG'Failed to check index %: %',r.relname,sqlerrm;
RAISEWARNING'Failed to check index %: %',r.relname,sqlerrm;
END;
ENDLOOP;
RAISENOTICE'Finished b-tree amcheck on database: %',current_database();
END$$;

You should receive output similar to the following:

WARNING:Failedtocheckindext_i_key:relhasindexisfalse,wanttrue
NOTICE:Checkingindext_pkey:
NOTICE:Checkingindext_i_key:
WARNING:Failedtocheckindext_i_key:itemorderinvariantviolatedforindex"t_i_key"
NOTICE:Checkingindext_j_key:
WARNING:Failedtocheckindext_j_key:itemorderinvariantviolatedforindex"t_j_key"
NOTICE:Checkingindexij:
WARNING:Failedtocheckindexij:itemorderinvariantviolatedforindex"ij"

For more information about viewing PostgreSQL logs, see View instance logs.

Identify and fix unique and primary key violations

This section describes how to check your index for unique and primary key violations, and if some exist, how you fix them.

Identify unique key violations

Unique key violations must be fixed before you reindex an index. To check for all unique key violations, run the following command in each database:

WITHqAS(
/* this gets info for all UNIQUE indexes */
SELECTindexrelid::regclassasidxname,
indrelid::regclassastblname,
indcollation,
pg_get_indexdef(indexrelid),
format('(%s)',(selectstring_agg(quote_ident(attname),', ')
frompg_attributea
joinunnest(indkey)ia(nr)onia.nr=a.attnum
whereattrelid=indrelid))asidxfields,
COALESCE(substring(pg_get_indexdef(indexrelid)FROM'[)] (WHERE .*)$'),'')aswhereclause
FROMpg_index
WHEREindisunique
/* next line excludes indexes not affected by collation changes */
ANDtrim(replace(indcollation::text,'0',''))!=''
)
SELECT
/* the format constructs the query to execute for each index */
format(
$sql$
DO$$BEGINRAISENOTICE'checking index=%3$I on table=%1$I key_columns=%2$I ';END;$$;
SELECTthis,
prev,
/* we detect both reversed ordering or just not unique */
(CASEWHENthis=prevTHEN'DUPLICATE'ELSE'BACKWARDS'END)asviolation_type
FROM(SELECT%2$sASthis,
lag(%2$s)OVER(ORDERBY%2$s)ASprev
FROM%1$s%4$s
)s
WHEREthis<=prevandthisISNOTNULLandprevISNOTNULL;/* change to just '<' if looking for reverse order in index */
$sql$,tblname,idxfields,idxname,whereclause
)
FROMq
-- LIMIT 20 /* may use limit for testing */
-- the next line tells psql to executes this query and then execute each returned line separately
\gexec

The output of the script is similar to the following:

NOTICE:checkingindex=users_email_keyontable=userskey_columns="(email)"
NOTICE:checkingindex=games_title_keyontable=gameskey_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

In this output, the table header NOTICE shows the index, column, and table for the values displayed below it. If your output contains rows displaying DUPLICATE or BACKWARDS, then this shows corruption in the index and may need to be fixed. Rows with BACKWARDS indicate possible duplicate values that might be hidden. If you see either of these entries in the table, see Fix duplicate key violations.

Fix duplicate key violations

If you have identified a duplicate unique index or if a reindex operation fails due to a duplicate key violation error, complete the following steps to find and remove the duplicate key(s).

1. Extract the key_columns from the NOTICE table header, as shown in the preceding sample output. In the following example, the key column is email.

   Code Sample

   NOTICE:checkingindex=users_email_keyontable=userskey_columns="(email)"

   Use these values in KEY_COLUMNS in the query in step 3.

2. Find the schema for your table. Use psql to connect to your database and run the following command:

   Code Sample

   \dtTABLE_NAME

   The value in the schema column is the value you use for SCHEMA_NAME in the query in step 3.

   For example, for the following query:

   \dtgames
   

   The output is similar to the following:

   Listofrelations
   Schema|Name|Type|Owner
   --------+-------+-------+----------
   public|games|table|postgres
   (1row)
   

3. Run the following statements to force a full table scan and get duplicate keys.

   Code Sample

   SETenable_indexscan=off;
   SETenable_bitmapscan=off;
   SETenable_indexonlyscan=off;
   SELECTKEY_COLUMNS,count(*)
   FROMSCHEMA_NAME.TABLE_NAME
   GROUPBYKEY_COLUMNS
   HAVINGcount(*)>1;

   In the above statement, KEY_COLUMNS are one or more columns covered by the unique index or primary key in the table you are checking. These were identified when you checked for unique key violations. The statement returns the duplicate keys and a count of the duplicates for each.

   For example, for the following query:

   SELECTname,count(*)
   FROMpublic.TEST_NAMES
   GROUPBYname
   HAVINGcount(*) > 1;
   

   The output is similar to the following:

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

   In this case, continue to the next step to remove the duplicate keys.

   If any of the columns in KEY_COLUMNS are null, you can ignore them because unique constraints do not apply for NULL columns.

   If no duplicate keys are found, you can move to Fix inconsistent indexes.

4. Optional but recommended: Create a backup for the records containing duplicate keys. Run the following statement to create backup records:

   Code Sample

   CREATETABLESCHEMA_NAME.TABLE_NAME_bak
   ASSELECT*FROMSCHEMA_NAME.TABLE_NAME
   WHERE(KEY_COLUMNS)
   IN((KEY_VALUES));
   

   In this statement, KEY_VALUES is a list of values copied from the result of the previous step. For example:

   Code Sample

   CREATETABLEpublic.TEST_NAMES_bak
   ASSELECT*FROMpublic.TEST_NAMES
   WHERE(name)IN(('Johnny'),('Peter'))
   

   For a large number of rows, it is easier to replace the ((KEY_VALUES)) parameter in the IN statement with the SELECT statement from step 2 without the count parameter. For example:

   Code Sample

   CREATETABLESCHEMA_NAME.TABLE_NAME_bak
   ASSELECT*FROMSCHEMA_NAME.TABLE_NAME
   WHERE(KEY_COLUMNS)
   IN(SELECT(KEY_COLUMNS)
   FROMSCHEMA_NAME.TABLE_NAME
   GROUPBY(KEY_COLUMNS)
   HAVINGcount(*)>1);
   

5. Add a replication role to the user to disable triggers:

   Code Sample

   ALTERUSERCURRENT_USERwithREPLICATION;
   SETsession_replication_role=replica;
   

6. Run the following statement to delete the duplicate keys:

   Code Sample

   BEGIN;
   DELETEFROMSCHEMA_NAME.TABLE_NAMEa
   USING(
   SELECTmin(ctid)ASctid,
   KEY_COLUMNS
   FROMSCHEMA_NAME.TABLE_NAME
   GROUPBYKEY_COLUMNS
   HAVINGcount(*)>1)b
   WHEREa.KEY_COLUMNS=b.KEY_COLUMNS
   ANDa.ctidb.ctid;
   

   For example, for multi-column KEY_COLUMNS:

   Code Sample

   DELETEFROMpublic.test_randoma
   USING(
   SELECTmin(ctid)ASctid,
   day,rnum
   FROMpublic.test_random
   GROUPBYday,rnum
   HAVINGcount(*) > 1)b
   WHEREa.day=b.dayanda.rnum=b.rnum
   ANDa.ctid <> b.ctid;
   

   Where day and rnum are KEY_COLUMNS.

   Running this statement keeps one row and deletes others for each set of duplicate rows. If you want to control which version of the row gets deleted, run the following filter in the delete statement:

   Code Sample

   DELETEFROMSCHEMA_NAME.TABLE_NAME
   WHERE(KEY_COLUMNS,ctid)=(KEY_VALUES,CTID_VALUE);
   

7. Complete the following steps to check that the DELETE command returned the expected number of rows without any errors:

   1. Run the following statement to identify the rows in which tables were changed:

      Code Sample

      SELECTschemaname,relname,n_tup_del,n_tup_upd
      FROMpg_stat_xact_all_tables
      WHEREn_tup_del+n_tup_upd>0;
      

   2. If all the rows are correct, commit the DELETE transaction:

      Code Sample

      END;
      

   3. If there are errors, roll back the changes to fix the errors:

      Code Sample

      ROLLBACK;
      

8. After the duplicate keys are deleted, you can reindex your index.

SELECTi.relnameASindex_name,
pg_size_pretty(pg_relation_size(x.indexrelid))ASindex_size
FROMpg_indexx
JOINpg_classi
ONi.oid=x.indexrelid
WHEREi.relname='INDEX_NAME';

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