D'où proviennent les index INVALID ?
Par admin le dimanche 17 mai 2020, 14:41 - PostgreSQL - Lien permanent
Cela ne vous est peut-être pas encore arrivé, ou vous lisez peut-être cet article pour juste chercher à comprendre ce que sont ces index INVALID car vous venez de les découvrir sur votre environnement.
Après de longues années d'utilisation de PostgreSQL je n'avais pas encore été confronté à ce phénomène. Il m'est apparu dans plusieurs cas de figure que nous allons rapidement détailler et surtout solutionner, enfin par les méthodes que j'ai pu trouver de mon côté.
Premier cas de figure qui se croisent également avec un autre cas de figure sur une table en production.
J'ai récupéré un fichier de plusieurs To de données à importer en base, avec des contenus par fois un peu fantasques. Une fois ces données importées, j'ai voulu indexer certains champs, et je me suis trouvé confronté au refus de PostgreSQL de créer tout simplement ces index. La taille des données dans le champ de type TEXT
dépassant les 8191 octets autorisés.
Sur mon autre base en production, j'ai également eu le souci, mais cette fois après coup ... Je stocke dans cette table une énorme chaine de caractère qui me permet de monter dynamiquement des requêtes SQL croisées. Un index à double entrée utilisait ce champ. Et j'ai vu un jour mes performances baisser et la charge de ma base de données tout à coup augmenter. Après analyse, il s'avérait que des index étaient devenus INVALID.
Alors pourquoi je vous parle de ces deux exemples différents, l'un a l'indexation, l'autre sur une index existant ? Tout simplement parce que la cause en est la même: les données dépassent la taille maximale en octet soit 8191 octets.
Comment donc faire pour trouver les enregistrements qui posent problème :
SELECT id_my_table FROM my_table WHERE octet_length(label) > 8191;
Cette requête va donc nous donner tous les enregistrement qui dépassent la taille autorisée.
Ensuite, libre à vous en fonction de l'importance des données d'agir comme bon vous semble. Soit en supprimant ces enregistrement, ce que j'ai fait dans mon premier cas, mais parce que ces données étaient véritablement inexploitables.
Soit comme pour mon deuxième cas, en conservant les données, mais en indexant non plus son contenu, mais un nouveau champ ajouté à la table qui contient le hash MD5 de ces données. Cela permet de toujours garder la données en elle-même, mais de supprimer radicalement cet index, et de le déplacer sur une chaine plus efficace pour PostgreSQL.
Autre cas de figure plus malicieux je trouve, car lié à un nouvelle fonctionnalité de PostgreSQL, le cas de la création d'un index unique concurrent.
Vous pouvez depuis quelque temps déjà créer des index sans bloquer l'accès à la table sur laquel vous apposez cet index.
Par une commande du type :
CREATE UNIQUE INDEX CONCURRENTLY ON my_table (login)
PostgreSQL va alors dans un premier temps faire un passage sur toute la table pour créer l'index, puis lancer une seconde passe pour les données ajoutées ou modifiées depuis la fin du premier passage. Cela fonctionne dans la majorité des cas, mais si on ajoute une données qui pourrait faire doublon de cette clé entre les deux étapes, que va-t-il se passer ? PostgreSQL va arrêter la création de l'index et il sera donc marqué INVALID.
Un point important à comprendre c'est que dès lors que votre index est marqué comme INVALID, PostgreSQL ne s'en servira plus du tout, mais il sera toujours mis à jour comme les autres index.
La solution pour ce cas sera donc, soit de lancer un REINDEX
, mais qui va bloquer l'accès à votre table en écriture. La meilleure solution serait de supprimer l'index via DROP INDEX
, corriger le problème d'unicité de la base de données, puis recréer votre index de la concurrente, en croisant les doigts pour qu'aucune données potentiellement dupliquée n'arrive dans cet espace temps.
Comment détecter les index INVALID
Voilà une requête qui vous permettra d'un seul coup d'oeil de trouver tous les index qui sont invalides dans votre base de données :
SELECT * FROM pg_class JOIN pg_index ON (pg_index.indexrelid = pg_class.oid) WHERE pg_index.indisvalid = false;
En espérant avoir répondu à votre problématique sur PostgreSQL.