PgBouncer : le mode transaction pooling expliqué (et ses limites)

PgBouncer propose trois modes, qui diffèrent uniquement par le moment où la connexion serveur Postgres retourne dans le pool commun. La documentation officielle les nomme session, transaction et statement (pgbouncer.org/features.html, section « Pooling modes », consultée le 24 août 2026).

Le mode session est le plus permissif mais le moins efficace en scalabilité : une connexion Postgres reste réservée à un client tant qu'il reste connecté, même s'il ne fait rien entre deux requêtes. Le mode statement est réservé à des cas très spécifiques (proxy read-only, health-checks) et casse même les transactions explicites classiques. Le mode transaction est le compromis qui domine en pratique pour une API REST : chaque requête HTTP correspond en général à une seule transaction Postgres courte.

En mode transaction, PgBouncer n'attache une connexion serveur à un client qu'au moment où celui-ci ouvre une transaction, et la restitue au pool dès le COMMIT ou le ROLLBACK. Entre deux transactions, le même client peut se retrouver réassigné à une connexion serveur totalement différente.

Concrètement, avec un default_pool_size de 20, PgBouncer peut absorber plusieurs centaines de clients simultanés qui n'ont, à un instant T, que quelques transactions réellement en cours. C'est ce ratio qui justifie le mode transaction pour une API REST à fort trafic mais à transactions courtes : la ressource rare (une connexion Postgres, coûteuse en mémoire côté serveur) n'est occupée que le temps strictement nécessaire.

[pgbouncer]
listen_port = 5432
; La connexion serveur est libérée dès la fin de chaque transaction
pool_mode = transaction
default_pool_size = 80
max_client_conn = 1000
; Ne pas utiliser avec des sessions stateful (SET, advisory locks, LISTEN/NOTIFY)

Ce dernier commentaire résume l'essentiel : le mode transaction fonctionne parce qu'il rompt délibérément le lien entre « ma session applicative » et « ma connexion Postgres ». Tout ce qui repose sur ce lien casse. La section suivante liste précisément quoi.

Un prepared statement protocolaire est nommé et mis en cache sur une connexion serveur précise, au moment du Parse. En mode transaction, cette connexion peut être réassignée à un autre client entre deux requêtes du même client logique. Si le driver rejoue ensuite le même nom de statement sur une connexion où il n'a jamais été préparé, Postgres répond par une erreur explicite, typiquement prepared statement "sqlx_s_N" does not exist pour un client sqlx. Le comportement est intermittent : il dépend de la façon dont les connexions tournent sous charge, pas d'un bug déterministe reproductible à chaque appel.

La correction côté client est la même quel que soit le langage : désactiver le cache de prepared statements nommés, ou forcer des requêtes non nommées, pour tout pool qui traverse un pooler en mode transaction. En Rust avec sqlx, ça passe par statement_cache_capacity(0) sur les options de connexion.

let connect_options = url
    .parse::<PgConnectOptions>()?
    .statement_cache_capacity(0);
// Equivalents : asyncpg -> statement_cache_size=0, pgjdbc -> prepareThreshold=0

Depuis sa version 1.21, PgBouncer atténue une partie du problème côté serveur : il peut suivre les prepared statements protocolaires en mode transaction et les répréparer à la volée sur la connexion assignée, avec un cache LRU par connexion dont la taille se règle via max_prepared_statements. Ça réduit le nombre d'échecs, mais ça ne dispense pas de désactiver le cache client dans un pool multi-tenant où le search_path change d'une requête à l'autre : un plan mis en cache fige l'identifiant interne (OID) de la table résolue au moment du Parse, et le rejouer sous un autre schéma peut renvoyer les données du mauvais tenant plutôt qu'une simple erreur.

Le dépôt Aurabase déploie PgBouncer en pool_mode=transaction devant le data plane partagé (deploy/helm/aurabase/templates/infra/pgbouncer.yaml), et un Pooler CNPG configuré de façon identique devant chaque instance Postgres dédiée d'un tenant (deploy/cnpg/tenant-pooler.yaml). Les deux chemins appliquent la même discipline décrite plus haut.

Le code source documente une raison de sécurité précise à ce choix, pas seulement une raison de stabilité. Les pools Postgres partagés entre tenants positionnent un search_path différent par projet sur des connexions réutilisées. Un prepared statement mis en cache fige l'OID de la table résolue au moment du Parse ; le rejouer pour un autre tenant sur la même connexion exécuterait la requête contre le schéma du premier tenant, un contournement d'isolation, pas seulement une erreur applicative. statement_cache_capacity(0) est donc appliqué sans exception, y compris sur les instances dédiées qui passent, elles aussi, par un Pooler CNPG en mode transaction.

Deuxième mesure de hygiène de session : au retour de chaque connexion dans le pool, un hook exécute DISCARD ALL (réinitialisation des réglages, désallocation des prepared statements côté serveur, libération des verrous advisory, purge des curseurs et tables temporaires). Sans ce hook, un résidu de session posé par une requête précédente pourrait fuiter sur la requête suivante d'un tenant différent réutilisant la même connexion recyclée.

Exception assumée : les instances PostgREST dédiées restent en connexion directe au primaire, sans passer par le pooler. Le rechargement de schéma de PostgREST repose sur LISTEN/NOTIFY, qui suppose une connexion persistante, exactement la fonctionnalité que le mode transaction casse (détail déjà documenté dans notre article sur la compatibilité PostgREST chez Aurabase). Les réglages RLS par requête, eux, passent en SET LOCAL à l'intérieur d'une transaction explicite, la seule façon de rester compatible avec un pool qui peut changer de connexion serveur à tout COMMIT (voir notre article sur l'isolation RLS multi-tenant).

1. Auditez le code applicatif. Cherchez les SET hors transaction, les LISTEN/NOTIFY, les verrous advisory de session, les curseurs WITH HOLD et les tables temporaires réutilisées entre requêtes.
2. Remplacez les SET de session par des SET LOCAL à l'intérieur d'une transaction explicite. C'est le seul réglage qui survit correctement au recyclage de connexion, parce qu'il est nettoyé au COMMIT/ROLLBACK plutôt que de fuiter sur la connexion suivante.
3. Désactivez le cache de prepared statements côté driver si votre pool traverse le pooler et que le schéma ou le rôle change d'une requête à l'autre. Le coût en performance est réel mais mesurable, et largement inférieur au risque de fuite entre tenants.
4. Isolez les connexions qui ont vraiment besoin du mode session (migrations, scripts admin, tout ce qui dépend de LISTEN/NOTIFY) sur une connexion directe hors pooler, plutôt que de renoncer au mode transaction pour tout le reste du trafic.
5. Dimensionnez default_pool_size et max_client_conn par rapport au max_connections réel de Postgres, pas par un chiffre arbitraire copié d'un autre projet.
6. Testez sous charge réelle, pas seulement en smoke test. Les erreurs de prepared statements et les fuites de réglages de session n'apparaissent quasiment jamais sur une seule connexion locale.
7. Surveillez SHOW POOLS et SHOW STATS depuis la console d'administration PgBouncer une fois en production, pour repérer une saturation du pool avant qu'elle ne devienne visible côté client.

Non, mais c'est le choix par défaut correct pour la grande majorité des API REST. Le mode session reste préférable pour une application legacy fortement dépendante de fonctionnalités de session que vous ne pouvez pas refactorer rapidement, ou pour un trafic faible où le gain de mutualisation ne compense pas l'effort de migration.

PgBouncer n'est pas non plus la seule implémentation de ce modèle de pooling : Supavisor (Supabase) et PgCat en sont deux alternatives récentes, avec des arbitrages différents sur la répartition de charge et le clustering. Voir notre comparatif détaillé, PgBouncer vs Supavisor vs PgCat, pour choisir entre les trois selon votre topologie.