Les index invalides de Postgres dégradent silencieusement les performances d'écriture après des échecs de création

Une pratique courante dans les migrations modernes de bases de données consiste à utiliser CREATE INDEX CONCURRENTLY pour éviter le verrouillage des tables lors des modifications du schéma. Cependant, lorsque cette opération échoue en raison de délais dépassés ou de conflits de données, elle laisse souvent derrière elle une entrée d'index « invalide ». Ces résidus n'aident pas aux requêtes mais peuvent ralentir considérablement les opérations d'écriture en forçant la base de données à les maintenir inutilement.

Ce qui s'est passé

Lorsqu'un développeur ou un agent automatisé demande un index unique sur une table active, l'approche correcte consiste à utiliser l'option de création concurrente. Si le processus échoue en cours de route—peut-être à cause d'une violation de clé unique, d'un interblocage (deadlock) ou d'un délai d'exécution dépassé—Postgres ne se contente pas de revenir en arrière sur l'ensemble de l'opération. Au contraire, il valide l'entrée initiale du catalogue pour l'index avant que la création ne soit terminée. Cela aboutit à un index qui existe dans le système mais est marqué comme invalide.

Le problème critique survient lorsque les scripts de migration sont relancés sans vérifier l'état de la base de données. Une erreur fréquente consiste à ajouter IF NOT EXISTS à la commande de nouvelle tentative. Cela permet à la migration de passer les vérifications d'intégration continue, mais ignore le fait que l'index existant est cassé. La base de données continue de supporter la charge de cette structure invalide, entraînant une dégradation des performances difficile à diagnostiquer car les plans de requête standards n'afficheront pas son utilisation.

Détails clés

• Les index invalides sont créés lorsqu'une création concurrente échoue après la validation de la transaction initiale du catalogue.
• Deux indicateurs déterminent le coût : indisvalid = false signifie que l'index est inutile pour les requêtes, tandis que indisready = false signifie que les écritures l'ignorent.
• Si indisready est vrai, la base de données met toujours à jour l'index à chaque insertion ou mise à jour, gaspillant CPU et stockage.
• Les contraintes d'unicité issues de créations échouées peuvent encore appliquer les règles d'unicité même si le planificateur n'utilise jamais l'index pour les recherches.
• Les outils de migration comme Rails, Knex et golang-migrate enveloppent souvent les commandes dans des transactions, ce qui provoque l'échec immédiat des opérations CONCURRENTLY.
• Le nettoyage nécessite DROP INDEX CONCURRENTLY, qui doit être exécuté en dehors d'un bloc de transaction.

Contexte

Pour comprendre pourquoi cela se produit, il est utile de savoir qu'une création d'index concurrente n'est pas une transaction atomique unique. Elle se déroule en trois phases distinctes au sein de transactions séparées. Premièrement, l'index est enregistré dans les catalogues système comme invalide. Deuxièmement, la base de données scanne les données existantes de la table pour peupler l'index. Troisièmement, elle attend la fin de toutes les anciennes transactions avant de marquer l'index comme prêt à l'utilisation.

Si le processus est interrompu après la première phase mais avant la dernière, l'index reste dans le catalogue. Selon le moment exact de l'échec, l'index peut se trouver dans un état où il n'est pas prêt pour les requêtes mais est toujours mis à jour par les nouvelles écritures. C'est particulièrement insidieux car l'index semble exister, tout en n'apportant aucun avantage en lecture tout en pénalisant activement les performances d'écriture.

Pourquoi c'est important

Pour les équipes qui hébergent leur propre logiciel, la dégradation silencieuse des performances est un risque opérationnel majeur. Un index invalide qui est encore maintenu consomme de l'espace disque, augmente le volume du journal d'écriture anticipée (WAL) et empêche les mises à jour Heap-Only Tuple (HOT). Cela entraîne des coûts de stockage plus élevés et des temps de réponse applicatifs plus lents lors de l'ingestion de données. Puisque l'index est ignoré par le planificateur de requêtes, les outils de surveillance standards axés sur les performances de lecture peuvent ne pas signaler le problème.

De plus, les pipelines de déploiement automatisés peuvent aggraver le problème. Si un agent de codage ou un script CI relance une migration échouée avec IF NOT EXISTS, il masque efficacement l'erreur. Le déploiement réussit, mais la base de données accumule une dette technique sous forme d'index cassés. Avec le temps, plusieurs tentatives échouées peuvent laisser plusieurs index invalides sur une seule table, aggravant la surcharge d'écriture et rendant la maintenance future plus complexe.

Ce que vous pouvez faire

• Interrogez régulièrement le catalogue système pg_index pour trouver les entrées où indisvalid est faux.
• Vérifiez l'indicateur indisready pour déterminer si l'index invalide consomme encore des ressources d'écriture.
• Utilisez DROP INDEX CONCURRENTLY pour supprimer les index invalides sans verrouiller la table pour les lectures ou les écritures.
• Assurez-vous que les outils de migration sont configurés pour désactiver l'enveloppement par transaction pour les opérations d'index concurrentes.
• Évitez d'utiliser IF NOT EXISTS dans les nouvelles tentatives ; vérifiez plutôt la validité et supprimez d'abord l'index cassé.
• Ajoutez une vérification post-déploiement qui lève une exception si des index invalides subsistent dans la base de données.