Identifier et corriger les index B-tree incohérents

Des incohérences au niveau des index de base de données peuvent survenir pour diverses raisons, y compris des défaillances logicielles, des problèmes matériels ou des modifications de comportement sous-jacentes telles que des modifications d'ordre de tri.

La communauté PostgreSQL a développé des outils permettant d'identifier et de résoudre ces problèmes. Cela inclut des outils tels que amcheck, que la communauté PostgreSQL recommande pour identifier les problèmes de cohérence, y compris les problèmes associés aux versions antérieures de PostgreSQL 14.

Nous avons écrit ce playbook comme référence pour les utilisateurs de Cloud SQL pour PostgreSQL qui rencontrent ces problèmes. Nous espérons que cette page fournit des informations susceptibles d'aider d'autres utilisateurs PostgreSQL à identifier et à corriger des index b-tree incohérents. Notre objectif est d'améliorer continuellement ce document en tant que ressource pour la communauté Open Source au sens large. Si vous avez des commentaires, utilisez le bouton "Envoyer des commentaires" en haut et en bas de cette page.

La résolution des incohérences d'un index implique les étapes suivantes :

Avant de commencer

Avant de commencer la réindexation, vous devez sauvegarder votre base de données, définir les autorisations appropriées, vérifier la version de votre client psql et télécharger l'extension amcheck.
Recherchez les index B-tree incohérents.

Pour identifier les index pour lesquels vous devez corriger des incohérences, vous devez identifier tous les index B-tree avec des incohérences et identifier toutes les violations de clés uniques et primaires.
Corrigez les incohérences de l'index.

La réindexation d'un index corrige toutes ses incohérences. Vous devrez peut-être ajuster les paramètres de mémoire de votre instance pour améliorer les performances.
Surveiller les opérations de réindexation

Nous vous recommandons de surveiller la progression de l'opération de réindexation pour vous assurer qu'elle est bien en cours et qu'elle n'est pas bloquée.
Vérifiez que les index sont cohérents.

Une fois la réindexation effectuée, nous vous recommandons de vérifier que l'index ne présente plus d'incohérences.

Avant de commencer

Sauvegarder votre base de données

Pour éviter toute perte de données lors de la réindexation, nous vous recommandons de sauvegarder votre base de données. Pour en savoir plus, consultez la section Créer une sauvegarde à la demande.

Définir l'autorisation `cloudsqlsuperuser`

Pour terminer les étapes décrites sur cette page, vous devez disposer des autorisations cloudsqlsuperuser. Pour en savoir plus, consultez la section session_replication_role.

S'assurer de disposer de la version 9.6 ou ultérieure du client `psql`

Pour suivre la procédure décrite sur cette page, vous devez vous assurer que la version de votre client psql est la version 9.6 ou ultérieure. Exécutez la commande psql --version pour vérifier la version du client psql dont vous disposez.

Installer l'extension amcheck

Pour vérifier les incohérences d'index, vous devez activer l'extension amcheck.

PostgreSQL 9.6

Remarque : Vous devez vous assurer que POSTGRES_9_6_24.R20220710.01_12 est installé avant d'installer amcheck_next. Pour plus d'informations sur l'installation d'une nouvelle version de maintenance cible, consultez la section Maintenance en libre-service.

Pour installer amcheck pour PostgreSQL 9.6, exécutez l'instruction suivante :

  CREATE EXTENSION amcheck_next;

Si vous obtenez une erreur indiquant "Could not open extension control file…" (Impossible d'ouvrir le fichier de contrôle des extensions…), vérifiez que vous exécutez la bonne version de maintenance cible (POSTGRES_9_6_24.R20220710.01_12).

PostgreSQL 10 et versions ultérieures

Pour installer amcheck pour PostgreSQL 10 et versions ultérieures, exécutez l'instruction suivante :

  CREATE EXTENSION amcheck;

Recherchez les index B-tree incohérents

Les sections suivantes expliquent comment détecter des index B-tree incohérents en recherchant les incohérences d'un index, ainsi que les violations de clés uniques et primaires.

Rechercher les incohérences

Exécutez l'instruction suivante pour vérifier les incohérences dans tous les index B-tree de chacune de vos bases de données :

Exemple de code

  DO $$
  DECLARE
    r RECORD;
    version varchar(100);
  BEGIN
    RAISE NOTICE 'Started 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 amcheck on database: %', current_database();
  END $$;

Des résultats semblables aux lignes suivantes devraient s'afficher :

Sortie

  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"

Pour en savoir plus sur l'affichage des journaux PostgreSQL, consultez la page Afficher les journaux d'instance.

Identifier et corriger les violations de clés uniques et primaires

Cette section explique comment vérifier les violations de clés uniques et primaires dans votre index et, le cas échéant, comment les corriger.

Identifier les violations de clés uniques

Les violations de clés uniques doivent être corrigées avant la réindexation d'un index. Pour vérifier toutes les violations de clé uniques, exécutez la commande suivante dans chaque base de données :

Exemple de code

  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

La sortie du script ressemble à ceci :

Sortie

  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

Dans ce résultat, l'en-tête de table NOTICE affiche l'index, la colonne et la table des valeurs affichées en dessous. Si le résultat contient des lignes affichant DUPLICATE ou BACKWARDS, cela indique une corruption de l'index et il peut-être nécessaire de le corriger. Les lignes avec BACKWARDS indiquent des valeurs en double potentielles pouvant être masquées. Si vous voyez l'une de ces entrées dans le tableau, consultez la section Corriger les cas de non-respect des clés en double.

Corriger les violations de clés en double

Si vous avez identifié un index unique en double ou si une opération de réindexation échoue en raison d'une erreur de violation de clé en double, procédez comme suit pour rechercher et supprimer la ou les clés en double.

Extrayez key_columns de l'en-tête de la table NOTICE, comme indiqué dans l'exemple de résultat précédent. Dans l'exemple suivant, la colonne de clé est email.
Exemple de code
```
  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
```
Utilisez ces valeurs dans KEY_COLUMNS dans la requête de l'étape 3.
Recherchez le schéma de votre table. Utilisez psql pour vous connecter à votre base de données et exécutez la commande suivante :
Exemple de code
```
\dt TABLE_NAME
```
La valeur de la colonne schema correspond à la valeur que vous utilisez pour SCHEMA_NAME dans la requête de l'étape 3.

Par exemple, pour la requête suivante :
```
 \dt games
 
```
Le résultat ressemble à ce qui suit :
```
 List of relations
 Schema  | Name  | Type  | Owner
 --------+-------+-------+----------
 public  | games | table | postgres
 (1 row)
 
```
Exécutez les instructions suivantes pour forcer une analyse complète de la table et obtenir les clés en double.
Exemple de code
```
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;
```
Remarque : Si votre base de données est volumineuse, cette instruction peut prendre beaucoup de temps.

Dans l'instruction ci-dessus, KEY_COLUMNS correspond à une ou plusieurs colonnes couvertes par l'index unique ou la clé primaire de la table que vous vérifiez. Ces identifiants ont été identifiés lors de la vérification des cas de violation des clés uniques. La déclaration renvoie les clés en double et le nombre de doublons pour chacune d'entre elles.

Par exemple, pour la requête suivante :
```
  SELECT name,count(*)
    FROM public.TEST_NAMES
  GROUP BY name
  HAVING count(*) > 1;
  
```
Le résultat ressemble à ce qui suit :
```
  name                | count
  --------------------+-------
  Johnny              |     2
  Peter               |     2
  (2 rows)
  
```
Dans ce cas, passez à l'étape suivante pour supprimer les clés en double.

Si l'une des colonnes de KEY_COLUMNS contient des valeurs nulles, vous pouvez l'ignorer, car les contraintes uniques ne s'appliquent pas aux colonnes NULL.

Si aucune clé en double n'est trouvée, vous pouvez passer à la section Corriger les index incohérents.
Facultatif, mais recommandé : créez une sauvegarde pour les enregistrements contenant des clés en double. Exécutez l'instruction suivante pour créer des enregistrements de sauvegarde :
Exemple de code
```
  CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
  AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
  WHERE (KEY_COLUMNS)
  IN ((KEY_VALUES));
  
```
Dans cette instruction, KEY_VALUES est une liste de valeurs copiées à partir du résultat de l'étape précédente. Exemple :
Exemple de code
```
  CREATE TABLE public.TEST_NAMES_bak
  AS SELECT * FROM public.TEST_NAMES
  WHERE (name) IN (('Johnny'),('Peter'))
  
```
Pour un grand nombre de lignes, il est plus facile de remplacer le paramètre ((KEY_VALUES)) dans l'instruction IN avec l'instruction SELECT de l'étape 2, sans le paramètre count. Exemple :
Exemple de code
```
  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);
  
```
Ajoutez un rôle de réplication à l'utilisateur pour désactiver les déclencheurs :
Exemple de code
```
  ALTER USER CURRENT_USER with REPLICATION;
  SET session_replication_role = replica;
  
```

Exécutez l'instruction suivante pour supprimer les clés en double :

Exemple de code

  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;

Par exemple, pour une déclaration KEY_COLUMNS portant sur plusieurs colonnes :

Exemple de code

  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;

Où day et rnum correspondent à KEY_COLUMNS.

L'exécution de cette instruction conserve une ligne et supprime les autres, pour chaque ensemble de lignes en double. Si vous souhaitez contrôler la version de la ligne à supprimer, exécutez le filtre suivant dans l'instruction de suppression :

Exemple de code

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

Procédez comme suit pour vérifier que la commande DELETE a renvoyé le nombre de lignes attendu sans erreur :
1. Exécutez l'instruction suivante pour identifier les lignes dans lesquelles les tables ont été modifiées :
  Exemple de code
```
  SELECT schemaname, relname, n_tup_del, n_tup_upd
    FROM pg_stat_xact_all_tables
  WHERE n_tup_del+n_tup_upd > 0;
  
```
2. Si toutes les lignes sont correctes, validez la transaction DELETE :
  Exemple de code
```
  END;
  
```
3. En cas d'erreurs, effectuez un rollback pour les corriger :
  Exemple de code
```
  ROLLBACK;
  
```
Après la suppression des clés en double, vous pouvez réindexer votre index.

Corriger les index incohérents

Les sections suivantes expliquent comment corriger les incohérences d'index détectées dans votre instance.

Selon la configuration de votre base de données, vous devrez peut-être effectuer les opérations suivantes pour chaque index identifié lors des étapes précédentes :

Préparer la réindexation de votre index.
Réindexer votre index.
Si l'opération de réindexation échoue en raison de violations de clés étrangères, vous devez trouver et corriger ces violations.
Réexécutez l'opération de réindexation.

Préparer la réindexation de votre index

Déterminer la taille de l'index

L'indexation de bases de données plus volumineuses nécessite plus de temps que l'indexation de bases de données plus petites. Pour améliorer la vitesse des opérations d'indexation et de réindexation des bases de données plus volumineuses, vous pouvez allouer plus de mémoire et de puissance de processeur à ces opérations. Il s'agit d'une étape importante dans la planification de l'opération de réindexation. Une fois que vous connaissez la taille de l'index, vous pouvez définir la taille de la mémoire utilisée par l'opération de réindexation et définir le nombre de nœuds de calcul en parallèle.

Exécutez la commande suivante pour trouver la taille, en kilo-octets, de l'index que vous souhaitez corriger :

Exemple de code

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

La sortie de l'instruction ressemble à ceci :

Sortie

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

Définir la taille de mémoire à utiliser pour la réindexation

En fonction de la taille de votre index, telle que déterminée dans la section précédente, il est important de définir la valeur appropriée pour maintenance_work_mem. Ce paramètre spécifie la quantité de mémoire à utiliser pour l'opération de réindexation. Par exemple, si la taille de votre index est supérieure à 15 Go, nous vous recommandons d'ajuster votre mémoire de maintenance. Pour en savoir plus, consultez la page Définir une option de base de données.

L'indexation de bases de données plus volumineuses nécessite plus de temps que l'indexation de bases de données plus petites. Pour améliorer la vitesse des opérations d'indexation et de réindexation, nous vous recommandons de définir maintenance_work_mem sur au moins 2% de la mémoire de l'instance pour les instances disposant d'au moins 4 Go de mémoire lors de cette opération de réindexation.

Définir le nombre de nœuds de calcul en parallèle

Vous pouvez augmenter le nombre de nœuds de calcul en parallèle pour la réindexation en définissant le paramètre max_parallel_maintenance_workers dans les bases de données à l'aide de PostgreSQL 11 ou version ultérieure. La valeur par défaut de ce paramètre est 2, mais elle peut être définie sur une valeur plus élevée pour augmenter le nombre de nœuds de calcul pour la réindexation. Pour les instances comportant au moins huit cœurs de processeur virtuel, nous vous recommandons de définir la valeur de l'option max_parallel_maintenance_workers sur 4.

Pour en savoir plus, consultez la page Définir une option de base de données.

Réindexer votre index

Vous pouvez réindexer un index sans bloquer votre charge de travail de production à l'aide de l'utilitaire pg_repack. Cet utilitaire automatise et simplifie le processus de réindexation simultané, ce qui vous permet de réindexer sans temps d'arrêt, en particulier pour les versions PostgreSQL 11 et antérieures, qui ne disposent pas de l'opération REINDEX CONCURRENTLY. Pour cette procédure, utilisez la version 1.4.7 de pg_repack.

Procédez comme suit pour réindexer votre index à l'aide de pg_repack :

Téléchargez, compilez et installez l'utilitaire pg_repack à partir de la page pg_repack.

Debian GNU/Linux 11

Pour plus de commodité, nous recommandons aux utilisateurs de Debian Linux de télécharger et d'installer ce binaire exécutable préconfiguré pour la plate-forme Linux x86_64.

La somme de contrôle (hachage sha256) du binaire est la suivante :

ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

Pour vérifier que votre version de Linux est Debian GNU/Linux 11, exécutez la commande hostnamectl.

Auto-compilation

Remarque : Pour cette procédure, vous devez exécuter la commande dans le terminal sous Linux ou sous Unix avec une chaîne d'outils GNU opérationnelle.

Téléchargez, compilez et installez l'utilitaire pg_repack à partir de la page pg_repack.
Créez l'extension pg_repack :
Exemple de code
```
  CREATE EXTENSION pg_repack;
  
```
Exécutez la commande suivante pour réindexer votre index simultanément :
Exemple de code
```
  pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
  
```
Remarque : pg_repack ne vous permet pas de réindexer un index sur une vue matérialisée.

Le résultat de cette commande ressemble à ce qui suit :
Sortie
```
  INFO: repacking index "public.t_i_key"
  
```
Si des erreurs se sont produites lors de l'exécution de pg_repack, vous pouvez les corriger, puis réessayer. Après avoir corrigé tous vos index de clé unique et tous vos index de clé primaire, vous devez vérifier les violations de clés étrangères et résoudre les problèmes détectés.

Rechercher et corriger les violations de clés étrangères

Pour savoir comment identifier et corriger les cas de violation de clés étrangères, consultez la page Rechercher et corriger les cas de violation de clés étrangères.

Surveiller les opérations de réindexation

Il peut arriver que l'opération de réindexation soit bloquée par d'autres sessions. Nous vous recommandons de la vérifier toutes les quatre heures. Si l'opération de réindexation est bloquée, vous pouvez annuler la session de blocage afin que l'opération de réindexation puisse être terminée.

Pour identifier les sessions bloquantes et en attente, puis les annuler dans l'opération INDEX, procédez comme suit :

Pour identifier les sessions bloquantes, exécutez la requête suivante :

Exemple de code

  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;

Pour annuler une session, exécutez la requête suivante à l'aide du PID de la session bloquante de la requête précédente :
Exemple de code
```
  SELECT pg_cancel_backend(PID);
  
```

Vérifier la cohérence de vos index

Vous devez continuer à rechercher les incohérences d'index pour chaque index incohérent. Une fois que vous avez corrigé tous les index et violations de clés de votre instance, vous pouvez vérifier qu'aucun problème n'existe en suivant les étapes décrites dans les sections précédentes :

Rechercher les incohérences d'un index
Rechercher des cas de violation de clés uniques

Identifier et corriger les index B-tree incohérents

Avant de commencer

Sauvegarder votre base de données

Définir l'autorisation cloudsqlsuperuser

S'assurer de disposer de la version 9.6 ou ultérieure du client psql

Installer l'extension amcheck

PostgreSQL 9.6

PostgreSQL 10 et versions ultérieures

Recherchez les index B-tree incohérents

Rechercher les incohérences

Exemple de code

Sortie

Identifier et corriger les violations de clés uniques et primaires

Identifier les violations de clés uniques

Exemple de code

Sortie

Corriger les violations de clés en double

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Exemple de code

Corriger les index incohérents

Préparer la réindexation de votre index

Déterminer la taille de l'index

Exemple de code

Sortie

Définir la taille de mémoire à utiliser pour la réindexation

Définir le nombre de nœuds de calcul en parallèle

Réindexer votre index

Debian GNU/Linux 11

Auto-compilation

Exemple de code

Exemple de code

Sortie

Rechercher et corriger les violations de clés étrangères

Surveiller les opérations de réindexation

Exemple de code

Exemple de code

Vérifier la cohérence de vos index

Définir l'autorisation `cloudsqlsuperuser`

S'assurer de disposer de la version 9.6 ou ultérieure du client `psql`