PgBouncer : choisir le mode de pool et tester la compatibilité

PgBouncer est un gestionnaire de pools de connexions pour PostgreSQL. Les applications s’y connectent, puis il leur attribue des connexions à la base depuis un pool. Il peut réduire les ouvertures répétées et contrôler le nombre de connexions serveur utilisées, mais le mode choisi détermine quelles hypothèses sur les sessions restent valides.

Commencez par identifier le problème : ouvertures fréquentes, trop de connexions inactives ou file d’attente causée par des transactions lentes. Le pooling modifie la réutilisation des connexions ; les plans de requêtes, les index et la contention sur les verrous demandent toujours leur propre analyse. Ce guide traite du choix du mode, du budget de connexions et des tests de compatibilité dans un environnement privé de test existant.

Où placer PgBouncer

Une requête passe généralement par le pool de l’application, puis par PgBouncer et enfin par PostgreSQL. Leurs limites sont distinctes. Un ORM peut conserver des connexions à PgBouncer tandis que celui-ci utilise moins de connexions serveur pour de courtes transactions.

Le placement dépend du déploiement. Notre guide de l’hébergement d’API (en anglais) compare les options ; ajouter un pooler implique aussi de gérer sa configuration, sa surveillance et sa récupération. La FAQ de PgBouncer examine son placement et précise que la détection d’une bascule de base de données exige un dispositif externe.

Choisir le mode avant de dimensionner les pools

Mode	Quand la connexion serveur redevient disponible	Point à examiner
Session	À la déconnexion du client	Un point de départ utile si l’application dépend de l’état de session ; les clients persistants peuvent conserver les connexions serveur.
Transaction	À la fin de la transaction	Permet le partage entre transactions courtes et indépendantes, après des tests de compatibilité.
Statement	À la fin de l’instruction	Interdit les transactions à plusieurs instructions ; ce n’est pas un bon choix par défaut pour une application qui en utilise.

Le tableau officiel des fonctionnalités constitue la première référence. En mode transaction, une même connexion cliente peut utiliser plusieurs connexions PostgreSQL au fil des transactions. Une transaction ouverte, même inactive, occupe encore une connexion serveur.

Relevez les versions exactes de PgBouncer, PostgreSQL, du pilote et de l’ORM avant les tests :

pgbouncer --version

Les notes de version, consultées le 9 novembre 2026, présentent PgBouncer 1.26.1 et décrivent des correctifs de sécurité ainsi que des changements de suivi des paramètres. Utilisez une version maintenue et corrigée. Ces notes signalent aussi des problèmes de suivi de search_path introduits en 1.26.0 : testez explicitement la sélection des schémas au lieu de supposer que tout SET résiste à un changement de connexion serveur.

Recenser les comportements liés à la session

Listez les fonctionnalités utilisées par les requêtes ordinaires, les tâches de fond, les migrations et les connexions persistantes. Vérifiez chacune pour le mode transaction :

• Paramètres de session : préférez une portée limitée à la transaction lorsque l’opération le permet. La documentation de SET LOCAL limite son effet à la transaction courante. Le suivi des autres paramètres dépend du paramètre et des versions PostgreSQL/PgBouncer.
• Notifications : LISTEN inscrit une session de base de données. Réservez une connexion directe ou en mode session à un écouteur persistant au lieu de supposer qu’il suivra les changements de connexion serveur.
• Tables temporaires : PostgreSQL propose ON COMMIT DROP. Une table créée, utilisée puis supprimée dans une même transaction diffère d’une table temporaire censée survivre entre transactions.
• Verrous : distinguez les verrous consultatifs de session et de transaction. Un verrou attaché à une session serveur ne peut pas représenter de façon fiable la propriété d’un client susceptible de changer de connexion serveur.

Prévoyez un accès privé distinct, direct ou en mode session, pour un outil qui exige un état de session persistant. Consultez sa documentation avant de faire passer les migrations par le même point de connexion que les requêtes ordinaires.

Vérifier les requêtes préparées avec le pilote utilisé

Les requêtes préparées nommées au niveau du protocole et la commande SQL PREPARE sont deux mécanismes différents. SQL PREPARE crée une requête dans la session de base courante. Une paire PREPARE/EXECUTE réussie sur une seule connexion serveur ne démontre pas la compatibilité entre transactions.

PgBouncer peut suivre les requêtes préparées nommées du protocole lorsque max_prepared_statements est non nul. Vérifiez aussi les exigences du pilote. Le guide Psycopg des requêtes préparées indique par exemple Psycopg 3.2+, PgBouncer 1.22+ et libpq 17+ côté client pour le fonctionnement documenté, avec une solution distincte pour un libpq plus ancien. Ces versions décrivent la compatibilité ; déployez des versions maintenues.

Exécutez assez souvent les vraies requêtes de l’application pour déclencher la préparation automatique. Répétez-les après un changement de connexion serveur, un rollback et un renouvellement de connexion. Une première requête réussie peut masquer un échec qui apparaît après le seuil de préparation du pilote.

Établir un budget pour toutes les instances

Partez des limites de connexion PostgreSQL. Réservez de la capacité pour l’administration, la surveillance, la maintenance et les autres clients avant de fixer le budget des pools applicatifs. Augmenter max_connections accroît aussi certaines allocations de ressources PostgreSQL.

Dans un exemple hypothétique, une base de 100 connexions pourrait en garder 20 pour les accès réservés et opérationnels, laissant 80 connexions. Deux instances PgBouncer plafonnées chacune à 30 connexions serveur utiliseraient au plus 60 connexions pour la base concernée, laissant 20 dans ce budget applicatif. Ce calcul sert à planifier ; ce n’est ni une recommandation de dimensionnement ni un benchmark.

Les limites PgBouncer s’appliquent par instance. default_pool_size concerne chaque paire utilisateur/base, tandis que max_db_connections plafonne un alias de base PgBouncer. Comptez les alias qui ciblent la même base PostgreSQL, les autres utilisateurs, les pools de réserve et les réplicas du déploiement. Une limite de clients entrants n’est pas le budget des connexions serveur.

Le fragment suivant contient uniquement des réglages de pool pour une instance de test isolée déjà configurée. La configuration reste incomplète : correspondances des bases, authentification, adresses d’écoute et TLS demandent leurs propres réglages.

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 10
reserve_pool_size = 0
max_db_connections = 30
max_prepared_statements = 100
query_wait_timeout = 10

Examinez ces options dans la référence de configuration. Le délai d’attente de dix secondes est un choix de test, pas une limite d’exécution de requête ni une recommandation de production. Adaptez les délais applicatifs et l’annulation à la politique d’attente souhaitée.

Comptez également les pools applicatifs. Quatre réplicas avec trois workers et un maximum de dix connexions par worker peuvent créer 120 clients PgBouncer. Ajoutez les dépassements autorisés et les workers de fond. La documentation du pooling SQLAlchemy détaille les limites et la remise à zéro ; utilisez les indications équivalentes pour votre pilote.

Garder les deux liaisons privées et authentifiées

Examinez séparément les liaisons application–PgBouncer et PgBouncer–PostgreSQL. Écoutez uniquement sur les interfaces prévues, restreignez l’accès réseau et séparez l’administration du rôle applicatif. Vérifiez l’authentification sur chaque liaison.

Vérifiez aussi TLS sur chacune. Pour un client libpq, verify-full valide la chaîne de certificats et le nom d’hôte. Configurez la vérification correspondante pour la connexion PostgreSQL de PgBouncer. Un réseau privé ne prouve pas à lui seul que l’identité du correspondant a été vérifiée.

Exécuter un petit test transactionnel, puis les tests applicatifs

Utilisez une base jetable et un rôle de test sans privilèges superutilisateur, autorisé à se connecter et à créer des tables temporaires. La commande suivante suppose qu’un point PgBouncer authentifié existe déjà sur le port local 6432, avec l’alias pooler_lab. Remplacez le rôle et l’adresse par ceux du laboratoire. Ne placez pas de mot de passe dans la ligne de commande.

Enregistrez ce contenu dans pooler-probe.sql :

BEGIN;
SET LOCAL statement_timeout = '2s';
CREATE TEMP TABLE pooler_article_probe (id integer) ON COMMIT DROP;
INSERT INTO pooler_article_probe VALUES (1), (2);
SELECT pg_backend_pid() AS backend_pid,
       current_setting('statement_timeout') AS timeout,
       count(*) AS row_count,
       sum(id) AS total
FROM pooler_article_probe;
COMMIT;
SELECT to_regclass('pg_temp.pooler_article_probe') IS NULL AS temp_table_gone;

Exécutez-le d’abord directement sur la base de test, puis via PgBouncer. Voici l’appel passant par le pooler :

psql "host=127.0.0.1 port=6432 dbname=pooler_lab user=pooler_app connect_timeout=5" -X -v ON_ERROR_STOP=1 -f pooler-probe.sql

Dans psql, -X ignore les personnalisations locales de démarrage et ON_ERROR_STOP arrête le script en cas d’erreur. Les valeurs attendues sont 2s, deux lignes, un total de trois et temp_table_gone = true. Le PID serveur est informatif ; il ne doit pas nécessairement être identique entre deux exécutions.

Ce test examine un paramètre et une table temporaire limités à la transaction. Il ne vérifie pas le protocole de requêtes préparées du pilote, l’isolation des tenants ni la concurrence de production. Ajoutez les contrôles propres à l’application :

1. Exécutez des lectures et écritures représentatives avec le vrai pilote, puis comparez les résultats à la connexion directe de référence.
2. Utilisez plusieurs clients simultanés dans le même pool utilisateur/base. Vérifiez qu’un client fonctionne après avoir reçu un autre PID serveur lors d’une transaction ultérieure ; un client seul peut retrouver toujours le même serveur.
3. Déclenchez une erreur contrôlée, effectuez un rollback et confirmez que l’opération suivante réussit avec les paramètres attendus, sans état provenant d’un autre client.
4. Testez les requêtes préparées, les annulations, les délais, les migrations et la sélection de schéma ou de tenant utilisée par l’application.

Observer la file avant d’augmenter une limite

Configurez un rôle de surveillance dans stats_users, puis examinez la base d’administration virtuelle pgbouncer :

psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=pooler_monitor connect_timeout=5" -X -c "SHOW POOLS;"

psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=pooler_monitor connect_timeout=5" -X -c "SHOW STATS;"

La référence de la console d’administration définit cl_waiting comme les clients qui attendent une connexion serveur. Comparez ce nombre à l’activité serveur, au temps d’attente et à la durée des transactions. Examinez pg_stat_activity pour repérer les transactions longues ou inactives et les attentes. Le pool applicatif peut aussi retenir les requêtes avant leur arrivée dans PgBouncer.

Mesurez latence, erreurs et charge de la base avec une concurrence représentative. Notre guide de déploiement FastAPI (en anglais) aborde les tests de charge applicatifs. Ajouter des connexions peut aggraver la contention si PostgreSQL est déjà occupé ; identifiez la limite réelle avant de relever le plafond.

Déployez le mode choisi sur une petite part du trafic, avec un retour arrière testé. Avant les changements de schéma associés, conservez une procédure vérifiée de sauvegarde et restauration PostgreSQL. Consignez les versions, les réglages du pilote, les contrôles de justesse et le comportement de la file qui ont justifié le changement.