Améliorer la scalabilité de PostgreSQL : Implémentation du pool de connexions PgBouncer

PostgreSQL utilise un processus backend par connexion client. Ce modèle est fiable, mais il devient coûteux lorsqu'une application web ouvre des centaines ou des milliers de connexions principalement inactives.

PgBouncer se situe entre votre application et PostgreSQL, maintenant un pool plus petit de connexions serveur et permettant à de nombreux clients de les réutiliser. Le résultat est une réduction de la surcharge des connexions et une utilisation plus prévisible de la mémoire de la base de données.

Le goulot d'étranglement : Surcharge native des connexions PostgreSQL

PostgreSQL utilise un modèle dédié de processus par connexion. Bien que très stable et garantissant l'isolation, cette architecture introduit une surcharge significative sous stress :

1. Consommation de ressources : Chaque nouvelle connexion oblige le serveur à créer un nouveau processus backend, consommant de la mémoire et du CPU. Des centaines ou des milliers de connexions inactives retiennent inutilement de la RAM.
2. Établissement lent : L'établissement d'une nouvelle connexion implique une poignée de main réseau, une authentification et une initialisation du processus, ajoutant une latence mesurable aux requêtes applicatives, surtout celles qui ouvrent et ferment fréquemment des connexions.
3. Limites de scalabilité : Ces demandes de ressources imposent un plafond effectif au nombre de connexions simultanées que le serveur PostgreSQL peut gérer avant que les performances ne s'effondrent.

Présentation de PgBouncer : Le proxy léger

PgBouncer agit comme un serveur proxy léger positionné entre les applications clientes et le serveur de base de données PostgreSQL. Sa fonction principale est de maintenir un nombre fixe et persistant de connexions ouvertes vers le backend PostgreSQL, en regroupant et en réutilisant ces connexions pour les requêtes clientes applicatives transitoires.

Cette approche offre deux avantages critiques :

1. Surcharge réduite : Le serveur PostgreSQL ne voit que le pool fixe de connexions maintenues par PgBouncer, éliminant le coûteux cycle de création de processus par connexion pour les requêtes clientes entrantes.
2. Débit accru : En réutilisant les connexions établies, PgBouncer minimise le temps d'authentification et d'initialisation des connexions, résultant en un débit applicatif significativement plus élevé et une latence plus faible.

Comprendre les modes de pooling de PgBouncer

L'efficacité de PgBouncer dépend fortement du mode de pooling choisi. PgBouncer propose trois modes fondamentaux, chacun adapté à différentes architectures applicatives et besoins de concurrence.

1. Pooling de session (pool_mode = session)

Le pooling de session est le mode par défaut et le plus sûr. Une fois qu'un client se connecte, PgBouncer dédie une connexion serveur poolée à ce client jusqu'à ce que le client se déconnecte. La connexion est retournée au pool uniquement lorsque le client ferme explicitement sa session.

• Cas d'utilisation : Applications qui dépendent fortement de fonctionnalités spécifiques à la session (par exemple, instructions préparées, tables temporaires, commandes SET pour des variables personnalisées).
• Avantages : Le plus sûr, entièrement compatible avec toutes les fonctionnalités de PostgreSQL.
• Inconvénients : Pooling le moins efficace, car les connexions sont conservées même pendant l'inactivité du client.

2. Pooling de transaction (pool_mode = transaction)

Le pooling de transaction est généralement recommandé pour les applications web à fort trafic, en particulier celles utilisant des API sans état. Une connexion serveur est dédiée à un client uniquement pour la durée d'une seule transaction (BEGIN à COMMIT/ROLLBACK). Dès que la transaction se termine, la connexion est immédiatement retournée au pool pour être réutilisée par un autre client en attente.

• Cas d'utilisation : Transactions courtes et fréquentes courantes dans les systèmes OLTP et les microservices.
• Avantages : Utilisation très efficace des ressources serveur.
• Inconvénients : Nécessite que les applications gèrent les transactions avec soin. Les changements d'état au niveau de la session (par exemple, SET extra_float_digits = 3) seront perdus entre les transactions ou fuiteront vers d'autres clients.

Avertissement sur le pooling de transaction

En pooling de transaction, évitez l'état de session tel que les tables temporaires, les modifications SET au niveau de la session, les verrous consultatifs de session et les instructions préparées de longue durée. PgBouncer réinitialise les connexions serveur entre les clients, mais le pooling de transaction nécessite tout de même des tests de compatibilité applicative.

3. Pooling d'instruction (pool_mode = statement)

Le pooling d'instruction est le mode le plus agressif. Une connexion serveur est retournée au pool après chaque exécution d'instruction unique. Ce mode empêche efficacement l'utilisation de transactions multi-instructions et est très restrictif.

• Cas d'utilisation : Charges de travail hautement spécialisées, en lecture seule, où les transactions sont explicitement interdites ou inutiles.
• Avantages : Maximise la réutilisation des connexions.
• Inconvénients : Casse toutes les transactions. Uniquement adapté aux environnements où l'utilisation de transactions est garantie.

Configuration et installation initiale de PgBouncer

1. Installation

PgBouncer est souvent disponible dans les dépôts de distribution standard :

# Sur Debian/Ubuntu
sudo apt update && sudo apt install pgbouncer

# Sur RHEL/CentOS
sudo dnf install pgbouncer

2. Fichiers de configuration

PgBouncer repose principalement sur deux fichiers de configuration, généralement situés dans /etc/pgbouncer/ :

• pgbouncer.ini : Configuration principale, définissant les bases de données, les limites de pool et les modes de fonctionnement.
• userlist.txt : Définit les utilisateurs et mots de passe que PgBouncer utilise pour s'authentifier auprès du serveur PostgreSQL.

3. Définition des utilisateurs (userlist.txt)

Pour des raisons de sécurité, PgBouncer ne lit pas directement la table pg_authid de PostgreSQL. Vous devez définir manuellement les utilisateurs avec lesquels il peut s'authentifier. Assurez-vous que ce fichier est sécurisé (par exemple, propriété de l'utilisateur pgbouncer et permissions restreintes).

"app_user" "md5<hachage-md5>"
"admin_user" "un_autre_hachage"

Les mots de passe en texte clair sont possibles avec certaines configurations d'authentification, mais préférez les hachages ou une authentification plus forte là où vos versions de PgBouncer et PostgreSQL le supportent. Pour l'authentification MD5 héritée, la valeur stockée est md5 plus le hachage MD5 du mot de passe et du nom d'utilisateur, pas seulement le mot de passe.

4. Configuration de pgbouncer.ini

Le fichier pgbouncer.ini définit le comportement du pooler. Voici un exemple adapté à une configuration d'application web courante utilisant le pooling de transaction.

[databases]
# Définition de la chaîne de connexion client :
# <nom_base> = host=<ip_serveur_pg> port=<port_pg> dbname=<nom_bd> user=<utilisateur_auth_pgbouncer>
myappdb = host=10.0.0.5 port=5432 dbname=productiondb user=pgbouncer_service

[pgbouncer]

; Configuration d'écoute
listen_addr = *
listen_port = 6432

; Configuration d'authentification
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Mode de pooling (défini selon les besoins de l'application)
pool_mode = transaction
; DISCARD ALL est la requête de réinitialisation par défaut pour le pooling de session.
; En pooling de transaction, testez soigneusement avant de compter sur l'état de session.
server_reset_query = DISCARD ALL

; Limites et tailles de connexion
; Nombre maximal de connexions client à PgBouncer
max_client_conn = 1000

; Nombre maximal de connexions que PgBouncer maintient ouvertes par base de données (taille du pool)
default_pool_size = 20

; Nombre maximal de connexions autorisées dans le pool globalement, toutes bases confondues
max_db_connections = 100

; Lorsque le pool est épuisé, réserver ce nombre d'emplacements
reserve_pool_size = 5

; Journalisation et administration
admin_users = postgres, admin_user
stats_users = postgres

Surveillance et administration

PgBouncer expose une pseudo-base de données nommée pgbouncer qui permet aux administrateurs de surveiller l'état, les statistiques et les connexions du pooler en temps réel. Vous vous connectez au port d'écoute de PgBouncer (par exemple, 6432) en utilisant l'un des admin_users définis.

psql -p 6432 -U admin_user pgbouncer

Commandes administratives clés :

Commande	Description	Note d'utilisation
SHOW STATS;	Affiche les statistiques de connexion (requêtes, octets, durée totale).	Utile pour l'analyse des performances.
SHOW POOLS;	Affiche l'état des pools pour toutes les bases de données configurées.	Surveillez cl_active, sv_active, sv_idle.
SHOW CLIENTS;	Liste toutes les connexions client connectées à PgBouncer.
RELOAD;	Tente de recharger la configuration sans interrompre les connexions.
PAUSE;	Arrête d'accepter de nouvelles requêtes, attend la fin des transactions en cours.	Utilisé avant une maintenance ou une mise à niveau de PgBouncer.

Conseils de dimensionnement

1. Emplacement : Installez PgBouncer sur le même serveur que votre application ou sur une machine dédiée hautement optimisée pour le réseau afin de minimiser la latence entre l'application et le pooler.
2. Dimensionnement du pool : Le default_pool_size doit être défini à un nombre raisonnable (souvent 10-50), généralement bien inférieur au nombre de connexions autorisées sur le serveur PostgreSQL lui-même. Une taille de pool excessive va à l'encontre du but du pooling.
3. Limites client : Utilisez max_client_conn pour éviter que des tempêtes de connexions ne submergent PgBouncer lui-même. Cela agit comme un limiteur frontal robuste.

En résumé

PgBouncer est le plus utile lorsque votre application a de nombreuses connexions de courte durée ou inactives. Choisissez le mode de pooling le moins agressif que votre application peut tolérer, maintenez les pools de serveurs PostgreSQL suffisamment petits pour protéger la base de données, et surveillez SHOW POOLS; pour détecter les clients en attente avant que les utilisateurs ne ressentent le ralentissement.