La base de données est le goulet d'étranglement le plus courant des applications web qui ralentissent. Avant d'augmenter la taille des serveurs ou de refactoriser l'architecture, une optimisation ciblée de la base de données résout souvent 80 % des problèmes de performance.
Nticstudio diagnostique et optimise les bases de données dans le cadre de l'offre Run.
Identifier les requêtes problématiques
La première étape est l'identification des requêtes lentes. PostgreSQL propose `pg_stat_statements` (extension qui trace les requêtes les plus fréquentes et les plus lentes) et `EXPLAIN ANALYZE` (plan d'exécution détaillé d'une requête spécifique). MySQL a des outils équivalents avec le slow query log et `EXPLAIN`.
Les requêtes qui méritent attention : celles qui prennent plus de 100 ms, celles qui sont exécutées des milliers de fois par heure, et celles qui font un scan séquentiel de table (Seq Scan dans PostgreSQL) sur des tables volumineuses.
Index : le levier le plus impactant
L'ajout d'index sur les colonnes fréquemment filtrées ou joinées est souvent le correctif le plus simple et le plus impactant. Une requête qui parcourt 1 million de lignes sans index peut être ramenée à quelques centaines de lignes avec le bon index.
Les index à créer en priorité : colonnes utilisées dans les clauses WHERE, colonnes de jointure (foreign keys), colonnes de tri fréquentes. Les index composites (sur plusieurs colonnes) sont nécessaires quand les filtres combinent systématiquement les mêmes colonnes.
Attention : les index accélèrent les lectures mais ralentissent les écritures. Sur des tables avec beaucoup d'insertions, chaque index supplémentaire a un coût.
Requêtes N+1 : le piège des ORMs
Le problème N+1 est l'un des bugs de performance les plus courants avec les ORMs (Sequelize, Prisma, SQLAlchemy) : au lieu d'une requête qui charge une liste avec ses relations, l'application fait N+1 requêtes (une pour la liste, une pour chaque élément). Sur une liste de 100 éléments, cela génère 101 requêtes au lieu d'une seule.
La solution : utiliser les mécanismes d'eager loading de l'ORM (include/join/prefetch_related). L'outil `bullet` (Ruby) ou le logging des requêtes en développement permettent de détecter automatiquement ces patterns.
Configuration et paramètres PostgreSQL
La configuration par défaut de PostgreSQL est conservative. Pour une application en production, plusieurs paramètres méritent d'être ajustés selon la mémoire disponible : `shared_buffers` (cache en mémoire), `work_mem` (mémoire pour les tris et agrégations), `effective_cache_size` (estimation de la mémoire disponible pour le cache OS).
Ces réglages peuvent améliorer les performances de 20 à 40 % sans modifier une ligne de code. Ils nécessitent un redémarrage du serveur PostgreSQL et doivent être testés sur staging avant la production.