PgBouncer : optimiser les connexions PostgreSQL en production

Les connexions PostgreSQL coûtent cher : chaque nouvelle connexion réserve environ 10 MB de mémoire, crée un processus dédié, et ajoute une latence de négociation SSL/TLS. À l'échelle d'une application serveur avec des centaines de requêtes concurrentes, ce surcoût sature vite la base. Le max_connections par défaut (100) se remplit en quelques secondes. PgBouncer répond à ce problème avec un pool de connexions léger : il garde un petit nombre de connexions physiques vers PostgreSQL et multiplexe par-dessus les milliers de connexions clientes.

Installation, configuration et tuning de PgBouncer en production, dans l'ordre.

1. Le problème des connexions PostgreSQL, contraintes de ressources
2. PgBouncer : principes et architecture, proxy léger et event-driven
3. Les trois modes de pooling, session, transaction, statement
4. Installation et configuration, Debian/Ubuntu, fichiers de config
5. Configuration avancée, tuning des paramètres critiques
6. Monitoring PgBouncer, SHOW commands, Prometheus, Grafana
7. PgBouncer vs alternatives, pgpool-II, Odyssey, solutions intégrées
8. Bonnes pratiques production, HA, sécurité, logging
9. Perspectives complémentaires, articles connexes et ressources
10. Conclusion, déploiement recommandé

PostgreSQL crée un processus OS indépendant pour chaque client connecté. Voici les coûts directs et indirects :

Exemple concret : Une application NodeJS avec 200 workers, chacun maintenant une connexion, consomme déjà 2 GB de RAM côté PostgreSQL avant même de traiter la première requête.

PgBouncer est un pool de connexions léger écrit en C. Au lieu d'ouvrir une connexion PostgreSQL par client, il :

1. Accepte les connexions clientes
2. Maintient un petit pool de vraies connexions vers PostgreSQL
3. Réutilise ces connexions selon le mode de pooling configuré

Architecture typique :

Clients (100s)
    ↓
PgBouncer (lightweight, single-threaded, event-based)
    ↓
PostgreSQL Pool (10-20 connexions réelles)
    ↓
PostgreSQL Server

PgBouncer est :

• Single-threaded : une seule boucle event (libevent)
• Stateless : pas de session persistante en mémoire
• Très léger : ~2 MB d'empreinte
• Compatible : clients PostgreSQL standard, pas de modification du code

Configuration par défaut : mode session. Recommandation production : mode transaction (meilleur compromis).

Installation sur Debian/Ubuntu

sudo apt-get update
sudo apt-get install pgbouncer

Fichier de configuration : /etc/pgbouncer/pgbouncer.ini

[databases]
myapp = host=localhost port=5432 dbname=mydb_prod

[pgbouncer]
; Écoute sur localhost:6432
listen_addr = 127.0.0.1
listen_port = 6432

; Pool size = nombre de connexions physiques par base
pool_size = 12
min_pool_size = 5
reserve_pool_size = 3
reserve_pool_timeout = 3

; Mode de pooling : session, transaction, statement
pool_mode = transaction

; Timeouts (secondes)
server_lifetime = 3600
server_idle_in_transaction_session_timeout = 60
idle_in_transaction_session_timeout = 600

; Log
logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 1
log_disconnections = 1
log_pooler_stats = 1

; Stats
stats_users = pgbouncer_stats
admin_users = pgbouncer_admin

; TCP keepalive
tcp_keepalives_idle = 30
tcp_keepalives_interval = 30
tcp_keepalives_count = 5

Fichier d'authentification : /etc/pgbouncer/userlist.txt

"appuser" "mot_de_passe_hash"
"pgbouncer_admin" "hash_admin"
"pgbouncer_stats" "hash_stats"

Les hashes sont générés avec :

echo -n "password" | md5sum | sed 's/-//'
# Pour md5 simple, ou utiliser pg_password:
python3 -c "import hashlib; print('md5' + hashlib.md5(b'passworduser').hexdigest())"

Tuning du pool_size

Règle empirique :

pool_size = (core_count * 2) + effective_spindle_count

Exemples :
- Serveur 4 cœurs, SSD : pool_size ≈ 10–12
- Serveur 16 cœurs, HDD : pool_size ≈ 20–25

Vérifier avec benchmark :

# Test de charge
pgbench -h localhost -p 6432 -U appuser -d mydb -c 50 -j 10 -T 60

server_lifetime et recyclage

; Recyclage des connexions après 1h
server_lifetime = 3600
; Timeout inactivité (évite les idle-in-transaction)
server_idle_in_transaction_session_timeout = 60

Authentification et sécurité

; md5, scram-sha-256, plain (déconseillé)
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

Commandes SHOW intégrées

Accès via admin :

psql -h localhost -p 6432 -U pgbouncer_admin -d pgbouncer

Requêtes utiles :

-- Vue d'ensemble
SHOW POOLS;

-- Statistiques clients
SHOW CLIENTS;

-- Statistiques serveurs
SHOW SERVERS;

-- État du pooler
SHOW STATS;

Export Prometheus

Utiliser pgbouncer_exporter :

wget https://github.com/pgbouncer/pgbouncer_exporter/releases/download/v0.5.1/pgbouncer_exporter-0.5.1.linux-amd64.tar.gz
tar xzf pgbouncer_exporter-0.5.1.linux-amd64.tar.gz
sudo mv pgbouncer_exporter /usr/local/bin/

Configuration systemd /etc/systemd/system/pgbouncer_exporter.service :

[Unit]
Description=PgBouncer Prometheus Exporter
After=network.target

[Service]
Type=simple
User=postgres
ExecStart=/usr/local/bin/pgbouncer_exporter --pgbouncer.address=localhost:6432
Restart=on-failure

[Install]
WantedBy=multi-user.target

Scrape Prometheus (prometheus.yml) :

scrape_configs:
  - job_name: pgbouncer
    static_configs:
      - targets: ['localhost:9127']

Métriques clés :

• pgbouncer_pools_client_connections_active
• pgbouncer_pools_server_connections_active
• pgbouncer_pools_server_connections_idle
• pgbouncer_stat_queries_total

Recommandation : PgBouncer pour pooling pur + Keepalived pour HA. pgpool-II si failover automatique requis.

Haute disponibilité avec Keepalived

Déploiement recommandé : deux instances PgBouncer avec VIP failover.

Configuration Keepalived /etc/keepalived/keepalived.conf :

vrrp_script check_pgbouncer {
  script "/usr/local/bin/check_pgbouncer.sh"
  interval 2
  weight -20
  fall 2
}

vrrp_instance PGBOUNCER {
  state MASTER
  interface eth0
  virtual_router_id 51
  priority 100
  advert_int 1
  virtual_ipaddress {
    10.0.0.100/24
  }
  track_script {
    check_pgbouncer
  }
}

Script de vérification :

#!/bin/bash
# /usr/local/bin/check_pgbouncer.sh
psql -h localhost -p 6432 -U pgbouncer_admin -d pgbouncer \
  -c "SHOW STATS" > /dev/null 2>&1
exit $?

Rotation des logs

Fichier /etc/logrotate.d/pgbouncer :

/var/log/pgbouncer/*.log {
  daily
  rotate 30
  compress
  delaycompress
  notifempty
  create 0640 postgres postgres
  postrotate
    systemctl reload pgbouncer > /dev/null 2>&1 || true
  endscript
}

Hardening sécurité

# pgbouncer.ini
; Désactiver les commandes dangereuses
allow_unsecured_connections = 0
; Authentification SCRAM-SHA-256
auth_type = scram-sha-256
; Restreindre admin_users
admin_users = pgbouncer_admin
; Limiter stats_users
stats_users = monitoring_user
; Unix socket (localhost uniquement)
unix_socket_dir = /var/run/pgbouncer
unix_socket_mode = 0660

Pour approfondir les sujets connexes :

• PostgreSQL : haute disponibilité avec Patroni, orchestration des replicas et failover automatique
• Optimiser les performances PostgreSQL, indexation, EXPLAIN ANALYZE, vacuum
• PostgreSQL avancé : tuning production, shared_buffers, effective_cache_size, work_mem
• Redis comme cache applicatif, réduire la charge sur PostgreSQL

• Documentation officielle PgBouncer : pgbouncer.github.io
• PostgreSQL Server Documentation : postgresql.org/docs
• PgBouncer Exporter for Prometheus : github.com/pgbouncer/pgbouncer_exporter
• Connection Pooling Patterns : Digital Ocean - Connection Pooling with PgBouncer
• Keepalived Documentation : keepalived.org

PgBouncer tient sa place dans la plupart des infrastructures PostgreSQL en production. Son empreinte de ~2 MB, sa stabilité et son fichier de config unique expliquent pourquoi les équipes DevOps et infrastructure le gardent par défaut.

Checklist de déploiement :

✅ Installation et configuration du pool_size optimal ✅ Choix du mode de pooling (transaction recommandé) ✅ Mise en place du monitoring Prometheus ✅ Configuration HA avec Keepalived et health-checks ✅ Logs rotatés et alertes Grafana ✅ Tests de charge et validation en staging

Chez SHPV, nous opérons PgBouncer sur nos infrastructures PostgreSQL pour tenir une latence prévisible et un SLA 99.9 %+ même sous forte charge. Le pool de connexions tient un rôle central dans nos offres d'infogérance de bases de données.

Pour toute question sur le déploiement en environnement critique, contactez notre équipe infrastructure.