Debian 13 : PostgreSQL semble lent de façon aléatoire — les 8 vérifications qui révèlent le vrai goulet d’étranglement

février 2, 2026 • février 3, 2026 • Lecture : 29 min • Views: 9

Cet article vous a aidé ?

« Lent de façon aléatoire » est ce que les gens disent quand ils n’ont pas encore de métrique qui explique la douleur. Votre application va bien pendant une heure, puis un paiement prend 12 secondes, puis tout redevient normal. Personne n’a changé quoi que ce soit (sauf les choses qui ont définitivement changé). Vous êtes sur Debian 13, PostgreSQL est « sain », et pourtant la base donne l’impression de réfléchir à ses choix de vie.

Ce n’est pas un problème de vibes. C’est presque toujours l’un de quelques goulets d’étranglement qui tournent : latence IO, pression WAL, attentes de verrous, reclamation mémoire, autovacuum, vol CPU, résolutions DNS, ou tempêtes de connexions. L’astuce est d’arrêter de fixer top et de lancer des vérifications qui séparent « requête lente » de « système lent ».

Playbook de diagnostic rapide (premier/deuxième/troisième)

Si vous êtes de garde, vous n’avez pas le temps pour un séminaire de philosophie. Vous avez besoin d’une séquence qui réduit rapidement l’espace de recherche, sans « corriger » accidentellement le symptôme en redémarrant des choses.

Premier : prouver si c’est PostgreSQL ou l’hôte

Vérifier si des sessions attendent. Si vous avez beaucoup d’activité wait_event, il s’agit probablement de contention ou d’IO, pas de « SQL lent ».
Vérifier la latence du stockage maintenant. Si les lectures/écritures montent à des dizaines ou centaines de millisecondes, PostgreSQL semblera « lent de façon aléatoire » peu importe la qualité de vos index.
Vérifier la file d’exécution CPU et le steal. Si vous manquez de CPU (ou êtes sur un VM bruyant), les requêtes stagnent même si l’IO est bon.

Second : identifier la catégorie du goulet d’étranglement

Verrous : les sessions bloquées s’accumulent ; une mauvaise transaction tient un verrou et ruine la journée de tout le monde.
WAL / commits : « les mises à jour simples sont lentes » avec beaucoup de temps en commit ; la latence pointe vers le flush WAL/le cache du périphérique.
Autovacuum : lenteurs périodiques ; augmentation de l’IO ; activité vacuum ou analyze coïncidant avec les plaintes applicatives.
Connexions : pics de création de connexions ; sauts CPU/latence ; la base passe du temps à accepter/journaliser/authentifier.

Troisième : confirmer avec une métrique de « vérité terrain »

Pour l’IO : await et l’utilisation dans iostat, plus pg_stat_io de PostgreSQL si disponible.
Pour les verrous : pg_locks avec cartographie bloqueur/bloqué.
Pour le WAL : pg_stat_wal, les checkpoints et pg_stat_bgwriter ; corrélez avec les écritures du périphérique.
Pour l’autovacuum : pg_stat_progress_vacuum et les indicateurs de bloat des tables.

Une fois que vous connaissez la catégorie, les corrections deviennent mécaniques. Jusque-là, chaque changement de « tuning » n’est qu’une nouvelle façon d’avoir tort.

Faits intéressants et contexte (pourquoi ça arrive)

Les valeurs par défaut de durabilité de PostgreSQL sont prudentes. fsync=on et les flushs WAL synchrones font des mensonges du stockage votre problème de latence, pas PostgreSQL.
Linux utilise volontiers la RAM « libre » pour le cache de pages. C’est bien… jusqu’à ce que la pression mémoire déclenche la reclamation, ce qui peut ressembler à des blocages aléatoires.
L’autovacuum existe parce que PostgreSQL utilise MVCC. Les anciennes versions de lignes ne disparaissent pas tant que le vacuum ne les nettoie pas. L’ignorer transforme votre « lent aléatoire » en lenteur permanente.
Le tuning des checkpoints est souvent mal compris depuis longtemps. Les gens augmentent checkpoint_timeout pour « réduire l’IO » et créent accidentellement de gros pics de checkpoint plus tard.
L’architecture connexion-par-requête était courante dans les premiers stacks web. Elle réapparaît dans les systèmes modernes via des pools mal configurés, et fait toujours des dégâts.
PostgreSQL 9.6 a introduit de grosses améliorations de l’autovacuum. La base s’est mieux maintenue, mais elle a toujours besoin d’un bon design de table et de surveillance.
Le NVMe moderne peut être rapide et malgré tout provoquer des blocages. Les pics de latence dus à la collecte de déchets du firmware ou au comportement du cache d’écriture peuvent transformer un « stockage rapide » en « stockage capricieux ».
Les choix par défaut de Debian favorisent la stabilité. C’est parfait pour éviter les plantages ; cela signifie aussi que vous devriez choisir activement des réglages de performance pour un hôte de base de données.
La compression et les checksums WAL sont des compromis. Ils peuvent améliorer les schémas d’IO ou la sécurité, mais changent aussi les caractéristiques CPU et de latence.

Une citation à garder à portée de main quand vous êtes tenté de deviner : L’espoir n’est pas une stratégie. — Général Gordon R. Sullivan.

Les 8 vérifications qui révèlent le vrai goulet d’étranglement

Vérification 1 : Les requêtes sont-elles lentes, ou elles attendent ?

« Lent » est souvent « en attente ». PostgreSQL distingue le travail CPU actif des attentes sur verrous, IO, flush WAL, lecture client, et une douzaine d’autres choses. Le moyen le plus rapide d’arrêter de deviner est de regarder wait_event_type et wait_event.

Si la plupart des sessions sont active sans attentes, vous avez un problème de CPU ou de plan de requête. Si la plupart attendent sur Lock, votre cause racine est la contention de verrous. Si elles attendent sur IO ou WAL, c’est le stockage et le chemin d’écriture.

Vérification 2 : L’hôte montre-t-il des pics de latence disque ?

« Lenteur aléatoire » est la description en langage courant de la variance de latence. Les bases détestent la variance. Quelques écritures à 200 ms ne semblent pas inquiétantes sur des graphes de débit, mais elles transforment des commits en timeouts et font paraître votre appli hantée.

Sur Debian 13, utilisez iostat pour une visibilité immédiate. Vous surveillez await et %util. Un %util élevé avec await en hausse signifie que le périphérique est saturé ou se met en file d’attente. Un %util bas avec await élevé pointe souvent vers des problèmes de stockage sous-jacents (virtualisation, contrôleur, multipath, flush du write cache).

Vérification 3 : Le WAL est-il le vrai goulet (latence de commit) ?

Beaucoup d’équipes poursuivent des « requêtes lentes » alors que la vraie douleur est les commits. Si votre charge est write-heavy, chaque transaction a besoin du WAL. Si la latence de flush WAL pique, tout ce qui commit devient lent — même les petites mises à jour.

Symptômes : des inserts/updates qui prennent normalement des millisecondes prennent parfois des secondes, et les lectures ne sont pas aussi affectées. Cela pointe loin des plans de requête et vers le chemin d’écriture.

Vérification 4 : Les checkpoints et le background writer font-ils monter l’IO ?

Les checkpoints sont nécessaires. Ils sont aussi une source fréquente de blocages périodiques quand ils sont mal réglés ou quand le stockage ne peut absorber les rafales. PostgreSQL essaie d’étaler l’IO des checkpoints, mais cela peut quand même s’agglomérer sous pression.

Si vous voyez « tous les N minutes tout ralentit » régulièrement, suspectez le comportement des checkpoints, l’autovacuum, ou des jobs externes (sauvegardes, scrubs, rotation de logs). N’optimisez pas à l’aveugle. Mesurez.

Vérification 5 : Les verrous s’empilent-ils derrière une transaction ?

La contention de verrous est la plus « aléatoire » des lenteurs car elle dépend du timing. Une longue transaction qui tient un verrou pendant 30 secondes n’est pas un problème… jusqu’aux mauvais 30 secondes.

Votre objectif est d’identifier le bloqueur et la liste des victimes, puis décider si vous devez tuer le bloqueur, corriger le chemin applicatif, ou changer le comportement d’isolation/verrouillage.

Vérification 6 : L’autovacuum vous fait-il concurrence (ou échoue-t-il silencieusement) ?

L’autovacuum, c’est sortir les poubelles : on ne le remarque que quand personne ne l’a fait. Lorsqu’il est trop agressif, il concurrence votre charge pour l’IO et le CPU. Lorsqu’il est trop faible, le bloat augmente, les index gonflent, et la performance se dégrade lentement.

Sur un système chargé, un autovacuum mal réglé peut causer des pics d’IO périodiques qui paraissent aléatoires aux équipes applicatives mais sont très réguliers pour qui les trace.

Vérification 7 : La pression mémoire provoque-t-elle de la reclamation ou du swap ?

PostgreSQL plus le cache page Linux plus « encore un sidecar » peuvent pousser un hôte en reclamation. Vous verrez de l’usage CPU, mais ce n’est pas du « vrai » CPU ; c’est le noyau qui tente de libérer de la mémoire. La base « ralentit au hasard » parce qu’elle attend littéralement que des pages mémoire deviennent disponibles.

Si vous voyez du swap actif sur un hôte PostgreSQL et que vous ne l’avez pas planifié, considérez-le comme une alarme incendie. Ce n’est peut‑être pas un feu, mais vous ne l’ignorez pas.

Vérification 8 : Le chemin réseau/auth/DNS injecte-t-il de la latence ?

Oui, le DNS peut ralentir PostgreSQL. Les recherches DNS inverses dans la journalisation, les hiccups LDAP, ou la perte de paquets entre l’appli et la BD peuvent aussi. La base peut être parfaite tandis que les connexions stagnent ou que l’authentification pause. « Lent de façon aléatoire » du point de vue de l’appli, parfaitement constant du point de vue du réseau.

Également : tempêtes de connexions. Quand un pool casse et que chaque requête ouvre sa propre connexion, PostgreSQL dépense du temps en gestion de processus et en surcharge d’auth. Vos requêtes ne sont pas devenues plus lentes ; votre système est devenu occupé à faire le mauvais travail.

Blague #1 : « La lenteur aléatoire » est ce que dit un système quand il veut que vous installiez de la supervision mais que vous continuez à lui offrir des ondes positives.

Tâches pratiques : commandes, signification des sorties, décisions

Ci‑dessous des tâches pratiques à exécuter sur Debian 13 et dans PostgreSQL. Chacune inclut : une commande, ce que signifie la sortie, et la décision à en tirer. Exécutez-les pendant une lenteur si possible ; sinon, exécutez‑les maintenant pour établir des baselines.

Tâche 1 : Voir qui attend et sur quoi

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT now(), state, wait_event_type, wait_event, count(*) FROM pg_stat_activity GROUP BY 1,2,3,4 ORDER BY count(*) DESC;"
              now              | state  | wait_event_type | wait_event | count
-------------------------------+--------+-----------------+------------+-------
 2025-12-29 10:14:02.12345+00 | active |                 |            |    12
 2025-12-29 10:14:02.12345+00 | active | IO              | DataFileRead |     7
 2025-12-29 10:14:02.12345+00 | active | Lock            | relation   |     5
 2025-12-29 10:14:02.12345+00 | idle   | Client          | ClientRead |    30

Ce que ça signifie : Vous catégorisez la douleur. IO/DataFileRead suggère une latence de lecture ou des défauts de cache. Lock/relation suggère de la contention. Beaucoup de ClientRead veut dire que les clients sont inactifs, pas la BD.

Décision : Si les attentes se concentrent sur un type, sautez directement à la vérification pertinente (IO, verrous, WAL). Si c’est majoritairement active sans attentes, concentrez‑vous sur le CPU et les plans de requête.

Tâche 2 : Identifier la seule pire requête en cours

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT pid, now()-query_start AS runtime, state, wait_event_type, wait_event, left(query, 120) AS query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY runtime DESC LIMIT 5;"
 pid  |  runtime  | state  | wait_event_type |  wait_event  |                         query
------+-----------+--------+-----------------+--------------+--------------------------------------------------------
 8421 | 00:02:31  | active | Lock            | transactionid| UPDATE orders SET status = $1 WHERE id = $2
 9112 | 00:01:04  | active | IO              | DataFileRead | SELECT ... FROM order_items WHERE order_id = $1

Ce que ça signifie : Si la requête la plus longue attend un verrou, ce n’est pas du « SQL lent » ; elle est bloquée. Si elle attend de l’IO, vous cherchez le stockage ou le comportement du cache.

Décision : Pour des attentes de verrous, trouvez le bloqueur (Tâche 7). Pour des attentes IO, vérifiez la latence du périphérique (Tâche 4) et le ratio de cache (Tâche 10).

Tâche 3 : Vérification rapide de la charge hôte et de la file d’exécution

cr0x@server:~$ uptime
 10:14:10 up 38 days,  2:11,  3 users,  load average: 18.42, 17.90, 16.12

Ce que ça signifie : La charge moyenne n’est pas l’usage CPU ; c’est tâches exécutables + tâches non interruptibles. Une charge de 18 sur une machine 8 cœurs signifie généralement que vous êtes en file pour le CPU ou coincé dans des attentes IO.

Décision : Corrélez avec la Tâche 4 (IO) et la Tâche 5 (CPU). Charge élevée + forte attente IO pointe vers le stockage. Charge élevée + CPU élevé pointe vers le calcul ou les plans de requête.

Tâche 4 : Mesurer la latence disque et la saturation avec iostat

cr0x@server:~$ sudo iostat -xz 1 5
Linux 6.12.0-debian (db01) 	12/29/2025 	_x86_64_	(16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          22.11    0.00    6.90   18.30    0.00   52.69

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   w_await wareq-sz  aqu-sz  %util
nvme0n1         820.0  55200.0     0.0    0.0   12.4    67.3     610.0   48800.0   45.8    80.0    42.3  98.7

Ce que ça signifie : w_await à ~46ms avec %util ~99% est un signal d’alarme. Les commits et checkpoints PostgreSQL vont se sentir lents. %iowait est aussi élevé.

Décision : Traitez le stockage comme suspect principal. Étapes suivantes : vérifier si c’est le même périphérique que PGDATA, confirmer les paramètres du write cache, investiguer les IO concurrents, et vérifier les options de montage du système de fichiers.

Tâche 5 : Vérifier rapidement saturation CPU vs attente IO

cr0x@server:~$ mpstat -P ALL 1 3
Linux 6.12.0-debian (db01) 	12/29/2025 	_x86_64_	(16 CPU)

12:14:20 PM  CPU   %usr  %sys  %iowait  %steal  %idle
12:14:21 PM  all   28.0   7.0    2.0     0.0    63.0
12:14:21 PM   3   92.0   5.0    0.0     0.0     3.0
12:14:21 PM   7   88.0   6.0    0.0     0.0     6.0

Ce que ça signifie : Quelques CPUs chauds peuvent indiquer un point chaud mono‑fil (souvent une requête, une construction d’index, un worker autovacuum, ou un chemin d’écriture WAL). %steal non nul sur des VM indique des voisins bruyants.

Décision : Si le CPU est saturé avec peu d’attente IO, inspectez les requêtes top et leurs plans. Si steal est élevé, impliquez l’équipe virtualisation/plateforme.

Tâche 6 : Détecter la pression mémoire et le swapping

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:            64Gi        51Gi       1.2Gi       1.1Gi        12Gi        7.8Gi
Swap:            8Gi       2.5Gi       5.5Gi

Ce que ça signifie : Du swap utilisé sur un hôte BD est une taxe de performance. Ça peut être historique (swapé une fois, jamais remonté) ou un thrash actif.

Décision : Confirmez l’activité swap avec vmstat (Tâche 12). Si swap actif, réduisez la pression mémoire : diminuer shared_buffers si trop ambitieux, réduire le nombre de connexions, arrêter les workloads co‑localisés, ou ajouter de la RAM.

Tâche 7 : Trouver bloqueurs et victimes (contention de verrous)

cr0x@server:~$ sudo -u postgres psql -X -c "
WITH locked AS (
  SELECT pid, locktype, relation::regclass AS rel, transactionid, mode, granted
  FROM pg_locks
),
activity AS (
  SELECT pid, usename, application_name, client_addr, state, now()-xact_start AS xact_age, left(query,120) AS query
  FROM pg_stat_activity
)
SELECT a.pid AS blocked_pid, a.usename, a.application_name, a.xact_age, a.query,
       b.pid AS blocker_pid, b.usename AS blocker_user, b.application_name AS blocker_app, b.xact_age AS blocker_xact_age, b.query AS blocker_query
FROM locked l1
JOIN locked l2 ON l1.locktype = l2.locktype
  AND coalesce(l1.rel::text,'') = coalesce(l2.rel::text,'')
  AND coalesce(l1.transactionid::text,'') = coalesce(l2.transactionid::text,'')
  AND l1.pid <> l2.pid
JOIN activity a ON a.pid = l1.pid
JOIN activity b ON b.pid = l2.pid
WHERE NOT l1.granted AND l2.granted
ORDER BY a.xact_age DESC;
"
 blocked_pid | usename | application_name | xact_age |                        query                        | blocker_pid | blocker_user | blocker_app | blocker_xact_age |           blocker_query
------------+---------+------------------+----------+-----------------------------------------------------+------------+--------------+-------------+------------------+----------------------------------
       8421 | app     | api              | 00:02:31 | UPDATE orders SET status = $1 WHERE id = $2         |       7710 | app          | worker      | 00:18:09         | UPDATE orders SET ... WHERE ...

Ce que ça signifie : Une longue transaction bloque les autres. Le bloqueur est en transaction depuis 18 minutes. Ce n’est presque jamais acceptable en OLTP.

Décision : Décidez de terminer le bloqueur (attention : il fera un rollback), ou de corriger le comportement applicatif (index manquant causant un update long, portée de transaction trop large, attente d’un appel externe en‑cours de transaction).

Tâche 8 : Inspecter la santé du WAL et des checkpoints

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT * FROM pg_stat_bgwriter;"
 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_backend_fsync | buffers_alloc
------------------+-----------------+-----------------------+----------------------+--------------------+---------------+------------------+----------------+-----------------------+--------------
             1289 |             412 |               8123456 |               923456 |           73456789 |       1234567 |             9876 |        2345678 |                 12345 |     987654321

Ce que ça signifie : Un checkpoints_req élevé suggère que vous forcez des checkpoints à cause du volume WAL, pas juste du calendrier. Des temps d’écriture/sync de checkpoint élevés corrèlent avec des blocages IO.

Décision : Si des checkpoints demandés sont fréquents, pensez à augmenter max_wal_size et assurer que checkpoint_completion_target est raisonnable. Mais ne « tunez » pas tant que vous n’avez pas confirmé la capacité IO et le comportement du périphérique WAL.

Tâche 9 : Regarder les stats WAL (quand disponibles)

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, wal_write, wal_sync, stats_reset FROM pg_stat_wal;"
 wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync |        stats_reset
------------+---------+-----------+------------------+-----------+----------+-------------------------------
   88234567 |  123456 | 98 GB     |             4212 |     91234 |    45678 | 2025-12-27 00:00:00+00

Ce que ça signifie : Des wal_buffers_full fréquents suggèrent une pression sur le buffering WAL. Un nombre élevé d’écritures/sync WAL n’est pas forcément mauvais ; corrélez‑les avec la latence du périphérique et les temps de commit.

Décision : Si la pression WAL coïncide avec des blocages, envisagez un stockage dédié et rapide pour le WAL, validez les attentes sur synchronous_commit, et inspectez la latence de commit côté appli.

Tâche 10 : Vérifier le ratio cache (avec avertissement)

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT datname, blks_hit, blks_read, round(100.0*blks_hit/GREATEST(blks_hit+blks_read,1),2) AS hit_pct FROM pg_stat_database ORDER BY hit_pct ASC;"
 datname  | blks_hit  | blks_read | hit_pct
----------+-----------+-----------+---------
 appdb    | 987654321 | 45678901  | 95.59

Ce que ça signifie : Le ratio de hit n’est pas un KPI auquel vous devez vouer un culte ; c’est un indice. 95 % peut être bien ou catastrophique selon la charge. Une chute soudaine pendant un incident compte plus que la valeur absolue.

Décision : Si le ratio chute pendant les lenteurs et que la latence IO monte, vous déversez sur disque. Examinez la mémoire, la taille du working set, et si les patterns de requêtes ont changé.

Tâche 11 : Trouver les plus gros consommateurs de temps avec pg_stat_statements

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT queryid, calls, round(total_exec_time::numeric,1) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms, rows, left(query,120) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"
 queryid  | calls  | total_ms | mean_ms | rows  |                          query
----------+--------+----------+---------+-------+--------------------------------------------------------
 12345678 | 998765 | 8123456.0|   8.13  | 998765| SELECT * FROM users WHERE email = $1
 88776655 |  12345 | 6234567.0| 505.12  |  12345| SELECT ... FROM orders JOIN ... WHERE created_at > $1

Ce que ça signifie : total_exec_time identifie le coût agrégé le plus important. Les requêtes avec un mean élevé sont des tueuses de latence ; celles avec un total élevé sont des tueuses de capacité.

Décision : Pour les requêtes à mean élevé, exécutez EXPLAIN (ANALYZE, BUFFERS) en environnement sûr. Pour les totaux élevés, envisagez cache, index, ou modification de requête. Si le temps de requête ne grimpe qu’en incident, corrélez d’abord avec les attentes et les métriques hôtes.

Tâche 12 : Confirmer swap actif ou reclamation avec vmstat

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 5  2 262144 312000  80000 9000000  12   38   120   890  900 2400 18  6 55 21
 7  1 262200 298000  76000 8800000   0   52    80  1200 1100 2700 20  7 48 25

Ce que ça signifie : si/so non nuls signifient swap in/out. Ce n’est pas « peut‑être ». C’est « votre base paye le loyer au périphérique swap ». Notez aussi les patterns b (bloqué) et wa (attente IO).

Décision : Si le swap est actif, arrêtez de tuner le SQL et corrigez la pression mémoire. Réduisez la concurrence, ajoutez de la RAM, ajustez le placement des workloads, ou stoppez les processus hors contrôle.

Tâche 13 : Vérifier le système de fichiers et les options de montage pour PGDATA

cr0x@server:~$ findmnt -no SOURCE,FSTYPE,OPTIONS /var/lib/postgresql
/dev/mapper/vg0-pgdata ext4 rw,relatime,errors=remount-ro

Ce que ça signifie : Vous voulez savoir sur quel système de fichiers vous êtes et si les options de montage sont étranges. Pour PostgreSQL, des options « intelligentes » deviennent souvent des rapports d’incident.

Décision : Si vous voyez des options exotiques (comme désactiver les barriers) sans raison solide et sans validation matérielle, revenez à des valeurs sûres et corrigez le vrai problème de performance ailleurs.

Tâche 14 : Valider que PostgreSQL n’est pas limité par des cgroups

cr0x@server:~$ systemctl show postgresql --property=CPUQuota,MemoryMax,IOReadBandwidthMax,IOWriteBandwidthMax
CPUQuota=
MemoryMax=infinity
IOReadBandwidthMax=
IOWriteBandwidthMax=

Ce que ça signifie : Si des quotas sont définis (surtout des limites de bande passante IO), vous pouvez vous être infligé une lenteur « aléatoire » quand la charge monte et que les limites se déclenchent.

Décision : Supprimez les limites inappropriées pour un service de base de données, ou dimensionnez‑les délibérément et créez des attentes. Les bases ne supportent pas bien les régimes surprises.

Tâche 15 : Vérifier les logs PostgreSQL pour checkpoints, autovacuum et pics de durée

cr0x@server:~$ sudo journalctl -u postgresql@15-main -S -2h | egrep -i "checkpoint|autovacuum|duration|could not|timeout" | tail -n 15
Dec 29 09:02:10 db01 postgres[2210]: LOG:  checkpoint complete: wrote 32145 buffers (1.9%); 0 WAL file(s) added, 2 removed, 1 recycled; write=78.123 s, sync=1.992 s, total=80.256 s
Dec 29 09:10:44 db01 postgres[2210]: LOG:  automatic vacuum of table "app.public.orders": index scans: 1 pages: 0 removed, 123456 remain, 12000 scanned (9.72% of total) tuples: 0 removed, 500000 remain
Dec 29 09:11:02 db01 postgres[2210]: LOG:  duration: 2412.889 ms  execute <unnamed>: UPDATE orders SET ...

Ce que ça signifie : Un checkpoint qui prend 80 secondes n’est pas subtil. Une activité autovacuum proche des fenêtres d’incident peut être causale ou juste corrélée ; il vous faut néanmoins des métriques IO.

Décision : Si les temps de checkpoints sont énormes, priorisez la performance du stockage et le tuning des checkpoints. Si les pics de durée s’alignent sur des attentes de verrous, corrigez le verrouillage. Si l’autovacuum est lourd, ajustez les réglages par table et vérifiez le bloat.

Tâche 16 : Confirmer le churn de connexions (trop de nouvelles connexions)

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database;"
 datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit
--------+-------------+-------------+---------------+----------+----------
 appdb  |         240 |   987654321 |        123456 | 45678901 | 987654321

Ce que ça signifie : numbackends est le nombre de connexions en cours. Des pics ici, surtout avec haut CPU et changements de contexte, signifient souvent un pool cassé ou un nouveau déploiement client ouvrant trop de sessions.

Décision : Si les connexions sont nombreuses, limitez‑les et imposez du pooling. Envisagez pgbouncer, mais ne l’installez pas en panique ; configurez‑le avec soin, puis validez la sémantique des transactions.

Blague #2 : Un réglage de débogage « temporaire » en production a la même demi‑vie qu’une tache de café sur une chemise blanche.

Trois mini-récits d’entreprise (ce qui a mal tourné, ce qui a marché)

Mini-récit 1 : L’incident causé par une mauvaise hypothèse

Une entreprise SaaS de taille moyenne a migré son hôte de base de données principal de vieux SSD SATA vers de brillants NVMe. La fenêtre de changement était propre. Les benchmarks étaient excellents. Tout le monde s’attendait à moins d’incidents.

Deux semaines plus tard, la file de support a commencé à décrire des « lenteurs aléatoires » en période de pointe : paiements qui timeoutent, jobs en arrière-plan qui réessaient, et 504 API sporadiques. Les métriques de la base ne hurlaient pas. Le CPU n’était pas saturé. Le réseau semblait correct. L’explication par défaut de l’équipe est devenue « Postgres est étrange sous charge », ce qui est l’équivalent ingénierie d’un haussement d’épaules dans un tableau.

La mauvaise hypothèse : « NVMe est rapide, donc le stockage ne peut pas être le goulot ». Ils avaient regardé le débit, pas la variance de latence. Sous pression d’écriture soutenue, le périphérique faisait parfois monter la latence d’écriture à plusieurs centaines de millisecondes à cause d’un comportement interne. PostgreSQL ne se soucie pas que votre débit moyen soit bon ; il se soucie qu’un fsync ait pris 400 ms et bloqué une pile de commits derrière lui.

Ils l’ont prouvé en capturant iostat -xz 1 pendant un incident et en corrélant avec les logs de transactions lentes. Les pics se sont alignés parfaitement. Une fois qu’ils ont déplacé le WAL sur un périphérique dédié et ajusté le dimensionnement des checkpoints/WAL pour réduire les poussées de pression, la « lenteur aléatoire » est devenue une prévisibilité ennuyeuse.

Conclusion : arrêtez de traiter le stockage comme binaire (rapide/lent). Pour les bases, la variance est le produit.

Mini-récit 2 : L’optimisation qui a mal tourné

Une plateforme proche de la finance avait des pauses périodiques toutes les 15 minutes. Quelqu’un a remarqué des checkpoints dans les logs et a fait ce que l’internet suggère souvent : augmenter drastiquement checkpoint_timeout et max_wal_size pour « réduire la fréquence des checkpoints ». Ça a marché — en quelque sorte.

Les checkpoints arrivaient moins souvent, donc le graphe semblait plus lisse un moment. Mais quand un checkpoint s’exécutait, il était massif. Le sous‑système IO n’était pas conçu pour ce type de rafale. Pendant ces checkpoints, la latence montait en flèche, et l’application accumulait des retries, créant encore plus de pression d’écriture. L’incident devenait pire, moins fréquent, et plus difficile à reproduire en staging. Magnifique.

Quand ils ont finalement mesuré la bonne chose — la latence du périphérique et checkpoint_write_time — le schéma était évident. L’« optimisation » avait redistribué la douleur en moins d’explosions plus violentes. Ils ont rétabli un checkpoint_timeout plus petit, augmenté checkpoint_completion_target pour lisser l’IO, et redimensionné le WAL pour que les checkpoints ne soient pas forcés par le churn WAL.

Ils ont aussi découvert un problème secondaire : un job analytics hebdomadaire faisait de grosses mises à jour sans batching, créant des pics WAL qui forçaient les checkpoints indépendamment du timeout. Corriger ce job a réduit l’IO et le volume WAL plus efficacement que n’importe quel réglage.

Conclusion : « moins de checkpoints » n’est pas automatiquement « mieux ». Le but est une IO stable, pas des catastrophes rares.

Mini-récit 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise

Une équipe plateforme interne d’entreprise faisait tourner PostgreSQL pour plusieurs unités. Rien de glamour : intégrations paie, workflows RH, achats. Leur meilleure caractéristique était que les incidents étaient rares et brefs.

Ce qu’ils faisaient n’était pas magique. Ils tenaient un runbook écrit avec une séquence de diagnostic rapide : vérifier les waits, vérifier la latence IO, vérifier les verrous, vérifier l’autovacuum, vérifier la pression mémoire, puis décider. Ils avaient aussi des baselines : latence normale iostat, compte de connexions typique, temps de checkpoint attendu. Chaque trimestre ils faisaient un exercice de 30 minutes où quelqu’un simulait une lenteur et tout le monde s’entraînait à collecter des preuves sans redémarrer les services.

Quand une mise à jour d’agent fournisseur a commencé à marteler le disque avec des écritures de logs, l’équipe n’a pas discuté de qui était fautif. Ils ont vu la latence IO monter, confirmé que PostgreSQL attendait de l’IO, identifié le processus fautif avec des stats IO par processus, et limité l’agent. Le business ne l’a pas remarqué, sauf que leur page de statut est restée ennuyeuse.

Conclusion : la discipline ennuyeuse bat le tuning héroïque. Baselines plus playbook exercé transforment la « lenteur aléatoire » en « ticket avec preuves ».

Erreurs courantes : symptôme → cause racine → correction

Cette section est volontairement spécifique. Si vous reconnaissez le symptôme, n’en débattez pas en réunion — allez tester la cause racine.

1) Symptôme : « Tout est lent, mais le CPU est bas »

Cause racine : attentes IO (pics de latence stockage) ou contention de verrous.
Correction : Vérifiez pg_stat_activity waits et iostat -xz. Si IO : réduire les IO concurrents, déplacer le WAL, vérifier la santé du stockage. Si verrous : trouver et corriger le bloqueur ; raccourcir les transactions.

2) Symptôme : Les écritures sont lentes ; les lectures semblent normales

Cause racine : latence de flush WAL, checkpoints, ou réplication synchrone qui attend (si configurée).
Correction : Inspecter pg_stat_wal, les stats de checkpoint, et la latence d’écriture du périphérique. Valider que le WAL est sur un stockage rapide et stable. Ne pas désactiver la durabilité comme « fix de performance » sauf si vous acceptez la perte de données.

3) Symptôme : Lenteurs périodiques à intervalles réguliers

Cause racine : checkpoints, cycles d’autovacuum, cron jobs, scrubs, backups, rotation des logs, ou workloads batch.
Correction : Corréler les fenêtres temporelles avec les logs (journalctl) et les métriques. Lisser l’IO des checkpoints ; tuner l’autovacuum par table ; replanifier les jobs batch ; isoler l’IO de sauvegarde.

4) Symptôme : Pics du nombre de connexions et latence lors de pics de trafic

Cause racine : défaillance du pooling de connexions ou un déploiement client ouvrant trop de sessions.
Correction : Faire respecter le pooling et limiter max_connections à quelque chose que votre RAM peut supporter. Utiliser un pooler si nécessaire, mais le tester pour la sémantique des transactions et le comportement des statements préparés.

5) Symptôme : Timeouts « aléatoires », mais seulement pour certains endpoints

Cause racine : Un plan de requête bascule entre index scan et seq scan, ou un plan sensible aux paramètres.
Correction : Utiliser pg_stat_statements pour trouver le coupable et exécuter EXPLAIN (ANALYZE, BUFFERS) avec des paramètres représentatifs. Envisager des ajustements de plan_cache_mode prudemment, ou réécrire les requêtes pour stabiliser le plan.

6) Symptôme : Vacuum tourne constamment, mais la performance se dégrade

Cause racine : L’autovacuum n’arrive pas à suivre, ou le bloat est déjà important ; des transactions longues empêchent le nettoyage.
Correction : Identifier les longues transactions, réduire leur durée, et tuner l’autovacuum par grande table. Pour un bloat sévère, planifier une maintenance (ex. VACUUM (FULL) ou stratégies de rebuild en ligne) avec un vrai plan.

7) Symptôme : Après la mise à jour Debian, PostgreSQL semble plus lent avec la même charge

Cause racine : Changements de noyau/ordonnanceur IO, nouveaux defaults, paramètres cgroup, ou comportement modifié du système de fichiers ; parfois c’est juste un nouveau goulet révélé.
Correction : Comparer les baselines : latence IO, steal CPU, reclamation mémoire, et événements d’attente PostgreSQL. Vérifier qu’aucune limite de service nouvelle n’a été ajoutée et que huge pages, THP, ou le governor n’ont pas modifié le comportement.

Listes de contrôle / plan étape par étape

Étape par étape : pendant une lenteur active (10 minutes, sans gestes héroïques)

Capturer les waits PostgreSQL : exécuter la Tâche 1 et sauvegarder la sortie.
Capturer les pires requêtes actives : exécuter la Tâche 2.
Capturer la latence IO : exécuter la Tâche 4.
Capturer la saturation CPU : exécuter la Tâche 5.
Capturer la pression mémoire : exécuter la Tâche 6 et la Tâche 12.
Si des verrous apparaissent : exécuter la Tâche 7 et décider de terminer le bloqueur ou d’attendre.
Si WAL/checkpoint suspectés : exécuter les Tâches 8 et 9 ; corréler avec les écritures IO et les logs (Tâche 15).
Si les connexions sont élevées : exécuter la Tâche 16 ; vérifier la santé du pool et les changements de déploiement applicatif.
Noter les bornes temporelles : « lent de 10:12:40 à 10:16:10 ». La corrélation a besoin d’horodatages.

Étape par étape : après l’incident (la partie que les équipes zappent, puis répètent les incidents)

Classer l’incident : IO-bound, lock-bound, CPU-bound, memory-bound, WAL-bound, connection-bound, ou network/auth-bound.
Choisir une métrique primaire pour l’alerte : ex. await disque au‑dessus d’un seuil, nombre d’attentes de verrous, temps de checkpoint, activité swap.
Ajouter un panneau de dashboard qui aurait rendu ça évident. Pas douze. Un seul.
Faire un changement de code/config avec plan de rollback. Ne pas « tout tuner ».
Mettre à jour le runbook. Si la correction a demandé du savoir tribal, vous avez un bug de fiabilité.

FAQ

1) Pourquoi PostgreSQL semble lent alors que l’utilisation CPU est faible ?

Parce qu’il attend souvent, il ne calcule pas. Les attentes de verrous et d’IO ne consomment pas de CPU. Regardez wait_event_type dans pg_stat_activity et la latence disque avec iostat.

2) Quels nombres « await » sont mauvais dans iostat ?

Pour l’OLTP, des millisecondes soutenues à un chiffre sont généralement acceptables ; des dizaines de millisecondes soutenues posent problème ; des pics à plusieurs centaines de millisecondes seront perceptibles par les utilisateurs. Le seuil exact dépend des SLOs, mais « await qui augmente avec %util proche de 100% » est une signature classique de saturation.

3) Dois‑je augmenter shared_buffers pour corriger la lenteur ?

Pas en réflexe. Trop de shared_buffers peut affamer le cache OS et pousser le système en reclamation ou swap. Dimensionnez‑le intentionnellement et vérifiez la pression mémoire avec vmstat et free.

4) L’autovacuum est‑il censé provoquer des baisses de performance ?

Il peut, surtout quand il doit rattraper son retard. L’objectif est de le régler pour qu’il tourne en continu et discrètement plutôt qu’occasionnellement et violemment. Les réglages autovacuum par table sont souvent la bonne approche.

5) Les checkpoints peuvent‑ils provoquer « tous les N minutes tout ralentit » ?

Oui. Les checkpoints peuvent créer des rafales d’écritures. Si le stockage ne peut absorber la rafale, la latence pique. Regardez le timing des checkpoints dans les logs et pg_stat_bgwriter, puis corrélez avec la latence du périphérique.

6) Mettre synchronous_commit=off est‑ce une bonne solution pour la lenteur aléatoire ?

C’est un compromis : vous réduisez la latence de commit en acceptant une perte potentielle de données en cas de crash. Pour certaines charges c’est acceptable ; pour beaucoup, non. Considérez‑le comme une décision produit explicite, pas comme un ajustement de performance.

7) Comment savoir si les verrous sont le problème ?

Si de nombreuses sessions attendent sur Lock et que vous identifiez un bloqueur tenant des verrous longtemps (Tâche 7), les verrous sont le problème. Corrigez les transactions longues et les patterns de hot‑row.

8) Pourquoi les tempêtes de connexions font-elles autant de dégâts ?

Chaque connexion coûte en mémoire, CPU, et souvent déclenche du travail d’auth/journalisation. En situation de tempête, la base dépense des ressources à gérer des connexions au lieu d’exécuter des requêtes. Corrigez côté client avec du pooling et des limites sensées.

9) Debian 13 change‑t‑il quelque chose qui peut affecter la latence PostgreSQL ?

Les mises à jour OS peuvent modifier le comportement du noyau, l’ordonnanceur IO, et les valeurs par défaut autour des services et des cgroups. Considérez‑le comme une nouvelle baseline : validez la latence IO, le steal CPU, et la reclamation mémoire sous charge représentative plutôt que d’assumer « même chose mais plus récent ».

10) Quelle est l’extension PostgreSQL la plus utile pour le triage de performance ?

pg_stat_statements. Elle ne vous dira pas directement la latence du stockage, mais elle identifiera rapidement si quelques requêtes dominent le temps, et si la « lenteur aléatoire » est en réalité un pattern connu de requêtes.

Conclusion : étapes suivantes que vous pouvez faire aujourd’hui

PostgreSQL n’est pas lent au hasard. Votre système l’est. La base de données est juste l’endroit où vos utilisateurs le ressentent en premier.

Implémentez le playbook rapide. Faites de « waits, latence IO, verrous » les trois premières vérifications. Exercez‑vous une fois.
Basez votre hôte. Exécutez iostat, vmstat, et comptez les connexions pendant une charge normale et sauvegardez les chiffres.
Transformez la plus grande inconnue en métrique. Si vous ne savez pas si les incidents sont IO-bound ou lock-bound, vous n’avez pas une réponse d’incident ; vous avez une superstition.
Corrigez un goulet à la fois. La façon la plus rapide d’étendre une panne est de changer cinq réglages puis de discuter de celui qui a aidé.

Si vous ne faites rien d’autre : la prochaine fois que quelqu’un dira « Postgres est lent de façon aléatoire », répondez en demandant un horodatage et lancez la Tâche 1 et la Tâche 4. Vous aurez l’air psychique. Vous ne l’êtes pas. Vous mesurez juste les bonnes choses.