pg_stat_statements expliqué : fonctionnement, limites et ce que PWR ajoute

pg_stat_statements en une phrase

pg_stat_statements est l'extension officielle de PostgreSQL qui agrège en mémoire les statistiques d'exécution de chaque requête normalisée (texte de requête avec les constantes remplacées par des paramètres), depuis le dernier redémarrage ou reset — sans jamais stocker le résultat des requêtes elles-mêmes, uniquement leurs métriques d'exécution.

Installation et activation

L'extension doit être déclarée dans shared_preload_libraries (ce qui impose un redémarrage du service PostgreSQL), puis créée explicitement sur la base cible. Sans cette étape, aucune donnée n'est collectée : c'est le prérequis technique le plus fréquemment oublié avant un premier diagnostic.

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Les colonnes essentielles à connaître

queryid identifie une requête normalisée de façon stable (utile pour la retrouver entre deux relevés). calls compte le nombre d'appels. total_exec_time et mean_exec_time donnent le temps cumulé et moyen passé à exécuter la requête. rows compte les lignes traitées. shared_blks_hit et shared_blks_read distinguent les blocs servis depuis le cache de ceux lus sur disque — un ratio clé pour repérer une requête qui sollicite excessivement le disque. temp_blks_written signale un tri ou un hash join qui déborde sur le disque temporaire faute de work_mem suffisant.

Un exemple concret : isoler les requêtes qui pèsent sur le cache

Cette requête classe les requêtes par volume de lectures disque plutôt que par temps d'exécution — utile pour cibler spécifiquement les requêtes responsables d'une dégradation du cache hit ratio.

SELECT query, calls, shared_blks_read, shared_blks_hit,
       round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;

Les limites concrètes de pg_stat_statements

pg_stat_statements ne conserve aucun historique dans le temps : ses compteurs sont cumulatifs depuis le dernier reset, sans notion de "avant" et "après" un incident précis — un pg_stat_statements_reset() efface d'un coup tout l'historique disponible, y compris celui d'un incident qu'on cherche justement à analyser après coup.

L'extension n'enregistre aucun plan d'exécution (il faut lancer EXPLAIN séparément), aucune information sur les verrous, et aucune granularité temporelle : impossible de savoir, à partir de pg_stat_statements seul, si le temps moyen d'une requête vient de doubler cette nuit ou progressivement sur plusieurs semaines.

Enfin, la normalisation des requêtes peut regrouper des requêtes aux profils de performance très différents sous un même queryid si seule la structure textuelle est identique, ce qui peut masquer des cas particuliers coûteux dans une moyenne globale rassurante.

Ce que PWR ajoute par-dessus pg_stat_statements

PWR capture périodiquement l'état de pg_stat_statements dans des snapshots persistés et horodatés, sans jamais dépendre d'un reset qui effacerait l'historique. Comparer deux snapshots (avant/après un déploiement, ou de part et d'autre d'un incident) permet de calculer un delta exact — combien d'appels, combien de temps CPU, combien de lignes — pour cette fenêtre précise, là où pg_stat_statements seul ne donne qu'un cumul global depuis le dernier redémarrage.

Le rapport PWR croise aussi ces données avec pg_stat_activity (sessions actives, verrous) et pg_buffercache, pour distinguer une requête réellement coûteuse d'une requête simplement bloquée par un verrou — une nuance que pg_stat_statements seul ne permet pas de faire. La popup Query History va plus loin en traçant, requête par requête, son évolution snapshot après snapshot, pour dater précisément l'apparition d'une régression.