Pooling de connexions PostgreSQL : pool applicatif vs PgBouncer

Pourquoi les pics de latence commencent souvent par les connexions

Une connexion à la base de données est comme une ligne téléphonique entre votre application et Postgres. L'ouvrir coûte du temps et du travail des deux côtés : mise en place TCP/TLS, authentification, allocation mémoire et un processus backend côté Postgres. Un pool de connexions garde un petit nombre de ces « lignes téléphoniques » ouvertes pour que votre application puisse les réutiliser au lieu de raccrocher et rappeler à chaque requête.

Quand le pooling est désactivé ou mal dimensionné, vous n'obtenez que rarement une erreur nette au départ. Vous constatez une lenteur aléatoire. Des requêtes qui prennent habituellement 20–50 ms prennent soudainement 500 ms ou 5 secondes, et le p95 s'envole. Puis arrivent les timeouts, suivis par des erreurs « too many connections » ou une file d'attente dans votre application qui attend une connexion libre.

Les limites de connexions comptent même pour les petites apps car le trafic est souvent en rafales. Un email marketing, une tâche cron ou quelques endpoints lents peuvent provoquer des dizaines de requêtes simultanées vers la base. Si chaque requête ouvre une connexion neuve, Postgres peut dépenser beaucoup de sa capacité à accepter et gérer des connexions au lieu d'exécuter des requêtes. Si vous avez déjà un pool mais qu'il est trop grand, vous pouvez surcharger Postgres avec trop de backends actifs et déclencher des changements de contexte et de la pression mémoire.

Surveillez les premiers symptômes comme :

• des pics de latence p95/p99 alors que la latence moyenne semble normale
• des timeouts qui se regroupent pendant les rafales de trafic
• une augmentation du "waiting for connection" côté app
• des connect/disconnect fréquents ou une saturation des connexions côté Postgres

Le pooling réduit le churn de connexions et aide Postgres à gérer les rafales. Il ne réparera pas un SQL lent. Si une requête fait un scan complet de table ou attend des verrous, le pooling change surtout la façon dont le système échoue (mise en file plus tôt, timeouts plus tard), pas sa vitesse.

Pooling applicatif vs PgBouncer : quel problème résout chacun

Le pooling de connexions consiste à contrôler combien de connexions à la base existent simultanément et comment elles sont réutilisées. Vous pouvez le faire dans votre application (pooling côté app) ou avec un service séparé devant Postgres (PgBouncer). Ils résolvent des problèmes liés mais différents.

Le pooling côté application (en Go, généralement le pool intégré database/sql) gère les connexions par processus. Il décide quand ouvrir une nouvelle connexion, quand en réutiliser une et quand fermer les connexions inactives. Cela évite de payer le coût d'initialisation à chaque requête. Ce qu'il ne peut pas faire, c'est se coordonner entre plusieurs instances d'app. Si vous exécutez 10 réplicas, vous avez en pratique 10 pools séparés.

PgBouncer se place entre votre app et Postgres et pool pour le compte de nombreux clients. Il est particulièrement utile lorsque vous avez beaucoup de requêtes courtes, de nombreuses instances d'app ou un trafic en rafales. Il plafonne les connexions serveur à Postgres même si des centaines de connexions clientes arrivent en même temps.

Une division simple des responsabilités :

• Le pooling applicatif façonne la concurrence à l'intérieur d'une instance d'app et évite les reconnexions par requête.
• PgBouncer plafonne le nombre total de connexions Postgres à travers toutes les instances et lisse les rafales.
• Postgres a toujours des limites fortes sur le CPU, les IO et la mémoire. Le pooling ne crée pas de capacité.

Ils peuvent fonctionner ensemble sans problème de « double pooling » tant que chaque couche a un objectif clair : un database/sql raisonnable par processus Go, plus PgBouncer pour faire respecter un budget global de connexions.

Une confusion fréquente est de penser que « plus de pools = plus de capacité ». Généralement c'est le contraire. Si chaque service, worker et réplique a son propre grand pool, le nombre total de connexions peut exploser et provoquer des files d'attente, du changement de contexte et des pics de latence soudains.

Comment database/sql de Go se comporte vraiment

En Go, sql.DB est un gestionnaire de pool de connexions, pas une connexion unique. Quand vous appelez db.Query ou db.Exec, database/sql tente de réutiliser une connexion inactive. S'il ne peut pas, il peut en ouvrir une nouvelle (jusqu'à votre limite) ou faire attendre la requête.

C'est cette attente qui est souvent la source de la « latence mystère ». Quand le pool est saturé, les requêtes font la queue dans votre application. De l'extérieur, on dirait que Postgres est lent, mais le temps est en réalité passé à attendre une connexion libre.

Les réglages qui comptent

La plupart des réglages se résument à quatre paramètres :

• MaxOpenConns : plafond dur sur les connexions ouvertes (inactives + en usage). Quand vous l'atteignez, les appels bloquent.
• MaxIdleConns : combien de connexions peuvent rester prêtes à être réutilisées. Trop bas cause des reconnexions fréquentes.
• ConnMaxLifetime : force le recyclage périodique des connexions. Utile pour les load balancers et les timeouts NAT, mais trop bas provoque du churn.
• ConnMaxIdleTime : ferme les connexions qui restent inutilisées trop longtemps.

La réutilisation des connexions diminue généralement la latence et le CPU de la base car vous évitez les setups répétés (TCP/TLS, authent, initialisation de session). Mais un pool surdimensionné peut faire l'inverse : il permet plus de requêtes concurrentes que ce que Postgres peut bien gérer, augmentant la contention et les frais généraux.

Pensez en totaux, pas par processus. Si chaque instance Go permet 50 connexions ouvertes et que vous montez à 20 instances, vous avez en pratique autorisé 1 000 connexions. Comparez ce nombre à ce que votre serveur Postgres peut réellement gérer confortablement.

Un point de départ pratique est de lier MaxOpenConns à la concurrence attendue par instance, puis de valider avec les métriques du pool (in-use, idle, temps d'attente) avant de l'augmenter.

Bases de PgBouncer et modes de pooling

PgBouncer est un petit proxy entre votre app et PostgreSQL. Votre service se connecte à PgBouncer, et PgBouncer maintient un nombre limité de connexions réelles vers Postgres. Pendant les rafales, PgBouncer met les clients en file d'attente plutôt que de créer immédiatement plus de backends Postgres. Cette file d'attente peut faire la différence entre un ralentissement contrôlé et une base qui bascule.

Les trois modes de pooling

PgBouncer propose trois modes de pooling :

• Session pooling : un client garde la même connexion serveur tant qu'il reste connecté.
• Transaction pooling : un client emprunte une connexion serveur pour la durée d'une transaction, puis la retourne.
• Statement pooling : un client emprunte une connexion serveur pour une seule instruction.

Le session pooling se comporte le plus comme des connexions directes à Postgres. C'est le moins surprenant, mais il économise moins de connexions serveur lors de charges en rafales.

Ce qui convient généralement aux APIs HTTP Go

Pour des APIs HTTP Go typiques, le transaction pooling est souvent un bon choix par défaut. La plupart des requêtes exécutent une petite requête ou une courte transaction, puis c'est fini. Le transaction pooling permet à de nombreuses connexions clientes de partager un budget de connexions Postgres plus petit.

Le compromis concerne l'état de session. En mode transaction, tout ce qui suppose qu'une même connexion serveur reste liée à un client peut casser ou se comporter bizarrement, notamment :

• les prepared statements créés une fois et réutilisés plus tard
• les paramètres de session attendus (SET, SET ROLE, search_path)
• les tables temporaires et les advisory locks utilisés à travers plusieurs instructions

Si votre app dépend de ce type d'état, le session pooling est plus sûr. Le statement pooling est le plus restrictif et convient rarement aux applications web.

Une règle utile : si chaque requête peut configurer ce dont elle a besoin à l'intérieur d'une seule transaction, le transaction pooling tend à maintenir la latence plus stable sous charge. Si vous avez besoin d'un comportement de session longue durée, utilisez le session pooling et concentrez-vous sur des limites plus strictes côté application.

Comment choisir la bonne stratégie pour un backend Go

Si vous exécutez un service Go avec database/sql, vous avez déjà un pooling côté app. Pour beaucoup d'équipes, cela suffit : quelques instances, trafic stable et requêtes qui ne sont pas excessivement rafales. Dans ce cas, le choix le plus simple et le plus sûr est d'ajuster le pool Go, de garder la limite de connexions de la base réaliste et de s'arrêter là.

PgBouncer aide surtout lorsque la base est frappée par trop de connexions clientes en même temps. Cela se manifeste par de nombreuses instances d'app (ou un scaling de type serverless), un trafic en rafales et beaucoup de requêtes courtes.

PgBouncer peut aussi nuire s'il est utilisé dans le mauvais mode. Si votre code dépend d'un état de session (tables temporaires, prepared statements réutilisés, advisory locks conservés à travers des appels, ou paramètres de session), le transaction pooling peut causer des échecs déroutants. Si vous avez vraiment besoin du comportement de session, utilisez le session pooling ou évitez PgBouncer et dimensionnez soigneusement les pools applicatifs.

Une règle de décision simple

Utilisez cette règle empirique :

• Si vous avez 1 à 3 instances d'app et que le total des connexions ouvertes reste confortablement sous la limite de la base, utilisez seulement le pooling côté app.
• Si vous avez beaucoup d'instances ou de l'autoscaling, et que la somme des MaxOpenConns pourrait dépasser ce que Postgres peut gérer, ajoutez PgBouncer.
• Si la plupart des requêtes sont courtes (lectures rapides, petites écritures), PgBouncer est généralement rentable.
• Si les requêtes gardent des connexions longtemps (rapports lents, longues transactions), corrigez d'abord les requêtes et soyez conservateur sur la taille des pools.

Étapes : dimensionner et déployer le pooling en sécurité

Les limites de connexion sont un budget. Si vous le dépensez tout d'un coup, chaque nouvelle requête attend et la latence en queue augmente. L'objectif est de plafonner la concurrence de façon contrôlée tout en maintenant le débit.

Séquence pratique de déploiement

1. Mesurez les pics et la latence tail actuels. Enregistrez les connexions actives au pic (pas la moyenne), ainsi que p50/p95/p99 pour les requêtes et les requêtes clés. Notez les erreurs de connexion ou les timeouts.

2. Définissez un budget de connexions Postgres sûr pour l'app. Partez de max_connections et soustrayez une marge pour l'accès admin, les migrations, les jobs en arrière-plan et les pics. Si plusieurs services partagent la base, répartissez intentionnellement le budget.

3. Mappez le budget aux limites Go par instance. Divisez le budget d'app par le nombre d'instances et fixez MaxOpenConns à cette valeur (ou légèrement en dessous). Fixez MaxIdleConns assez haut pour éviter des reconnects constants, et des durées de vie pour que les connexions se recyclent occasionnellement sans churn.

4. Ajoutez PgBouncer seulement si nécessaire, et choisissez un mode. Utilisez le session pooling si vous avez besoin d'état de session. Utilisez le transaction pooling si vous voulez la plus grande réduction de connexions serveur et que votre app est compatible.

5. Déployez progressivement et comparez avant/après. Changez une chose à la fois, faites un canary, puis comparez la latence tail, le temps d'attente du pool et le CPU de la DB.

Exemple : si Postgres peut donner en sécurité 200 connexions à votre service et que vous avez 10 instances Go, commencez avec MaxOpenConns=15-18 par instance. Cela laisse de la marge pour les rafales et réduit les chances que chaque instance atteigne le plafond en même temps.

Métriques à surveiller pour détecter les problèmes tôt

Les problèmes de pooling n'apparaissent que rarement d'abord comme « trop de connexions ». Le plus souvent, vous voyez une montée lente du temps d'attente puis un saut soudain du p95 et p99.

Commencez par ce que votre app Go rapporte. Avec database/sql, surveillez les connexions ouvertes, en-usage, idle, le nombre d'attentes (wait count) et le temps d'attente (wait time). Si le wait count augmente alors que le trafic est stable, votre pool est sous-dimensionné ou les connexions sont gardées trop longtemps.

Côté base, suivez les connexions actives vs max, le CPU et l'activité de verrous. Si le CPU est bas mais la latence haute, il s'agit souvent de mise en file ou de verrous, pas de calcul brut.

Si vous utilisez PgBouncer, ajoutez une troisième vue : connexions clientes, connexions serveur vers Postgres et profondeur de file. Une file croissante alors que les connexions serveur sont stables indique clairement que le budget est saturé.

Signaux d'alerte utiles :

• p95/p99 qui montent tandis que p50 reste normal
• temps d'attente de connexion côté app qui augmente, surtout avant les timeouts
• file d'attente PgBouncer qui grossit plus vite qu'elle ne se vide
• taux d'erreur et timeouts qui augmentent ensemble
• verrous en hausse accompagnant des requêtes longues

Erreurs de configuration courantes qui provoquent des pics

Les problèmes de pooling apparaissent souvent pendant les rafales : les requêtes s'entassent en attendant une connexion, puis tout revient normal. La cause racine est souvent un réglage raisonnable sur une instance mais dangereux quand vous exécutez de nombreuses copies du service.

Causes fréquentes :

• MaxOpenConns défini par instance sans budget global. 100 connexions par instance sur 20 instances = 2 000 connexions potentielles.
• Trop de connexions inactives. Les backends inactifs consomment encore de la mémoire et peuvent étouffer d'autres travaux.
• ConnMaxLifetime / ConnMaxIdleTime trop bas. Cela peut déclencher des tempêtes de reconnexions quand beaucoup de connexions se recyclent en même temps.
• PgBouncer en transaction pooling avec du code dépendant de la session. Tables temporaires, advisory locks et paramètres de session peuvent casser subtilement.
• Jobs background et health checks qui créent des rafales. Des pings à courte fréquence ou des patterns "open and close per request" peuvent créer des vagues de nouvelles connexions.

Une manière simple de réduire les pics est de traiter le pooling comme une limite partagée, pas un défaut local à l'app : plafonnez les connexions totales à travers toutes les instances, gardez un pool idle modeste et utilisez des durées de vie assez longues pour éviter des reconnexions synchronisées.

Que faire quand la demande dépasse votre budget de connexions

Quand le trafic explose, vous observez généralement trois issues : les requêtes font la file en attendant une connexion libre, les requêtes time-outent, ou tout ralentit au point que les retries s'empilent.

La mise en file est l'option la plus sournoise. Votre handler tourne toujours, mais il est en attente d'une connexion. Cette attente fait partie du temps de réponse, donc un petit pool peut transformer une requête à 50 ms en un endpoint de plusieurs secondes sous charge.

Un modèle mental utile : si votre pool a 30 connexions utilisables et que vous avez soudainement 300 requêtes concurrentes nécessitant la base, 270 d'entre elles doivent attendre. Si chaque requête garde une connexion 100 ms, la latence tail monte rapidement à plusieurs secondes.

Définissez un budget de timeout clair et respectez-le. Le timeout côté app devrait être légèrement plus court que le timeout côté base pour échouer vite et réduire la pression au lieu de laisser le travail traîner.

• App : une deadline de requête, plus une deadline plus courte autour de l'appel DB
• DB : statement_timeout pour qu'une requête mauvaise ne puisse pas monopoliser les connexions
• Pooler (si utilisé) : un timeout d'attente de pool pour obtenir un refus plutôt qu'une file infinie

Ensuite, ajoutez du backpressure pour ne pas surcharger le pool dès le départ. Choisissez un ou deux mécanismes prévisibles, comme limiter la concurrence par endpoint, dégrader la charge avec des erreurs claires (429), ou séparer les jobs background du trafic utilisateur.

Enfin, corrigez d'abord les requêtes lentes. Sous pression du pooling, une requête lente garde une connexion plus longtemps, ce qui augmente les attentes, les timeouts puis les retries. Cette boucle de rétroaction transforme un "un peu lent" en "tout est lent".

Test de charge et capacity planning sans approximation

Considérez le test de charge comme un moyen de valider votre budget de connexions, pas seulement le débit. L'objectif est de confirmer que le pooling se comporte sous pression comme en staging.

Testez avec un trafic réaliste : même mix de requêtes, mêmes patterns de rafales et même nombre d'instances d'app que vous avez en production. Les benchmarks "un endpoint" cachent souvent les problèmes de pool jusqu'au jour du lancement.

Incluez une phase de warm-up pour éviter de mesurer les caches froids et les effets de montée en charge. Laissez les pools atteindre leur taille normale, puis commencez à enregistrer les mesures.

Si vous comparez des stratégies, gardez la charge identique et exécutez :

• pooling uniquement côté app (tuning database/sql, sans PgBouncer)
• PgBouncer en front (apps gardent de petits pools, PgBouncer plafonne les connexions serveur)
• les deux ensemble (petits pools d'app + PgBouncer)

Après chaque run, enregistrez une petite fiche :

• p95 et p99 pendant l'état stable et pendant une rafale
• max total de connexions (côté client et côté serveur)
• signaux de temps de file (attente d'une connexion)
• taux d'erreur et nombre de timeouts
• débit au point où la latence commence à monter vite

Avec le temps, cela transforme le capacity planning en un processus répétable plutôt qu'en devinette.

Checklist rapide et prochaines étapes

Avant de toucher aux tailles de pool, notez un nombre : votre budget de connexions. C'est le nombre maximal sûr de connexions actives Postgres pour cet environnement (dev, staging, prod), y compris les jobs background et l'accès admin. Si vous ne pouvez pas le nommer, vous devinez.

Une checklist rapide :

• Fixez un max explicite dans Go, et assurez-vous que (instances x MaxOpenConns) tient dans le budget (ou le cap de PgBouncer).
• Définissez des timeouts pour que "attendre indéfiniment" ne masque pas les problèmes jusqu'à un pic.
• Si vous utilisez PgBouncer, choisissez le mode qui correspond à votre usage d'état de session.
• Évitez des durées de vie de connexion très courtes qui provoquent des reconnexions constantes.
• Confirmez que max_connections et les connexions réservées correspondent à votre plan.

Plan de déploiement avec rollback facile :

1. Appliquez les changements en staging sous un test de charge qui reproduit la concurrence et le mix lecture/écriture de production.
2. Déployez en production par petites étapes (un sous-ensemble d'instances ou un service à la fois).
3. Surveillez p95, le temps d'attente du pool, les erreurs et le nombre de connexions Postgres pendant au moins une fenêtre de pic.
4. Si le p95 monte ou que le wait du pool explose, faites un rollback et réduisez la concurrence ou les limites de pool.

Si vous développez et hébergez une app Go + PostgreSQL sur Koder.ai (koder.ai), Planning Mode peut vous aider à cartographier le changement et ce que vous mesurerez, et les snapshots plus le rollback facilitent le retour arrière si la latence tail empire.

Prochaine étape : ajoutez une mesure avant le prochain pic de trafic. Le "temps passé à attendre une connexion" côté app est souvent la métrique la plus utile, car elle montre la pression du pooling avant que les utilisateurs ne la ressentent.