Imagine la pull request. Une migration, une douzaine de lignes, qui ajoute un index sur customer_id. Le relecteur l'approuve en quatre-vingt-dix secondes environ, et pourquoi pas ? La colonne apparaît dans la clause WHERE, donc l'index a l'air évidemment juste. Ce que personne n'a ouvert, c'est la méthode du repository que cet index était censé servir. Elle se trouve trois dossiers plus loin et s'est vu ajouter un tri depuis la dernière fois que quelqu'un l'a lue. Je pense que la plupart de nos bugs d'indexation commencent exactement là. Un index, c'est un contrat passé avec une requête précise, et dans un projet PHP typique, le contrat s'écrit dans un fichier pendant que la requête vit dans un autre, si bien que personne ne vérifie jamais que les deux sont toujours d'accord.

SELECT * FROM order_events
WHERE customer_id = 8842
AND created_at > now() - interval '30 days'
ORDER BY created_at DESC;

L'article récent de Farhad Ali sur Medium montre ce que cet écart coûte. Son équipe avait une table PostgreSQL appelée order_events, environ quatre millions de lignes retraçant le cycle de vie des commandes, et une requête qui récupérait les événements d'un client sur les trente derniers jours, du plus récent au plus ancien. Ils avaient indexé customer_id tout seul. EXPLAIN ANALYZE montrait que le planificateur ignorait cet index et faisait le travail de la manière la plus coûteuse, notamment en triant tout l'historique d'un client par created_at après avoir trouvé les lignes. Remplacer cet index par un index composite sur (customer_id, created_at DESC) a fait passer la requête d'un peu plus de quatre secondes à 9 millisecondes. Le détail qui me reste en tête, c'est que c'était la troisième fois. Le même décalage avait déjà frappé une appli de prise de rendez-vous médicaux et un outil d'analyse interne pour un client dans la logistique.

Trois projets et une seule cause, ça dessine un schéma, et je mettrais ce schéma sur le compte de notre façon de travailler plutôt que sur un développeur en particulier. Pense à la manière dont une requête évolue vraiment dans une appli Laravel ou Symfony. Quelqu'un ajoute orderByDesc('created_at') à une chaîne de query builder parce que le product owner veut les éléments les plus récents en haut. Quelques mois plus tard, quelqu'un d'autre ajoute un scope qui fait une jointure sur une deuxième table. Dans le diff, chaque changement ressemble à une petite retouche d'affichage. Aucun ne touche à une migration, donc l'index reste exactement tel qu'il était le jour de sa création, pendant que la requête pour laquelle il a été pensé devient peu à peu une autre requête.

Le ORDER BY est le vrai piège, parce que presque personne n'y voit une décision de performance. On le lit comme de la mise en forme. Pour PostgreSQL, pourtant, c'est une exigence : soit un index lui livre les lignes déjà dans cet ordre, soit la base rassemble toutes les lignes correspondantes et les trie elle-même. Prends un client fidèle depuis des années, avec des dizaines de milliers d'événements. Ce tri tourne à chaque chargement de son tableau de bord, et quand quelques-unes de ces requêtes s'empilent en même temps, ton pool PHP-FPM se remplit de workers qui ne font qu'attendre Postgres.

Alors voilà ce que je propose. Fais en sorte que l'index et la requête se connaissent. La méthode du repository reçoit un commentaire qui nomme l'index dont elle dépend, et la migration un commentaire qui nomme la méthode pour laquelle il a été créé. Modifier la chaîne du builder de cette méthode devrait déclencher en revue la même question qu'un changement de schéma : l'index colle-t-il encore à ce que demande la requête ? Si ton équipe aime automatiser, un job de CI peut remplir une table de données et lancer EXPLAIN sur tes requêtes les plus sollicitées. Dans tous les cas, la personne qui change le tri doit être celle qui se pose la question de l'index.

Le meilleur argument contre cette idée est sérieux, et je le prends au sérieux. Les index sont une infrastructure partagée. Si tu raisonnes en un index par requête, une table d'événements finit avec quatorze index, et chaque INSERT doit les mettre à jour tous les quatorze. Sur un journal très sollicité en écriture comme order_events, ça se traduit par des insertions plus lentes, un autovacuum plus occupé et un planificateur qui doit choisir entre des quasi-doublons. En plus, les statistiques du planificateur dérivent, donc un index parfait en mars peut être ignoré en novembre. Lie trop étroitement les index à des requêtes individuelles et tu échanges des lectures lentes contre des écritures lentes et un schéma auquel personne n'ose toucher.

Je reste quand même du côté du couplage, et c'est justement à cause de cet encombrement. Les index s'accumulent parce que personne ne sait à qui ils appartiennent. Un index sans consommateur identifié ne peut pas être supprimé sans risque, donc il ne l'est jamais, et le développeur suivant en ajoute un autre à côté, par précaution. Quand chaque index liste les requêtes qui en dépendent, en fusionner deux devient l'affaire de quelques minutes : tu sais exactement sur quelles requêtes relancer EXPLAIN ANALYZE, et tu peux consulter pg_stat_user_indexes pour voir lesquels ne sont jamais parcourus. Savoir à qui appartient chaque index, c'est ce qui permet de faire le ménage. L'ignorance, elle, te mène tout droit aux quatorze.

Je connais des équipes qui gèrent ça avec des conventions de nommage, d'autres qui gardent un fichier markdown à côté des migrations, et au moins une qui vérifie les compteurs idx_scan chaque trimestre et supprime tout ce qui est encore à zéro. Je suis curieux de savoir ce qui tient vraiment le coup après deux ans et trois rotations de développeurs. Dans ton code, comment quelqu'un qui modifie une requête découvre-t-il de quel index elle dépend ? Et est-ce que ça t'a déjà permis de rattraper un tableau de bord à quatre secondes avant qu'un client ne tombe dessus ?