Debian 13 : tempêtes de connexions PostgreSQL — pooler vs tuning : ce qui fonctionne réellement (cas n°37)

Ce qu’est vraiment une tempête de connexions (et pourquoi c’est dommageable)

Une « tempête de connexions » PostgreSQL n’est pas seulement « beaucoup de connexions ». C’est le moment pathologique où le taux de nouvelles connexions
ou de tentatives de reconnexion submerge une partie du système : création de processus backend Postgres, authentification, handshake TLS, ordonnanceur CPU,
limites du noyau, latence disque, ou simplement la capacité du serveur à suivre le changement de contexte.

Postgres utilise un modèle processus-par-connexion (et non thread-par-connexion). C’est un choix d’architecture avec des avantages nets :
isolation, débogage plus simple, confinement des défaillances prévisible. Mais cela signifie aussi qu’une connexion a un coût réel :
un processus backend, consommation mémoire (work_mem n’est pas la seule variable ; il y a de l’état par backend), et coût d’ordonnancement.

Les tempêtes commencent typiquement en amont :

• Un déploiement d’application réinitialise les pools de connexions.
• L’autoscaling ajoute des pods qui se connectent tous en même temps.
• Des vérifications de santé du load balancer mal configurées deviennent un DOS de connexion.
• Un incident réseau déclenche des retries en boucle serrée.
• Une mauvaise requête fait grimper les temps de réponse ; les clients expirent et se reconnectent, multipliant la charge.

Le mode de défaillance est sournois parce qu’il est non linéaire. La base peut être « correcte » à 200 connexions stables, mais s’effondrer sous
2000 tentatives de connexion par seconde même si seulement 200 sont actives en parallèle.

Voici la règle pratique : quand le churn de connexions est le problème, augmenter max_connections revient à élargir une porte
alors que le bâtiment est en feu. Vous ne ferez que déplacer plus de fumée.

Blague #1 : Une tempête de connexions, c’est comme une pizza gratuite au bureau — personne n’en a « besoin », mais soudainement tout le monde est très motivé pour venir.

Faits et contexte qui changent votre perspective

• Le modèle processus-par-connexion de Postgres remonte à des décennies et reste un choix architectural central ; il privilégie la robustesse sur la capacité brute de fan-out de connexions.
• pg_stat_activity existe depuis longtemps ; c’est toujours le premier endroit où regarder quand la réalité contredit les tableaux de bord.
• Le TLS généralisé a changé les règles : ce qui était du trafic « connect/auth » bon marché inclut maintenant des handshakes cryptographiques plus lourds si vous terminez TLS dans Postgres.
• L’adoption de SCRAM-SHA-256 a renforcé la sécurité des mots de passe, mais une authentification plus forte peut rendre les tempêtes plus sensibles au CPU que les anciens réglages MD5 sous un churn extrême.
• Les cgroups Linux et les limites systemd sont devenus une source discrète d’incidents « ça marchait sur l’ancien OS » ; les unités gérées par systemd de Debian 13 rendent ces limites plus visibles (et applicables).
• La popularité de PgBouncer en transaction pooling n’est pas due au fait que Postgres soit « mauvais », mais parce que les applications abusent ou créent trop de connexions.
• « Idle in transaction » reste un piège classique de Postgres : cela retient des verrous et risque d’engendrer du bloat tout en paraissant « idle », ce qui ralentit la réponse en incident.
• L’observabilité s’est améliorée : Postgres expose maintenant les wait events (pg_stat_activity.wait_event) pour que vous arrêtiez de deviner si vous êtes lié au CPU, aux verrous ou à l’IO.

Une idée paraphrasée qui mérite de rester en tête provient d’un expert fiabilité :
paraphrased idea — John Allspaw : « En incident, le système a du sens pour les personnes qui y sont dedans ; corrigez les conditions, pas le blâme. »

Playbook de diagnostic rapide

Quand vous êtes de garde, vous n’avez pas le temps pour des débats philosophiques sur les poolers. Vous devez trouver le goulot d’étranglement en quelques minutes,
pas dans un postmortem. Voici l’ordre de triage qui fonctionne sur Debian 13 avec Postgres en production.

Premier point : déterminez si vous échouez à « accepter les connexions » ou à « servir les requêtes »

• Si les clients ne peuvent pas se connecter du tout : vérifiez le backlog d’écoute, les descripteurs de fichiers, les pics CPU liés à l’auth/TLS, et les limites de processus.
• Si les clients se connectent mais expirent sur les requêtes : vérifiez les verrous, l’IO, la saturation CPU, et les requêtes lentes qui causent des tempêtes de retries.

Deuxième point : classifiez la tempête

• Tempête de churn : beaucoup de connexions/déconnexions, sessions de courte durée, pg_stat_activity dominé par des sessions nouvelles.
• Tempête d’inactivité : les connexions s’accumulent et restent, beaucoup de sessions idle, probablement un mauvais pooling côté application.
• Tempête « idle in transaction » : moins de sessions mais elles retiennent des verrous et provoquent des embouteillages.
• Tempête de retries : la latence des requêtes provoque des timeouts, les retries applicatifs amplifient la charge ; souvent ce n’est pas une question de « connexions » à la racine.

Troisième point : choisissez le levier

• Utilisez un pooler quand vous devez limiter le fan-out de connexions serveur ou amortir le churn.
• Réglez Postgres quand le serveur est sous-dimensionné, mal configuré, ou bloqué (verrous/IO) et que les connexions ne sont que le symptôme.
• Corrigez l’application quand elle crée des connexions par requête, ne les réutilise pas, ou a un comportement de retry/backoff défectueux.

Pooler vs tuning : logique de décision, pas d’idéologie

Vous pouvez « résoudre » une tempête de trois façons : faire en sorte que Postgres accepte plus de connexions, réduire le nombre de connexions, ou réduire le besoin de retries.
Le piège est de supposer que ces approches sont interchangeables. Elles ne le sont pas.

Ce que le tuning peut faire (et ce qu’il ne peut pas)

Le tuning aide quand Postgres gaspille des ressources ou est bloqué. Exemples :

• Verrous : une mauvaise hygiène des transactions crée des files d’attente de verrous ; le tuning ne « réparera » pas cela, mais la surveillance des verrous et les timeouts réduiront le champ de dégâts.
• Mémoire : des réglages inappropriés de shared_buffers et de work_mem peuvent transformer une charge normale en cauchemar de swap.
• IO : stockage lent, pics de checkpoint, ou autovacuum mal réglé peuvent pousser la latence suffisamment haut pour déclencher des retries.
• CPU : une authentification coûteuse (TLS/SCRAM) plus un churn élevé peuvent dominer le CPU ; le tuning peut déplacer des pièces (ex. décharger TLS) mais n’élimine pas le churn lui-même.

Ce que le tuning ne fera pas : faire en sorte que 10 000 clients se comportent de manière responsable. S’ils se connectent comme des moustiques au crépuscule, il vous faudra une moustiquaire.

Ce qu’un pooler change

Un pooler (souvent PgBouncer) s’intercale entre les clients et Postgres, regroupant de nombreuses connexions clientes en moins de connexions serveur.
Vous obtenez :

• Des plafonds stricts sur les connexions serveur même quand les clients font un pic.
• Un établissement de connexion client plus rapide (surtout si le pooler tourne près de l’app et maintient les connexions serveur chaudes).
• Protection contre les tempêtes causées par les déploiements : les redémarrages d’app ne se traduisent pas par un churn de backend Postgres.

Mais les poolers suppriment aussi certaines garanties :

• Le pool en mode transaction casse l’état de session : tables temporaires, prepared statements, variables de session, advisory locks — gérez cela avec précaution.
• La visibilité change : vous devez surveiller les métriques du pooler, pas seulement Postgres.
• Une mauvaise configuration peut être pire que pas de pooler : des tailles de pool et des timeouts erronés peuvent créer des délais d’attente auto-infligés.

Si votre charge est orientée web (beaucoup de transactions courtes), le pooling transactionnel est généralement gagnant. Si votre charge est orientée session
(beaucoup d’état de session, transactions longues), vous pouvez quand même utiliser un pooler — mais vous devrez peut-être opter pour le pooling de session ou refactoriser l’application.

Blague #2 : Augmenter max_connections en réponse à une tempête, c’est comme acheter plus de chaises alors que l’ordre du jour de la réunion est le vrai problème.

Tâches pratiques (commandes, sorties, décisions)

Voici les tâches que j’exécute réellement pendant les incidents et lors des sessions « corrigeons ça avant que ça n’arrive ». Chaque tâche inclut : commande, sortie exemple, ce que ça signifie, et quelle décision prendre.

Task 1: Confirmez que vous atteignez les limites de connexions (côté Postgres)

cr0x@server:~$ sudo -u postgres psql -XAtc "SHOW max_connections; SHOW superuser_reserved_connections;"
200
3

Sens : Seules 197 connexions non-superuser sont effectivement disponibles.

Décision : Si vous voyez des erreurs comme « too many clients already », n’augmentez pas immédiatement cette valeur. Trouvez d’abord pourquoi les connexions montent ou restent.

Task 2: Voir le nombre actuel de connexions et leurs états

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT state, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;"
   state   | count
-----------+-------
 idle      |   160
 active    |    25
           |     3
 idle in transaction | 9
(4 rows)

Sens : Beaucoup de sessions idle. Quelques « idle in transaction » sont des signaux d’alerte.

Décision : Si l’inactivité domine et que le nombre de connexions approche le maximum, concentrez-vous sur le pooling applicatif et les timeouts d’inactivité ; un pooler est souvent la containment la plus rapide.

Task 3: Identifier les principales sources clientes (les tempêtes viennent souvent d’un seul endroit)

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT client_addr, usename, count(*) AS conns FROM pg_stat_activity GROUP BY 1,2 ORDER BY conns DESC LIMIT 10;"
 client_addr | usename | conns
-------------+---------+-------
 10.42.7.19  | appuser |   120
 10.42.8.11  | appuser |    95
 10.42.9.02  | appuser |    70
(3 rows)

Sens : Quelques nœuds applicatifs dominent. Bonne nouvelle : vous pouvez corriger ou limiter un petit ensemble de coupables.

Décision : Si une adresse cliente explose, isolez ce déploiement applicatif, sidecar ou job runner.

Task 4: Mesurer les symptômes de surcharge connect/auth via les wait events Postgres

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE state='active' GROUP BY 1,2 ORDER BY 3 DESC;"
 wait_event_type |     wait_event      | count
-----------------+---------------------+-------
 Lock            | transactionid        |    12
 IO              | DataFileRead         |     5
 CPU             |                     |     3
(3 rows)

Sens : Votre « tempête de connexions » pourrait en réalité être une tempête de verrous provoquant des timeouts et des retries.

Décision : Si les waits de type Lock dominent, arrêtez de penser poolers et commencez à chercher la transaction bloquante et les timeouts.

Task 5: Trouver rapidement les bloqueurs (les verrous causent des tempêtes de retries)

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT blocked.pid AS blocked_pid, blocker.pid AS blocker_pid, blocked.query AS blocked_query, blocker.query AS blocker_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked ON blocked.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocker_locks ON blocker_locks.locktype = blocked_locks.locktype AND blocker_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocker_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocker_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocker_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocker_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocker_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocker_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocker_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocker_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocker_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocker ON blocker.pid = blocker_locks.pid WHERE NOT blocked_locks.granted;"
 blocked_pid | blocker_pid |        blocked_query         |        blocker_query
------------+-------------+------------------------------+-----------------------------
      24811 |       20777 | UPDATE accounts SET ...      | ALTER TABLE accounts ...
(1 row)

Sens : Une instruction DDL bloque des mises à jour. Cela peut enchaîner des timeouts applicatifs et des reconnexions.

Décision : Tuez ou reportez le bloqueur si c’est sûr, et implémentez des pratiques de migration plus sûres (lock timeouts, patterns de migration en ligne).

Task 6: Vérifier le plafond de descripteurs de fichiers au niveau OS (cause classique de « connexions échouent »)

cr0x@server:~$ cat /proc/$(pidof postgres | awk '{print $1}')/limits | grep -E "Max open files"
Max open files            1024                 1048576              files

Sens : La limite souple est 1024 pour le PID principal. C’est dangereusement bas pour une base de données chargée.

Décision : Corrigez les limites de l’unité systemd (Task 7). Si vous fonctionnez avec des limites souples basses, vous aurez des échecs aléatoires sous charge.

Task 7: Vérifier systemd LimitNOFILE pour PostgreSQL

cr0x@server:~$ systemctl show postgresql --property=LimitNOFILE
LimitNOFILE=1024

Sens : Le service PostgreSQL est plafonné à 1024 fichiers ouverts. Cela inclut sockets et fichiers de données.

Décision : Fixez une valeur raisonnable via un drop-in. Puis redémarrez pendant une fenêtre de maintenance.

Task 8: Appliquer un drop-in systemd pour augmenter les limites de fichiers

cr0x@server:~$ sudo systemctl edit postgresql
# (editor opens)
# add:
# [Service]
# LimitNOFILE=1048576

cr0x@server:~$ sudo systemctl daemon-reload
cr0x@server:~$ sudo systemctl restart postgresql
cr0x@server:~$ systemctl show postgresql --property=LimitNOFILE
LimitNOFILE=1048576

Sens : PostgreSQL hérite maintenant d’une limite élevée de descripteurs de fichiers.

Décision : Si les échecs de connexion disparaissent, vous avez trouvé au moins un plafond dur. Continuez d’enquêter sur la raison pour laquelle les connexions montent.

Task 9: Inspecter les files d’attente d’écoute TCP (douleurs de connexion côté noyau)

cr0x@server:~$ ss -ltn sport = :5432
State  Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 64     4096   0.0.0.0:5432      0.0.0.0:*

Sens : Recv-Q montre les connexions en file. Si elle est fréquemment proche de Send-Q (backlog), vous perdez ou retardez des établissements de connexion.

Décision : Si Recv-Q grimpe pendant les tempêtes, envisagez un pooler proche des clients et réexaminez le tuning du backlog du noyau (somaxconn) ainsi que la stratégie Postgres sur listen_addresses/tcp_keepalives.

Task 10: Vérifier le backlog du noyau et la gestion des SYN

cr0x@server:~$ sysctl net.core.somaxconn net.ipv4.tcp_max_syn_backlog
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096

Sens : Ces valeurs sont raisonnablement élevées. Si elles sont basses (128/256), les tempêtes peuvent submerger la file d’attente de handshake TCP.

Décision : Si les valeurs sont basses et que vous voyez des pertes de connexion, augmentez-les prudemment et testez. Mais souvenez-vous : le tuning du backlog soulage les symptômes ; un pooler corrige le comportement.

Task 11: Déterminer si l’authentification est coûteuse (indices dans pg_hba.conf)

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT name, setting FROM pg_settings WHERE name IN ('password_encryption','ssl');"
        name         | setting
---------------------+---------
 password_encryption | scram-sha-256
 ssl                 | on
(2 rows)

Sens : SCRAM + TLS est sécurisé ; cela peut aussi être plus lourd pendant un churn massif.

Décision : Ne fragilisez pas l’authentification en réponse initiale. Préférez un pooler pour réduire le volume de handshakes, ou terminez TLS en amont si la politique le permet.

Task 12: Inspecter le taux de churn de connexions via les statistiques Postgres

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database ORDER BY numbackends DESC;"
  datname  | numbackends | xact_commit | xact_rollback | blks_read | blks_hit
-----------+-------------+-------------+---------------+-----------+----------
 appdb     |         190 |     9921330 |         12344 |   1209932 |  99881233
 postgres  |           3 |        1200 |             0 |       120 |     22000
(2 rows)

Sens : numbackends élevé proche de votre maximum effectif. Combiné à une faible activité active, c’est un signe clair de « problème de gestion des connexions ».

Décision : Contenez avec un pooler ou des limites strictes côté application ; ensuite corrigez le retry/backoff et la discipline de pooling.

Task 13: Vérifier « idle in transaction » et appliquer des timeouts

cr0x@server:~$ sudo -u postgres psql -Xc "SHOW idle_in_transaction_session_timeout;"
idle_in_transaction_session_timeout
-----------------------------------
0
(1 row)

Sens : Timeout désactivé. Les sessions « idle in transaction » peuvent retenir des verrous indéfiniment.

Décision : Définissez une valeur raisonnable (souvent 1–5 minutes pour de l’OLTP) pour réduire le risque en queue d’incident.

Task 14: Appliquer des timeouts (des valeurs sensées valent mieux que du débogage héroïque)

cr0x@server:~$ sudo -u postgres psql -Xc "ALTER SYSTEM SET idle_in_transaction_session_timeout = '2min';"
ALTER SYSTEM
cr0x@server:~$ sudo -u postgres psql -Xc "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

Sens : Configuration rechargée, les nouvelles sessions hériteront du timeout.

Décision : Si vous avez des sessions légitimes qui restent longues en idle-in-tx (rare et généralement erroné), corrigez ce chemin de code ; ne désactivez pas la garde-fou.

Task 15: Vérifier que le pooling côté client n’est pas « infini » (ex. : psql montre application_name)

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT application_name, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC LIMIT 10;"
 application_name | count
------------------+-------
 myapi            |   180
 migration-job    |     9
 psql             |     1
(3 rows)

Sens : L’API est le principal consommateur. Vous devez inspecter ses réglages de pool et son comportement de déploiement.

Décision : Limitez les pools par instance, ajoutez un démarrage avec jitter, et assurez-vous d’un backoff exponentiel sur les retries.

Task 16: Vérifier le risque mémoire avant d’augmenter max_connections (le piège)

cr0x@server:~$ sudo -u postgres psql -Xc "SHOW shared_buffers; SHOW work_mem; SHOW maintenance_work_mem; SHOW max_connections;"
shared_buffers
--------------
4GB
(1 row)

work_mem
--------
16MB
(1 row)

maintenance_work_mem
--------------------
512MB
(1 row)

max_connections
---------------
200
(1 row)

Sens : Si vous passez à 800 connexions en laissant work_mem à 16MB, vous invitez la pression mémoire. Toutes les connexions n’utilisent pas la totalité de work_mem, mais les incidents aiment les hypothèses du pire.

Décision : Si vous devez augmenter max_connections, révisez la stratégie mémoire et envisagez d’abord un pooler. Idéal : évitez d’avoir besoin de l’augmentation.

Task 17: Installer et vérifier rapidement PgBouncer sur Debian 13

cr0x@server:~$ sudo apt-get update
cr0x@server:~$ sudo apt-get install -y pgbouncer
Reading package lists... Done
...
Setting up pgbouncer ...

Sens : Le pooler est installé. Il faut maintenant le configurer volontairement ; les valeurs par défaut ne sont pas un plan de production.

Décision : Placez PgBouncer près des applications si possible (même nœud/cluster) pour réduire l’overhead handshake réseau et isoler les tempêtes.

Task 18: Vérifications minimales de configuration PgBouncer pour éviter l’auto-dommage

cr0x@server:~$ sudo grep -E "^(listen_addr|listen_port|pool_mode|max_client_conn|default_pool_size|server_reset_query|ignore_startup_parameters)" /etc/pgbouncer/pgbouncer.ini
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 50
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits

Sens : Le pooling transactionnel avec une taille de pool raisonnable peut absorber les pics clients. DISCARD ALL est plus sûr, bien qu’avec un coût.

Décision : Si votre appli dépend d’état de session, le pooling transactionnel le cassera. Soit refactorez, soit utilisez le pooling de session pour ces cas.

Task 19: Observer les files d’attente PgBouncer (pour savoir si vous déplacez juste la douleur)

cr0x@server:~$ psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"
 database | user    | cl_active | cl_waiting | sv_active | sv_idle | sv_used | maxwait
----------+---------+-----------+------------+----------+---------+---------+---------
 appdb    | appuser |       120 |         80 |       50 |       0 |       50 |     12
(1 row)

Sens : Des clients attendent. Les connexions serveur sont plafonnées à 50 et pleinement utilisées. C’est attendu lors des pics.

Décision : Si maxwait augmente et que la latence des requêtes souffre, ajustez prudemment la taille des pools et — plus important — réduisez la concurrence côté client et corrigez les requêtes lentes.

Task 20: Confirmer que l’application utilise bien le pooler

cr0x@server:~$ sudo -u postgres psql -Xc "SELECT client_addr, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;"
 client_addr | count
-------------+-------
 127.0.0.1   |    52
(1 row)

Sens : Postgres voit désormais PgBouncer comme client (loopback). C’est bon ; le fan-out a été déplacé vers le pooler.

Décision : Si vous voyez encore de nombreuses adresses d’app qui se connectent directement, vous avez un problème de déploiement/configuration, pas de tuning.

Trois mini-récits d’entreprise depuis le terrain

Mini-récit n°1 : L’incident causé par une mauvaise hypothèse (mythe « les connexions sont bon marché »)

Une entreprise de taille moyenne exécutait une API client sur Debian, Postgres sur une VM musclée, et un service mesh qui appliquait fièrement du mTLS partout.
Quelqu’un a fait les calculs sur le CPU et l’IO, a vu beaucoup de marge, et a déclaré la base « sur-dimensionnée ».
La plus grande crainte de l’équipe était les requêtes lentes. Personne ne s’est soucié des connexions.

Un déploiement de routine a été poussé sur quelques centaines de conteneurs. Chaque conteneur démarrait, exécutait une vérification de santé, et établissait une nouvelle connexion
à la base pour vérifier que les migrations étaient « OK ». Cette vérification s’exécutait toutes les quelques secondes parce que « la détection rapide, c’est bien », et ouvrait
une connexion à chaque fois parce que le chemin de code utilisait un client éphémère.

Postgres ne meurt pas immédiatement. Il devient juste progressivement moins réactif. Le volume d’authentification et de handshake TLS explose.
Les processus backend se multiplient. Le CPU de la VM semblait modestement utilisé parce que le coût était surtout du côté du scheduler et des opérations noyau.

La correction n’était pas un tuning héroïque. Ils ont arrêté le connect-per-healthcheck, ajouté un petit sidecar pooler pour les pods API,
et limité la fréquence des checks de readiness. Ils ont aussi arrêté de prétendre que « la connexion est bon marché » dans un monde TLS.
L’incident ne s’est jamais reproduit.

Mini-récit n°2 : L’optimisation qui a mal tourné (augmentation de max_connections)

Une autre organisation a eu la classique page « too many clients already ». Un ingénieur bien intentionné a augmenté max_connections d’une paire
de centaines à plus d’un millier. Le changement a pris quelques minutes. La page a disparu. Tout le monde est reparti travailler.

Deux semaines plus tard, des plaintes de latence sont revenues — mais avec une torsion. La base n’acceptait plus les connexions ; elle les acceptait et
sombrant silencieusement. Les temps de réponse se sont dégradés partout. L’autovacuum est resté à la traîne. Les checkpoints sont devenus en pics.

Le postmortem a été gênant parce qu’aucune requête unique n’était « le problème ». Le problème était la concurrence et la pression mémoire.
Plus de backends signifiait plus d’overhead mémoire, plus de changements de contexte, et plus de consommation simultanée de work_mem lors des pointes.
Le système se comportait bien jusqu’à franchir un seuil. Puis il se comportait comme une caissière fatiguée dans un supermarché à la fermeture.

La correction a été de revenir sur max_connections, déployer PgBouncer en mode transactionnel, et plafonner les pools côté app.
Ils ont aussi implémenté un meilleur jitter de retry pour désynchroniser les timeouts. La leçon : augmenter les limites de connexions peut transformer une panne visible en un effondrement de performance invisible.

Mini-récit n°3 : La pratique ennuyeuse mais correcte qui a sauvé la mise (timeouts et garde-fous)

Un service proche de la finance tournait Postgres avec un processus de changement strict. Les ingénieurs râlaient, comme d’habitude.
Mais leur DBA avait insisté sur quelques valeurs par défaut « ennuyeuses » : statement_timeout pour certains rôles, idle_in_transaction_session_timeout,
et des timeouts de verrou prudents pour les migrations.

Un après-midi, une migration a été poussée qui aurait été bénigne en staging. En production, elle est entrée en collision avec un job par lot et
a pris des verrous plus lourds que prévu. Sans garde-fous, la migration serait restée indéfiniment et aurait tenu tout le monde en otage.

À la place, la migration a heurté le timeout de verrou et a échoué rapidement. L’application a continué de fonctionner. Quelques requêtes ont retenté et ont réussi.
Le ticket d’incident a été un non-événement : « migration échouée, relancer plus tard. » Pas de tempête, pas de panique, pas de réveil à 3h du matin.

C’est ça l’intérêt des contrôles ennuyeux : ils sont invisibles jusqu’au jour où ils vous empêchent de trop bien connaître votre pager.

Erreurs courantes : symptôme → cause racine → correction

1) Symptom : « too many clients already » lors des déploiements

Cause racine : les instances applicatives démarrent simultanément et chacune ouvre un pool pleine taille immédiatement ; pas de jitter ; pas de plafonds de pool.

Correction : plafonnez les pools par instance ; ajoutez du jitter au démarrage ; ajoutez PgBouncer pour réduire le fan-out ; assurez-vous que les retries utilisent un backoff exponentiel avec jitter.

2) Symptom : Postgres accepte les connexions mais les requêtes expirent

Cause racine : contention sur les verrous ou transactions longues, souvent déclenchées par du DDL en période de trafic élevé.

Correction : identifiez les bloqueurs ; adoptez des lock timeouts ; utilisez des patterns de migration plus sûrs ; tuez ou reportez la session bloquante en incident.

3) Symptom : Les connexions échouent de façon intermittente sous charge

Cause racine : limites de descripteurs de fichiers (systemd LimitNOFILE), ou limites de backlog du noyau.

Correction : augmentez les limites du service via un drop-in systemd ; validez avec /proc/PID/limits ; revoyez les sysctls liés au backlog.

4) Symptom : Beaucoup de sessions « idle in transaction »

Cause racine : l’application ouvre une transaction puis attend une interaction utilisateur, un travail en arrière-plan ou un appel réseau.

Correction : définissez idle_in_transaction_session_timeout ; corrigez les bornes transactionnelles de l’application ; envisagez des transactions plus courtes et des verrous explicites uniquement si nécessaire.

5) Symptom : Après ajout de PgBouncer, certaines fonctionnalités cassent (tables temporaires, prepared statements)

Cause racine : le pooling transactionnel invalide l’état de session.

Correction : déplacez ces charges vers du pooling de session, ou refactorez l’application pour éviter l’état de session ; utilisez les prepared statements côté serveur avec précaution ou désactivez-les côté client.

6) Symptom : PgBouncer « corrige » les tempêtes mais la latence devient en dents de scie

Cause racine : la mise en queue se déplace vers PgBouncer ; le pool serveur est trop petit ou les requêtes sont lentes ; la concurrence dépasse la capacité DB.

Correction : ajustez prudemment les tailles de pool ; réduisez la concurrence applicative ; corrigez les requêtes lentes et l’IO ; considérez la file PgBouncer comme un signal, pas un problème à masquer.

7) Symptom : Pics CPU pendant les tempêtes même quand la charge de requêtes semble faible

Cause racine : les handshakes TLS et le coût d’auth dominent sous churn ; aussi la surcharge de création de processus.

Correction : réduisez le churn avec un pooler ; assurez les keepalives ; évitez le connect-per-request ; considérez une stratégie de terminaison TLS si la politique le permet.

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

Checklist A : Première heure de confinement (arrêter l’hémorragie)

1. Confirmez s’il s’agit d’un refus de connexion ou de timeouts de requêtes. Utilisez pg_stat_activity, les erreurs applicatives et les vérifications de backlog ss.
2. Trouvez la source cliente dominante. Si un groupe d’applications se comporte mal, isolez-le (scale down, rollback, ou throttle).
3. Recherchez les verrous et « idle in transaction ». Ne tuez le bloqueur que si vous comprenez l’impact.
4. Vérifiez les limites OS. Descripteurs et limites systemd sont des gains rapides.
5. Réduisez le taux de reconnexion. Corrigez les boucles de retry et ajoutez backoff/jitter ; augmentez temporairement les timeouts clients pour réduire le thrash.

Checklist B : Correctif sur deux jours (rendre les tempêtes moins probables)

1. Implémentez PgBouncer pour les charges web et réglez le pooling sur transaction sauf raison contraire.
2. Mettez des garde-fous : idle_in_transaction_session_timeout, statement_timeout par rôle, et timeouts de verrou pour les migrations.
3. Plafonnez les pools applicatifs par instance et documentez le calcul : instances × taille du pool ne doit pas dépasser ce que Postgres peut servir.
4. Instrumentez le churn : suivez le taux de connexions, pas seulement le nombre de connexions.
5. Exécutez un test contrôlé de tempête en staging : redéploiement progressif + charge, surveillez backlog, CPU d’auth, et files PgBouncer.

Checklist C : Hygiène long terme (ne plus réapprendre la même leçon)

1. Standardisez les réglages clients (timeouts, keepalives, retry backoff) en tant que bibliothèque, pas en savoir-faire tacite.
2. Rendez les migrations banales : imposez des patterns sûrs et séparez les changements de schéma des backfills de données.
3. Planifiez la capacité sur le « travail utile », pas sur max connections : mesurez le débit, les SLOs de latence et la marge IO ; traitez les connexions comme un plan de contrôle.
4. Exécutez des drills d’incident centrés sur la contention de verrous et le churn de connexions, car ce sont ceux qui surprennent le plus.

FAQ

1) Dois-je toujours déployer PgBouncer sur Debian 13 pour Postgres ?

Pour la plupart des charges OLTP/web : oui, c’est une valeur par défaut pratique. Pas parce que Postgres est faible, mais parce que les clients sont désordonnés.
Si vous avez des charges riches en session, vous pouvez toujours utiliser PgBouncer en pooling de session ou isoler ces clients.

2) Est-ce qu’augmenter max_connections est parfois la bonne décision ?

Parfois — quand vous êtes sûr d’avoir de la marge mémoire, que votre charge nécessite réellement plus de concurrence, et que le churn de connexions n’est pas le problème.
C’est rarement la meilleure première réponse à une tempête. Mesurez l’overhead mémoire par backend et surveillez le swap comme un aigle.

3) Pourquoi ma base a-t-elle ralenti après avoir « corrigé » les erreurs de connexion ?

Vous avez probablement converti une erreur de refus en un échec par mise en file. Plus de backends signifie plus d’overhead d’ordonnanceur et plus de risque mémoire.
Si vous n’avez pas réduit la demande réelle (concurrence client ou requêtes lentes), le système ne peut toujours pas suivre — il échoue simplement différemment.

4) Quel mode de pooling devrais-je utiliser dans PgBouncer ?

Pooling transactionnel pour le trafic API typique. Pooling de session seulement quand vous avez besoin d’état de session (tables temporaires, GUCs de session,
advisory locks maintenus entre transactions, certains patterns de prepared statements).

5) Comment savoir si ma « tempête de connexions » est en réalité due aux verrous ?

Regardez les wait events dans pg_stat_activity et les graphes de verrous. Si de nombreuses sessions attendent des événements de type Lock et que vous identifiez un bloqueur,
vous êtes dans un incident de verrous qui peut déclencher des retries de connexion. Résolvez la cause des verrous ; n’ajoutez pas seulement du pooling.

6) Est-ce que PgBouncer peut masquer les requêtes lentes ?

Il peut masquer les signes avant-coureurs en tamponnant les clients. C’est utile pour contenir. Mais la file va croître et la latence va monter.
Utilisez les métriques PgBouncer (clients en attente, maxwait) comme un canari indiquant que la base est saturée ou bloquée.

7) Quel est le réglage le plus sous-estimé pour réduire l’amplitude d’une tempête ?

idle_in_transaction_session_timeout. Il empêche un petit nombre de requêtes défectueuses de retenir des verrous indéfiniment et de transformer votre journée en archéologie.

8) Dois-je tuner les paramètres noyau pour les tempêtes de connexions Postgres ?

Parfois. Si vous voyez la saturation du backlog ou des problèmes de file SYN, le tuning de somaxconn et tcp_max_syn_backlog aide.
Mais si votre application se connecte comme un solo de batterie, le tuning noyau n’est pas un plan d’affaires. Corrigez le churn avec du pooling et une meilleure discipline de retry.

9) Mettre PgBouncer sur l’hôte de la base a-t-il du sens ?

Cela peut faire sens et c’est courant. Mais le placer plus près des clients (par nœud, par cluster, ou en sidecar) réduit souvent l’overhead handshake réseau
et rend les tempêtes moins susceptibles de traverser les frontières de blast-radius. Choisissez selon la simplicité opérationnelle et les domaines de défaillance.

10) Quelle est l’amélioration « assez bonne » la plus rapide si je ne peux pas déployer un pooler tout de suite ?

Plafonnez les pools côté app, ajoutez un backoff exponentiel avec jitter, et ajoutez des timeouts (idle_in_transaction_session_timeout, timeouts de verrou pour les migrations).
Confirmez aussi que les limites de fichiers systemd ne vous sabotent pas.

Prochaines étapes réalisables cette semaine

Si vous êtes en train de combattre des tempêtes, faites d’abord du confinement : identifiez le client dominant, arrêtez la marée de retries, et confirmez que vous n’êtes pas
bloqué par des limites OS ou une contention de verrous. Ensuite, choisissez une correction structurelle et livrez-la.

1. Ajoutez PgBouncer (pooling transactionnel) pour votre charge applicative principale, et vérifiez l’utilisation en observant les adresses clientes Postgres qui se résument au pooler.
2. Mettez des garde-fous : activez idle_in_transaction_session_timeout et ajoutez des timeouts par rôle quand pertinent.
3. Corrigez le comportement de connexion : plafonnez les pools par instance et implémentez un backoff jitterisé pour les retries. Si vous ne pouvez pas décrire votre politique de retry, vous n’en avez pas.
4. Faites une répétition de tempête en staging : redémarrage progressif de votre flotte applicative et surveillez ss backlog, files PgBouncer, et wait events Postgres.
5. Arrêtez d’utiliser max_connections comme doudoune émotionnelle. Alignez-le avec la mémoire, le CPU, et la concurrence que Postgres peut servir correctement.

L’objectif n’est pas « éviter les erreurs ». L’objectif est de garder la base à faire du travail utile quand le reste du système fait une journée bruyante.
Poolers, tuning, et comportement client sensé effectuent chacun une partie de ce travail. Utilisez le bon outil, et votre pager s’ennuiera à nouveau.