Inconsistencies in database indexes can occur for a variety of reasons includingsoftware defects, hardware issues, or underlying changes in behavior such assort order changes.

The PostgreSQL community has built tools to identify and remediate such issues.This includes tools likeamcheck,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 theseconsistency issues. The playbook provides information that helps PostgreSQLusers 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 thecorrect permissions, verify yourpsql client version, and download theamcheck extension.

2. Check for inconsistent indexes.

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

3. Fix the index's inconsistencies.

   Reindexing an index fixes all its inconsistencies. You mayneed 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 toensure 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 youverify 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 upyour database. For more information, seeCreate an on-demand backup.

To complete the steps on this page, you must havecloudsqlsuperuserpermissions. For more information, seesession_replication_role.

Ensure that thepsql client version is 9.6 or higher

To complete the steps on this page, you must ensure that yourpsql clientversion is 9.6 or higher. Run the commandpsql --version to verify yourcurrentpsql client version.

Install the amcheck extension

To check for index inconsistencies, you must enable theamcheckextension.

CREATEEXTENSIONamcheck_next;

CREATEEXTENSIONamcheck;

Check for inconsistent indexes

The following sections describe how to check for inconsistent indexes bychecking 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$$DECLARErRECORD;versionvarchar(100);BEGINRAISENOTICE'Started relhasindex validation on database: %',current_database();FORrINSELECTindexrelid::regclassrelnameFROMpg_indexWHEREindrelidNOTIN(SELECToidFROMpg_classWHERErelhasindex)LOOPRAISELOG'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;FORrINSELECTc.oid,c.oid::regclassrelname,i.indisuniqueFROMpg_indexiJOINpg_opclassopONi.indclass[0]=op.oidJOINpg_amamONop.opcmethod=am.oidJOINpg_classcONi.indexrelid=c.oidJOINpg_namespacenONc.relnamespace=n.oidWHEREam.amname='btree'ANDc.relpersistence!='t'ANDc.relkind='i'ANDi.indisreadyANDi.indisvalidLOOPBEGINRAISENOTICE'Checking index %:',r.relname;IFversion='10'THENPERFORMbt_index_check(index=>r.oid);ELSEPERFORMbt_index_check(index=>r.oid,heapallindexed=>r.indisunique);ENDIF;EXCEPTIONWHENundefined_functionTHENRAISEEXCEPTION'Failed to find the amcheck extension';WHENOTHERSTHENRAISELOG'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,wanttrueNOTICE: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, seeView instance logs.

Identify and fix unique and primary key violations

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

Identify unique key violations

Unique key violations must befixed before you reindex an index. To check forall 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_attributeajoinunnest(indkey)ia(nr)onia.nr=a.attnumwhereattrelid=indrelid))asidxfields,COALESCE(substring(pg_get_indexdef(indexrelid)FROM'[)] (WHERE .*)$'),'')aswhereclauseFROMpg_indexWHEREindisunique/* 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_typeFROM(SELECT%2$sASthis,lag(%2$s)OVER(ORDERBY%2$s)ASprevFROM%1$s%4$s)sWHEREthis<=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 | DUPLICATEGame#18 $soccer 2  | Game #18 $soccer 2 | DUPLICATEGame#2 $soccer 2   | Game #2 $soccer 2  | DUPLICATEGame#5 $soccer 2   | Game #5 $soccer 2  | DUPLICATE

In this output, the table headerNOTICE shows the index, column, and tablefor the values displayed below it. If your output contains rows displayingDUPLICATE orBACKWARDS, then this shows corruption in the index and may needto be fixed. Rows withBACKWARDS indicate possible duplicate values thatmight be hidden. If you see either of these entries in the table, seeFix duplicate key violations.

Fix duplicate key violations

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

1. Extract thekey_columns from theNOTICE table header, as shown inthe preceding sample output. In the following example, the key column isemail.

   Code Sample

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

   Use these values inKEY_COLUMNS in the query in step 3.

2. Find the schema for your table. Usepsql to connect to yourdatabase and run the following command:

   Code Sample

   \dtTABLE_NAME

   The value in theschema column is the value you use forSCHEMA_NAME in the query in step 3.

   For example, for the following query:

   \dtgames

   The output is similar to the following:

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

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

   Code Sample

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

   Note: If your database is large, this statement may take a long time tocomplete.

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

   For example, for the following query:

   SELECTname,count(*)FROMpublic.TEST_NAMESGROUPBYnameHAVINGcount(*) >1;

   The output is similar to the following:

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

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

   If any of the columns inKEY_COLUMNS are null, you can ignorethem because unique constraints do not apply for NULL columns.

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

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

   Code Sample

   CREATETABLESCHEMA_NAME.TABLE_NAME_bakASSELECT*FROMSCHEMA_NAME.TABLE_NAMEWHERE(KEY_COLUMNS)IN((KEY_VALUES));

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

   Code Sample

   CREATETABLEpublic.TEST_NAMES_bakASSELECT*FROMpublic.TEST_NAMESWHERE(name)IN(('Johnny'),('Peter'))

   For a large number of rows, it is easier to replace the((KEY_VALUES)) parameter in theIN statement with theSELECTstatement from step 2 without thecount parameter. Forexample:

   Code Sample

   CREATETABLESCHEMA_NAME.TABLE_NAME_bakASSELECT*FROMSCHEMA_NAME.TABLE_NAMEWHERE(KEY_COLUMNS)IN(SELECT(KEY_COLUMNS)FROMSCHEMA_NAME.TABLE_NAMEGROUPBY(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_NAMEaUSING(SELECTmin(ctid)ASctid,KEY_COLUMNSFROMSCHEMA_NAME.TABLE_NAMEGROUPBYKEY_COLUMNSHAVINGcount(*)>1)bWHEREa.KEY_COLUMNS=b.KEY_COLUMNSANDa.ctid<>b.ctid;

   For example, for multi-columnKEY_COLUMNS:

   Code Sample

   DELETEFROMpublic.test_randomaUSING(SELECTmin(ctid)ASctid,day,rnumFROMpublic.test_randomGROUPBYday,rnumHAVINGcount(*) >1)bWHEREa.day=b.dayanda.rnum=b.rnumANDa.ctid <>b.ctid;

   Whereday andrnum areKEY_COLUMNS.

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

   Code Sample

   DELETEFROM  SCHEMA_NAME.TABLE_NAMEWHERE(KEY_COLUMNS,ctid)=(KEY_VALUES,CTID_VALUE);

   Warning: Committing this transaction mutates your database and removesduplicate keys. Make sure that you don't delete any data that's stillneeded. Restoring from a backup might restore the inconsistent indexes.

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

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

      Code Sample

      SELECTschemaname,relname,n_tup_del,n_tup_updFROMpg_stat_xact_all_tablesWHEREn_tup_del+n_tup_upd>0;

   2. If all the rows are correct, commit theDELETE 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.

Fix inconsistent indexes

The following sections describe how you can fix the index inconsistenciesfound in your instance.

1. Prepare to reindex your index.

2. Reindex your index.

3. If the reindex operation fails due to foreign key violations, you mustfind and fix these violations.

4. Run the reindex operation again.

Prepare to reindex your index

Find the index size

Indexing larger databases requires more time than smaller databases. To improvethe speed of index and reindex operations of larger databases, you can allocatemore memory and CPU power to these operations. This is an important step inplanning your reindex operation. After you know the index size, you canset the memory size used by the reindex operation andset the number of parallel workers.

Run the following statement to find the index size, in kilobytes, of the indexthat you want to fix:

SELECTi.relnameASindex_name,pg_size_pretty(pg_relation_size(x.indexrelid))ASindex_sizeFROMpg_indexxJOINpg_classiONi.oid=x.indexrelidWHEREi.relname='INDEX_NAME';

The output of this statement is similar to the following:

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

Set memory size to use for reindexing

Based on the size of your index as determined in the previous section, it isimportant to set the appropriate value formaintenance_work_mem. This parameterspecifies the amount of memory to use for the reindexing operation. Forexample, ifyour index size is greater than 15 GB, we recommend that you adjust yourmaintenance memory. For moreinformation, seeSet a database flag.

You can increase the number of parallel workers for reindexing by setting themax_parallel_maintenance_workersparameter in databases using PostgreSQL 11 or higher. The default value of thisparameter is 2 but can be set to a higher value to increase the number ofworkers for reindexing. For instances with 8 or more vCPU cores, we recommendsetting themax_parallel_maintenance_workers flag value to 4.

For more information, seeSet a database flag.

Reindex your index

You can reindex an index without blocking your production workload using thepg_repack utility. This utility automates and simplifies the concurrentreindex process, enabling you to reindex without downtime, especially forPostgreSQL versions 11 and earlier, which do not have theREINDEX CONCURRENTLY operation. For this procedure, usepg_repackversion 1.4.7.

Complete the following steps to reindex your index usingpg_repack:

1. Download, compile, and install thepg_repack utility from thepg_repack page.

   Debian GNU/Linux 11

   For convenience, we recommend that Debian Linux users download and install thispre-built executable binary for the Linux x86_64 platform.

   The sha256 checksum hash of the binary is the following:

   ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

   To verify that your Linux version is Debian GNU/Linux 11, run the commandhostnamectl.

   Self compile

   Note: For this procedure, you should run the command in the terminal on either Linux or on Unix with a working GNU toolchain.

   Download, compile, and install thepg_repack utility from thepg_repack page.

2. Create thepg_repack extension:

   Code Sample

   CREATEEXTENSIONpg_repack;

3. Run the following command to reindex your index concurrently:

   Code Sample

   pg_repack-hHOSTIP-p5432-UUSERNAME-d"DATABASE_NAME"-i"INDEX_NAME"--no-superuser-check--no-kill-backend--wait-timeout=3600

   Note:pg_repack does not allow you to reindex an index on amaterialized view.

   This command has output similar to the following:

   Output

   INFO:repackingindex"public.t_i_key"

   If any errors occurred when runningpg_repack, you can correct the errorand try again. After you have fixed all of your unique key indexes andprimary key indexes, you shouldcheck for foreign key violationsand fix any that are found.

Find and fix foreign key violations

For information about how to find and fix foreign key violations, seeFind and fix foreign key violations.

Monitor reindexing operations

Occasionally, the reindex operation may be blocked by other sessions. Werecommended that you check this every 4 hours. If the reindex operation isblocked, you can cancel the blocking session so the reindex operation cancomplete.

Complete the following steps to identify blocking and waiting sessions and thencancel them in the INDEX operation:

1. To identify blocking sessions, run the following query:

   Code Sample

   SELECTpid,usename,pg_blocking_pids(pid)ASblocked_by,queryASblocked_queryFROMpg_stat_activityWHEREcardinality(pg_blocking_pids(pid))>0;

2. To cancel a session, run the following query using the PID ofthe blocking session from the previous query:

   Code Sample

   SELECTpg_cancel_backend(PID);

Verify that your indexes are consistent

You must continue to check for index inconsistencies for each inconsistentindex. After you have fixed all your instance's inconsistent indexes and keyviolations, you can check that no issues exist by following the steps in theprevious sections:

• Check for an index's inconsistencies

• Check for unique key violations