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:

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.
Check for inconsistent indexes .

One statement identifies unique and primary key violations, and another statement detects a variety of other inconsistencies.
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.
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.
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.

PostgreSQL 9.6

Note: You must ensure that POSTGRES_9_6_24.R20220710.01_12 is installed before installing amcheck_next . For more information on installing a new target maintenance version, see Self Service Maintenance .

To install amcheck for PostgreSQL 9.6, run the following statement:

  
CREATE  
EXTENSION  
amcheck_next ;

If you get an error saying `Could not open extension control file...`, verify that you're running the correct target maintenance version (POSTGRES_9_6_24.R20220710.01_12).

PostgreSQL 10 and later

To install amcheck for PostgreSQL 10 and later, run the following statement:

  
CREATE  
EXTENSION  
amcheck ;

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:

Code Sample

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

You should receive output similar to the following:

Output

  
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"

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:

Code Sample

  
 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

The output of the script is similar to the following:

Output

  
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

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).

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 
 : 
  
 checking 
  
 index 
 = 
 users_email_key 
  
 on 
  
 table 
 = 
 users 
  
 key_columns 
 = 
 "(email)" 
```
Use these values in KEY_COLUMNS in the query in step 3.

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

Code Sample

 \ 
 dt 
  
  TABLE_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:

  
 \ 
 dt 
  
  games

The output is similar to the following:

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

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

Code Sample

 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 
 ;

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:

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

The output is similar to the following:

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

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 .

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

Code Sample

  
 CREATE 
  
 TABLE 
  
  SCHEMA_NAME 
 
 . 
  TABLE_NAME 
 
_bak  
 AS 
  
 SELECT 
  
 * 
  
 FROM 
  
  SCHEMA_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

  
 CREATE 
  
 TABLE 
  
 public 
 . 
 TEST_NAMES_bak 
  
 AS 
  
 SELECT 
  
 * 
  
 FROM 
  
 public 
 . 
 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

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

Add a replication role to the user to disable triggers:

Code Sample

  
 ALTER 
  
 USER 
  
  CURRENT_USER 
 
  
 with 
  
 REPLICATION 
 ; 
  
 SET 
  
 session_replication_role 
  
 = 
  
 replica 
 ;

Run the following statement to delete the duplicate keys:

Code Sample

  
 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 
 ;

For example, for multi-column KEY_COLUMNS :

Code Sample

  
 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 
 ;

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

  
 DELETE 
  
 FROM 
  SCHEMA_NAME 
 
 . 
  TABLE_NAME 
 
  
 WHERE 
  
 ( 
  
  KEY_COLUMNS 
 
 , 
  
 ctid 
 ) 
  
 = 
  
 ( 
  KEY_VALUES 
 
 , 
  
  CTID_VALUE 
 
 );

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
  SELECT schemaname , relname , n_tup_del , n_tup_upd FROM pg_stat_xact_all_tables WHERE n_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 ;
After the duplicate keys are deleted, you can reindex your index.

Fix inconsistent indexes

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

Depending on how your database is configured, you may need to do the following for each index identified in the previous steps:

Prepare to reindex your index .
Reindex your index .
If the reindex operation fails due to foreign key violations, you must find and fix these violations .
Run the reindex operation again.

Prepare to reindex your index

Find the index size

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

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

Code Sample

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

The output of this statement is similar to the following:

Output

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

Set memory size to use for reindexing

Based on the size of your index as determined in the previous section, it is important to set the appropriate value for maintenance_work_mem . This parameter specifies the amount of memory to use for the reindexing operation. For example, if your index size is greater than 15 GB, we recommend that you adjust your maintenance memory. For more information, see Set a database flag .

Indexing larger databases requires more time than smaller databases. To improve the speed of index and reindex operations, we recommend setting maintenance_work_mem to at least 2% of the instance memory for instances with 4GB or more memory during this reindexing operation.

Set the number of parallel workers

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

For more information, see Set a database flag .

Reindex your index

You can reindex an index without blocking your production workload using the pg_repack utility. This utility automates and simplifies the concurrent reindex process, enabling you to reindex without downtime, especially for PostgreSQL versions 11 and earlier, which do not have the REINDEX CONCURRENTLY operation. For this procedure, use pg_repack version 1.4.7.

Complete the following steps to reindex your index using pg_repack :

Download, compile, and install the pg_repack utility from the pg_repack page .

Debian GNU/Linux 11

For convenience, we recommend that Debian Linux users download and install this pre-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 command hostnamectl .

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 the pg_repack utility from the pg_repack page .

Create the pg_repack extension:

Code Sample

  
 CREATE 
  
 EXTENSION 
  
 pg_repack 
 ;

Run the following command to reindex your index concurrently:
Code Sample
```
  
pg_repack  
-h  
 HOSTIP 
  
-p  
 5432 
  
-U  
 USERNAME 
  
-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 a materialized view.

This command has output similar to the following:
Output
```
  
INFO:  
repacking  
index  
 "public.t_i_key" 
  
```
If any errors occurred when running pg_repack , you can correct the error and try again. After you have fixed all of your unique key indexes and primary key indexes, you should check for foreign key violations and fix any that are found.

Find and fix foreign key violations

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

Monitor reindexing operations

Occasionally, the reindex operation may be blocked by other sessions. We recommended that you check this every 4 hours. If the reindex operation is blocked, you can cancel the blocking session so the reindex operation can complete.

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

To identify blocking sessions, run the following query:

Code Sample

  
 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 
 ;

To cancel a session, run the following query using the PID of the blocking session from the previous query:
Code Sample
```
  
 SELECT 
  
 pg_cancel_backend 
 ( 
  PID 
 
 ); 
  
```

Verify that your indexes are consistent

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

Check for an index's inconsistencies
Check for unique key violations

Find and fix inconsistent indexes Stay organized with collections Save and categorize content based on your preferences.

Before you begin

Backup your database

Set the cloudsqlsuperuser permission

Ensure that the psql client version is 9.6 or higher

Install the amcheck extension

PostgreSQL 9.6

PostgreSQL 10 and later

Check for inconsistent indexes

Check for inconsistencies

Code Sample

Output

Identify and fix unique and primary key violations

Identify unique key violations

Code Sample

Output

Fix duplicate key violations

Code Sample

Code Sample

Code Sample

Code Sample

Code Sample

Code Sample

Code Sample

Code Sample

Code Sample

Code Sample

Code Sample

Code Sample

Code Sample

Fix inconsistent indexes

Prepare to reindex your index

Find the index size

Code Sample

Output

Set memory size to use for reindexing

Set the number of parallel workers

Reindex your index

Debian GNU/Linux 11

Self compile

Code Sample

Code Sample

Output

Find and fix foreign key violations

Monitor reindexing operations

Code Sample

Code Sample

Verify that your indexes are consistent

Find and fix inconsistent indexes

Set the `cloudsqlsuperuser` permission

Ensure that the `psql` client version is 9.6 or higher