Health Score pour PostgreSQL : surveillance de la santé

Score de santé dans PostgreSQL : un indicateur global pour votre base de données

La surveillance traditionnelle avec des dizaines de tableaux de bord Grafana conduit souvent à une surcharge d'informations — les opérateurs ignorent les graphiques, les alertes sont soit trop fréquentes (faux positifs), soit déclenchées trop tard. Le Score de santé résout ce problème en regroupant plus de 150 métriques issues de pg_stat_* en un seul indicateur compris entre 0 et 100. La formule intègre des poids par catégorie et tient compte des combinaisons dangereuses, permettant une évaluation rapide : 95+ est normal, moins de 70 nécessite une intervention.

La formule de base : Score de santé = 100 - Σ(pénalité_i × poids_i). Les pénalités sont calculées de manière non linéaire, réagissant aux tendances et aux anomalies. Ce n’est pas une alerte seuil — c’est une métrique composite, similaire à l’Apdex en APM ou à l’EWS en médecine.

Structure et calculs par catégorie

Le Score de santé repose sur cinq catégories fixes avec des poids prédéfinis. Chaque catégorie évalue des métriques clés via des requêtes SQL sur les vues système de PostgreSQL.

Connexions (poids 0,20)

Mesure la saturation du pool de connexions et les états inactifs :

SELECT 
    count(*) FILTER (WHERE state = 'active') AS active_connections,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
    now() - min(xact_start) AS longest_transaction_age
FROM pg_stat_activity
WHERE pid != pg_backend_pid();

La pénalité augmente exponentiellement au-delà de 80 % d’utilisation. Les sessions en attente (idle in transaction) depuis plus de 30 secondes subissent une pénalité supplémentaire, car elles bloquent autovacuum et contribuent à la bloat.

Performance (poids 0,25)

Se concentre sur l’expérience utilisateur : taux de réussite du cache et requêtes lentes.

-- Taux de réussite du cache
SELECT 
    round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Requêtes lentes
SELECT count(*), round(avg(mean_exec_time)::numeric, 2)
FROM pg_stat_statements
WHERE mean_exec_time > 1000 AND calls > 10;

Un taux de réussite inférieur à 95 % déclenche une pénalité ; en dessous de 90 %, c’est critique. Le temps d’exécution est comparé à des bases historiques pour détecter les anomalies.

Stockage (poids 0,20)

Surveille les tuples morts et la bloat :

SELECT 
    schemaname, relname, n_dead_tup, n_live_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
    pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 10000
ORDER BY dead_ratio DESC LIMIT 20;

La bloat est évaluée via pgstattuple ou des heuristiques basées sur relpages. L’utilisation disque est comparée aux limites du système de fichiers.

Réplication (poids 0,15)

Vérifie le retard des répliques :

SELECT application_name, state, (sent_lsn - replay_lsn) AS lag_bytes,
    write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Un retard supérieur à 1 Mo ou 5 secondes entraîne une pénalité. L’absence de répliques ne pénalise pas cette catégorie.

Maintenance (poids 0,20)

Suivi de l’avancement du vacuum et de l’âge des XID :

-- Âge des XID
SELECT datname, age(datfrozenxid), 2147483648 - age(datfrozenxid)
FROM pg_database ORDER BY age(datfrozenxid) DESC;

-- Âge du vacuum
SELECT schemaname, relname, now() - greatest(last_vacuum, last_autovacuum) AS vacuum_age
FROM pg_stat_user_tables WHERE n_live_tup > 10000
ORDER BY vacuum_age DESC NULLS FIRST LIMIT 20;

Un âge des XID supérieur à 1,5 milliard déclenche une forte pénalité en raison du risque de wraparound.

Logique des poids et interprétation

Les poids ont été choisis empiriquement : la performance est prioritaire (0,25) car elle impacte directement l’expérience utilisateur. Les connexions et la maintenance (0,20) sont des points de défaillance rapides. La réplication (0,15) est optionnelle.

• 95+ : condition stable.
• 70–94 : surveiller, planifier les corrections.
• 40–69 : action immédiate requise.
• <40 : statut d’incident.

Diagnostic automatisé avec recommandations

Le Score de santé inclut une liste priorisée des problèmes :

• HAUT : table avec >40 % de tuples morts et vacuum >4 jours — exécuter VACUUM ANALYZE, ajuster autovacuum_vacuum_scale_factor.
• MOYEN : retard de réplication >500 ms — vérifier la charge du réplique, wal_receiver_timeout.
• FAIBLE : taux de succès du cache <99 % — augmenter shared_buffers à 25 % de la RAM.

Les recommandations sont générées par règles, incluant des vérifications contre les seuils d’autovacuum.

SELECT relname, n_dead_tup > (n_live_tup * current_setting('autovacuum_vacuum_scale_factor')::float + current_setting('autovacuum_vacuum_threshold')::float) AS vacuum_due
FROM pg_stat_user_tables;

Points clés

• Le Score de santé agrège des métriques pondérées, révélant des combinaisons cachées comme "idle in transaction" + "bloat", que les alertes seuil manquent.
• La formule non linéaire fournit des alertes précoce avant les seuils critiques.
• Intégration possible avec Prometheus/Grafana via postgres_exporter.
• Le diagnostic automatisé donne des recommandations concrètes avec des exemples de requêtes SQL réelles.
• Évolutive : compare les tendances historiques pour détecter les anomalies.

— Editorial Team