Optimisation des requêtes de base de données : index, plans d'exécution et partitionnement

Un seul index manquant peut transformer une requête de 2 millisecondes en une analyse de table de 20 secondes. À mesure que votre base de données passe de milliers à des millions de lignes, la différence entre une requête optimisée et non optimisée est la différence entre une application réactive et une application qui expire sous charge. L'optimisation des bases de données offre le meilleur retour sur le temps d'ingénierie parmi tous les travaux de performance que vous pouvez effectuer.

Points clés à retenir

• EXPLAIN ANALYZE est votre outil de diagnostic le plus puissant : apprenez à lire les plans d'exécution avant d'optimiser quoi que ce soit.
• Choisissez stratégiquement les types d'index : B-tree pour l'égalité et la plage, GIN pour le texte intégral et JSONB, index partiels pour les sous-ensembles filtrés
• Les requêtes N+1 sont les plus courantes en termes de performances dans les applications basées sur ORM : détectez-les précocement grâce à la journalisation des requêtes.
• Le partitionnement des tables devient essentiel lorsque les tables dépassent 10 à 50 millions de lignes, réduisant ainsi le temps de planification des requêtes et permettant une gestion efficace du cycle de vie des données.

---

Lecture des plans d'exécution avec EXPLAIN ANALYZE

Avant d'optimiser une requête, vous devez comprendre comment PostgreSQL l'exécute actuellement. EXPLAIN ANALYZE exécute la requête et affiche le plan d'exécution réel avec des données de synchronisation réelles.

Un résultat de base EXPLAIN ANALYZE vous montre la stratégie choisie par le planificateur, le nombre de lignes estimé par rapport au nombre réel et le temps passé à chaque étape. Les indicateurs clés sur lesquels se concentrer sont :

• Seq Scan -- la base de données lit chaque ligne du tableau. Acceptable pour les petites tables (moins de 10 000 lignes) mais signal d’alarme pour les plus grandes.
• Index Scan -- la base de données utilise un index pour trouver efficacement les lignes correspondantes. C'est ce que vous souhaitez pour les requêtes filtrées sur les grandes tables.
• Index Only Scan -- la base de données répond entièrement à la requête à partir de l'index sans toucher à la table. Le type de numérisation le plus rapide.
• Nested Loop -- joint les tables en analysant la table interne une fois par ligne dans la table externe. Efficace lorsque l’analyse interne utilise un index.
• Hash Join -- construit une table de hachage d'un côté de la jointure, puis la sonde de l'autre. Efficace pour les ensembles de résultats plus volumineux.
• Tri -- une étape de tri explicite, souvent pour ORDER BY. Surveillez les tris qui se répandent sur le disque (indiqués par « Méthode de tri : fusion externe »).

Que rechercher

Le signal le plus important dans un plan d’exécution est l’écart entre les lignes estimées et réelles. Lorsque PostgreSQL estime 10 lignes mais en trouve 100 000, il a choisi le mauvais plan. Cela se produit lorsque les statistiques de la table sont obsolètes : exécutez ANALYZE sur la table pour les mettre à jour.

Surveillez les analyses séquentielles sur les grandes tables, les tris sans index et les boucles imbriquées avec des analyses séquentielles sur la table interne. Chacun de ces modèles indique un index manquant ou une requête qui doit être réécrite.

---

Types d'index et quand les utiliser

PostgreSQL propose plusieurs types d'index, chacun optimisé pour différents modèles de requête. Choisir le bon type est essentiel : un index GIN sur une colonne qui n'a besoin que de contrôles d'égalité gaspille le stockage et ralentit les écritures sans améliorer les lectures.

Index B-tree

B-tree est le type d’index par défaut et le plus polyvalent. Il prend en charge l'égalité (=), la plage (<, >, BETWEEN), le tri (ORDER BY) et la correspondance de modèles de préfixe (LIKE 'abc%'). Pour la plupart des colonnes des clauses WHERE, JOIN et ORDER BY, un index B-tree est le bon choix.

Les index composites combinent plusieurs colonnes en un seul arbre B. L'ordre des colonnes est important : l'index sur (statut, créé_at) prend en charge efficacement le filtrage des requêtes sur le statut seul ou sur le statut et créé_at, mais pas uniquement sur créé_at. Placez la colonne la plus sélective en premier et la colonne utilisée pour le filtrage par plage en dernier.

Index GIN

Les index GIN excellent dans la recherche dans les valeurs composites. Ils sont essentiels pour la recherche en texte intégral (colonnes tsvector), les requêtes de confinement JSONB (@>, ?) et les requêtes de chevauchement de tableaux (&&, @>). Les index GIN sont plus volumineux et plus lents à mettre à jour que les index B-tree, utilisez-les donc uniquement là où B-tree ne peut pas servir le modèle de requête.

Pour les colonnes JSONB qui stockent des attributs flexibles, un index GIN sur la colonne entière prend en charge toute requête basée sur une clé. Pour les colonnes dans lesquelles vous interrogez uniquement des clés spécifiques, un index B-tree sur une colonne ou une expression générée est plus efficace.

Index partiels

Les index partiels indexent uniquement les lignes correspondant à une condition WHERE. Ils sont puissants pour les tables dans lesquelles les requêtes filtrent systématiquement un petit sous-ensemble de données.

Par exemple, si votre table de commandes comporte 10 millions de lignes mais que vous interrogez presque exclusivement les commandes actives (5 % de la table), un index partiel sur (customer_id, create_at) WHERE status = 'active' est 20 fois plus petit qu'un index complet et tout aussi rapide pour vos requêtes réelles.

---

Détection et correction des requêtes N+1

Le problème de requête N+1 est le problème de performances le plus courant dans les applications utilisant des ORM. Cela se produit lorsque le code charge une liste de N enregistrements, puis exécute une requête supplémentaire par enregistrement pour charger les données associées, ce qui donne un total de N+1 requêtes au lieu de 1-2.

Comment se produisent les requêtes N+1

Pensez à charger une liste de commandes avec les noms de leurs clients. Une implémentation naïve charge la liste des commandes (1 requête), puis pour chaque commande, charge le client (N requêtes). Avec 100 commandes, cela génère 101 allers-retours dans la base de données. À 1 ms par requête, cela équivaut à 101 ms – mais sous une charge simultanée avec conflit de pool de connexions, cela peut facilement atteindre 500 ms ou plus.

Méthodes de détection

1. Journalisation des requêtes - activez temporairement la journalisation des requêtes PostgreSQL et recherchez des requêtes identiques répétées avec des valeurs de paramètres différentes
2. Journalisation au niveau ORM -- Drizzle ORM, Prisma et TypeORM prennent tous en charge la journalisation des requêtes qui affiche chaque instruction SQL exécutée.
3. Outils APM – Datadog, New Relic et Sentry peuvent regrouper les requêtes par point de terminaison et mettre automatiquement en évidence N+1 modèles.
4. pg_stat_statements -- cette extension PostgreSQL suit les statistiques d'exécution des requêtes et révèle les modèles de requêtes identiques fréquemment exécutés

Correction des requêtes N+1

Le correctif dépend de votre ORM et de votre modèle de requête :

• Chargement impatient -- indique à l'ORM de charger les données associées dans la requête initiale à l'aide de JOIN. Dans Drizzle, utilisez l'option with dans les générateurs de requêtes.
• Chargement par lots -- collectez tous les ID de clé étrangère, puis chargez les enregistrements associés dans une seule requête WHERE id IN (...). Il s'agit du modèle DataLoader.
• Dénormalisation : pour les cas d'utilisation nécessitant beaucoup de lecture, stockez les données associées directement dans l'enregistrement parent. Échangez la complexité d’écriture contre des performances de lecture.

---

Techniques de réécriture de requêtes

Parfois, la requête elle-même a besoin d'être restructurée, et pas seulement de meilleurs index.

Sous-requête en conversion JOIN

Les sous-requêtes corrélées s'exécutent une fois par ligne dans la requête externe. Les convertir en JOIN permet à PostgreSQL d'utiliser des stratégies de jointure plus efficaces.

Au lieu de sélectionner les commandes avec une sous-requête qui recherche la dernière date de commande par client, réécrivez-la sous forme de JOIN avec une table dérivée ou une fonction de fenêtre. La version JOIN permet à PostgreSQL de choisir entre une boucle imbriquée, une jointure par hachage et une jointure par fusion en fonction de la distribution des données.

Expressions de table communes (CTE)

Dans PostgreSQL 12 et versions ultérieures, les CTE sont intégrés par défaut, ce qui signifie que l'optimiseur peut y insérer des prédicats. Utilisez les CTE pour plus de lisibilité sans vous soucier des limites de performances. Dans les cas où vous souhaitez explicitement une matérialisation (pour éviter la réexécution de sous-requêtes coûteuses), ajoutez le mot clé MATERIALIZED.

Fonctions de fenêtre vs GROUP BY

Lorsque vous avez besoin à la fois de lignes de détail et d'agrégats, les fonctions de fenêtre évitent le besoin d'une jointure automatique ou d'une sous-requête. Le calcul d'un total cumulé, le classement au sein des groupes ou la comparaison de chaque ligne à la moyenne du groupe sont tous plus efficaces avec les fonctions de fenêtre qu'avec les sous-requêtes corrélées.

---

Stratégies de partitionnement de tables

Lorsque les tables dépassent 10 à 50 millions de lignes, même les requêtes bien indexées ralentissent en raison de la profondeur de l'index, de la surcharge du vide et de la complexité du planificateur. Le partitionnement divise une grande table en morceaux physiques plus petits tout en conservant une seule interface de table logique.

Types de partitions

Partitionnement de la plage par date

Le modèle le plus courant est le partitionnement mensuel par colonne d'horodatage. Les données de chaque mois résident dans sa propre partition. Les requêtes qui filtrent par date analysent automatiquement uniquement les partitions pertinentes (élagage des partitions).

Avantages du partitionnement temporel :

• Performances des requêtes -- les requêtes pour les données récentes analysent uniquement les partitions récentes
• Maintenance -- VACUUM et ANALYZE s'exécutent plus rapidement sur des partitions plus petites
• Cycle de vie des données : la suppression d'anciennes partitions est instantanée par rapport à la suppression de millions de lignes.
• Efficacité de la sauvegarde - sauvegardez uniquement les partitions récentes pour une récupération à un moment précis

Considérations sur le partitionnement

Le partitionnement ajoute de la complexité. Chaque requête doit inclure la clé de partition dans sa clause WHERE pour que l'élagage de partition fonctionne. Les contraintes uniques doivent inclure la clé de partition. Les clés étrangères faisant référence à des tables partitionnées ont des limites. Démarrez le partitionnement uniquement lorsque vous avez mesuré que la taille de la table entraîne une dégradation des performances.

---

Réglage de la configuration PostgreSQL

La configuration par défaut de PostgreSQL est conservatrice et conçue pour fonctionner sur un matériel minimal. Les charges de travail de production bénéficient du réglage des paramètres clés.

Ces paramètres doivent être adaptés à votre matériel et à votre charge de travail spécifiques. Surveillez pg_stat_bgwriter, pg_stat_activity et pg_stat_user_tables pour vérifier que les modifications améliorent les performances.

---

Questions fréquemment posées

Combien d'index une table doit-elle avoir ?

Il n'y a pas de limite fixe, mais chaque index ralentit les opérations INSERT, UPDATE et DELETE car l'index doit être conservé. Une bonne règle générale consiste à créer des index pour les colonnes qui apparaissent dans les clauses WHERE, JOIN ON et ORDER BY de vos requêtes les plus fréquentes. Utilisez pg_stat_user_indexes pour rechercher les index inutilisés qui peuvent être supprimés.

Dois-je utiliser des clés primaires UUID ou entières pour des raisons de performances ?

Les clés primaires entières (BIGSERIAL) sont plus rapides pour les jointures et l'indexation car elles sont plus petites (8 octets contre 16 octets) et naturellement ordonnées. Les UUID offrent une unicité globale sans coordination, ce qui est important pour les systèmes distribués. Pour la plupart des applications, utilisez des UUID pour les identifiants externes et des entiers pour les jointures internes.

Quand dois-je passer d'une base de données unique aux réplicas en lecture ?

Lorsque votre charge de travail de lecture dépasse 70 à 80 % de la capacité de votre base de données, ou lorsque les requêtes de reporting sont en concurrence avec les requêtes transactionnelles pour les ressources. Les réplicas en lecture gèrent la charge de lecture tandis que le réplica principal se concentre sur les écritures. Cela est généralement nécessaire pour 5 000 à 10 000 utilisateurs simultanés pour une application Web typique.

Comment gérer les requêtes lentes en production sans temps d'arrêt ?

Créez des index avec l'option CONCURRENTLY pour éviter de verrouiller la table. Utilisez pg_stat_statements pour identifier les requêtes les plus lentes. Déployez des optimisations de requêtes derrière les indicateurs de fonctionnalités. Pour les modifications de schéma qui réécrivent les tables, utilisez des outils comme pg_repack pour réorganiser les tables sans verrouillage.

---

Quelle est la prochaine étape

L'optimisation des bases de données est la base des performances de la plateforme. Commencez par activer pg_stat_statements, identifiez vos requêtes les plus lentes et traitez-les systématiquement avec EXPLAIN ANALYZE. Ajoutez des index manquants, corrigez les modèles N+1 et envisagez le partitionnement de vos plus grandes tables.

Pour une vision plus large des performances, consultez notre guide des piliers sur faire évoluer votre plateforme commerciale de la startup à l'entreprise. Pour en savoir plus sur la prochaine couche d'optimisation, lisez notre guide sur les stratégies de mise en cache avec la mise en cache Redis, CDN et HTTP.

ECOSIRE fournit une optimisation de base de données experte pour les plates-formes basées sur PostgreSQL, notamment Odoo ERP et les applications personnalisées. Contactez-nous pour un audit des performances de la base de données.

---

Publié par ECOSIRE — aider les entreprises à évoluer grâce à des solutions basées sur l'IA dans Odoo ERP, Shopify eCommerce et OpenClaw AI.