Quand l'ajout d'un index fige votre application

Votre équipe traque une requête lente depuis des semaines. La table users a atteint des millions de lignes, et la recherche par email prend désormais des secondes au lieu de millisecondes. Quelqu'un suggère d'ajouter un index. La solution semble simple : une commande CREATE INDEX, problème résolu.

Vous lancez la migration en pleine journée car la requête pénalise déjà les utilisateurs. L'index commence à se construire. Puis les alertes arrivent : timeouts applicatifs, transactions échouées, tickets de support qui s'accumulent. L'index censé améliorer les performances vient de provoquer un incident de production.

Ce scénario se produit plus souvent que la plupart des équipes ne l'imaginent. Les index et les contraintes ressemblent à des changements de schéma simples, mais ils cachent des coûts qui ne se révèlent qu'en charge.

Que se passe-t-il quand vous créez un index

Quand vous exécutez CREATE INDEX idx_users_email ON users(email), la base de données doit lire chaque ligne de la table, construire une structure d'arbre équilibré et l'écrire sur le disque. Pendant ce processus, la base de données verrouille généralement la table pour empêcher les modifications qui corrompraient la structure de l'index.

Le type de verrou dépend de votre base de données. Dans PostgreSQL, un CREATE INDEX standard acquiert un SHARE LOCK. Les lectures peuvent encore avoir lieu, mais toute opération d'écriture — insertions, mises à jour, suppressions — doit attendre. Dans MySQL avec InnoDB, le comportement de verrouillage varie selon le type d'opération et la version du moteur de stockage.

Pour une table servant des milliers d'utilisateurs, même quelques secondes de verrouillage en écriture peuvent créer un goulot d'étranglement. Les connexions applicatives s'empilent, les timeouts se propagent, et ce qui devait être une amélioration de performance devient un incident.

L'option concurrente que la plupart des équipes oublient

La plupart des bases de données majeures offrent un moyen de construire des index sans bloquer les écritures. PostgreSQL propose CREATE INDEX CONCURRENTLY. MySQL supporte ALGORITHM=INPLACE et LOCK=NONE sur les versions compatibles.

-- PostgreSQL : construit l'index sans bloquer les écritures
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL : construit l'index sans bloquer les écritures (InnoDB)
ALTER TABLE users ADD INDEX idx_users_email (email), ALGORITHM=INPLACE, LOCK=NONE;

L'approche concurrente prend plus de temps. CREATE INDEX CONCURRENTLY de PostgreSQL doit attendre la fin des transactions en cours, puis effectuer des passes supplémentaires pour capturer les modifications effectuées pendant la construction. Mais le compromis est clair : votre application continue de fonctionner pendant la construction de l'index.

L'erreur est de considérer la création concurrente d'index comme optionnelle ou avancée. Elle devrait être la valeur par défaut pour toute migration de production sur une table active. Écrivez-la explicitement dans vos scripts de migration. Ne vous fiez pas au comportement par défaut.

Les contraintes verrouillent aussi

Les contraintes de clé étrangère et d'unicité apportent leurs propres surprises en matière de verrouillage. Quand vous ajoutez une clé étrangère, la base de données valide que toutes les lignes existantes satisfont la relation. Cette validation parcourt toute la table et la verrouille pendant le processus.

PostgreSQL offre une échappatoire pragmatique : NOT VALID. Cela ajoute la contrainte uniquement pour les nouvelles données, en ignorant la validation sur les lignes existantes. Vous pouvez ensuite exécuter VALIDATE CONSTRAINT séparément, pendant les heures creuses.

-- Ajouter une clé étrangère sans valider les données existantes
ALTER TABLE orders ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Valider plus tard, quand le trafic est faible
ALTER TABLE orders VALIDATE CONSTRAINT fk_user_id;

Les contraintes d'unicité suivent une logique similaire. Ajouter UNIQUE sur une colonne nécessite de vérifier les doublons sur toute la table. Sur une grande table, cette analyse peut verrouiller les écritures pendant une durée inconfortable.

Planifiez les migrations d'index séparément

De nombreuses équipes regroupent toutes les modifications de schéma dans un seul fichier de migration : ajouter une colonne, créer un index, ajouter une clé étrangère. Cela fonctionne bien sur les petites tables. Sur les tables de production avec des millions de lignes, cela crée un point de défaillance unique.

Les modifications d'index et de contrainte méritent leurs propres fichiers de migration, planifiés séparément des modifications de structure de table. Certaines équipes vont plus loin et exécutent les migrations d'index manuellement, en dehors du pipeline automatisé, pour garder un contrôle strict sur le timing.

Une approche pratique :

• Exécutez les modifications de structure de table (ajout de colonne, modification de type) dans une migration
• Exécutez les modifications d'index et de contrainte dans des migrations séparées
• Planifiez les migrations d'index pendant les fenêtres de faible trafic
• Utilisez les options concurrentes explicitement dans chaque script de migration
• Validez les contraintes séparément de leur ajout

La vraie question

Une fois les index et les contraintes en place, vous devez vérifier qu'ils ne cassent pas l'application en cours d'exécution. Un index qui modifie les plans de requête peut provoquer des changements de performance inattendus. Une clé étrangère qui bloque une cascade de suppression peut stopper des tâches de fond.

Cela mène à une question plus large : comment garantir la sécurité des migrations quand les versions anciennes et nouvelles de votre application accèdent à la même base de données ? C'est le sujet de la prochaine discussion, mais le principe commence ici : traitez chaque modification de schéma comme un événement de production potentiel, pas seulement comme un script à exécuter.

Liste de vérification pour les migrations d'index et de contrainte

• Utilisez la création concurrente d'index (CREATE INDEX CONCURRENTLY ou équivalent) sur toute table avec des écritures actives
• Ajoutez les clés étrangères avec NOT VALID quand c'est possible, validez séparément
• Exécutez les migrations d'index dans des fichiers séparés des modifications de structure de table
• Planifiez les constructions d'index volumineuses pendant les fenêtres de faible trafic
• Surveillez la durée de verrouillage et les performances des requêtes pendant et après la migration
• Testez la migration sur une copie des données de production avant de l'exécuter en direct

Ce qu'il faut retenir

Un index n'est pas seulement un outil de performance. C'est une modification de schéma qui peut verrouiller votre table, mettre en file d'attente vos écritures et faire expirer vos utilisateurs. Traitez-le avec la même prudence que tout autre déploiement en production. Utilisez les options concurrentes, séparez vos migrations et planifiez pour la sécurité. La vitesse de requête que vous gagnez ne vaut rien si la migration elle-même casse l'application.