🔝 Retour au Sommaire

14.2.2. pg_stat_database : Métriques par base

Introduction

La vue pg_stat_database est un tableau de bord statistique qui vous donne une vue d'ensemble de l'activité et de la santé de chaque base de données dans votre instance PostgreSQL. Contrairement à pg_stat_activity qui montre l'activité en temps réel, pg_stat_database accumule des statistiques depuis le démarrage du serveur (ou depuis la dernière réinitialisation des statistiques).

Imaginez pg_stat_database comme un compteur kilométrique pour chaque base de données : elle vous indique le nombre de transactions effectuées, le nombre de lignes lues, les erreurs de cache, et bien d'autres métriques essentielles pour évaluer la performance et l'utilisation de vos bases.

Pourquoi pg_stat_database est importante ?

Cette vue vous permet de répondre à des questions critiques :

Quelle base de données génère le plus de charge ?
Combien de transactions sont effectuées par seconde ?
Quel est le taux de succès du cache (cache hit ratio) ?
Y a-t-il beaucoup de deadlocks dans une base ?
Combien de temps CPU et I/O consomment mes requêtes ?
Quand a eu lieu la dernière sauvegarde ou le dernier VACUUM ?

Ces informations sont essentielles pour optimiser les performances et planifier la capacité de votre infrastructure.

Accéder à pg_stat_database

Pour consulter cette vue, exécutez simplement :

SELECT * FROM pg_stat_database;

Vous verrez une ligne par base de données dans votre instance PostgreSQL, y compris les bases système (template0, template1, postgres).

Vue simplifiée :

SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit  
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY datname;

Les colonnes principales de pg_stat_database

1. datid et datname

datid : L'identifiant numérique (OID) de la base de données
datname : Le nom de la base de données

Note : Il existe une ligne spéciale avec datname = NULL qui agrège les statistiques de toutes les bases, incluant les bases qui ont été supprimées.

Exemple :

-- Voir uniquement les bases actives
SELECT datname FROM pg_stat_database WHERE datname IS NOT NULL;

2. numbackends

Le nombre de connexions actives (backends) actuellement connectées à cette base de données.

Cas d'usage : Identifier quelle base a le plus de connexions actives.

Exemple :

SELECT datname, numbackends  
FROM pg_stat_database  
ORDER BY numbackends DESC;

⚠️ Différence avec pg_stat_activity :

pg_stat_activity montre le détail de chaque connexion
pg_stat_database.numbackends donne juste le compte total par base

3. xact_commit et xact_rollback

xact_commit : Nombre total de transactions validées (committed) depuis le démarrage ou la réinitialisation des stats
xact_rollback : Nombre total de transactions annulées (rolled back)

Pourquoi c'est important ?

Le ratio rollback/commit peut indiquer des problèmes applicatifs. Un taux de rollback élevé peut signifier :

Erreurs fréquentes dans le code applicatif
Deadlocks répétés
Violations de contraintes d'intégrité

Calculer le taux de rollback :

SELECT
    datname,
    xact_commit,
    xact_rollback,
    CASE
        WHEN (xact_commit + xact_rollback) > 0
        THEN round(100.0 * xact_rollback / (xact_commit + xact_rollback), 2)
        ELSE 0
    END AS taux_rollback_pct
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY taux_rollback_pct DESC;

Benchmark : Un taux de rollback supérieur à 5-10% mérite investigation.

4. blks_read et blks_hit

Ces colonnes sont parmi les plus importantes pour évaluer les performances I/O :

blks_read : Nombre de blocs (pages de 8 Ko par défaut) lus depuis le disque
blks_hit : Nombre de blocs trouvés dans le cache mémoire (shared buffers)

Le Cache Hit Ratio (CHR) :

C'est l'une des métriques les plus surveillées en production. Elle indique le pourcentage de lectures satisfaites par le cache plutôt que par le disque.

Formule :

Cache Hit Ratio = blks_hit / (blks_hit + blks_read) × 100

Calculer le CHR :

SELECT
    datname,
    blks_hit,
    blks_read,
    CASE
        WHEN (blks_hit + blks_read) > 0
        THEN round(100.0 * blks_hit / (blks_hit + blks_read), 2)
        ELSE 0
    END AS cache_hit_ratio
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY cache_hit_ratio;

Interprétation :

> 99% : Excellent. La plupart des données sont en cache.
95-99% : Bon, mais pourrait être amélioré.
< 95% : Problématique. Vous lisez trop sur disque. Envisagez d'augmenter shared_buffers.

⚠️ Attention : Un CHR bas peut aussi indiquer des requêtes qui scannent de grandes tables (sequential scans). Ce n'est pas toujours un problème de configuration mémoire.

5. tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted

Ces colonnes comptent le nombre de lignes (tuples) manipulées :

tup_returned : Nombre total de lignes retournées par les requêtes
tup_fetched : Nombre de lignes réellement récupérées par les requêtes
tup_inserted : Nombre de lignes insérées
tup_updated : Nombre de lignes mises à jour
tup_deleted : Nombre de lignes supprimées

Différence entre returned et fetched :

returned : Lignes scannées (potentiellement filtrées ensuite)
fetched : Lignes réellement renvoyées au client après filtrage

Un grand écart entre ces deux valeurs peut indiquer des requêtes inefficaces (scans complets avec beaucoup de filtrage).

Ratio de sélectivité :

SELECT
    datname,
    tup_returned,
    tup_fetched,
    CASE
        WHEN tup_returned > 0
        THEN round(100.0 * tup_fetched / tup_returned, 2)
        ELSE 0
    END AS selectivite_pct
FROM pg_stat_database  
WHERE datname IS NOT NULL;

Interprétation :

Sélectivité basse (< 10%) : Beaucoup de lignes scannées pour peu de résultats. Manque d'index ?
Sélectivité élevée (> 90%) : Requêtes bien optimisées.

Exemple - Voir l'activité d'écriture :

SELECT
    datname,
    tup_inserted AS insertions,
    tup_updated AS mises_a_jour,
    tup_deleted AS suppressions,
    (tup_inserted + tup_updated + tup_deleted) AS total_ecritures
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY total_ecritures DESC;

6. conflicts et deadlocks

conflicts : Nombre de requêtes annulées à cause de conflits de récupération (surtout sur les replicas en lecture)
deadlocks : Nombre de deadlocks détectés

Les deadlocks :

Un deadlock se produit quand deux transactions s'attendent mutuellement, créant un blocage circulaire. PostgreSQL détecte automatiquement ces situations et annule l'une des transactions.

Surveiller les deadlocks :

SELECT
    datname,
    deadlocks,
    deadlocks::numeric / GREATEST(xact_commit + xact_rollback, 1) * 100000 AS deadlocks_par_100k_tx
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY deadlocks DESC;

⚠️ Important : Même un seul deadlock régulier peut être problématique. Cela indique un problème de conception dans l'ordre des verrous.

7. temp_files et temp_bytes

temp_files : Nombre de fichiers temporaires créés
temp_bytes : Volume total de données écrites dans les fichiers temporaires

Pourquoi c'est important ?

PostgreSQL crée des fichiers temporaires quand une opération (tri, jointure, agrégation) ne tient pas en mémoire (work_mem). Les opérations qui utilisent le disque temporaire sont beaucoup plus lentes.

Indicateur de problème :

SELECT
    datname,
    temp_files,
    pg_size_pretty(temp_bytes) AS volume_temp,
    temp_bytes / GREATEST(temp_files, 1) AS taille_moyenne_fichier
FROM pg_stat_database  
WHERE datname IS NOT NULL AND temp_files > 0  
ORDER BY temp_bytes DESC;

Solutions si temp_bytes est élevé :

Augmenter work_mem (mémoire pour les opérations de tri/jointure)
Optimiser les requêtes (ajout d'index, réécriture)
Ajouter plus de RAM au serveur

8. blk_read_time et blk_write_time

blk_read_time : Temps total (en millisecondes) passé à lire des blocs depuis le disque
blk_write_time : Temps total passé à écrire des blocs sur le disque

⚠️ Prérequis : Ces statistiques nécessitent que track_io_timing soit activé dans postgresql.conf :

-- Vérifier si le tracking I/O est activé
SHOW track_io_timing;

-- Si 'off', l'activer (nécessite redémarrage ou reload)
ALTER SYSTEM SET track_io_timing = 'on';  
SELECT pg_reload_conf();

Analyser les temps I/O :

SELECT
    datname,
    blks_read,
    blk_read_time,
    CASE
        WHEN blks_read > 0
        THEN round(blk_read_time / blks_read, 2)
        ELSE 0
    END AS temps_moyen_lecture_ms
FROM pg_stat_database  
WHERE datname IS NOT NULL AND blks_read > 0  
ORDER BY blk_read_time DESC;

Interprétation :

Temps moyen < 1 ms : Excellent (probablement SSD ou cache OS)
Temps moyen 1-5 ms : Bon (SSD)
Temps moyen > 10 ms : Lent (HDD traditionnel ou problème de performance disque)

9. stats_reset

L'horodatage de la dernière réinitialisation des statistiques pour cette base.

Pourquoi c'est utile ?

Toutes les statistiques sont cumulatives depuis stats_reset. Pour calculer des métriques par unité de temps (transactions/seconde, cache hit ratio sur une période), vous devez connaître ce timestamp.

Exemple :

SELECT
    datname,
    stats_reset,
    now() - stats_reset AS duree_collecte
FROM pg_stat_database  
WHERE datname IS NOT NULL;

Réinitialiser les statistiques (rarement nécessaire) :

-- Réinitialiser pour une base spécifique
SELECT pg_stat_reset_single_table_counters(NULL);

-- Réinitialiser toutes les stats de la base actuelle
SELECT pg_stat_reset();

10. Colonnes de checksum et de session (PostgreSQL 12+)

checksum_failures : Nombre d'échecs de checksum détectés (corruption de données)
checksum_last_failure : Date du dernier échec de checksum

⚠️ Les checksums de données :

Si activés (option --data-checksums à l'init), PostgreSQL détecte les corruptions silencieuses sur disque.

Surveiller les corruptions :

SELECT
    datname,
    checksum_failures,
    checksum_last_failure
FROM pg_stat_database  
WHERE checksum_failures > 0;

🚨 Si checksum_failures > 0 : C'EST GRAVE !

Cela indique une corruption de données. Actions immédiates :

Vérifier l'intégrité du stockage (disques, RAID)
Restaurer depuis une sauvegarde saine
Contacter votre équipe infrastructure

11. session_time, active_time, idle_in_transaction_time (PostgreSQL 14+)

À partir de PostgreSQL 14, des statistiques de temps de session ont été ajoutées :

session_time : Temps total de toutes les sessions (en ms)
active_time : Temps passé à exécuter des requêtes
idle_in_transaction_time : Temps passé en état "idle in transaction"

Calculer le pourcentage de temps actif :

SELECT
    datname,
    round(100.0 * active_time / GREATEST(session_time, 1), 2) AS pct_temps_actif,
    round(100.0 * idle_in_transaction_time / GREATEST(session_time, 1), 2) AS pct_idle_in_tx
FROM pg_stat_database  
WHERE datname IS NOT NULL;

Interprétation :

Si pct_idle_in_tx > 10% : Problème applicatif (transactions non fermées)

Requêtes pratiques courantes

1. Vue d'ensemble de toutes les bases

SELECT
    datname AS base,
    numbackends AS connexions,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    blks_read AS blocs_disque,
    blks_hit AS blocs_cache,
    round(100.0 * blks_hit / GREATEST(blks_hit + blks_read, 1), 2) AS cache_hit_ratio,
    deadlocks,
    temp_files AS fichiers_temp,
    pg_size_pretty(temp_bytes) AS volume_temp
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY datname;

2. Identifier la base la plus active

SELECT
    datname,
    xact_commit + xact_rollback AS total_transactions,
    tup_inserted + tup_updated + tup_deleted AS total_modifications,
    blks_read + blks_hit AS total_blocs_accedes
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY total_transactions DESC  
LIMIT 5;

3. Calculer les transactions par seconde

SELECT
    datname,
    xact_commit + xact_rollback AS total_tx,
    EXTRACT(EPOCH FROM (now() - stats_reset)) AS secondes_depuis_reset,
    round((xact_commit + xact_rollback)::numeric /
          EXTRACT(EPOCH FROM (now() - stats_reset)), 2) AS tx_par_seconde
FROM pg_stat_database  
WHERE datname IS NOT NULL  
  AND stats_reset IS NOT NULL
ORDER BY tx_par_seconde DESC;

Note : Cette métrique est plus pertinente si les stats n'ont pas été réinitialisées récemment.

4. Analyser le cache hit ratio de toutes les bases

SELECT
    datname,
    blks_hit AS cache,
    blks_read AS disque,
    blks_hit + blks_read AS total,
    round(100.0 * blks_hit / GREATEST(blks_hit + blks_read, 1), 2) AS cache_hit_pct
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY cache_hit_pct;

Alerte si une base a un CHR < 95% :

SELECT
    datname,
    round(100.0 * blks_hit / GREATEST(blks_hit + blks_read, 1), 2) AS chr
FROM pg_stat_database  
WHERE datname IS NOT NULL  
  AND (blks_hit + blks_read) > 1000  -- Filtrer les bases peu utilisées
  AND 100.0 * blks_hit / (blks_hit + blks_read) < 95
ORDER BY chr;

5. Détecter les problèmes de fichiers temporaires

SELECT
    datname,
    temp_files,
    pg_size_pretty(temp_bytes) AS volume_temp,
    round(temp_bytes::numeric / GREATEST(temp_files, 1) / 1024 / 1024, 2) AS taille_moy_mb
FROM pg_stat_database  
WHERE datname IS NOT NULL  
  AND temp_files > 0
ORDER BY temp_bytes DESC;

Si volume_temp est élevé : Vérifiez work_mem et les requêtes lourdes (tri, jointures).

6. Surveiller les deadlocks

SELECT
    datname,
    deadlocks,
    xact_commit + xact_rollback AS total_tx,
    CASE
        WHEN (xact_commit + xact_rollback) > 0
        THEN round(deadlocks::numeric / (xact_commit + xact_rollback) * 100000, 2)
        ELSE 0
    END AS deadlocks_par_100k_tx
FROM pg_stat_database  
WHERE datname IS NOT NULL  
  AND deadlocks > 0
ORDER BY deadlocks DESC;

Benchmark : Même 1 deadlock par 100k transactions peut être problématique si c'est régulier.

7. Temps I/O par base (si track_io_timing = on)

SELECT
    datname,
    blks_read,
    round(blk_read_time::numeric, 2) AS temps_lecture_ms,
    CASE
        WHEN blks_read > 0
        THEN round(blk_read_time / blks_read, 2)
        ELSE 0
    END AS temps_moyen_lecture_ms,
    round(blk_write_time::numeric, 2) AS temps_ecriture_ms
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY blk_read_time DESC;

8. Comparer l'activité entre bases

SELECT
    datname,
    round(100.0 * xact_commit / GREATEST(SUM(xact_commit) OVER (), 1), 2) AS pct_commits,
    round(100.0 * (blks_read + blks_hit) / GREATEST(SUM(blks_read + blks_hit) OVER (), 1), 2) AS pct_io
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY pct_commits DESC;

Cette requête montre quelle base consomme quel pourcentage de l'activité totale.

9. Vue combinée avec taille des bases

SELECT
    d.datname,
    pg_size_pretty(pg_database_size(d.datname)) AS taille,
    s.numbackends AS connexions,
    s.xact_commit + s.xact_rollback AS transactions,
    round(100.0 * s.blks_hit / GREATEST(s.blks_hit + s.blks_read, 1), 2) AS cache_hit_ratio,
    s.deadlocks
FROM pg_database d  
JOIN pg_stat_database s ON d.datname = s.datname  
WHERE d.datname IS NOT NULL  
ORDER BY pg_database_size(d.datname) DESC;

10. Statistiques depuis le dernier reset

SELECT
    datname,
    stats_reset,
    age(now(), stats_reset) AS age_stats,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    deadlocks,
    temp_files
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY stats_reset DESC NULLS LAST;

Monitoring et alertes recommandés

Métriques critiques à surveiller en production

Cache Hit Ratio < 95%

-- Alerte si CHR trop bas
SELECT datname, round(100.0 * blks_hit / (blks_hit + blks_read), 2) AS chr
FROM pg_stat_database
WHERE datname IS NOT NULL
  AND (blks_hit + blks_read) > 1000
  AND 100.0 * blks_hit / (blks_hit + blks_read) < 95;

Taux de rollback > 5%

-- Alerte si trop de rollbacks
SELECT datname,
       round(100.0 * xact_rollback / (xact_commit + xact_rollback), 2) AS rollback_pct
FROM pg_stat_database
WHERE datname IS NOT NULL
  AND (xact_commit + xact_rollback) > 100
  AND 100.0 * xact_rollback / (xact_commit + xact_rollback) > 5;

Deadlocks détectés

-- Alerte si deadlocks
SELECT datname, deadlocks
FROM pg_stat_database
WHERE datname IS NOT NULL AND deadlocks > 0;

Fichiers temporaires excessifs

-- Alerte si > 1 GB de fichiers temp
SELECT datname, pg_size_pretty(temp_bytes) AS volume_temp
FROM pg_stat_database
WHERE datname IS NOT NULL
  AND temp_bytes > 1073741824;  -- 1 GB

Corruptions de données (checksums)

-- 🚨 ALERTE CRITIQUE 🚨
SELECT datname, checksum_failures, checksum_last_failure
FROM pg_stat_database
WHERE checksum_failures > 0;

Combiner pg_stat_database avec d'autres vues

Avec pg_stat_activity (connexions actives)

SELECT
    d.datname,
    d.numbackends AS connexions_selon_stat,
    COUNT(a.pid) AS connexions_actives_reelles,
    d.xact_commit + d.xact_rollback AS total_tx
FROM pg_stat_database d  
LEFT JOIN pg_stat_activity a ON d.datname = a.datname  
WHERE d.datname IS NOT NULL  
GROUP BY d.datname, d.numbackends, d.xact_commit, d.xact_rollback  
ORDER BY connexions_actives_reelles DESC;

Avec pg_database (informations système)

SELECT
    d.datname,
    pg_encoding_to_char(d.encoding) AS encodage,
    d.datcollate AS collation,
    pg_size_pretty(pg_database_size(d.datname)) AS taille,
    s.xact_commit + s.xact_rollback AS transactions
FROM pg_database d  
LEFT JOIN pg_stat_database s ON d.datname = s.datname  
WHERE d.datname IS NOT NULL  
ORDER BY pg_database_size(d.datname) DESC;

Réinitialiser les statistiques

Quand réinitialiser ?

Rarement ! La réinitialisation supprime tout l'historique. Cas d'usage légitimes :

Après une maintenance majeure
Pour mesurer les performances d'un changement spécifique
Après correction d'un problème et besoin de statistiques "propres"

Réinitialiser les stats de la base actuelle :

SELECT pg_stat_reset();

Réinitialiser les stats d'une base spécifique :

-- Se connecter d'abord à cette base
\c ma_base
SELECT pg_stat_reset();

⚠️ Attention : Cela affecte aussi pg_stat_statements et autres statistiques !

Configuration recommandée

Pour obtenir des statistiques complètes, configurez ces paramètres dans postgresql.conf :

# Activer le tracking I/O (indispensable pour blk_read_time)
track_io_timing = on

# Activer les statistiques de fonctions (optionnel)
track_functions = all

# Niveau de détail des statistiques d'activité
track_activities = on  
track_counts = on

Après modification :

SELECT pg_reload_conf();

Différences selon les versions PostgreSQL

PostgreSQL 12+

Ajout de checksum_failures et checksum_last_failure

PostgreSQL 13+

Amélioration des statistiques de WAL

PostgreSQL 14+

Ajout de session_time, active_time, idle_in_transaction_time
Ajout de sessions, sessions_abandoned, sessions_fatal, sessions_killed

PostgreSQL 15+

Amélioration du tracking des sessions

PostgreSQL 18+ (Septembre 2025)

Statistiques I/O et WAL par backend
Améliorations du sous-système I/O asynchrone

Cas d'usage en production

Scénario 1 : "Identifier la base qui consomme le plus de ressources"

SELECT
    datname,
    round(100.0 * (xact_commit + xact_rollback) /
          SUM(xact_commit + xact_rollback) OVER (), 2) AS pct_transactions,
    round(100.0 * (blks_read + blks_hit) /
          SUM(blks_read + blks_hit) OVER (), 2) AS pct_io,
    pg_size_pretty(pg_database_size(datname)) AS taille
FROM pg_stat_database  
WHERE datname IS NOT NULL  
ORDER BY (xact_commit + xact_rollback) DESC;

Scénario 2 : "Audit de performance mensuel"

WITH stats AS (
    SELECT
        datname,
        xact_commit + xact_rollback AS total_tx,
        round(100.0 * blks_hit / GREATEST(blks_hit + blks_read, 1), 2) AS chr,
        deadlocks,
        temp_files,
        pg_size_pretty(temp_bytes) AS vol_temp,
        stats_reset
    FROM pg_stat_database
    WHERE datname IS NOT NULL
)
SELECT * FROM stats  
WHERE total_tx > 0  
ORDER BY total_tx DESC;

Exportez ce résultat régulièrement pour suivre les tendances.

Scénario 3 : "Capacité restante (connexions)"

SELECT
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn,
    SUM(numbackends) AS conn_utilisees,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') -
    SUM(numbackends) AS conn_disponibles
FROM pg_stat_database;

Scénario 4 : "Détecter une base zombie ou inutilisée"

SELECT
    datname,
    xact_commit + xact_rollback AS total_tx,
    stats_reset,
    age(now(), stats_reset) AS age_stats
FROM pg_stat_database  
WHERE datname IS NOT NULL  
  AND datname NOT IN ('postgres', 'template0', 'template1')
  AND (xact_commit + xact_rollback) < 100  -- Très peu d'activité
  AND age(now(), stats_reset) > interval '7 days'
ORDER BY total_tx;

Intégration avec des outils de monitoring

Prometheus + postgres_exporter

Les métriques de pg_stat_database sont automatiquement exportées :

# Exemple de métriques Prometheus
pg_stat_database_xact_commit  
pg_stat_database_xact_rollback  
pg_stat_database_blks_read  
pg_stat_database_blks_hit  
pg_stat_database_tup_returned  
pg_stat_database_deadlocks

Grafana

Créez des dashboards avec :

Graphiques de transactions/sec par base
Cache hit ratio en temps réel
Nombre de deadlocks cumulés
Volume de fichiers temporaires

Requête d'export pour Grafana

SELECT
    datname AS metric,
    EXTRACT(EPOCH FROM now()) AS time,
    xact_commit + xact_rollback AS transactions,
    round(100.0 * blks_hit / GREATEST(blks_hit + blks_read, 1), 2) AS cache_hit_ratio,
    deadlocks
FROM pg_stat_database  
WHERE datname IS NOT NULL;

Bonnes pratiques

Surveillez le cache hit ratio : Objectif > 99% pour les bases OLTP
Activez track_io_timing : Essentiel pour diagnostiquer les problèmes I/O
Configurez des alertes : Sur deadlocks, rollback rate, temp_bytes, checksum_failures
Ne réinitialisez jamais les stats en production : Sauf raison exceptionnelle et documentée
Corrélation temporelle : Utilisez stats_reset pour calculer des métriques par période
Combinez avec pg_stat_activity : Pour une vue complète (agrégé + détail)
Exportez régulièrement : Pour l'analyse de tendances (weekly/monthly reports)
Documentez les baselines : Connaissez vos métriques normales pour détecter les anomalies

Résumé des colonnes clés

Colonne	Description	Seuil d'alerte
numbackends	Connexions actives	Proche de max_connections
xact_commit / xact_rollback	Transactions validées/annulées	Rollback > 5%
blks_hit / blks_read	Cache vs disque	Cache hit ratio < 95%
deadlocks	Deadlocks détectés	> 0 régulièrement
temp_files / temp_bytes	Fichiers temporaires	> 1 GB
blk_read_time / blk_write_time	Temps I/O	> 10 ms moyen
checksum_failures	Corruptions détectées	> 0 (CRITIQUE)

Différences avec pg_stat_activity

Aspect	pg_stat_database	pg_stat_activity
Granularité	Par base de données	Par connexion
Type de données	Statistiques cumulatives	État en temps réel
Période	Depuis stats_reset	Instantané
Usage principal	Performance, tendances	Diagnostic, troubleshooting

En pratique :

Utilisez pg_stat_database pour les dashboards et l'analyse de tendances
Utilisez pg_stat_activity pour le troubleshooting et les investigations

Conclusion

pg_stat_database est votre tableau de bord de santé pour chaque base de données PostgreSQL. Les métriques essentielles à retenir :

✅ Cache hit ratio : > 99% idéalement
✅ Taux de rollback : < 5%
✅ Deadlocks : 0 ou très peu
✅ Temp files : Le moins possible
✅ Checksum failures : JAMAIS (si activés)

Combinez ces statistiques avec pg_stat_activity (temps réel) et pg_stat_user_tables (détail par table) pour une vision complète de votre instance PostgreSQL.

Prochaines étapes : Explorez pg_stat_user_tables pour des statistiques au niveau des tables, et pg_stat_statements pour analyser les requêtes individuelles.

Ressources complémentaires :

Documentation officielle : Monitoring Database Activity
Outils : Prometheus, Grafana, pgBadger, pg_stat_monitor

⏭️ pg_stat_user_tables : Statistiques de tables

FilesExpand file tree

02.2-pg-stat-database.md

Latest commit

History

02.2-pg-stat-database.md

File metadata and controls

14.2.2. pg_stat_database : Métriques par base

Introduction

Pourquoi pg_stat_database est importante ?

Accéder à pg_stat_database

Les colonnes principales de pg_stat_database

1. datid et datname

2. numbackends

3. xact_commit et xact_rollback

4. blks_read et blks_hit

5. tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted

6. conflicts et deadlocks

7. temp_files et temp_bytes

8. blk_read_time et blk_write_time

9. stats_reset

10. Colonnes de checksum et de session (PostgreSQL 12+)

11. session_time, active_time, idle_in_transaction_time (PostgreSQL 14+)

Requêtes pratiques courantes

1. Vue d'ensemble de toutes les bases

2. Identifier la base la plus active

3. Calculer les transactions par seconde

4. Analyser le cache hit ratio de toutes les bases

5. Détecter les problèmes de fichiers temporaires

6. Surveiller les deadlocks

7. Temps I/O par base (si track_io_timing = on)

8. Comparer l'activité entre bases

9. Vue combinée avec taille des bases

10. Statistiques depuis le dernier reset

Monitoring et alertes recommandés

Métriques critiques à surveiller en production

Combiner pg_stat_database avec d'autres vues

Avec pg_stat_activity (connexions actives)

Avec pg_database (informations système)

Réinitialiser les statistiques

Configuration recommandée

Différences selon les versions PostgreSQL

PostgreSQL 12+

PostgreSQL 13+

PostgreSQL 14+

PostgreSQL 15+

PostgreSQL 18+ (Septembre 2025)

Cas d'usage en production

Scénario 1 : "Identifier la base qui consomme le plus de ressources"

Scénario 2 : "Audit de performance mensuel"

Scénario 3 : "Capacité restante (connexions)"

Scénario 4 : "Détecter une base zombie ou inutilisée"

Intégration avec des outils de monitoring

Prometheus + postgres_exporter

Grafana

Requête d'export pour Grafana

Bonnes pratiques

Résumé des colonnes clés

Différences avec pg_stat_activity

Conclusion