Optimiser les connexions PostgreSQL avec PgBouncer : installation, configuration et bonnes pratiques

📌 Introduction

PostgreSQL est une base de données robuste… mais pas toujours taillée pour absorber des centaines de connexions simultanées.
Dans des architectures modernes avec plusieurs front-ends, cette limite peut entraîner saturation, lenteurs, voire crashes.

Heureusement, il existe une solution simple, légère et redoutablement efficace : PgBouncer, un proxy de connexions PostgreSQL.

🎯 Problème rencontré

Dans un environnement avec :

• 4 front-ends applicatifs
• 500+ connexions simultanées
• PostgreSQL 16 sur 16 vCPU / 24 Go RAM
• Charges massives en INSERT / UPDATE

… nous constations une consommation mémoire excessive, une charge CPU anormale, et des temps de réponse aléatoires.

Solution : PgBouncer

PgBouncer agit comme un pooler de connexions. Il intercepte les connexions client, et les mutualise vers PostgreSQL, en maintenant un nombre limité de connexions physiques.

Avantages :

• Connexions PostgreSQL réduites de 80%
• Stabilité mémoire/CPU
• Montée en charge sans augmenter max_connections

🛠️ Installation de PgBouncer (Debian/Ubuntu)

sudo apt update
sudo apt install pgbouncer

⚙️ Configuration de base

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 600
default_pool_size = 80
reserve_pool_size = 40

log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60
logfile = /var/log/pgbouncer/pgbouncer.log

server_idle_timeout = 600
client_idle_timeout = 600
query_timeout = 300

admin_users = postgres, pgbouncer
stats_users = postgres, pgbouncer

Fichier /etc/pgbouncer/userlist.txt

“appuser” “md5”

🔧 PostgreSQL : ajustements nécessaires

pg_hba.conf

host    all    all    127.0.0.1/32    md5

Redémarrer PostgreSQL :

sudo systemctl restart postgresql

Connexion côté application (JDBC)

Les versions récentes du driver PostgreSQL envoient automatiquement un paramètre non supporté par PgBouncer : extra_float_digits.

Il est indispensable de l’ajouter à l’URL JDBC :

url=”jdbc:postgresql://10.252.50.29:6432/appdb?extra_float_digits=0″

📈 Journalisation et logs

Les logs PgBouncer sont essentiels pour :

• Suivre les connexions entrantes
• Détecter les erreurs de pool
• Analyser la charge avec SHOW STATS
• Fichier de log par défaut :
  • /var/log/pgbouncer/pgbouncer.log
• Surveillance en live :
  • tail -f /var/log/pgbouncer/pgbouncer.log

🧪 Commandes utiles (depuis psql -p 6432 -d pgbouncer)

SHOW clients;
SHOW servers;
SHOW pools;
SHOW stats;

Afficher les connexions actives :

psql -U pgbouncer -h 127.0.0.1 -p 6432 -d pgbouncer

📊 Résultats obtenus

📉 Réduction de la latence perçue

🔻 Connexions PostgreSQL réduites de 500 à ~100

⚡ Stabilité CPU et RAM

🧘‍♂️ Temps de réponse linéaires, même sous charge

Architecture :

🎓 En résumé

Ne laissez pas votre PostgreSQL se noyer sous les connexions.
Utilisez PgBouncer pour rationaliser, stabiliser et faire évoluer votre architecture en toute sérénité.

---

🤝 Besoin d’accompagnement ?

Nous pouvons vous aider à :

• Déployer PgBouncer sur vos serveurs
• Ajuster vos paramètres PostgreSQL
• Intégrer la supervision et les alertes
• Former vos équipes Dev & Ops

📧 Contactez notre équipe pour une mission d’audit ou de mise en production

contact@dba-expert-consulting.fr