Suivi des transactions actives en temps réel avec pg_stat_activity

pg_stat_activity, c'est en gros une fenêtre en temps réel qui t'aide à piger ce qui se passe dans ta base de données à l'instant T. Dans la leçon précédente, on a vu les bases, maintenant on va creuser un peu plus dans ce super outil.

Exemple de requête basique sur pg_stat_activity :

SELECT * 
FROM pg_stat_activity;

Cette requête va afficher toutes les connexions actives et les requêtes en cours. Cool ! Mais il y aura trop de données, et tu risques d’y passer ta vie. Du coup, c’est utile de filtrer pour ne garder que ce qui compte vraiment.

Champs principaux dans pg_stat_activity

Regardons les champs clés qui vont t’être utiles en plus de ceux que tu connais déjà. query_start indique quand la requête a commencé, super important pour repérer les opérations longues. pid contient l’identifiant du process de connexion — c’est ce qu’il te faut pour gérer (genre terminer) une connexion. state_change montre quand l’état actuel de la connexion a été défini, ce qui aide à analyser les états problématiques qui durent.

Exemple pour sélectionner les process actifs :

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

Comment repérer les requêtes longues ?

Imagine, t’es admin de la base, et d’un coup la charge du serveur explose. Que faire ? D’abord, faut piger quelle requête bouffe toutes les ressources. On utilise pg_stat_activity pour trouver ces requêtes « gourmandes ».

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

Cette requête va te montrer toutes les requêtes qui tournent depuis plus de 10 secondes. Adapte la valeur de l’intervalle selon tes besoins.

Terminer les requêtes problématiques

Voyons comment se débarrasser des requêtes qui tournent trop longtemps et qui bloquent la base. Utilise la fonction pg_terminate_backend() pour forcer l’arrêt d’un process.

Exemple pour terminer un process avec un PID précis :

SELECT pg_terminate_backend(12345);

Où 12345 — c’est l’identifiant du process (champ pid) de pg_stat_activity.

Important : Terminer un process peut provoquer un rollback si la transaction n’est pas finie proprement, donc fais gaffe.

Maintenant, si tu veux terminer automatiquement tous les process « bloqués », genre les transactions idle, tu peux lancer ce bloc PL/pgSQL. Comme t’as déjà vu la prog, le concept de boucle (loop) te parle — c’est une structure qui répète des instructions tant qu’une condition est vraie ou jusqu’à ce que toutes les données soient traitées :

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Cette solution dynamique permet de nettoyer le système des transactions problématiques. La boucle FOR passe sur chaque ligne du résultat et termine le process pour chaque PID trouvé.

Bientôt on va attaquer PL/pgSQL, patience, c’est pour bientôt :P

Filtrer par état de transaction

Parfois tu veux pas juste trouver une requête active, mais aussi voir quelles connexions sont dans un état particulier, genre idle ou idle in transaction. Ça peut t’aider à repérer des soucis avant qu’ils deviennent critiques.

Exemple de requête pour trouver les transactions en idle in transaction :

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Le champ state_change montre quand cet état a été défini. Comme ça, tu peux repérer les transactions qui traînent, qui font rien d’utile mais qui peuvent bloquer des ressources de la base.

Application pratique

Monitoring des requêtes longues en prod : tu peux mettre en place un monitoring régulier des requêtes qui dépassent un certain seuil de temps, pour envoyer des alertes via Slack, Telegram ou n’importe quel outil de notif. Comme ça tu réagis vite aux soucis de perf.

Analyse des requêtes pendant les incidents : si le serveur rame, premier réflexe : regarde dans pg_stat_activity pour trouver la cause. Ça doit devenir ton protocole standard pour réagir aux problèmes de perf.

Maintenance de la base : analyser régulièrement pg_stat_activity t’aide à repérer les requêtes inefficaces et à les optimiser (genre en ajoutant des index ou en réécrivant les requêtes).

Quand tu fais du monitoring, tu peux te planter à cause d’un mauvais filtrage ou d’une mauvaise interprétation des données. Par exemple, si tu filtres sur l’état active, tu risques de louper les requêtes en idle in transaction, qui peuvent aussi bloquer des ressources. Autre erreur : terminer trop agressivement les process, ce qui peut provoquer des rollbacks non voulus et la perte de données. Analyse toujours le contexte avant de prendre des mesures radicales.

Techniques de monitoring avancées

Pour aller plus loin, tu peux écrire des requêtes plus complexes qui montrent des stats par utilisateur, base ou type de requête. Par exemple, tu peux voir combien de temps chaque utilisateur passe en moyenne sur ses requêtes, ou trouver les bases avec le plus de connexions actives.

C’est aussi utile de configurer la journalisation automatique des requêtes longues dans les logs PostgreSQL, avec les paramètres log_min_duration_statement et log_statement. Ça t’aidera à analyser les soucis de perf après coup et à repérer des patterns dans le comportement des applis.