PostgreSQL: Des Métriques à l'Analyse des Plans de Requêtes

Avoir un minimum d'observabilité lorsqu'on a du PostgreSQL en production n'est pas optionnel. Que ce soit pour suivre les Golden Signals, analyser les requêtes lentes ou les patterns de connexion. Cela nécessite souvent de jongler avec plusieurs outils, configurer divers exporters et extraire manuellement les requêtes pour les analyser.

❓ Et si nous pouvions obtenir un niveau de supervision complet pour la production avec un minimum de configuration?

Dans cet article, nous allons explorer comment aller plus loin en ajoutant à une supervision déjà bien fournie par CloudNativePG, une capacité d'analyse des performances de requêtes simple, efficace et facile à utiliser grâce à la puissance des outils que nous avons à disposition: Vector, VictoriaMetrics et VictoriaLogs.

📊 Fourni avec CloudNativePG

Lorsque vous déployez un cluster PostgreSQL avec CNPG, une multitude de métriques ainsi que des dashboards Grafana très complets sont mis à disposition. L'opérateur expose des métriques via un endpoint dédié sur chaque instance PostgreSQL avec les informations suivantes :

• Opérations de Base de Données : Taux de transactions, requêtes par seconde, statistiques des tuples
• État de Réplication : Lag, état du streaming, métriques de synchronisation
• Utilisation des Ressources : Connexions, taux de hit du cache, statistiques des buffers
• Santé du Système : État des instances, événements de failover, états des sauvegardes

GitOps et Opérateurs Kubernetes

Les exemples de cet article proviennent de configurations disponibles dans le dépôt Cloud Native Ref. Il exploite plusieurs opérateurs, notamment CloudNativePG pour la gestion PostgreSQL, VictoriaMetrics pour la collecte de métriques et VictoriaLogs pour la collecte de logs. Ce projet vise à démarrer rapidement une plateforme complète qui suit les meilleures pratiques en termes d'automatisation, de monitoring, de sécurité et plus encore. Les commentaires et contributions sont les bienvenus 🙏

Collecter les Métriques avec VictoriaMetrics

CloudNativePG expose automatiquement des métriques sur chaque pod PostgreSQL. Pour activer leur collecte, il suffit de configurer le Helm chart comme suit :

1# CloudNativePG Helm chart
2monitoring:
3  podMonitorEnabled: true

Cette simple configuration crée un PodMonitor (ressource Prometheus Operator) qui est automatiquement converti par l'opérateur VictoriaMetrics en ressource native compatible. Les métriques de tous les pods PostgreSQL (primary et replicas) sont ainsi collectées et disponibles dans VictoriaMetrics.

Métriques Essentielles à Surveiller

CloudNativePG expose des métriques qui s'alignent parfaitement avec la méthodologie des Golden Signals que nous avons abordée dans les articles précédents :

Latence ⏳

1# Durée moyenne des requêtes
2rate(cnpg_backends_total_seconds_sum[5m]) / rate(cnpg_backends_total_seconds_count[5m])

Trafic 📶

1# Transactions par seconde
2rate(pg_stat_database_xact_commit[5m]) + rate(pg_stat_database_xact_rollback[5m])

Erreurs ❌

1# Échecs de connexion et deadlocks
2rate(pg_stat_database_deadlocks[5m])
3rate(cnpg_pg_postmaster_start_time_seconds[5m])

Saturation 📈

1# Utilisation du pool de connexions
2cnpg_backends_total / cnpg_pg_settings_max_connections
3
4# Taux de hit du cache (devrait être > 95%)
5sum(rate(pg_stat_database_blks_hit[5m])) /
6  (sum(rate(pg_stat_database_blks_hit[5m])) + sum(rate(pg_stat_database_blks_read[5m])))

Visualisation avec Grafana

En utilisant l'opérateur Grafana que nous avons exploré dans les articles précédents, Nous pouvons déployer les dashboards CNPG de manière déclarative :

 1apiVersion: grafana.integreatly.org/v1beta1
 2kind: GrafanaDashboard
 3metadata:
 4  name: databases-cloudnative-pg
 5spec:
 6  allowCrossNamespaceImport: true
 7  folderRef: "databases"
 8  datasources:
 9    - inputName: "DS_PROMETHEUS"
10      datasourceName: "VictoriaMetrics"
11  instanceSelector:
12    matchLabels:
13      dashboards: "grafana"
14  url: "https://grafana.com/api/dashboards/20417/revisions/4/download"

Le dashboard fournit des vues complètes de nos clusters PostgreSQL, incluant le lag de réplication, les performances des requêtes et l'utilisation des ressources.

🔍 Comprendre les Performances des Requêtes

Les métriques nous donnent le "quoi" et le "quand". En revanche, elles ne nous disent pas toujours le "pourquoi". Savoir que les requêtes sont lentes est utile ; comprendre pourquoi elles sont lentes nous oriente souvent vers des axes d'optimisations.

L'analyse traditionnelle des requêtes PostgreSQL nécessite d'exécuter manuellement les commandes EXPLAIN et EXPLAIN ANALYZE. Cette approche a, cependant, ses limites :

• Réactive : Vous n'analysez que les requêtes que vous soupçonnez d'être problématiques
• Manuelle : Nécessite une investigation active par les DBAs
• Ponctuelle : Capture le plan d'exécution actuel, pas les tendances historiques
• Incomplète : Difficile de corréler avec les patterns de charge en production

Idéalement nous aurions besoin d'une capture automatique et continue des plans de requêtes qui nous permette de :

1. Identifier automatiquement les requêtes lentes
2. Suivre les changements de plans d'exécution dans le temps
3. Corréler les performances des requêtes avec les métriques système
4. Débugger les problèmes de performance sans avoir à les reproduire manuellement

Certaines solutions de supervision managées offrent celà. Mais peut-on s'approcher de cet objectif sur Kubernetes ?

✨ Historique des Plans de Requêtes : Implémentation avec des Outils Open Source

La bonne nouvelle, c'est que nous pouvons construire un système de suivi des performances de requêtes qui rivalise avec les offres commerciales avec un peu de configuration.

L'Architecture

Notre solution exploite deux extensions PostgreSQL ainsi qu'un paramètre de configuration et les intègre avec l'écosystème VictoriaMetrics :

Extensions PostgreSQL :

• pg_stat_statements : Agrège les statistiques d'exécution des requêtes
• auto_explain : Capture automatiquement les plans d'exécution des requêtes lentes

Configuration PostgreSQL :

• compute_query_id : Paramètre qui génère des identifiants uniques pour la corrélation des requêtes

Stack d'Observabilité :

• VictoriaMetrics : Stocke les métriques de requêtes depuis pg_stat_statements
• VictoriaLogs : Stocke les plans d'exécution qui sont générés grâce à auto_explain
• Vector : Parse les logs PostgreSQL et extrait les plans d'exécution
• Grafana : Visualise les données de performance et permet l'exploration de l'historique des plans

Le lien essentiel entre tous ces éléments est la corrélation entre métriques et logs utilisant l'identifiant de requête. Cela nous permet de :

1. Voir qu'une requête est lente (depuis les métriques)
2. Cliquer pour voir son historique de plans d'exécution (depuis les logs)
3. Identifier les changements de plan qui ont causé des régressions de performance

J'ai appelé cette fonctionnalité "Performance Insights". Toute ressemblance avec une solution existante serait fortuite 😆.

Activer Performance Insights

Grâce aux "Managed Extensions" de CloudNativePG (disponible depuis la v1.23), activer la supervision complète des requêtes est hyper simple.

🏗️ Platform Engineering : Le Bon Niveau d'Abstraction

L'un des principes clés du platform engineering est de fournir le bon niveau d'abstraction aux développeurs/ses. Ils/Elles ne devraient pas avoir besoin de comprendre les internals de PostgreSQL ni de mémoriser plus de 15 paramètres de configuration spécifiques à PostgreSQL.

C'est ici que les compositions Crossplane démontrent leur intérêt. Dans le projet Cloud Native Ref, nous utilisons Crossplane avec KCL (Kubernetes Configuration Language) pour créer une abstraction de plus haut niveau appelée SQLInstance.

Sans Composition (Cluster CNPG brut) :

 1apiVersion: postgresql.cnpg.io/v1
 2kind: Cluster
 3metadata:
 4  name: myapp-postgres
 5spec:
 6  instances: 3
 7  postgresql:
 8    shared_preload_libraries:
 9      - pg_stat_statements
10      - auto_explain
11    parameters:
12      pg_stat_statements.max: "10000"
13      pg_stat_statements.track: all
14      pg_stat_statements.track_utility: "on"
15      pg_stat_statements.track_planning: "on"
16      pg_stat_statements.save: "on"
17      auto_explain.log_format: json
18      auto_explain.log_min_duration: "1000"
19      auto_explain.log_analyze: "on"
20      auto_explain.log_buffers: "on"
21      auto_explain.log_timing: "off"
22      auto_explain.log_triggers: "on"
23      auto_explain.log_verbose: "on"
24      auto_explain.log_nested_statements: "on"
25      auto_explain.sample_rate: "0.2"
26      compute_query_id: on
27      track_activity_query_size: 2048
28      track_io_timing: "on"
29      log_min_duration_statement: 1000
30      # ... et plus encore

Avec la Composition et les paramètres que nous jugeons pertinents :

 1apiVersion: cloud.ogenki.io/v1alpha1
 2kind: App
 3metadata:
 4  name: myapp
 5spec:
 6  sqlInstance:
 7    enabled: true
 8    size: small
 9    storageSize: 20Gi
10    instances: 3
11    performanceInsights:
12      enabled: true
13      explain:
14        sampleRate: 0.2       # 20% sampling (default: safe for production)
15        minDuration: 1000     # Log queries > 1 second (default)
16      logStatement: none      # Optional: none (default) / ddl / mod / all

La composition Crossplane SQLInstance gère toute la complexité.

Cette approche par composition offre plusieurs avantages :

1. Expérience Développeur/ses : Les développeurs/ses d'applications n'ont pas besoin d'expertise PostgreSQL
2. Cohérence : La collecte des données de performances est configuré uniformément sur toutes les bases de données
3. Maintenabilité : L'équipe plateforme contrôle la configuration de supervision de manière centralisée
4. Évolutivité : Facile de mettre à jour les paramètres pour toutes les instances
5. Découvrabilité : Les développeurs/ses peuvent parcourir les options disponibles (performanceInsights: true) plutôt que mémoriser les noms de paramètres

Comprendre la Configuration

Détaillons ce que fait chaque composant :

pg_stat_statements : Cette extension suit les statistiques d'exécution de toutes les instructions SQL exécutées par un serveur. Elle enregistre :

• Le temps d'exécution total et le nombre d'appels
• Les lignes traitées et retournées
• Les hits et lectures de buffer
• Le temps de planification des requêtes

auto_explain : Logge automatiquement les plans d'exécution des requêtes dépassant un seuil de durée. Les paramètres clés sont :

• log_format: json : Sortie structurée pour le parsing
• log_min_duration: 1000 : Capturer les requêtes prenant plus d'1 seconde
• log_analyze: on : Inclure les comptages de lignes réels (exécute la requête)
• sample_rate: 0.2 : Échantillonnage de 20% des requêtes lentes pour réduire l'overhead (défaut)

compute_query_id : La clé de corrélation qui lie tout ensemble. Cela génère un identifiant unique pour chaque requête qui apparaît à la fois dans les métriques pg_stat_statements et les logs auto_explain.

Configuration du Pipeline de Logs Vector

Voici ce que Vector fait concrètement - transformation d'un log PostgreSQL auto_explain en événement indexable :

{
  "timestamp": "2025-01-15T14:32:18.456Z",
  "message": "{\"level\":\"info\",
    \"record\":{\"query_id\":\"8765432109876543210\",
      \"database_name\":\"production\",
      \"message\":\"duration: 245.678 ms plan:\\n{...}\"
    }
  }",
  "kubernetes": {
    "pod_labels": {
      "cnpg.io/cluster": "myapp-postgres"
    }
  }
}

{
  "_time": "2025-01-15T14:32:18.456Z",
  "cluster_name": "myapp-postgres",
  "namespace": "apps",
  "database": "production",
  "query_id": "8765432109876543210",
  "duration_ms": 245.678,
  "query_text": "SELECT users.email...",
  "plan_json": {
    "Node Type": "Hash Join",
    "..."
  }
}

Pipeline en 3 Étapes

Le pipeline Vector se compose de 3 transforms et 2 sinks (configuration complète) :

1. Parser les logs JSON CloudNativePG

1if .kubernetes.container_name == "postgres" && exists(.kubernetes.pod_labels."cnpg.io/cluster") {
2  .log = parse_json(.message)
3}

2. Filtrer les plans d'exécution

1exists(.log.record.message) && contains(.log.record.message, "plan:")

3. Extraire les métadonnées et le plan

1.query_id = to_string!(.log.record.query_id)  # Clé de corrélation
2.cluster_name = .kubernetes.pod_labels."cnpg.io/cluster"
3.database = .log.record.database_name
4# Parser le JSON du plan depuis "duration: X ms plan: {...}"
5.plan_json = parse_json(split(.log.record.message, "plan:")[1])

Les événements parsés sont envoyés vers deux sinks :

• Plans réussis → VictoriaLogs avec indexation sur cluster_name,namespace,database,query_id
• Échecs de parsing → Flux séparé pour debugging

La Clé : Corrélation via query_id

L'élément critique est query_id qui apparaît dans les deux systèmes :

• VictoriaMetrics : pg_stat_statements{queryid="8765432109876543210"} (métriques)
• VictoriaLogs : {query_id="8765432109876543210"} (plans)

Cette corrélation permet de sauter instantanément d'une métrique de performance à l'historique des plans d'exécution.

🔬 Analyser les Performances des Requêtes en Action

Une fois qu'on a identifié une requête problématique, nous pouvons voir son historique de plans d'exécution dans VictoriaLogs. En utilisant le query_id de pg_stat_statements, nous pouvons interroger VictoriaLogs :

1# Trouver tous les plans d'exécution pour une requête spécifique
2{cluster_name="myapp-postgres", query_id="1234567890"} | limit 50

Cela nous montre :

• Tous les plans d'exécution capturés pour cette requête dans le temps
• Les variations de plan (ex : index scans vs. sequential scans)
• Les comptages de lignes réels et temps d'exécution
• L'utilisation des buffers et statistiques I/O

Comprendre la Sortie d'EXPLAIN

Quand auto_explain capture un plan, il fournit des informations détaillées :

 1{
 2  "Query Text": "SELECT * FROM users WHERE email = ?",
 3  "Query Identifier": 1234567890,
 4  "Duration": 1567.234,
 5  "Plan": {
 6    "Node Type": "Seq Scan",
 7    "Relation Name": "users",
 8    "Actual Rows": 1,
 9    "Actual Loops": 1,
10    "Actual Total Time": 1567.123,
11    "Shared Hit Blocks": 0,
12    "Shared Read Blocks": 54321
13  }
14}

Points clés de ce plan :

• Sequential Scan : Scan de toute la table au lieu d'utiliser un index
• Lectures de blocs élevées : 54 321 blocs lus depuis le disque (mauvaise utilisation du cache)
• Une seule ligne retournée : Malgré le scan de toute la table

Cela suggère immédiatement le besoin d'un index sur la colonne email.

🎨 Visualiser les Plans d'Exécution avec pev2

Comprendre des plans d'exécution complexes depuis des logs peut être difficile. C'est là que pev2 (PostgreSQL Explain Visualizer 2) devient très utile. C'est un outil web qui transforme les plans d'exécution JSON en diagrammes interactifs et visuels.

 1apiVersion: cloud.ogenki.io/v1alpha1
 2kind: App
 3metadata:
 4  name: xplane-pev2
 5  namespace: tooling
 6spec:
 7  image:
 8    repository: ghcr.io/smana/pev2
 9    tag: "v1.17.0"
10
11  resources:
12    requests:
13      cpu: "10m" # Minimal CPU for static content
14      memory: "32Mi" # Small memory footprint
15    limits:
16      cpu: "300m" # Cap to prevent runaway
17      memory: "128Mi" # Limit memory usage
18
19  # Accessible only via Tailscale VPN at: https://pev2.priv.cloud.ogenki.io
20  route:
21    enabled: true
22    hostname: "pev2" # Results in: pev2.priv.cloud.ogenki.io

Pour garantir que les données sensibles de requêtes ne quittent jamais le réseau, pev2 est auto-hébergé dans le cluster via la composition App. Cela illustre encore une fois le niveau d'abstraction de la plateforme : déployer un outil web statique utilise la même API déclarative qu'une application complète avec base de données.

Analyser avec Grafana

L'intégration avec Grafana permet d'identifier rapidement les requêtes problématiques et de naviguer jusqu'à leurs plans d'exécution.

Dashboard Performance Analysis

Ce dashboard affiche les métriques clés depuis pg_stat_statements : top requêtes par durée totale, latence moyenne, nombre d'appels. Chaque query_id est cliquable pour explorer les détails.

Dashboard Correlation

Ce dashboard corrèle les métriques (VictoriaMetrics) avec les plans d'exécution (VictoriaLogs) pour une requête spécifique. Il permet de voir l'évolution des performances et les changements de plan dans le temps.

Workflow : De Grafana à pev2

La vidéo ci-dessous montre le workflow complet d'investigation : depuis l'identification d'une requête lente dans Grafana jusqu'à l'analyse visuelle du plan d'exécution avec pev2.

Étapes du workflow :

1. Identifier la requête lente dans le dashboard Grafana (métriques pg_stat_statements)
2. Cliquer sur le query_id pour voir l'historique des plans dans VictoriaLogs
3. Copier le plan JSON et ouvrir pev2 (https://pev2.priv.cloud.ogenki.io)
4. Coller le plan (Ctrl+V) pour visualiser l'exécution

Exploiter pev2

pev2 transforme les plans JSON en diagrammes interactifs qui révèlent :

• Goulots d'étranglement : Les nœuds les plus gros = temps d'exécution le plus élevé (badges orange/jaunes = warnings)
• Estimations du planificateur : Divergences entre lignes estimées et réelles (ex: "under estimated by 443×" visible sur le Hash Join principal)
• Scans séquentiels inefficaces : Indicateurs sur de grandes tables suggérant des index manquants
• Stratégies de jointure : Hash Join, Nested Loop, Merge Join avec leurs coûts respectifs
• Patterns I/O : Ratios de blocs lus depuis le disque vs. cache (buffers hits)

L'interface interactive permet de cliquer sur chaque nœud pour voir les détails (coût, timing, nombre de lignes, buffers). Les badges de warning signalent immédiatement les problèmes potentiels (estimations erronées, scans inefficaces).

Pour les régressions de performance, VictoriaLogs permet de comparer les plans avant/après en filtrant par période temporelle (_time:[...]), révélant les changements de stratégie du planificateur PostgreSQL.

💭 Dernières remarques

Nous avons construit dans cet article un système complet d'analyse des performances PostgreSQL qui combine métriques (pg_stat_statements), plans d'exécution (auto_explain), et visualisation (pev2). La clé de cette approche réside dans la corrélation via query_id : depuis un dashboard Grafana montrant une requête lente, quelques clics suffisent pour naviguer jusqu'à son plan d'exécution visualisé dans pev2, permettant ainsi une analyse puis une optimisation des performances.

Il s'agit, encore une fois, d'une démonstration de la puissance des outils open source disponibles. CloudNativePG avec l'ajout d'extensions, VictoriaMetrics et VictoriaLogs stockent efficacement métriques et logs, Vector parse et structure les données, et Grafana offre une visualisation unifiée. Cette approche native à Kubernetes est portable, et donne un contrôle total.

L'abstraction apportée par Crossplane amplifie encore cette facilité. Grâce aux compositions App et SQLInstance, activer Performance Insights se résume à performanceInsights.enabled: true avec quelques paramètres d'ajustement (sampleRate, minDuration). Les développeurs/ses n'ont pas besoin de comprendre les internals PostgreSQL ni Vector—la plateforme masque la complexité. Cette même API déclarative déploie aussi bien une base de données complète qu'un outil web statique comme pev2, démontrant la cohérence du niveau d'abstraction.

Le projet cloud-native-ref rassemble toutes ces pièces et montre comment Gateway API, Tailscale, Crossplane/KCL, et l'écosystème VictoriaMetrics s'assemblent pour créer une plateforme d'observabilité complète.

🔖 Références

Documentation CloudNativePG

• Documentation Officielle CloudNativePG
• Extensions Auto-Gérées
• Monitoring et Observabilité

Extensions et Fonctionnalités PostgreSQL

• pg_stat_statements - Extension de statistiques de requêtes
• auto_explain - Logging automatique des plans d'exécution
• compute_query_id - Génération d'identifiant de requête
• Documentation EXPLAIN - Comprendre les plans de requêtes

Écosystème VictoriaMetrics

• Documentation VictoriaMetrics
• VictoriaLogs LogsQL
• Langage VRL de Vector

Visualisation des Plans de Requêtes

• pev2 (PostgreSQL Explain Visualizer 2) - Visualisation interactive des plans d'exécution
• Image Docker pev2 - Déploiement auto-hébergé

Configuration et Implémentation

• Configuration Vector VRL - Pipeline de parsing des logs PostgreSQL
• Composition CloudNativePG - Abstraction SQLInstance avec KCL
• Architecture de Monitoring PostgreSQL - Documentation complète de l'architecture