Introduction à pg_stat_statements : installation et configuration de l’extension

L’extension pg_stat_statements dans PostgreSQL, c’est un outil pour collecter des stats sur les requêtes. Ça te permet de voir quelles requêtes sont les plus fréquentes, lesquelles prennent le plus de temps, et comment les ressources de la base sont utilisées. Plutôt que d’analyser chaque requête à la main avec EXPLAIN, tu peux avoir une vue d’ensemble sur les perfs de ta base.

Avantages de pg_stat_statements :

Monitoring en temps réel : tu peux voir direct quelles requêtes font chauffer la base en ce moment.

Analyse des perfs de tout le système : t’as des infos sur toutes les requêtes, pas juste celles que tu décides d’analyser à la main.

Repérage des requêtes lentes : tu identifies facilement celles qui prennent le plus de temps.

Détection des requêtes répétitives : ça aide à optimiser le cache et à ajouter des index pour les requêtes populaires.

Installation et configuration de pg_stat_statements

Maintenant que tu vois à quoi sert pg_stat_statements, on va voir comment l’installer et le configurer étape par étape.

1. Vérifie si PostgreSQL est prêt. Assure-toi que ta version de PostgreSQL supporte l’extension pg_stat_statements. Elle est incluse de base depuis PostgreSQL 9.2. Pour vérifier si elle est dispo, lance :

SELECT extname FROM pg_extension;

Si pg_stat_statements n’est pas dans la liste, c’est peut-être que l’admin ne l’a pas installée.

Voilà à quoi ça doit ressembler si l’extension est installée et activée :

2. Ajouter l’extension.

Pour activer pg_stat_statements, il faut l’ajouter à la liste des libs à précharger dans PostgreSQL. Ça se fait dans le fichier de config postgresql.conf.

Étapes :

1. Repère le fichier postgresql.conf. En général, il est dans le dossier data de PostgreSQL.
2. Ouvre-le pour l’éditer.
3. Ajoute ou modifie la ligne :

   shared_preload_libraries = 'pg_stat_statements'

Pourquoi ? Parce que pg_stat_statements a besoin d’être préchargé pour pouvoir traquer les requêtes au niveau système.

4. Sauvegarde les modifs et redémarre le serveur PostgreSQL pour activer les changements. Voici la commande sous Linux :

   sudo systemctl restart postgresql
   

Si tu bosses ou testes en local, un simple restart du serveur suffit aussi.

3. Créer l’extension dans la base. Une fois le serveur PostgreSQL relancé, tu peux créer l’extension pg_stat_statements dans la base de ton choix. Connecte-toi à la base via psql ou un autre outil et lance :

CREATE EXTENSION pg_stat_statements;

Si tout se passe bien, la commande passe sans erreur. Maintenant pg_stat_statements est actif pour ta base.

4. Configurer les paramètres de pg_stat_statements.

Après l’install, c’est utile de régler les paramètres pour bien collecter les stats. Les principaux paramètres se mettent dans postgresql.conf.

Paramètres principaux

• pg_stat_statements.track
• Définit quelles requêtes seront suivies.
• Valeurs possibles :
  • all — suivre toutes les requêtes (recommandé pour debug et analyse).
  • top — suivre seulement les requêtes de haut niveau.
  • none — désactiver le suivi.
• Exemple de config :

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • Indique le nombre max de requêtes à garder dans les stats.
  • Par défaut : 5000.
  • Si t’as beaucoup de requêtes, monte la valeur, genre :

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • Définit si les stats sont sauvegardées entre les redémarrages du serveur.
  • Valeurs : on ou off.
  • On conseille de laisser on :

    pg_stat_statements.save = on
    

Après avoir changé les paramètres, redémarre encore le serveur PostgreSQL.

Vérifier que pg_stat_statements fonctionne

Maintenant que l’extension est installée et configurée, on va checker si elle marche. Pour voir les stats collectées, lance cette requête :

SELECT
    queryid,        -- Identifiant unique de la requête
    query,          -- Texte de la requête
    calls,          -- Nombre d’appels de la requête
    total_time,     -- Temps total d’exécution (en millisecondes)
    rows            -- Nombre de lignes retournées par la requête
FROM pg_stat_statements
ORDER BY total_time DESC;

À quoi servent les colonnes ?

• queryid : identifiant unique de la requête, pratique pour retrouver les requêtes identiques avec des paramètres différents.
• query : le texte SQL de la requête exécutée.
• calls : combien de fois la requête a été appelée.
• total_time : temps total (somme du temps de tous les appels de la requête).
• rows : nombre total de lignes retournées par la requête.

Par exemple, si tu vois une requête avec calls = 100 et total_time = 50000 (50 secondes) qui prend la majorité du temps système, c’est un gros signal qu’il faut l’optimiser.

Cas d’usage typiques de pg_stat_statements

1. Repérer les requêtes les plus lentes. Pour trouver celles qui prennent le plus de temps, trie par total_time :

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

2. Détecter les requêtes les plus actives. Pour voir celles qui tournent le plus souvent, trie par calls :

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. Analyser l’utilisation des index. Si tu vois plein de requêtes lentes, checke l’utilisation des index. Par exemple, dans les requêtes avec des filtres (WHERE), l’absence d’index est souvent la cause des perfs pourries.

Reset des données de pg_stat_statements

Parfois, tu voudras remettre les stats à zéro pour repartir sur une analyse clean. Tu peux le faire avec :

SELECT pg_stat_statements_reset();

Après le reset, toutes les stats sont vidées et la collecte recommence à zéro.

Conseils pratiques

Limite la quantité de stats collectées : si tu bosses sur un système très chargé avec des millions de requêtes, garde pg_stat_statements.max à un niveau raisonnable pour éviter de surcharger la base.

Nettoie régulièrement les stats : c’est utile avant d’analyser les perfs, pour pas mélanger les vieilles et les nouvelles données.

Fais gaffe aux requêtes lentes : même si elles sont rares, une seule requête lente peut plomber ta base.

Maintenant tu sais installer, configurer et utiliser l’extension pg_stat_statements pour analyser les perfs des requêtes. Dans la prochaine leçon, on verra comment trouver les requêtes lentes avec cet outil et comment les optimiser.