Documentation PostgreSQL 14.22 » Référence » Commandes SQL » REINDEX

Précédent | Niveau supérieur | Suivant
REFRESH MATERIALIZED VIEW | Sommaire | RELEASE SAVEPOINT

## REINDEX

REINDEX — reconstruit les index

## Synopsis

    REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] nom

    où option peut faire
    partie de :

        CONCURRENTLY [ boolean ]
        TABLESPACE new_tablespace
        VERBOSE [ boolean ]

## Description

`REINDEX` reconstruit un index en utilisant les données stockées dans la table, remplaçant l'ancienne copie de l'index. Il y a plusieurs raisons pour utiliser `REINDEX` :

- Un index a été corrompu et ne contient plus de données valides. Bien qu'en théorie, ceci ne devrait jamais arriver, en pratique, les index peuvent se corrompre à cause de bogues dans le logiciel ou d'échecs matériels. `REINDEX` fournit une méthode de récupération.

- L'index en question a « explosé », c'est-à-dire qu'il contient beaucoup de pages d'index mortes ou presque mortes. Ceci peut arriver avec des index B-tree dans PostgreSQL sous certains modèles d'accès inhabituels. `REINDEX` fournit un moyen de réduire la consommation d'espace de l'index en écrivant une nouvelle version de l'index sans les pages mortes. Voir Section 25.2 pour plus d'informations.

- Vous avez modifié un paramètre de stockage (par exemple, fillfactor) pour un index et vous souhaitez vous assurer que la modification a été prise en compte.

- Si la création d'un index échoue avec l'option `CONCURRENTLY`, cet index est laissé comme « invalide ». De tels index sont inutilisables mais il peut être pratique d'utiliser alors `REINDEX` pour les reconstruire. Il est à noter que seul `REINDEX INDEX` peut reconstruire un index invalide en parallèle.

## Paramètres

`INDEX`

  Recrée l'index spécifié. Cette forme de `REINDEX` ne peut pas être exécutée à l'intérieur d'un bloc de transaction quand elle est utilisée avec un index partitionné.

`TABLE`

  Recrée tous les index de la table spécifiée. Si la table a une seconde table « TOAST », elle est aussi ré-indexée. Cette forme de `REINDEX` ne peut pas être exécutée à l'intérieur d'un bloc de transaction quand elle est utilisée avec un index partitionné.

`SCHEMA`

  Recrée tous les index du schéma spécifié. Si une table de ce schéma a une table secondaire (« TOAST »), elle est aussi ré-indexée. Les index sur les catalogues systèmes partagés sont aussi traités. Cette forme de `REINDEX` ne peut pas être exécutée dans un bloc de transaction.

`DATABASE`

  Recrée tous les index de la base de données en cours. Les index sur les catalogues système partagés sont aussi traités. Cette forme de `REINDEX` ne peut pas être exécutée à l'intérieur d'un bloc de transaction.

`SYSTEM`

  Recrée tous les index des catalogues système à l'intérieur de la base de données en cours. Les index sur les catalogues système partagés sont aussi inclus. Les index des tables utilisateur ne sont pas traités. Cette forme de `REINDEX` ne peut pas être exécutée à l'intérieur d'un bloc de transaction.

`nom`

  Le nom de l'index, de la table ou de la base de données spécifique à ré-indexer. Les noms de table et d'index peuvent être qualifiés du nom du schéma. Actuellement, `REINDEX DATABASE` et `REINDEX SYSTEM` ne peuvent ré-indexer que la base de données en cours, donc ce paramètre doit correspondre au nom de la base de données en cours.

`CONCURRENTLY`

  Lorsque cette option est utilisée, PostgreSQL va reconstruire l'index sans prendre de verrou pouvant empêcher les insertions, mises à jour et suppressions en parallèle sur la table ; alors qu'une réindexation standard empêche les écritures (mais pas les lectures) sur la table jusqu'à ce qu'elle soit terminée. Certaines mises en garde sont à prendre en compte lors de l'utilisation de cette option -- voir Reconstruire des index en parallèle.

  Pour les tables temporaires, `REINDEX` est toujours non concurrent car aucune autre session ne peut y accéder, et qu'une réindexation non concurrente est moins coûteuse.

`TABLESPACE`

  Spécifie le nouveau tablespace des index une fois reconstruits.

`VERBOSE`

  Affiche un message de progression à chaque index traité.

`boolean`

  Indique si l'option sélectionnée doit être activée ou désactivée. Vous pouvez écrire `TRUE`, `ON`, ou `1` pour activer l'option, et `FALSE`, `OFF` ou `0` pour la désactiver. La valeur `boolean` peut aussi être omise, auquel cas `TRUE` esy supposée.

`nouveau_tablespace`

  Le tablespace où les index seront reconstruits.

## Notes

Si vous suspectez la corruption d'un index sur une table utilisateur, vous pouvez simplement reconstruire cet index, ou tous les index de la table, en utilisant `REINDEX INDEX` ou `REINDEX TABLE`.

Les choses sont plus difficiles si vous avez besoin de récupérer la corruption d'un index sur une table système. Dans ce cas, il est important pour le système de ne pas avoir utilisé lui-même un des index suspects. (En fait, dans ce type de scénario, vous pourriez constater que les processus serveur s'arrêtent brutalement au lancement du service, mettant en cause l'utilisation des index corrompus.) Pour récupérer proprement, le serveur doit être lancé avec l'option `-P`, qui inhibe l'utilisation des index pour les recherches dans les catalogues système.

Une autre façon est d'arrêter le serveur et de relancer le serveur PostgreSQL en mode simple utilisateur avec l'option `-P` placée sur la ligne de commande. Ensuite, `REINDEX DATABASE`, `REINDEX SYSTEM`, `REINDEX TABLE` ou `REINDEX INDEX` peuvent être lancés suivant ce que vous souhaitez reconstruire. En cas de doute, utilisez la commande `REINDEX SYSTEM` pour activer la reconstruction de tous les index système de la base de données. Enfin, quittez la session simple utilisateur du serveur et relancez le serveur en mode normal. Voir la page de référence de postgres pour plus d'informations sur l'interaction avec l'interface du serveur en mode simple utilisateur.

Une session standard du serveur peut aussi être lancée avec `-P` dans les options de la ligne de commande. La méthode pour ce faire varie entre les clients mais dans tous les clients basés sur libpq, il est possible de configurer la variable d'environnement `PGOPTIONS` à `-P` avant de lancer le client. Notez que, bien que cette méthode ne verrouille pas les autres clients, il est conseillé d'empêcher les autres utilisateurs de se connecter à la base de données endommagée jusqu'à la fin des réparations.

`REINDEX` est similaire à une suppression et à une nouvelle création de l'index. Dans les faits, le contenu de l'index est complètement recréé. Néanmoins, les considérations de verrouillage sont assez différentes. `REINDEX` verrouille les écritures mais pas les lectures de la table mère de l'index. Il positionne également un verrou de type `ACCESS EXCLUSIVE` sur l'index en cours de traitement, ce qui bloque les lectures qui tentent de l'utiliser. Au contraire, `DROP INDEX` prends temporairement un verrou de type `ACCESS EXCLUSIVE` sur la table parent, bloquant ainsi écritures et lectures. Le `CREATE INDEX` qui suit verrouille les écritures mais pas les lectures ; comme l'index n'existe pas, aucune lecture ne peut être tentée, signifiant qu'il n'y a aucun blocage et que les lectures sont probablement forcées de réaliser des parcours séquentiels complets.

Ré-indexer un seul index ou une seule table requiert d'être le propriétaire de cet index ou de cette table. Ré-indexer un schéma ou une base de données requiert d'être le propriétaire du schéma ou de la base de données. Notez que, du coup, il est parfois possible pour des utilisateurs standards de reconstruire les index de tables dont ils ne sont pas propriétaires Néanmoins, il existe une exception spéciale, quand la commande `REINDEX DATABASE`, `REINDEX SCHEMA` ou `REINDEX SYSTEM` est exécutée par un utilisateur standard, les index sur les catalogues partagés seront ignorés sauf si l'utilisateur possède le catalogue (ce qui ne sera généralement pas le cas). Bien sûr, les super-utilisateurs peuvent toujours tout ré-indexer.

Réindexer des index partitionnés ou des tables partitionnées est supporté avec les commandes respectives `REINDEX INDEX` et `REINDEX TABLE`. Chaque partition de la relation partitionnée est réindexée dans une transaction séparée. Ces commandes ne peuvent pas être utilisées à l'intérieur d'un bloc de transaction lors d'un travail sur une table partitionnée ou sur un index partitionnée.

Lors de l'utilisation de la clause `TABLESPACE` avec `REINDEX` sur une table ou un index partitionné, seules les références du tablespace des partitions feuilles sont mises à jour. Comme les index partitionnées ne sont pas mis à jour, il est recommandé d'utiliser séparément `ALTER TABLE ONLY` sur celles-ci pour que toute nouvelle partition attachée hérite du nouveau tablespace. En cas d'échec, il pourrait ne pas avoir déplacer tous les index vers le nouveau tablespace. Ré-exécuter la commande reconstruira les partitions feuilles et déplacera les index non traités précédemment vers le nouveau tablespace.

Si `SCHEMA`, `DATABASE` ou `SYSTEM` sont utilisés avec `TABLESPACE`, les relations systèmes sont ignorés et un simple message `WARNING` sera généré. Les index sur les tables TOAST sont reconstruits, mais pas déplacés dans le nouveau tablespace.

### Reconstruire des index en parallèle

Recréer un index peut interférer avec les opérations normales d'une base de données. Habituellement, PostgreSQL verrouille la table dont dépend l'index à reconstruire pour la protéger des écritures et reconstruit l'index complet avec un seul parcours de la table. Les autres transactions peuvent toujours lire la table mais si elles essaient d'insérer, mettre à jour ou supprimer des lignes dans la table, elles seront bloquées jusqu'à la fin de la reconstruction de l'index. Ceci peut avoir un effet sérieux si le système est une base en production. Les très grosses tables peuvent demander plusieurs heures pour être indexées. Même pour les petites tables, une reconstruction d'index peut bloquer les processus qui voudraient écrire dans la table pendant des périodes longues inacceptables sur un système de production.

PostgreSQL supporte la reconstruction des index sans verrouillage des écritures. Cette méthode est appelée en précisant l'option `CONCURRENTLY` de `REINDEX`. Quand cette option est utilisée, PostgreSQL doit réaliser deux parcours de table pour chaque index à reconstruire et doit attendre la fin de toutes les transactions existantes qui peuvent utiliser cet index. Du coup, cette méthode requiert plus de travail qu'une reconstruction standard de l'index et est bien plus longue à se terminer puisqu'elle doit également attendre la fin des transactions en cours qui pourraient modifier cet index. Néanmoins, comme cela autorise la poursuite des opérations pendant la reconstruction de l'index, cette méthode est utile pour reconstruire des index dans un environnement en production. Bien sûr, la charge CPU et I/O supplémentaire imposée par la recréation de l'index peut ralentir les autres opérations.

Les étapes suivantes interviennent dans la ré-indexation en parallèle. Chaque étape se déroule dans une transaction séparée. Si plusieurs index doivent être reconstruits, alors chaque étape itérera sur l'ensemble des index avant de passer à l'étape suivante.

1. Une nouvelle définition transitoire de l'index est ajoutée au catalogue `pg_index`. Cette définition sera utilisée pour remplacer celle de l'ancien index. Un verrou `SHARE UPDATE EXCLUSIVE` est posé au niveau de la session sur les index reconstruits ainsi que sur les tables qui leurs sont associées pour éviter toute modification de structure pendant le processus.

2. Une première itération de construction de l'index est réalisée pour chaque nouvel index. Dès que l'index est construit, son marqueur `pg_index.indisready` est positionné à « true » pour le rendre disponible aux insertions et visible aux autres transactions dès que la session ayant procédé à la construction est terminée. Cette étape est réalisée dans une transaction séparée pour chaque index.

3. Ensuite, une seconde itération est réalisée pour ajouter les enregistrements qui ont été créés pendant que la première itération était exécutée. Cette étape est également réalisée dans une transaction séparée pour chaque index.

4. Toutes les contraintes qui font référence à l'index reconstruit sont changées pour pointer sur la nouvelle définition d'index, et le nom des index sont également changés. À ce stade, `pg_index.indisvalid` est positionné à « true » pour le nouvel index et à « false » pour l'ancien. Une invalidation de cache est alors demandée entraînant l'annulation de toutes les sessions référençant l'ancien index.

5. Les anciens index voient leurs marqueurs `pg_index.indisready` positionnés à « false » pour y empêcher tout nouvel ajout d'enregistrement, après avoir attendu que les requêtes en cours qui pourraient faire référence à ces index se terminent.

6. Les anciens index sont supprimés. Les verrous de session `SHARE UPDATE EXCLUSIVE` pour les index et leurs tables sont relâchés.

Si un problème survient pendant la recréation des index, comme une violation d'unicité dans un index unique par exemple, la commande `REINDEX` va échouer mais laisser le nouvel index « invalide » en plus de celui qui existait déjà. L'index sera ignoré par les requêtes car il pourrait être incomplet ; cependant, il entraînera encore un surcoût lors des mises à jour. La commande psql `\d` rapportera un tel index comme `INVALID` :

    postgres=# \d tab
           Table "public.tab"
     Column |  Type   | Modifiers
    --------+---------+-----------
     col    | integer |
    Indexes:
        "idx" btree (col)
        "idx_ccnew" btree (col) INVALID

Si l'index marqué `INVALID` a pour suffixe `_ccnew`, alors il correspond à l'index transitoire créé pendant l'opération concurrente, et la méthode de récupération recommandée est de le supprimer en utilisant `DROP INDEX`, puis de tenter de nouveau un `REINDEX CONCURRENTLY`. Si l'index invalide a pour suffixe `_ccold`, il correspond à l'index original qui n'a pas pu être supprimé ; la méthode de récupération recommandé est de seulement supprimer l'index car la reconstruction a réussi. Un nombre différent de zéro peut être ajouté au suffixe des noms d'index invalides pour qu'ils restent uniques, par exemple `_ccnew1`, `_ccold2`, etc.

La construction normale d'index permet d'autres constructions d'index sur la même table simultanément, mais uniquement une seule création d'index en parallèle peut être réalisée sur une table à la fois. Dans les deux cas, il n'est pas permis de procéder à une modification de structure de la table pendant l'opération. Une autre différence est que les commandes `REINDEX TABLE` ou `REINDEX INDEX` classiques peuvent être exécutées à l'intérieur d'un bloc de transaction, alors que ce n'est pas le cas avec `REINDEX CONCURRENTLY`.

Comme toute transaction longue, `REINDEX` sur une table peut affecter les lignes supprimables par un `VACUUM` concurrent sur toute autre table.

`REINDEX SYSTEM` ne supporte pas `CONCURRENTLY` puisque les catalogues systèmes ne peuvent pas être re-indexés en parallèle.

En outre, les index des contraintes d'exclusion ne peuvent pas être reconstruits en parallèle. Si un tel index est appelé directement dans cette commande, une erreur est soulevée. Si une table ou base de données avec des index de contraintes d'exclusion est ré-indexée en parallèle, ces index seront ignorés. (Il est toutefois possible de reconstruire de tels index sans l'option `CONCURRENTLY`.)

Chaque processus exécutant `REINDEX` indiquera sa progression dans la vue `pg_stat_progress_create_index`. Voir Section 28.4.2 pour les détails.

## Exemples

Reconstruit un index simple :

    REINDEX INDEX my_index;

Recrée les index sur la table `ma_table` :

    REINDEX TABLE ma_table;

Reconstruit tous les index d'une base de données particulière sans faire confiance à la validité des index système :

    $ export PGOPTIONS="-P"
    $ psql broken_db
    ...
    broken_db=> REINDEX DATABASE broken_db;
    broken_db=> \q

Reconstruit les index d'une table tout en ne bloquant pas les opérations de lectures et d'écriture sur les relations impliquées lors de la réindexation :

    REINDEX TABLE CONCURRENTLY my_broken_table;

## Compatibilité

Il n'existe pas de commande `REINDEX` dans le standard SQL.

## Voir aussi

CREATE INDEX, DROP INDEX, reindexdb, Section 28.4.2