pg_stat_statements + auto_explain : diagnostiquer les requêtes lentes PostgreSQL — 2026

📍 Article principal : PostgreSQL en surcharge pour PME. Ce tutoriel installe pg_stat_statements et auto_explain pour identifier précisément les requêtes lentes et leur appliquer les bons correctifs.

Diagnostiquer un PostgreSQL lent à l’aveugle est un cauchemar — augmenter la RAM, multiplier les index, redémarrer en espérant. La méthode rigoureuse exige des outils. PostgreSQL fournit deux extensions natives complémentaires : pg_stat_statements qui agrège les statistiques d’exécution de chaque requête (fréquence, temps moyen, lignes retournées), et auto_explain qui capture automatiquement le plan d’exécution des requêtes au-delà d’un seuil de durée. Ensemble, elles permettent d’identifier en quelques minutes les top 10 requêtes consommatrices et de comprendre pourquoi elles sont lentes. Ce tutoriel installe ces outils, montre comment les exploiter, et présente cinq scénarios concrets de diagnostic avec leur correctif.

Prérequis

• PostgreSQL 16 ou 17 en production avec accès superuser
• Tuning de base déjà appliqué (voir tutoriel tuning) et idéalement partitioning si nécessaire (voir tutoriel partitioning)
• Notions SQL et plans d’exécution
• Niveau : avancé
• Temps estimé : 3 à 4 heures

Étape 1 — Installer pg_stat_statements

pg_stat_statements est livré avec PostgreSQL mais pas activé par défaut. Modifier postgresql.conf pour ajouter le module à shared_preload_libraries : shared_preload_libraries = 'pg_stat_statements'. Augmenter aussi pg_stat_statements.max à 10000 pour stocker plus d’entrées et pg_stat_statements.track à all pour capturer toutes les requêtes y compris dans les fonctions imbriquées.

Redémarrer PostgreSQL pour que le module soit chargé. Puis créer l’extension dans la base à analyser : CREATE EXTENSION pg_stat_statements. À partir de cet instant, chaque requête exécutée alimente la vue pg_stat_statements qui contient les statistiques agrégées. Laisser tourner au moins une journée pour avoir un échantillon représentatif de l’activité réelle.

Étape 2 — Identifier les top requêtes consommatrices

La requête fondamentale pour identifier les requêtes les plus coûteuses :

SELECT
  substring(query, 1, 80) as query,
  calls,
  round(total_exec_time::numeric, 2) as total_ms,
  round(mean_exec_time::numeric, 2) as mean_ms,
  round(stddev_exec_time::numeric, 2) as stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Cette vue triée par total_exec_time identifie les requêtes qui consomment le plus de temps cumulé sur la base. Une requête appelée 1 million de fois à 5 ms moyennes représente un total de 5 000 secondes — souvent plus problématique qu’une requête appelée 100 fois à 1 seconde. C’est cette consommation cumulée qui cause les ralentissements perçus, pas les requêtes individuellement lentes mais rares.

Examiner les 10 premières requêtes du résultat. Pour chacune : comprendre l’origine applicative (chercher la requête dans le code source par grep), évaluer si la fréquence est normale, mesurer le mean_exec_time. Une requête appelée à très haute fréquence avec un mean_ms supérieur à 50 est candidate prioritaire à l’optimisation.

Étape 3 — Activer auto_explain pour les requêtes lentes

auto_explain capture automatiquement le plan d’exécution des requêtes au-delà d’un seuil de durée. Configuration recommandée : session_preload_libraries = 'auto_explain', auto_explain.log_min_duration = 1000 (1 seconde), auto_explain.log_analyze = on, auto_explain.log_buffers = on, auto_explain.log_format = json.

Avec ces paramètres, toute requête de plus d’1 seconde voit son plan d’exécution complet (avec mesures réelles) écrit dans le journal PostgreSQL au format JSON. Ce journal devient une mine d’or pour le diagnostic — chaque ralentissement laisse une trace exploitable même longtemps après. L’overhead reste modéré (quelques pourcents de CPU) tant que le seuil reste raisonnable. Pour le diagnostic intensif, baisser temporairement à 100 ms ; pour la production normale, garder à 1000 ms.

Étape 4 — Lire un plan EXPLAIN ANALYZE

Comprendre les plans EXPLAIN ANALYZE est la compétence clé du diagnostic PostgreSQL. Trois éléments à scruter en priorité. Premier : les Seq Scan sur grosses tables, qui indiquent un index manquant. Deuxième : les écarts importants entre rows estimés et rows actuels, qui signalent des statistiques obsolètes (lancer ANALYZE). Troisième : les Sort sur disque (Sort Method: external merge), qui indiquent un work_mem insuffisant.

Outils visualisation du plan : explain.depesz.com et explain.dalibo.com proposent une visualisation graphique des plans qui rend la lecture beaucoup plus facile pour les non-experts. Coller un plan JSON, voir le diagramme arborescent avec coloration des nœuds coûteux. Pour les développeurs, ces outils accélèrent l’apprentissage de la lecture des plans.

Étape 5 — Cinq scénarios concrets et leurs correctifs

Premier scénario : Seq Scan sur une table de 5 millions de lignes pour un filtre WHERE status = 'pending'. Diagnostic : index manquant. Correctif : CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status) WHERE status = 'pending'. Index partiel qui ne couvre que les lignes pending — beaucoup plus petit qu’un index complet, beaucoup plus rapide. Gain typique : 100 fois.

Deuxième scénario : requête JOIN qui scanne toute la table products à chaque exécution. Diagnostic : foreign key sans index. Correctif : ajouter l’index sur la colonne foreign key. Erreur classique car PostgreSQL ne crée pas automatiquement les index sur les FK contrairement à MySQL. Gain typique : 10 à 50 fois.

Troisième scénario : requête qui devient lente après quelques semaines de production. Diagnostic : bloat accumulé sur la table. Correctif : lancer VACUUM (FULL, ANALYZE) orders hors heures ouvrées, ou utiliser pg_repack pour ne pas bloquer. Gain typique : récupérer la performance initiale, parfois 10 fois meilleur que avant.

Quatrième scénario : requête analytique complexe qui tape un Sort Method: external merge dans le plan. Diagnostic : work_mem insuffisant. Correctif : SET LOCAL work_mem = '256MB' au début de la transaction. Gain typique : 10 à 30 fois sur les tris complexes.

Cinquième scénario : requête sur colonne JSONB lente. Diagnostic : pas d’index GIN sur la colonne. Correctif : CREATE INDEX idx_metadata ON orders USING GIN (metadata jsonb_path_ops). Gain typique : 100 fois sur les requêtes avec opérateur ? ou @> sur le JSONB.

Étape 6 — Boucle d’amélioration continue

Le diagnostic n’est pas un acte ponctuel mais un processus continu. Mettre en place un rituel hebdomadaire : chaque lundi, ouvrir la vue pg_stat_statements et identifier le top 5 requêtes consommatrices. Les comparer à la semaine précédente. Si une nouvelle requête lente apparaît, l’investiguer immédiatement. Si une requête s’aggrave (mean_exec_time qui grimpe), chercher la cause (croissance de table, statistiques obsolètes, bloat).

Cette discipline simple détecte les régressions avant qu’elles ne deviennent perceptibles par les utilisateurs. Pour automatiser, créer un script Python ou Bash qui exporte chaque jour le top 20 des requêtes vers un fichier versionné Git. La diff Git montre les changements et révèle automatiquement les nouvelles requêtes problématiques apparues entre deux versions de l’application.

Outils complémentaires de diagnostic

Au-delà de pg_stat_statements et auto_explain, plusieurs outils enrichissent l’arsenal de diagnostic. pgBadger analyse les journaux PostgreSQL et produit un rapport HTML détaillé avec graphiques : requêtes les plus fréquentes, requêtes les plus lentes, distribution temporelle, erreurs récurrentes. Lancer pgBadger une fois par semaine sur les logs de la semaine donne une vision panoramique précieuse. pg_activity est un top-like temps réel pour PostgreSQL — affiche en direct les requêtes en cours, leur durée, les locks, l’utilisation CPU. Indispensable pour le diagnostic en direct d’un incident.

L’extension pg_qualstats capture les prédicats utilisés dans les WHERE et identifie les colonnes qui mériteraient un index — analyse complémentaire à pg_stat_statements qui se concentre sur les requêtes complètes. Pour les bases très chargées, pgmetrics fournit un snapshot complet de l’état du serveur en une commande : configuration, taille des tables, fragmentation, activité — pratique pour les health checks périodiques et les revues de capacité.

Anti-patterns SQL fréquents

Quelques anti-patterns reviennent fréquemment dans les diagnostics. Les fonctions sur les colonnes filtrées invalident les index — WHERE LOWER(email) = ? ne peut pas utiliser un index sur email. Solution : créer un index fonctionnel CREATE INDEX ON users (LOWER(email)) ou refactoriser pour stocker l’email en lowercase à l’écriture. Les comparaisons avec des types différents forcent aussi des conversions implicites qui invalident les index — WHERE id = '42' sur une colonne integer.

Les COUNT(*) sur grosses tables sans filtre sont coûteux car PostgreSQL doit scanner tout pour le calcul exact. Pour les estimations, utiliser SELECT reltuples FROM pg_class WHERE relname = 'orders' qui donne une approximation instantanée. Les requêtes avec OFFSET élevé deviennent lentes — OFFSET 100000 oblige PostgreSQL à scanner et jeter 100 000 lignes avant de retourner les suivantes. Solution pour la pagination : keyset pagination avec WHERE created_at < last_seen_at ORDER BY created_at DESC LIMIT 50, qui reste constant en performance quel que soit la profondeur.

Erreurs fréquentes

Adaptation au contexte ouest-africain

Pour les PME ouest-africaines avec des équipes techniques restreintes, l’approche méthodique pg_stat_statements transforme la maintenance corrective subie en optimisation proactive maîtrisée. Au lieu d’attendre les plaintes utilisateurs pour découvrir un ralentissement, l’équipe identifie chaque semaine les optimisations à apporter et les applique dans les fenêtres de maintenance prévues. Cette discipline simple positionne la PME dans les meilleures pratiques mondiales avec un investissement minime — quelques heures hebdomadaires d’un développeur senior.

Intégration avec Grafana et alerting

Pour exploiter pg_stat_statements à l’échelle, brancher Grafana avec datasource PostgreSQL pointant directement sur la base de production. Construire un dashboard avec quatre panels essentiels : top 10 requêtes par temps total des dernières 24 heures, évolution du temps moyen sur les requêtes critiques, taux de cache hit ratio, nombre de requêtes par seconde. Ce dashboard partagé entre développeurs et ops crée un langage commun et facilite les conversations sur la performance.

Configurer aussi des alertes proactives. Dès qu’une requête connue voit son temps moyen doubler par rapport à la baseline historique, déclencher une alerte. Cette détection précoce permet d’investiguer avant que les utilisateurs ne perçoivent le ralentissement. Les outils comme PostgreSQL Cluster Monitoring ou Datadog Database Monitoring proposent ces alertes hors-de-la-boîte avec un setup initial de quelques heures.

Workflow d’incident : méthode systématique

Quand un ralentissement est signalé en production, suivre une procédure systématique évite la panique et accélère la résolution. Étape 1, ouvrir pg_activity pour voir les requêtes actives en temps réel — repérer immédiatement les requêtes longues ou les locks. Étape 2, lancer une requête sur pg_stat_statements pour voir les top requêtes des dernières heures et identifier celle qui a explosé. Étape 3, prendre EXPLAIN ANALYZE sur la requête problématique pour voir ce qui a changé dans le plan.

Étape 4, comparer avec l’EXPLAIN ANALYZE historique (si on a une trace dans les logs auto_explain). Le diff entre l’ancien plan et le nouveau plan révèle souvent la cause : nouvel index manquant après ajout de données massives, statistiques obsolètes après un chargement, dégradation par bloat. Étape 5, appliquer le correctif : ANALYZE table, création d’index, VACUUM. Étape 6, valider que la performance revient et documenter l’incident pour référence future. Cette méthode systématique transforme une expérience douloureuse en routine maîtrisée.

Formation continue de l’équipe

La compétence en diagnostic PostgreSQL ne se transmet pas par lecture seule — elle s’acquiert par pratique répétée. Organiser des sessions hebdomadaires de 30 minutes où l’équipe analyse ensemble une requête lente identifiée la veille. Chaque développeur prend son tour pour proposer un diagnostic et un correctif. Ce rituel crée une culture commune autour des performances et fait monter en compétence l’ensemble de l’équipe technique.

Pour aller plus loin, l’équipe peut suivre des formations en ligne (Postgres Conference talks gratuits, courses Bruce Momjian sur YouTube), et idéalement participer une fois par an à un événement Postgres en présentiel ou en ligne. Cet investissement modeste dans la formation continue construit une compétence technique différenciante pour la PME et améliore la rétention des talents — les développeurs apprécient les organisations qui investissent dans leur montée en compétence.

Cette montée en compétence collective transforme PostgreSQL d’une boîte noire intimidante en outil maîtrisé qui sert pleinement les ambitions techniques et commerciales de la PME, devenant un levier de différenciation durable face aux concurrents qui restent dans l’ignorance technique de leur propre socle de données.

L’investissement dans la maîtrise de PostgreSQL est l’un des actifs techniques les plus durables et transférables qu’une équipe puisse construire : les compétences acquises servent pendant des décennies et restent pertinentes même quand les frameworks applicatifs autour évoluent.

Pour aller plus loin

🔝 Retour à l’article principal : PostgreSQL en surcharge pour PME. Tutoriels précédents : tuning shared_buffers, partitioning. Documentation pg_stat_statements : postgresql.org/docs/17/pgstatstatements, auto_explain : postgresql.org/docs/17/auto-explain, visualisation plans : explain.dalibo.com.

La maîtrise de pg_stat_statements et auto_explain transforme un développeur en véritable spécialiste PostgreSQL. Cette compétence devient rapidement irremplaçable dans une PME en croissance — savoir identifier en cinq minutes la cause d’un ralentissement et appliquer le bon correctif distingue les équipes qui maintiennent une plateforme performante de celles qui subissent les incidents répétitifs sans en comprendre les causes profondes.