Monitorer une base de données sans pg_stat_activity et pg_stat_user_tables, c’est un peu comme surveiller ta santé juste avec la température. Tu piges pas où est le souci si tu mates que la vue d’ensemble. Ces deux commandes clés de PostgreSQL vont t’aider non seulement à observer, mais aussi à vraiment analyser ce qui se passe dans ta base.
C’est quoi pg_stat_activity ?
pg_stat_activity, c’est une vue système PostgreSQL qui te montre toutes les connexions à ta base. Elle répond aux questions : qui est connecté, quelles requêtes tournent en ce moment, et quelles connexions sont "bloquées" en mode inactif. C’est ton outil pour analyser l’activité en temps réel sur le serveur.
Voyons les champs principaux dispo dans pg_stat_activity. Le champ datname contient le nom de la base à laquelle le client est connecté, usename affiche le nom de l’utilisateur connecté. application_name indique le nom de l’appli qui utilise la connexion, client_addr contient l’adresse IP du client connecté au serveur. backend_start montre quand le client s’est connecté, state affiche l’état actuel de la connexion (active, idle, idle in transaction), et query contient la requête en cours ou la dernière exécutée.
Exemple 1 : voir toutes les connexions actives
Pour voir les connexions actives, lance cette requête :
SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';
Mate bien le champ query. Il montre les requêtes qui tournent en ce moment. Si une requête prend trop de temps, y’a sûrement un souci avec elle.
Exemple 2 : Analyse de l’état des transactions
Parfois, des connexions restent bloquées en mode idle in transaction. Ça veut dire qu’une transaction a été démarrée mais pas finie, ce qui peut causer des locks.
SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';
Comment régler ça ? Si tu repères une transaction "bloquée", tu peux la terminer avec cette commande :
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';
Certains devs abusent de cette commande. On te conseille de checker avec l’équipe avant de "tuer" un process. Oups, pardon, je voulais dire — fermer la connexion.
Monitoring de l’utilisation des tables : la vue pg_stat_user_tables
Si pg_stat_activity te permet de suivre les connexions, pg_stat_user_tables te parle des perfs des tables. Grâce à elle, tu sais : à quelle fréquence les données sont lues ou écrites, quelles tables sont les plus sollicitées, et où ça peut coincer niveau perfs.
Voici les champs principaux de pg_stat_user_tables pour analyser tes tables. relname contient le nom de la table, seq_scan montre le nombre de scans séquentiels, idx_scan — le nombre de scans via index. n_tup_ins indique le nombre de lignes insérées, n_tup_upd — le nombre de lignes modifiées, et n_tup_del — le nombre de lignes supprimées.
Exemple 1 : comparer l’utilisation des index et des scans séquentiels
Si un index est peu utilisé (idx_scan proche de zéro), c’est sûrement que tes requêtes sur cette table sont optimisables.
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
Exemple de résultat :
Si tu vois que la table orders a plein de scans séquentiels (seq_scan), pense à ajouter un index. Imagine la table orders avec 3500 scans séquentiels et seulement 100 scans via index, alors que la table employees a 50 scans séquentiels et 1000 scans via index — c’est un gros signal pour optimiser.
Exemple 2 : analyse du nombre d’opérations sur les tables
Pour voir à quel point tes données sont "vivantes", demande l’info sur les lignes insérées, modifiées et supprimées :
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;
Qu’est-ce que tu peux apprendre ? Les tables avec beaucoup d’inserts (n_tup_ins) et de deletes (n_tup_del) peuvent être des hotspots dans ta base. Ça veut dire que leur perf mérite une attention particulière.
Utilisation pratique des commandes pour l’analyse des perfs : combiner les données de pg_stat_activity et pg_stat_user_tables
Quand tu analyses les perfs de ta base, tu peux croiser les infos de ces deux vues. D’abord, repère les requêtes longues via pg_stat_activity, puis checke quelles tables elles utilisent avec pg_stat_user_tables. Si les requêtes longues bossent sur des tables avec un seq_scan élevé, tente d’optimiser les requêtes ou d’ajouter un index.
Exemple de requête :
WITH active_queries AS (
SELECT pid, query
FROM pg_stat_activity
WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';
GO TO FULL VERSION