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](https://www.postgresql.org/docs/current/amcheck.html),
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](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#before).

   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](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#check-index).

   One statement identifies unique and primary key violations, and another
   statement detects a variety of other inconsistencies.
3. [Fix the index's inconsistencies](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#fix-indexes).

   Reindexing an index fixes all its inconsistencies. You may
   need to adjust your instance's memory settings to improve performance.
4. [Monitor reindexing operations](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#monitor).

   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](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#verify).

   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](https://docs.cloud.google.com/sql/docs/mysql/backup-recovery/backing-up#on-demand).

> [!CAUTION]
> **Caution:** A backup taken on a database that already has index inconsistencies will still have inconsistencies when restored.

### Set the `cloudsqlsuperuser` permission

To complete the steps on this page, you must have `cloudsqlsuperuser`
permissions. For more information, see [session_replication_role](https://docs.cloud.google.com/sql/docs/postgres/flags.md.txt).

### 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]
> **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](https://docs.cloud.google.com/sql/docs/postgres/self-service-maintenance.md.txt).

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

```bash
  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:

```bash
  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

```sql
  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

```bash
  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"
  
```

> [!WARNING]
> **Warning:** If your output doesn't contain WARNING notices, unique or key violations could still exist. To check for violations, perform the procedures in Identify and fix unique and primary key violations.

For more information about viewing PostgreSQL logs, see
[View instance logs](https://docs.cloud.google.com/sql/docs/postgres/logging.md.txt).

### 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](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#unique-key) before you reindex an index. To check for
all unique key violations, run the following command in each database:

### Code Sample

```sql
  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

```bash
  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](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#unique-key).

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

> [!NOTE]
> **Note:** You need to repeat the following steps for each index that has key violations.

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

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

   Use these values in <var translate="no">KEY_COLUMNS</var> 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:

   <br />

   ### Code Sample

   ```sql
   \dt TABLE_NAME
   ```
   The value in the `schema` column is the value you use for <var translate="no">SCHEMA_NAME</var> in the query in step 3.

   <br />

   For example, for the following query:

   <br />

   ```sql
    \dt games
    
   ```

   <br />

   The output is similar to the following:

   <br />

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

   <br />

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

   ### Code Sample

   ```sql
   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;
   ```

   > [!NOTE]
   > **Note:** If your database is large, this statement may take a long time to complete.

   In the above statement, <var translate="no">KEY_COLUMNS</var> 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:

   <br />

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

   <br />

   The output is similar to the following:

   <br />

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

   <br />

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

   If any of the columns in <var translate="no">KEY_COLUMNS</var> 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](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#fix-indexes).
4. Optional but recommended: Create a backup for the records containing
   duplicate keys. Run the following statement to create backup records:

   ### Code Sample

   ```sql
     CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
     AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
     WHERE (KEY_COLUMNS)
     IN ((KEY_VALUES));
     
   ```

   In this statement, <var translate="no">KEY_VALUES</var> is a list of values copied from the
   result of the previous step. For example:

   ### Code Sample

   ```sql
     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
   ((<var translate="no">KEY_VALUES</var>)) parameter in the `IN` statement with the `SELECT`
   statement from step 2 without the `count` parameter. For
   example:

   ### Code Sample

   ```sql
     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. Add a replication role to the user to disable triggers:

   ### Code Sample

   ```sql
     ALTER USER CURRENT_USER with REPLICATION;
     SET session_replication_role = replica;
     
   ```
6. Run the following statement to delete the duplicate keys:

   ### Code Sample

   ```sql
     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 <var translate="no">KEY_COLUMNS</var>:

   <br />

   ### Code Sample

   ```sql
     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 <var translate="no">KEY_COLUMNS</var>.

   <br />

   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

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

   > [!WARNING]
   > **Warning:** Committing this transaction mutates your database and removes duplicate keys. Make sure that you don't delete any data that's still needed. Restoring from a backup might restore the inconsistent indexes.

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

      ```sql
        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

      ```sql
        END;
        
      ```
   3. If there are errors, roll back the changes to fix the errors:

      ### Code Sample

      ```sql
        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 inconsistencies
found in your instance.

> [!NOTE]
> **Note:** If your index has unique and primary key violations, you must fix these before reindexing.

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

1. [Prepare to reindex your index](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#prepare).

2. [Reindex your index](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#rebuild).

3. If the reindex operation fails due to foreign key violations, you must
   [find and fix these violations](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#check-fk).

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 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](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#memory) and
[set the number of parallel workers](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#workers).

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

### Code Sample

```sql
  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

```bash
  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`](https://docs.cloud.google.com/sql/docs/postgres/flags.md.txt#postgres-m). 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](https://docs.cloud.google.com/sql/docs/postgres/flags.md.txt#set_a_database_flag).

> [!NOTE]
> **Note:** The value of `maintenance_work_mem` is set for each session. You don't need to restart your instance after changing the value. However, if you connect to the database again in a new session, the value from the previous setting is not saved.

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](https://docs.cloud.google.com/sql/docs/postgres/flags.md.txt#postgres-m)
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](https://docs.cloud.google.com/sql/docs/postgres/flags.md.txt#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`:

1. Download, compile, and install the `pg_repack` utility from the
   [pg_repack page](https://reorg.github.io).

   ### 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](https://storage.googleapis.com/cloud-sql-pg-repack/ver_1.4.7/pg_repack).

   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](https://reorg.github.io/pg_repack/).
2. Create the `pg_repack` extension:

   ### Code Sample

   ```sql
     CREATE EXTENSION pg_repack;
     
   ```
3. Run the following command to reindex your index concurrently:

   ### Code Sample

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

   > [!NOTE]
   > **Note:** `pg_repack` does not allow you to reindex an index on a materialized view.

   This command has output similar to the following:

   ### Output

   ```bash
     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](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#check-fk)
   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](https://docs.cloud.google.com/sql/docs/postgres/fix-foreign-keys.md.txt).

## 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:

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

   ### Code Sample

   ```sql
     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. To cancel a session, run the following query using the PID of
   the blocking session from the previous query:

   ### Code Sample

   ```sql
     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](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#check-index)

- [Check for unique key violations](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes.md.txt#check-uk)