Observabilité PostgreSQL

Questions

Qui-suis-je ?

Administrateur de Bases de Données – DBA
Expert PostgreSQL
Consultant chez DALIBO
Vibe Codeur Go

Point important

Version officielle de PostgreSQL

Observabilité PostgreSQL

Observabilité ?

L’observabilité est une mesure de la capacité à déduire l’état interne d’un système à partir de la connaissance de ses sorties externes.

Observabilité ?

L’observabilité est une mesure de la capacité à déduire l’état interne d’un système à partir de la connaissance de ses sorties externes.

Observabilité ?

L’observabilité est une mesure de la capacité à déduire l’état interne d’un système à partir de la connaissance de ses sorties externes.

Quels symptômes ?

Performance
- “Ce traitement dure 10 minutes au lieu de 15 secondes”
Disponibilité
- “Mon instance n’accepte plus de nouvelle connexion”
Mesures
- “On constate un pic CPU / Le FS est plein à 90%”

Quels Problèmes ?

Causes Racines
- Quelles requêtes posent problème ?
- Quelles applications posent problème ?
- Quelles connexions posent problème ?
- Quels utilisateurs posent problème ?

Outils

vues internes
extension pg_stat_statements
tableaux de bord

Vues internes

~40 vues de statistiques
- pg_stat_activity, pg_stat_io, pg_stat_user_table
- sessions, réplication, tables, index, IO…
~40 vues système
- pg_locks, pg_wait_events, pg_aios, pg_tables…
peu de granularité, compteurs cumulés

Vues de stats : - pg_stat_activity : qui fait quoi en ce moment (premier réflexe DBA) - pg_stat_user_tables : lectures/écritures par table - pg_stat_io : IO par type de backend (v16, amélioré v18 avec suivi WAL)

Vues système : - pg_locks : verrous en cours, qui bloque qui - pg_wait_events : sur quoi chaque backend attend - pg_aios : handles d’IO asynchrone en cours (nouveau v18)

Peu de granularité : - pg_stat_activity : par session en cours - pg_stat_user_tables : par table - pg_stat_database : par base - pg_locks : par verrou/processus → pas de granularité par requête (c’est pg_stat_statements qui l’apporte)

Compteurs cumulés : on sait « combien au total », pas « quand ». Survivent à un redémarrage propre (depuis v15), perdus en cas de crash ou restauration PITR. Source : postgresql.org/docs/current/monitoring-stats.html

Extension/vue `pg_stat_statements`

Granularité par requête normalisée :
- nombre d’appels, temps, lignes, blocs, fichiers temporaires…
compteurs cumulés, pas d’historique
Pas installée par défaut
Nécessite un redémarrage (shared_preload_libraries)

Tableaux de bord

Permettent l’historicisation et la visualisation
Spécialisés PostgreSQL
- temBoard, PoWA…
- pgAnalyze
Plateformes généralistes
- Datadog, New Relic…
- Grafana + Prometheus
connaissance

Tableaux de bord – les limites

Chers et complexes
- déployer, configurer, maintenir
Angles morts
- quelle requête provoque un verrou ?
- quelle erreur de connexion revient 157 fois ?
déduction

Un super outil d’observabilité

Connexions/déconnexions
Verrous et attentes
Requêtes lentes, fréquentes, avec paramètres et plan
Fichiers temporaires
Activité en écriture
Toutes les erreurs applicatives
Adaptable sans redémarrage
sans agent
horodaté nativement

…les logs :)

Configuration des logs

Langue

lc_messages = C

FATAL: le rôle « app_user » n'existe pas

FATAL: role "app_user" does not exist

pas par défaut

Format

format jsonl
csv aussi possible

logging_collector = on
log_destination = jsonlog

{"timestamp":"2026-01-01 09:10:11.176 CET",
 "pid":198716,
 "user":"app_user","dbname":"appdb",
 "error_severity":"LOG",
 "message":"duration: 3118.394 ms  execute: SELECT …",
 "application_name":"backend-api",
 "backend_type":"client backend",
 "query_id":0}

pas par défaut

Préfixe

log_line_prefix = '%t [%p] %e: db=%d,user=%u,app=%a,client=%h '

2026-01-01 09:10:11.176 CET [198716] LOG:
    duration: 3118.394 ms  execute: SELECT …

2026-01-01 09:10:11 CET [198716] 00000: db=appdb,user=app_user,
    app=backend-api,client=10.0.170.81 LOG:
    duration: 3118.394 ms  execute: SELECT …

pas par défaut

Connexions

lien avec max_connections

log_connections = on

…: user=app_user,db=appdb,app=[unknown],client=10.0.170.82 LOG:
    connection authorized: user=app_user database=appdb
    application_name=backend-api

pas par défaut

Déconnexions

durée des sessions

log_disconnections = on

…: user=app_user,db=appdb,app=backend-api,client=10.0.170.82 LOG:
    disconnection: session time: 0:00:03.418
    user=app_user database=appdb host=10.0.170.82

pas par défaut

Verrous

log_lock_waits = on # deadlock_timeout = 1s (défaut)

…[1074]: db=app_db,user=batch,app=batch_job,client=172.28.0.20 LOG:
    duration: 12045.334 ms  statement:
    UPDATE lock_test SET value = value + 50 WHERE id = 1;
…[1075]: db=app_db,user=batch,app=batch_job,client=172.28.0.20 LOG:
    process 1075 still waiting for ShareLock on transaction 863
    after 1005.176 ms
…[1075]: DETAIL: Process holding the lock: 1074. Wait queue: 1075.
…[1075]: STATEMENT:
    UPDATE lock_test SET value = value + 100 WHERE id = 1;

pas par défaut

Fichier temporaires

lien avec work_mem

log_temp_files = 0 # 100kB, 1MB…

…: db=appdb,user=analytics,app=reporting,client=10.0.171.10 LOG:
    temporary file: path "base/pgsql_tmp/pgsql_tmp1849757.0",
    size 2756640
…: db=appdb,user=analytics,app=reporting,client=10.0.171.10 STATEMENT:
    SELECT * FROM large_transactions
    ORDER BY amount DESC, created_at, transaction_id
    LIMIT 10000;

pas par défaut

Autovacuum

maintenance automatique Postgres
critique pour les écritures

log_autovacuum_min_duration = 0 # 100ms, 1s

…: LOG:  automatic vacuum of table "appdb.public.app_lock":
    index scans: 1
    pages: 0 removed, 12600 remain, 0 skipped due to pins
    tuples: 74 removed, 1464 remain, 172 are dead but not yet removable
…: LOG:  automatic aggressive vacuum to prevent wraparound
    of table "appdb.public.enq_pli": index scans: 0
    pages: 0 removed, 7135 remain, 1 scanned (0.01% of total)
    tuples: 0 removed, 244187 remain
    new relfrozenxid: 820687730, which is 200001140 XIDs
    ahead of previous value

pas par défaut

Requêtes

log_min_duration_statement = 0 # 100ms, 1s

…: db=appdb,user=app_user,app=backend-api,client=10.0.170.83 LOG:
    duration: 3118.394 ms  execute dbdpg_p33048_42:
    SELECT DISTINCT vrp.valeur
    FROM referentiel_param vrp
    WHERE vrp.code = $1

pas par défaut

Auto EXPLAIN

pas de redémarrage nécessaire
sample_rate = 0.1 → ~10% d’overhead

session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on
auto_explain.sample_rate = 0.1 # 10% des requêtes

…: db=appdb,user=app_user,app=backend-api,client=10.0.170.83 LOG:
    duration: 3118.394 ms  plan:
    Query Text: SELECT * FROM orders WHERE status = $1
    Seq Scan on orders (cost=0.00..1250.00 rows=5000 width=64)
      (actual time=0.015..3118.100 rows=4832 loops=1)
      Filter: (status = 'pending')
      Rows Removed by Filter: 95168

pas par défaut

Auto EXPLAIN logge automatiquement le plan d’exécution des requêtes lentes. Avec session_preload_libraries : pas de redémarrage, actif sur les nouvelles sessions. On peut aussi cibler un user : ALTER ROLE app_user SET session_preload_libraries = ‘auto_explain’; Ou charger dans la session courante : LOAD ‘auto_explain’;

sample_rate = 0.1 : seules 10% des requêtes sont instrumentées (timing des noeuds). → ~10% d’overhead (mesuré sur un workload e-commerce, PG 18, Linux bare metal) → 95% de chance d’avoir au moins un plan après ~30 exécutions de la même requête Sans sample_rate (défaut = 1.0) : TOUTES les requêtes sont instrumentées, même celles sous le seuil log_min_duration → ~20% d’overhead. Le seuil ne réduit que le volume de logs, pas le coût CPU d’instrumentation.

log_analyze = on ajoute les temps réels (EXPLAIN ANALYZE) — plus coûteux mais indispensable pour voir les écarts estimation/réalité. Complémentaire de log_min_duration_statement : on voit la requête ET son plan.

Checkpoints

log_checkpoints = on

…: [107-1] user=,db= LOG:  checkpoint starting: time
…: [108-1] user=,db= LOG:  checkpoint complete: wrote 2425 buffers (0.3%); 
    0 transaction log file(s) added, 0 removed, 15 recycled; 
    write=305.997 s, sync=21.950 s, total=327.995 s; 
    sync files=842, longest=12.712 s, average=0.027 s; 
    distance=236621 kB, estimate=245993 kB

par défaut !!

Exploitation des logs

awk

Combien de fichiers temporaires ? Quelle taille totale ?

awk '/temporary file/{n++;s+=$NF}
  END{print n" fichiers, "s" octets"}' \
  pg.log

7 fichiers, 58805856 octets

grep, jq

Combien de fichiers temporaires ? Quelle taille totale ?

grep "temporary file" pg.json | jq -rs '
  [.[] | .message
       | capture("size (?<s>[0-9]+)").s
       | tonumber]
  | {nb: length, octets: add}'

{"nb": 7, "octets": 58805856}

pgBadger

pgbadger postgresql.log -o report.html

Perl
tous formats PG, autodetect
rapport HTML seulement

quellog

extrêmement rapide
CLI
dev & ops friendly

Démo

quellog – pour commencer

un seul binaire Go
linux, macOS, windows
tous formats PG : stderr, csv, json, syslog
PG officiel, CNPG, cloud providers…
0 configuration
licence PostgreSQL

quellog – déduire

connexions/déconnexions par user, app, IP
requêtes lentes avec paramètres
verrous : qui bloque qui, combien de temps
fichiers temporaires : quelle requête, quelle taille
erreurs applicatives : type, fréquence, source
checkpoints et activité autovacuum

Observabilité PostgreSQL

Questions

Qui-suis-je ?

Point important

Menu

Observabilité PostgreSQL

Observabilité ?

Observabilité ?

Observabilité ?

Quels symptômes ?

Quels Problèmes ?

Outils

Vues internes

Extension/vue pg_stat_statements

Tableaux de bord

Tableaux de bord – les limites

Un super outil d’observabilité

…les logs :)

Configuration des logs

Langue

Format

Préfixe

Connexions

Déconnexions

Verrous

Fichier temporaires

Autovacuum

Requêtes

Auto EXPLAIN

Checkpoints

Exploitation des logs

awk

grep, jq

pgBadger

quellog

Démo

quellog – pour commencer

quellog – déduire

quellog – exploiter

Conclusion

Références

Merci !

Extension/vue `pg_stat_statements`