Comment auditer les requêtes lentes d'une base PostgreSQL

Une base PostgreSQL qui ralentit n'a presque jamais un seul coupable. Le ralentissement vient d'une poignée de requêtes qui s'exécutent souvent, ou de quelques requêtes lentes très coûteuses. Identifier précisément ces requêtes est le préalable à toute optimisation.

PostgreSQL fournit nativement ce qu'il faut pour les détecter. Les outils tiers ajoutent du confort, mais le socle est dans le serveur. Cet article décrit les sources de données disponibles et comment les combiner pour bâtir un audit fiable.

pg_stat_statements, le point de départ

L'extension pg_stat_statements est livrée avec PostgreSQL et agrège les statistiques d'exécution par requête normalisée. Elle est activée via shared_preload_libraries dans postgresql.conf, puis créée dans la base avec CREATE EXTENSION pg_stat_statements.

Une fois en place, la vue pg_stat_statements expose pour chaque empreinte de requête le nombre d'appels, le temps total, le temps moyen, les lignes retournées, et les blocs lus en cache et sur disque. Un simple SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20 donne une vision claire de ce qui consomme la base.

Cet outil ne capture pas les requêtes individuelles, il les regroupe par forme. Les littéraux sont remplacés par des paramètres. C'est précisément ce qui le rend utilisable en production sans saturer le stockage.

log_min_duration_statement pour les requêtes ponctuellement lentes

pg_stat_statements donne une vue agrégée mais ne dit rien d'une requête qui n'est lente qu'occasionnellement. Pour la capturer, le paramètre log_min_duration_statement logge chaque requête dont la durée dépasse un seuil.

Réglé à 500ms par exemple, il écrit dans le log toutes les exécutions au-dessus de ce seuil, avec le texte SQL complet et la durée. C'est l'outil de référence pour repérer des pics ponctuels qui ne ressortent pas dans la moyenne.

Le coût est modéré tant que le seuil est élevé. Régler ce paramètre à 0 logge toutes les requêtes et peut saturer un disque rapidement.

auto_explain pour capturer le plan

Connaître la durée d'une requête lente ne suffit pas pour la corriger. Il faut son plan d'exécution. L'extension auto_explain capture automatiquement le plan EXPLAIN de chaque requête dépassant un seuil, et l'écrit dans le log avec la requête.

Configurée via auto_explain.log_min_duration et auto_explain.log_analyze, elle donne le plan réel utilisé en production, sans intervention manuelle. C'est la différence entre savoir qu'une requête est lente et comprendre pourquoi.

En contrepartie, l'option log_analyze ré-exécute en mode instrumenté, ce qui ajoute un overhead non négligeable. À réserver aux investigations ciblées.

EXPLAIN ANALYZE pour les investigations manuelles

Pour les requêtes identifiées par pg_stat_statements ou les logs, EXPLAIN ANALYZE reste l'outil de référence. Il exécute réellement la requête et retourne le plan effectif avec les temps par nœud, les lignes effectives versus estimées, et les boucles.

Les options BUFFERS et FORMAT JSON enrichissent la sortie. BUFFERS révèle les lectures disque et les hits de cache, ce qui aide à comprendre si la lenteur vient d'un accès disque ou d'un calcul. FORMAT JSON facilite l'envoi du plan à un visualiseur comme explain.depesz.com ou explain.dalibo.com.

Les outils GUI : pgBadger, pganalyze, QoreDB

pgBadger analyse les logs PostgreSQL hors ligne et génère un rapport HTML détaillé. Il classe les requêtes par temps total, par fréquence, par utilisateur, et trace les graphes d'activité par heure. Adapté à une analyse post-mortem ou à un audit régulier.

pganalyze est une plateforme SaaS qui ingère pg_stat_statements et les logs en continu, et propose des recommandations d'index. Adapté aux équipes qui acceptent l'envoi de métriques vers un service externe.

QoreDB intègre un Universal Query Interceptor qui logge chaque requête exécutée depuis le client. Le seuil de slow query est configurable, par défaut à 1000ms. Les requêtes au-dessus sont conservées avec leur fingerprint, leur temps d'exécution et leur contexte. Les percentiles P50, P95 et P99 sont calculés sur les dernières exécutions et exposés dans un panneau dédié. C'est un audit côté client, complémentaire des outils serveur.

Un workflow d'audit reproductible

Un audit régulier passe par trois étapes. D'abord, identifier les requêtes coûteuses avec pg_stat_statements pour la vue agrégée, et avec log_min_duration_statement pour les pics. Ensuite, récupérer le plan de chaque requête prioritaire avec auto_explain ou un EXPLAIN ANALYZE manuel. Enfin, agir : ajout d'index, réécriture de la requête, ou changement de configuration.

Cette boucle se répète. Une optimisation déplace souvent le goulot vers une autre requête, et seule la mesure continue permet d'en être conscient.

L'audit des requêtes lentes n'a rien d'exotique en PostgreSQL : les briques sont natives et matures. Le travail consiste à les combiner et à les inscrire dans la durée. Un outil client comme QoreDB complète cette chaîne en offrant un audit local et fingerprinté sur ce qui passe par le développeur, et reste compatible avec l'outillage serveur classique.