🐘 Signaux d'Alertes & Paramètres Critiques

1.1 Hit ratio global & par base

Où regarder : pg_stat_database (blks_hit, blks_read).

                        SELECT datname,
                        blks_hit,
                        blks_read,
                        100.0 * blks_hit / NULLIF(blks_hit + blks_read,0) AS hit_ratio
                        FROM pg_stat_database
                        ORDER BY hit_ratio ASC;
                    

• En OLTP, viser > 98–99 % sur les bases critiques.
• En workload analytique, < 90–95 % peut être normal mais doit être compris.

Conséquences quand ça passe sous les seuils

• Explosions d’I/O disque, latence qui s’envole sous charge.
• CPU souvent bas, mais serveur « bloqué » en attente d’I/O.
• Souvent le symptôme de :
  • shared_buffers trop petit,
  • requêtes non indexées,
  • scans séquentiels massifs sur des tables « hot ».

1.1 bis Hit ratio par table/index

Où regarder : pg_statio_user_tables, pg_statio_user_indexes.

                        SELECT relname,
                        heap_blks_read, heap_blks_hit,
                        100.0 * heap_blks_hit
                        / NULLIF(heap_blks_hit + heap_blks_read,0) AS heap_hit_ratio
                        FROM pg_statio_user_tables
                        ORDER BY heap_hit_ratio ASC
                        LIMIT 20;
                    

• Met en avant quelques tables « hot » qui détruisent le cache.
• Compléter par une vue similaire sur pg_statio_user_indexes.
• Point d’entrée pour prioriser les index et les refactorings de requêtes.

1.2 Où regarder & signaux

• pg_stat_database : temp_files, temp_bytes.
• Logs avec log_temp_files activé pour voir les sorts/hash qui sortent sur disque.

                        SELECT datname,
                        temp_files,
                        pg_size_pretty(temp_bytes) AS temp_bytes
                        FROM pg_stat_database
                        ORDER BY temp_bytes DESC;
                    

• Red flag : beaucoup de temp files, temp_bytes qui grimpe rapidement sur une base OLTP.

Paramètres impliqués

• work_mem (par opération, pas par session).
• maintenance_work_mem (VACUUM, CREATE INDEX, etc.).

Conséquences & cas typiques

• Disque /tmp ou tablespace temporaire saturé → erreurs & crash si plus de place.
• Requêtes qui passent de 50 ms à 5 s dès que la charge monte.
• Quelques requêtes mal écrites suffisent à plomber tout le cluster.
• Anti-pattern classique : work_mem trop bas + gros ORDER BY / GROUP BY sur des tables chaudes.

Surveiller les temp files est un excellent indicateur de dimensionnement mémoire et de qualité des requêtes.

2.1 Sessions qui attendent des locks

Où regarder :

• pg_stat_activity : state, wait_event_type, wait_event.
• pg_locks : verrous détenus vs en attente.
• pg_stat_database.deadlocks.

                        SELECT a.pid, a.usename, a.application_name,
                        now() - a.query_start AS age,
                        a.state, a.wait_event_type, a.wait_event,
                        l.locktype, l.mode, l.granted,
                        a.query
                        FROM pg_stat_activity a
                        JOIN pg_locks l ON l.pid = a.pid
                        WHERE a.pid <> pg_backend_pid()
                        ORDER BY age DESC;
                    

• Red flags :
  • beaucoup de lignes avec granted = false sur des tables/index critiques,
  • deadlocks qui s’accumulent dans pg_stat_database.

2.2 Transactions longues & idle in transaction

Où regarder : toujours pg_stat_activity.

                        SELECT pid, usename, application_name,
                        now() - xact_start AS tx_age,
                        state, state_change,
                        wait_event_type, wait_event,
                        query
                        FROM pg_stat_activity
                        WHERE xact_start IS NOT NULL
                        ORDER BY tx_age DESC;
                    

• Transactions ouvertes depuis des minutes / heures avec state = 'idle in transaction'.
• Transactions READ COMMITTED qui auraient dû être très courtes.

Conséquences

• Vacuum bloqué → tuples morts qui s’accumulent → bloat & temps de scan qui explosent.
• Files d’attente monstrueuses sur les tables chaudes (queues, séquences, journaux d’événements).
• Timeouts applicatifs, erreurs HTTP 500, rollback en cascade.
• Sur réplication logique, ces transactions peuvent bloquer la progression du WAL.

3.1 Autovacuum en retard / désactivé

• pg_stat_all_tables : last_vacuum, last_autovacuum, n_dead_tup.
• pg_stat_user_tables pour les tables applicatives clés.
• pg_database : age(datfrozenxid).

                        SELECT relname,
                        n_live_tup, n_dead_tup,
                        last_autovacuum,
                        now() - last_autovacuum AS autovacuum_age
                        FROM pg_stat_user_tables
                        ORDER BY n_dead_tup DESC
                        LIMIT 20;
                    

• Tables très actives avec last_autovacuum très ancien et n_dead_tup énorme.

Paramètres fréquemment mal réglés

• autovacuum = off (oui, ça existe encore…).
• autovacuum_vacuum_scale_factor trop haut sur les grosses tables (0.2 par défaut).
• autovacuum_vacuum_cost_limit / autovacuum_vacuum_cost_delay trop restrictifs.

Bloat & wraparound

                        SELECT datname,
                        age(datfrozenxid)               AS age_xid,
                        current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age
                        FROM pg_database
                        ORDER BY age_xid DESC;
                    

• Si age(datfrozenxid) se rapproche trop de autovacuum_freeze_max_age, risque de wraparound.
• Postgres peut forcer des vacuums de secours, voire bloquer les nouvelles transactions si on ignore les warnings.
• Bloat massif (tables & index) → tout devient plus lourd à lire, cache saturé, scans 10× plus longs.

5.1 Paramètres mémoire structurels

• shared_buffers : souvent ridiculement bas (128–512 Mo) sur des serveurs à 32–64 Go.
• effective_cache_size : laissé à 4 Go par défaut, ce qui fait croire à l’optimiseur qu’il n’y a presque pas de cache.
• work_mem, maintenance_work_mem : à calibrer selon les workloads (cf. temp files).

Conséquence : l’optimiseur sous-utilise les index, évite certains hash joins et choisit des plans catastrophiques alors que la machine a de la RAM.

5.2 Trop / pas assez de work_mem

• Trop bas → explosions de temp_files / temp_bytes.
• Trop haut sur un cluster avec beaucoup de connexions → surconsommation RAM, swap, voire OOM killer.

6.1 max_connections & pools

• Anti-pattern : max_connections à 500 ou 1000, aucune couche de pooling.
• Beaucoup de sessions idle ou idle in transaction.
• Chaque connexion consomme de la mémoire (stack, buffers, structures internes).

                        SHOW max_connections;

                        SELECT state, count(*)
                        FROM pg_stat_activity
                        GROUP BY state;
                    

Sous pic de charge, la montée en latence devient non linéaire : le noyau lutte pour gérer le nombre de processus, Postgres passe plus de temps à gérer sa propre concurrence qu’à traiter les requêtes.

• Imposer un pool type PgBouncer en mode transaction sur OLTP.
• Abaisser max_connections à un niveau raisonnable et réserver des marges pour l’admin/maintenance.

Étape 1 – Constater & stabiliser

• Confirmer l’impact : latence globale, timeouts appli, erreurs HTTP 500.
• Limiter les entrées :
  • Réduire les workers applicatifs / threads web.
  • Couper temporairement certains batchs / jobs lourds.
• Regarder CPU vs I/O :
  • CPU haut + disques OK → suspicion de locks / plans foireux.
  • CPU bas + disques saturés → manque de cache, scans & temp files.

Étape 2 – Localiser le problème

• pg_stat_activity : sessions les plus anciennes, wait_event_type, wait_event.
• pg_locks : objets avec beaucoup de verrous en attente.
• pg_stat_database : temp_bytes, deadlocks.
• pg_stat_statements : top requêtes par total_time.

Étape 3 – Actions à chaud

• Tuer ou suspendre les transactions très longues qui bloquent des verrous stratégiques.
• Stopper les batchs gourmands (exports massifs, reporting ad-hoc) si nécessaire.
• En cas de temp files monstrueux, couper la/les requêtes fautives et relever les plans.
• Sur réplication sync lente : passer temporairement en async si le métier le tolère, le temps de corriger.

Étape 4 – Après l’incident

• Documenter les indicateurs qui ont viré au rouge : hit ratio, temp files, deadlocks, checkpoints, etc.
• Créer des vues / tableaux de bord pour les suivre en continu.
• Lier chaque signal faible à une action : tuning, ajout d’index, refonte de requêtes, modification d’architecture.