Configuration du Pooling de Connexions PostgreSQL avec PgBouncer pour les Applications à Fort Trafic

Lorsque les bases de données PostgreSQL sont confrontées à des volumes de connexions élevés, les performances peuvent se dégrader rapidement. Chaque connexion client correspond à un processus backend PostgreSQL, donc une application web très sollicitée peut consommer trop de mémoire et de CPU simplement pour maintenir les sessions ouvertes. Le pooling de connexions PgBouncer réduit cette pression en permettant à de nombreuses connexions client de réutiliser un ensemble plus restreint de connexions serveur.

Pourquoi le Pooling de Connexions est Important

Le Problème des Connexions

Surcharge des ressources : Chaque connexion PostgreSQL a un processus backend et une surcharge mémoire.
Limites de connexions : max_connections est fini, et l'augmenter trop peut rendre la base de données moins stable au lieu de plus rapide.
Coût de démarrage : Créer de nouvelles connexions à la base de données ajoute de la latence.
Changement de contexte : Trop de processus backend actifs peuvent gaspiller du CPU.

Avantages de PgBouncer

Permet à de nombreux clients d'applications de partager moins de connexions serveur PostgreSQL.
Met les clients en file d'attente lorsque le pool est occupé au lieu de submerger la base de données.
Prend en charge les modes de pooling session, transaction et instruction.
Fournit des commandes opérationnelles telles que SHOW POOLS, SHOW CLIENTS et RELOAD.

Installation et Configuration de Base

Installation de PgBouncer

Sur Ubuntu/Debian :

sudo apt update
sudo apt install pgbouncer

Sur CentOS/RHEL :

sudo yum install pgbouncer

Sur macOS :

brew install pgbouncer

Emplacements de Fichiers Courants

/etc/pgbouncer/
  ├── pgbouncer.ini        # Configuration principale
  └── userlist.txt         # Identifiants d'authentification

Configuration du Fichier de Configuration

Configuration de Base de pgbouncer.ini

[databases]
; database_name = host=hostname port=5432 dbname=actual_db
myapp = host=localhost port=5432 dbname=production_db

[pgbouncer]
; Mode de pooling de connexions
pool_mode = transaction

; Connexions maximales
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; Réseau
listen_addr = 0.0.0.0
listen_port = 6432

; Authentification
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Journalisation
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Performances
max_prepared_statements = 0

Les valeurs par défaut des paquets varient selon la distribution, vérifiez donc le fichier de service installé et le chemin de configuration après l'installation. Sur de nombreux paquets Linux, /etc/pgbouncer/pgbouncer.ini est le fichier principal.

Comprendre les Modes de Pooling

1. Pooling de Session (pool_mode = session)

Comportement : Connexion attribuée au client pour toute la session
Cas d'utilisation : Applications utilisant des tables temporaires, des instructions préparées
Efficacité : Faible (ratio de connexion 1:1)

pool_mode = session

2. Pooling de Transaction (pool_mode = transaction) - Recommandé

Comportement : Connexion retournée au pool après chaque transaction
Cas d'utilisation : La plupart des applications web avec des transactions courtes
Efficacité : Élevée, lorsque votre application ne dépend pas de l'état de la session

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

Le pooling de transaction est une bonne valeur par défaut pour les applications web requête/réponse, mais il peut briser les hypothèses concernant les fonctionnalités au niveau de la session. Soyez prudent avec les tables temporaires, les variables de session, les verrous consultatifs maintenus en dehors d'une transaction, LISTEN/NOTIFY et les instructions préparées au niveau du pilote, sauf si vous les avez testés avec votre version et vos paramètres PgBouncer.

3. Pooling d'Instruction (pool_mode = statement)

Comportement : Connexion retournée après chaque instruction
Cas d'utilisation : Requêtes simples en lecture seule sans transactions
Efficacité : Maximale (mais très restrictive)

pool_mode = statement
; À utiliser avec prudence - interrompt les transactions multi-instructions

Configuration de l'Authentification

Création de userlist.txt

PgBouncer nécessite un fichier d'authentification séparé. Générez un hachage MD5 et ajoutez-le à userlist.txt.

Exemple de userlist.txt :

"app_user" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"readonly_user" "md5a3c7f5e89d24e7c8b1f9d2e4a6c8b0d2"

Pour les mots de passe MD5 de style PostgreSQL, la valeur est md5 plus le hachage MD5 de mot_de_passe + nom_utilisateur. Ne collez pas de faux hachages en production ; générez des entrées à partir de vos vrais noms d'utilisateur et mots de passe, ou utilisez une méthode d'authentification plus sûre prise en charge par votre environnement.

Utilisation de auth_query PostgreSQL (Avancé)

PgBouncer peut interroger PostgreSQL pour les identifiants utilisateur lorsqu'un utilisateur n'est pas trouvé dans auth_file, mais cela nécessite un auth_user avec lequel PgBouncer peut se connecter. Un exemple minimal ressemble à ceci :

auth_type = md5
auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Limitez les privilèges de l'utilisateur d'authentification et suivez les recommandations de votre version de PostgreSQL. De nombreuses équipes utilisent une fonction SECURITY DEFINER au lieu d'accorder un accès direct aux données de mot de passe du catalogue.

Configuration Optimale pour un Trafic Élevé

Dimensionnement du Pool de Connexions

Il n'existe pas de formule universelle pour la taille du pool. Commencez avec une valeur prudente basée sur le nombre de requêtes que votre base de données peut exécuter activement, puis ajustez en fonction des métriques réelles.

Pour une application web typique, vous pourriez commencer ici et ajuster :

default_pool_size = 25
reserve_pool_size = 5
max_client_conn = 1000

Surveillez le CPU PostgreSQL, la latence des requêtes, les attentes de verrouillage et le compteur cl_waiting de PgBouncer. Si les clients attendent alors que la base de données est inactive, le pool est peut-être trop petit. Si la base de données est saturée, augmenter le pool peut empirer les choses.

Configuration Complète de Production

[databases]
production = host=db.example.com port=5432 dbname=prod_db pool_size=30
analytics = host=db-replica.example.com port=5432 dbname=prod_db pool_size=15

[pgbouncer]
pool_mode = transaction

; Limites de connexions
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 8
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600

; Réseau
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192

; Sécurité
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

; Journalisation
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

; Performances
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

max_prepared_statements = 0 désactive le suivi des instructions préparées de PgBouncer. Les versions récentes de PgBouncer peuvent prendre en charge les instructions préparées au niveau du protocole dans le pooling de transaction lorsque celui-ci est configuré au-dessus de zéro, mais vous devez tester votre pilote et votre charge de travail avant de l'activer.

Chaîne de Connexion de l'Application

Avant PgBouncer

# Connexion directe PostgreSQL
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

Après PgBouncer

# Connexion via PgBouncer
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

Surveillance et Gestion

Commandes de la Console d'Administration

Connectez-vous à la console d'administration PgBouncer :

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Commandes essentielles :

-- Afficher les statistiques du pool
SHOW POOLS;

-- Afficher les connexions actives
SHOW CLIENTS;
SHOW SERVERS;

-- Afficher la configuration
SHOW CONFIG;

-- Recharger la configuration
RELOAD;

Résolution des Problèmes Courants

Problème 1 : "no more connections allowed"

Cela peut signifier que PgBouncer rejette les connexions client ou que PostgreSQL rejette les connexions serveur. Vérifiez où l'erreur apparaît.

Modifications possibles côté PgBouncer :

max_client_conn = 5000
default_pool_size = 50

Avant d'augmenter les deux, confirmez que la limite de descripteurs de fichier de votre système d'exploitation et max_connections de PostgreSQL peuvent prendre en charge les nouveaux totaux. PgBouncer lui-même a également besoin de suffisamment de descripteurs de fichier pour les sockets client et serveur.

Problème 2 : Compteur cl_waiting Élevé

Solutions :

Augmenter la taille du pool
Optimiser les requêtes lentes
Ajouter un pool de réserve

Problème 3 : Erreurs d'Instructions Préparées

Si votre application ou pilote utilise des instructions préparées et que vous êtes en mode de pooling de transaction, vous pouvez voir des erreurs lorsqu'une instruction ultérieure atterrit sur une connexion serveur différente. Les options incluent :

Désactiver les instructions préparées côté pilote.
Utiliser le pooling de session pour cette charge de travail.
Tester la prise en charge des instructions préparées de PgBouncer avec une valeur positive de max_prepared_statements sur une version récente de PgBouncer.

Paramètre prudent :

max_prepared_statements = 0

Exemple de Déploiement Pratique

Imaginez que vos serveurs d'application peuvent ouvrir des centaines de requêtes HTTP simultanées, mais que la base de données fonctionne mieux avec quelques dizaines de requêtes actives. Pointez l'application sur PgBouncer sur le port 6432, définissez max_client_conn suffisamment haut pour les pics de clients, et maintenez default_pool_size proche du nombre de connexions de base de données que vous souhaitez réellement actives pour cette paire base de données/utilisateur.

Validez ensuite avec :

SHOW POOLS;
SHOW STATS;

Si cl_waiting reste au-dessus de zéro pendant le trafic normal, enquêtez sur les requêtes lentes avant d'augmenter simplement la taille du pool. PgBouncer protège PostgreSQL des tempêtes de connexions, mais il ne rend pas les requêtes SQL lentes économiques.

En Résumé Pratique

Commencez avec le pooling de transaction pour les charges de travail web sans état, maintenez les tailles de pool intentionnellement petites, et ajustez en fonction des métriques de PgBouncer et PostgreSQL. Si votre application dépend du comportement de session ou des instructions préparées, testez ces chemins avant de mettre PgBouncer devant le trafic de production.