Comment une colonne non indexée a tué notre base de données

Un seul index manquant a transformé une API de 8 ms en un cauchemar de 8 secondes.

Nous avons effectué un test de charge avant une mise en production majeure. Le développement local fonctionnait parfaitement avec 100 enregistrements. Ensuite, nous avons simulé 500 utilisateurs sur 1,5 million de lignes avec des données de taille réelle (production).

Les résultats étaient mauvais :

  • Les temps de réponse de l'API sont passés de 45 ms à 8 000 ms.
  • Le CPU de la base de données a atteint 100 %.
  • Les pools de connexions ont été épuisés, provoquant des erreurs de timeout.

Nous avons consulté le journal des requêtes lentes (slow-query log) et avons trouvé un endpoint qui récupérait l'historique des commandes par ID utilisateur et par statut.

L'exécution de EXPLAIN ANALYZE sur PostgreSQL a révélé un Sequential Scan. Le moteur lisait les 1,5 million de lignes pour chaque requête car il n'y avait aucun index sur user_id.

Avec 100 requêtes simultanées, il a scanné 150 millions de lignes d'un coup.

Le correctif a pris cinq minutes.

Au lieu d'un simple index sur user_id, nous avons créé un index composite sur (user_id, status, created_at DESC). Cela a permis à la base de données de :

  • Filtrer par user_id.
  • Filtrer par status.
  • Retourner immédiatement les lignes les plus récentes.
  • Éviter des étapes de tri supplémentaires.

Nous avons utilisé CREATE INDEX CONCURRENTLY pour que la table reste déverrouillée pendant l'opération.

Après le correctif :

  • Le temps de requête est passé de 8 150 ms à 0,14 ms.
  • La latence de l'API est passée de 8 secondes à 12 ms.
  • L'utilisation du CPU est descendue de 100 % à moins de 8 %.

Leçons apprises :

  • Les tests locaux sont trompeurs ; 100 lignes ne représentent pas un million.
  • Indexez vos clés étrangères — la plupart des ORM omettent de le faire.
  • Exécutez EXPLAIN ANALYZE avant d'acheter du matériel supplémentaire.
  • Concevez vos index en fonction des requêtes que vous exécutez réellement.

Ne passez pas d'abord à l'échelle au niveau des serveurs. Passez à l'échelle au niveau des requêtes.

Source : https://dev.to/mia_keller_ffd2584c046ecb/how-an-unindexed-column-silently-killed-our-database-under-load-and-the-5-minute-fix-m32