🔝 Retour au Sommaire

19.4.6. Connection storms et pooling

Introduction : Quand les connexions deviennent un problème

Imaginez un restaurant avec seulement 10 tables. Si 100 clients arrivent en même temps, le chaos s'installe : certains attendent debout, les serveurs sont débordés, la cuisine est saturée. C'est exactement ce qui se passe lors d'un connection storm (tempête de connexions) dans PostgreSQL.

Contrairement à ce qu'on pourrait penser, PostgreSQL ne gère pas des milliers de connexions simultanées facilement. Chaque connexion consomme des ressources (mémoire, CPU) et au-delà d'un certain seuil, les performances s'effondrent.

Dans ce chapitre, vous apprendrez :

Pourquoi trop de connexions posent problème
Comment détecter un connection storm
Le concept de connection pooling
PgBouncer : la solution la plus populaire
Les bonnes pratiques pour gérer les connexions

Partie 1 : Comprendre le problème des connexions

Comment PostgreSQL gère les connexions

PostgreSQL utilise une architecture process-per-connection : chaque connexion client = un processus backend distinct.

┌─────────────┐
│   Client 1  │─────┐
└─────────────┘     │
                    ▼
┌─────────────┐   ┌──────────────────┐
│   Client 2  │──▶│  PostgreSQL      │
└─────────────┘   │  (Postmaster)    │
                  └──────────────────┘
┌─────────────┐     │         │
│   Client 3  │─────┘         │
└─────────────┘               │
                              ▼
                    ┌──────────────────┐
                    │  Backend 1 (PID) │
                    │  Backend 2 (PID) │
                    │  Backend 3 (PID) │
                    └──────────────────┘

Conséquence : Chaque connexion crée un processus système complet avec :

Mémoire propre (~10 MB par connexion)
Ressources CPU pour la gestion
Overhead de synchronisation entre processus

Le coût d'une connexion

-- Voir la mémoire utilisée par connexion
SELECT
    pid,
    usename,
    application_name,
    pg_size_pretty(
        pg_backend_memory_contexts.total_bytes
    ) AS memory_used
FROM pg_stat_activity  
JOIN pg_backend_memory_contexts ON pg_stat_activity.pid = pg_backend_memory_contexts.pid  
ORDER BY pg_backend_memory_contexts.total_bytes DESC  
LIMIT 10;

Coûts typiques par connexion :

Mémoire de base : ~5-10 MB
Mémoire work_mem (si utilisée) : 64 MB par défaut
CPU : Context switching, gestion des verrous
Connexion/déconnexion : ~5-10 ms par cycle

La limite théorique

-- Voir les connexions configurées
SHOW max_connections;
-- Valeur par défaut : 100

-- Voir les connexions actuellement utilisées
SELECT count(*) FROM pg_stat_activity;

Calcul de mémoire potentielle :

Configuration :
- max_connections = 200
- work_mem = 64 MB

Mémoire potentielle = 200 × (10 MB base + 64 MB work_mem × 2-4 opérations)
                    = 200 × (10 + 128-256 MB)
                    = 27 GB - 53 GB de RAM !

⚠️ Danger : Avec seulement 32 GB de RAM, vous risquez un Out Of Memory (OOM) !

Qu'est-ce qu'un connection storm ?

Un connection storm se produit quand un grand nombre de connexions sont créées en très peu de temps.

Scénarios typiques :

Application redémarre : Tous les workers se reconnectent en même temps
Traffic spike : Pic soudain de trafic (Black Friday, annonce virale)
Retry loops : Application retry automatiquement après échec, créant une boucle
Load balancer mal configuré : Ouvre trop de connexions
Fonction serverless : Chaque invocation = nouvelle connexion

Symptômes d'un connection storm

1. Erreurs de connexion

FATAL: sorry, too many clients already  
FATAL: remaining connection slots are reserved for non-replication superuser connections

2. Performances dégradées

-- Voir le nombre de connexions actives
SELECT
    count(*) FILTER (WHERE state = 'active') as active,
    count(*) FILTER (WHERE state = 'idle') as idle,
    count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction,
    count(*) as total
FROM pg_stat_activity;

Résultat problématique :

 active | idle | idle_in_transaction | total
--------+------+---------------------+-------
    450 |  200 |                  50 |   700

Si total approche max_connections (ex: 800 connexions pour max_connections=800), vous êtes en saturation !

3. Système surchargé

# CPU élevé pour gestion des processus
top
# Charge : load average > nombre de cœurs × 3

# Beaucoup de processus PostgreSQL
ps aux | grep postgres | wc -l
# Résultat : 700+ processus

Partie 2 : Détecter et diagnostiquer

Monitoring des connexions

Vue d'ensemble des connexions

SELECT
    datname,
    count(*) as connections,
    max(now() - backend_start) as oldest_connection
FROM pg_stat_activity  
WHERE pid != pg_backend_pid()  
GROUP BY datname  
ORDER BY connections DESC;

Exemple de résultat :

  datname  | connections | oldest_connection
-----------+-------------+-------------------
 mydb      |         650 | 02:34:56.789012
 postgres  |          10 | 01:23:45.678901

Connexions par utilisateur et application

SELECT
    usename,
    application_name,
    count(*) as connection_count,
    count(*) FILTER (WHERE state = 'active') as active,
    count(*) FILTER (WHERE state = 'idle') as idle
FROM pg_stat_activity  
GROUP BY usename, application_name  
ORDER BY connection_count DESC;

Résultat typique d'un problème :

  usename  | application_name | connection_count | active | idle
-----------+------------------+------------------+--------+------
 app_user  | MyWebApp         |              580 |     45 |  535
 app_user  | BackgroundJob    |               80 |     12 |   68

Analyse : 580 connexions pour une application web = PROBLÈME !

Pourquoi autant de connexions idle ?
L'application ouvre-t-elle trop de connexions ?
Y a-t-il des connection leaks ?

Identifier les connexions problématiques

-- Connexions idle depuis longtemps
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    backend_start,
    state,
    state_change,
    now() - state_change as idle_duration
FROM pg_stat_activity  
WHERE state = 'idle'  
  AND now() - state_change > interval '10 minutes'
ORDER BY state_change;

Connexions "idle in transaction" (très problématiques) :

SELECT
    pid,
    usename,
    application_name,
    now() - xact_start as transaction_duration,
    query
FROM pg_stat_activity  
WHERE state = 'idle in transaction'  
  AND now() - xact_start > interval '5 minutes';

⚠️ Ces connexions bloquent VACUUM et peuvent causer des problèmes de performance !

Visualisation graphique

-- Connexions par état (pour graphique)
SELECT
    state,
    count(*) as count
FROM pg_stat_activity  
GROUP BY state;

Résultat à exporter vers Grafana :

        state         | count
----------------------+-------
 active               |    45
 idle                 |   520
 idle in transaction  |    15
 disabled             |     0

Alertes proactives

-- Créer une fonction d'alerte
CREATE OR REPLACE FUNCTION check_connection_saturation()  
RETURNS TABLE(  
    max_conn int,
    current_conn bigint,
    percent_used numeric,
    status text
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        current_setting('max_connections')::int as max_conn,
        count(*)::bigint as current_conn,
        round(100.0 * count(*) / current_setting('max_connections')::numeric, 2) as percent_used,
        CASE
            WHEN count(*) > current_setting('max_connections')::numeric * 0.9 THEN 'CRITICAL'
            WHEN count(*) > current_setting('max_connections')::numeric * 0.75 THEN 'WARNING'
            ELSE 'OK'
        END as status
    FROM pg_stat_activity;
END;
$$ LANGUAGE plpgsql;

-- Utiliser
SELECT * FROM check_connection_saturation();

Partie 3 : Connection Pooling - La solution

Qu'est-ce que le connection pooling ?

Le connection pooling est comme un système de location de voitures :

Au lieu que chaque personne achète sa propre voiture (connexion dédiée)
On partage un parc de voitures (pool de connexions)
Quand vous avez besoin, vous louez une voiture
Quand vous avez fini, vous la rendez au pool

SANS POOLING :
┌─────────┐    ┌─────────┐    ┌─────────┐
│ Client1 │────│ Conn1   │────│         │
└─────────┘    └─────────┘    │         │
┌─────────┐    ┌─────────┐    │  SGBD   │
│ Client2 │────│ Conn2   │────│         │
└─────────┘    └─────────┘    │ (500    │
┌─────────┐    ┌─────────┐    │  conn)  │
│ Client3 │────│ Conn3   │────│         │
└─────────┘    └─────────┘    └─────────┘
     ...           ...
┌─────────┐    ┌─────────┐
│ Client500─── │ Conn500 │
└─────────┘    └─────────┘

Problème : 500 connexions = Saturation !


AVEC POOLING :
┌─────────┐
│ Client1 │────┐
└─────────┘    │
┌─────────┐    │  ┌──────────────┐   ┌─────────┐
│ Client2 │────┼─▶│  Connection  │───│ Conn1   │
└─────────┘    │  │     Pool     │───│ Conn2   │───▶ SGBD
┌─────────┐    │  │  (PgBouncer) │───│ Conn3   │   (3 conn)
│ Client3 │────┘  └──────────────┘   │  ...    │
└─────────┘                          │ Conn20  │
     ...                             └─────────┘
┌─────────┐
│ Client500
└─────────┘

Solution : 500 clients → 20 connexions réelles !

Avantages du pooling

Aspect	Sans Pooling	Avec Pooling
Connexions SGBD	1 par client	Partagées
Mémoire PostgreSQL	500 × 10MB = 5GB	20 × 10MB = 200MB
Overhead connexion	5-10ms par requête	0.1ms (réutilisation)
Scalabilité	Limitée (max_connections)	Illimitée côté app
Performances	Dégradées > 200 conn	Stables

Types de pooling

1. Session Pooling (par session)

Client connect → Get connection → Keep connection → Client disconnect
                                   (toute la session)

Caractéristiques :

Une connexion = une session client complète
TOUTES les fonctionnalités PostgreSQL disponibles
Prepared statements, temporary tables, cursors : OK
Moins d'économie de connexions

Cas d'usage : Applications avec sessions longues, utilisant features avancées.

2. Transaction Pooling (par transaction)

Client → BEGIN → Get connection → COMMIT → Release connection

Caractéristiques :

Connexion rendue après chaque transaction
Maximum d'économie de connexions
Limitations : Pas de prepared statements entre transactions
Pas de session state (SET commands limités)

Cas d'usage : APIs web, microservices (recommandé).

3. Statement Pooling (par requête)

Client → Query → Get connection → Execute → Release connection

Caractéristiques :

Connexion rendue après chaque requête
Économie maximale mais limitations majeures
Pas de transactions multi-requêtes
Rarement utilisé

Cas d'usage : Très rare, seulement pour read-only queries simples.

Partie 4 : PgBouncer - Le pooler de référence

Qu'est-ce que PgBouncer ?

PgBouncer est un connection pooler léger et ultra-performant pour PostgreSQL. C'est la solution la plus utilisée en production.

Avantages :

✅ Très léger (< 10 MB RAM)
✅ Très rapide (~2 MB/s overhead)
✅ Facile à configurer
✅ Support transaction et session pooling
✅ Peut gérer 10,000+ connexions clients

Installation de PgBouncer

# Ubuntu/Debian
sudo apt update  
sudo apt install pgbouncer  

# CentOS/RHEL
sudo yum install pgbouncer

# macOS
brew install pgbouncer

# Vérifier l'installation
pgbouncer --version

Configuration de base

Fichier pgbouncer.ini

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
# Mode de pooling (RECOMMANDÉ pour web apps)
pool_mode = transaction

# Connexions
listen_addr = 0.0.0.0  
listen_port = 6432  
auth_type = scram-sha-256  
auth_file = /etc/pgbouncer/userlist.txt  

# Limites connexions
max_client_conn = 10000          # Connexions clients max  
default_pool_size = 25           # Connexions PostgreSQL par base  
min_pool_size = 5                # Connexions minimum gardées  
reserve_pool_size = 5            # Connexions de réserve  
reserve_pool_timeout = 3         # Timeout pour obtenir connexion (secondes)  

# Logs
log_connections = 1  
log_disconnections = 1  
log_pooler_errors = 1  

# Performance
max_db_connections = 100         # Limite totale connexions PostgreSQL  
ignore_startup_parameters = extra_float_digits  

# Sécurité
admin_users = postgres  
stats_users = postgres, monitoring_user

Explication des paramètres clés :

Paramètre	Description	Valeur recommandée
`pool_mode`	transaction\|session\|statement	`transaction`
`max_client_conn`	Connexions clients max	10000
`default_pool_size`	Connexions PostgreSQL par DB	25
`reserve_pool_size`	Connexions réserve	5

Fichier userlist.txt (authentification)

# Format : "username" "password" (hashed avec SCRAM-SHA-256)
# Obtenir le hash :
echo -n "passwordMD5$(echo -n 'passwordusername' | md5sum | awk '{print $1}')" | md5sum

# Ou utiliser le format SCRAM (PostgreSQL 10+)
"app_user" "SCRAM-SHA-256$4096:salt:hash:serverkey"

# Pour développement (INSECURE) :
"app_user" "plain_password"

Méthode recommandée : Copier depuis PostgreSQL

-- Dans PostgreSQL, voir le hash du mot de passe
SELECT rolname, rolpassword  
FROM pg_authid  
WHERE rolname = 'app_user';  

-- Copier ce hash dans userlist.txt

Démarrer PgBouncer

# Démarrer
sudo systemctl start pgbouncer

# Activer au démarrage
sudo systemctl enable pgbouncer

# Vérifier le statut
sudo systemctl status pgbouncer

# Voir les logs
sudo tail -f /var/log/postgresql/pgbouncer.log

Se connecter via PgBouncer

# Connexion client → PgBouncer (port 6432)
psql -h localhost -p 6432 -U app_user -d mydb

# PgBouncer → PostgreSQL (port 5432)
# (géré automatiquement par PgBouncer)

Console d'administration PgBouncer

# Se connecter à la console d'admin
psql -h localhost -p 6432 -U postgres pgbouncer

# Commandes disponibles :
pgbouncer=# SHOW HELP;

Commandes utiles :

-- Voir les statistiques
SHOW STATS;

-- Voir les pools de connexions
SHOW POOLS;

-- Voir les clients connectés
SHOW CLIENTS;

-- Voir les connexions PostgreSQL
SHOW SERVERS;

-- Voir la configuration
SHOW CONFIG;

-- Recharger la configuration (sans redémarrage)
RELOAD;

-- Suspendre les connexions (maintenance)
PAUSE;

-- Reprendre
RESUME;

-- Tuer une connexion client
KILL client_addr;

SHOW POOLS (exemple de résultat)

 database  |   user   | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | pool_mode
-----------+----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+-----------
 mydb      | app_user |        12 |          0 |        12 |      13 |       0 |         0 |        0 |       0 | transaction

Interprétation :

cl_active : 12 clients actifs
cl_waiting : 0 clients en attente (bon signe !)
sv_active : 12 connexions PostgreSQL actives
sv_idle : 13 connexions PostgreSQL idle (disponibles)
25 connexions PostgreSQL au total (12 + 13) = default_pool_size

SHOW STATS (exemple de résultat)

 database  | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
-----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
 mydb      |          1234567 |           2345678 |      123456789 |   98765432 |       123456789 |        234567890 |            1234 |            123 |             234 |    12345 |     9876 |           123 |            234 |             1

Métriques importantes :

total_xact_count : Nombre de transactions traitées
avg_wait_time : Temps d'attente moyen (doit être < 1ms)
total_query_time / total_query_count : Latence moyenne

Partie 5 : Configuration optimale

Dimensionner le pool

Formule de base :

default_pool_size = Nombre de cœurs CPU × 2 à 4

Exemples :

Serveur	Cœurs CPU	default_pool_size	Justification
Petit (dev)	2	8-10	2 × 4
Moyen (prod)	8	20-30	8 × 3
Grand (prod)	32	80-100	32 × 3

Règle importante : Plus n'est pas toujours mieux !

Trop de connexions PostgreSQL = contention, context switching
Optimal : Garder CPU à 70-80% d'utilisation

Configuration par base de données

[databases]
# Base principale (beaucoup de traffic)
mydb = host=localhost port=5432 dbname=mydb pool_size=50

# Base analytics (requêtes lourdes, peu de concurrence)
analytics = host=localhost port=5432 dbname=analytics pool_size=10 pool_mode=session

# Base de reporting (read-only)
reports = host=replica.example.com port=5432 dbname=mydb pool_size=20

Transaction pooling : Limitations et solutions

Problème : Prepared Statements

# ❌ NE FONCTIONNE PAS avec transaction pooling
conn = psycopg2.connect("host=pgbouncer port=6432 dbname=mydb")  
cursor = conn.cursor()  
cursor.execute("PREPARE myplan AS SELECT * FROM users WHERE id = $1")  
cursor.execute("EXECUTE myplan (123)")  # ERREUR : prepared statement n'existe pas

Solution : Ne pas utiliser PREPARE/EXECUTE explicites. Les drivers modernes gèrent ça automatiquement avec le protocole Extended Query.

# ✅ FONCTIONNE : Utilise le protocole Extended Query
cursor.execute("SELECT * FROM users WHERE id = %s", (123,))

Problème : Temporary Tables

-- ❌ NE FONCTIONNE PAS : table perdue après transaction
BEGIN;  
CREATE TEMP TABLE temp_data (id int);  
INSERT INTO temp_data VALUES (1), (2);  
COMMIT;  

-- Nouvelle transaction = nouvelle connexion = table perdue !
SELECT * FROM temp_data;  -- ERROR: relation does not exist

Solution 1 : Tout faire dans une transaction

BEGIN;  
CREATE TEMP TABLE temp_data (id int);  
INSERT INTO temp_data VALUES (1), (2);  
-- Utiliser la table
SELECT * FROM temp_data;
-- Nettoyage automatique
COMMIT;

Solution 2 : Utiliser CTE au lieu de TEMP TABLE

WITH temp_data AS (
    SELECT generate_series(1, 100) as id
)
SELECT * FROM temp_data WHERE id < 10;

Problème : Session variables

-- ❌ PEUT NE PAS FONCTIONNER
SET work_mem = '256MB';  -- Perdu après transaction

-- ✅ FONCTIONNE : SET LOCAL dans transaction
BEGIN;  
SET LOCAL work_mem = '256MB';  
-- Requête lourde ici
COMMIT;

Configuration PgBouncer pour autoriser certains SET :

[pgbouncer]
# Ignorer ces paramètres (ne pas les réinitialiser)
ignore_startup_parameters = extra_float_digits,options

# Autoriser ces commandes SET
server_reset_query = DISCARD ALL  
server_reset_query_always = 0

Monitoring PgBouncer

Métriques à surveiller

-- Dans la console PgBouncer
SHOW POOLS;

-- Métriques clés :
-- cl_waiting > 0 : Clients en attente → Augmenter pool_size
-- sv_active ≈ pool_size : Pool saturé → Augmenter pool_size
-- avg_wait_time > 1ms : Latence élevée → Problème de sizing

Exporter vers Prometheus

# Installer pgbouncer_exporter
wget https://github.com/prometheus-community/pgbouncer_exporter/releases/download/v0.7.0/pgbouncer_exporter-0.7.0.linux-amd64.tar.gz  
tar xvfz pgbouncer_exporter-0.7.0.linux-amd64.tar.gz  
cd pgbouncer_exporter-0.7.0.linux-amd64  

# Lancer l'exporter
./pgbouncer_exporter \
  --pgBouncer.connectionString="postgres://stats_user:password@localhost:6432/pgbouncer?sslmode=disable"

Dashboards Grafana recommandés :

PgBouncer Dashboard (ID: 12421)

Partie 6 : Alternatives et solutions complémentaires

1. pgpool-II

Caractéristiques :

Plus de fonctionnalités que PgBouncer (load balancing, replication)
Plus complexe à configurer
Plus de overhead

Cas d'usage : Quand vous avez besoin de load balancing automatique + pooling.

# Installation
sudo apt install pgpool2

# Configuration de base
/etc/pgpool2/pgpool.conf

2. Connection Pooling intégré aux applications

Python (psycopg2)

from psycopg2 import pool

# Créer un pool de connexions
connection_pool = pool.SimpleConnectionPool(
    minconn=5,      # Minimum 5 connexions
    maxconn=20,     # Maximum 20 connexions
    host="localhost",
    database="mydb",
    user="app_user",
    password="secret"
)

# Utiliser une connexion
conn = connection_pool.getconn()  
try:  
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    results = cursor.fetchall()
finally:
    # IMPORTANT : Rendre la connexion au pool
    connection_pool.putconn(conn)

Node.js (node-postgres)

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'mydb',
  user: 'app_user',
  password: 'secret',
  max: 20,                  // Maximum 20 connexions
  idleTimeoutMillis: 30000, // Timeout connexion idle
  connectionTimeoutMillis: 2000,
});

// Utiliser le pool
pool.query('SELECT * FROM users', (err, result) => {
  console.log(result.rows);
});

Java (HikariCP)

HikariConfig config = new HikariConfig();  
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");  
config.setUsername("app_user");  
config.setPassword("secret");  
config.setMaximumPoolSize(20);  
config.setMinimumIdle(5);  

HikariDataSource ds = new HikariDataSource(config);

// Utiliser
Connection conn = ds.getConnection();  
try {  
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM users");
    // ...
} finally {
    conn.close(); // Rend au pool
}

Avantage : Pas de composant externe.
Inconvénient : Chaque instance d'application a son propre pool.

3. AWS RDS Proxy (Cloud)

Pour PostgreSQL sur AWS RDS :

Application → RDS Proxy → RDS PostgreSQL
   (10000)      (100)       (100 connexions)

Avantages :

Géré par AWS (pas de maintenance)
Connection pooling automatique
Failover automatique
Intégration IAM

Configuration :

# Via AWS CLI
aws rds create-db-proxy \
  --db-proxy-name mydb-proxy \
  --engine-family POSTGRESQL \
  --auth {...} \
  --role-arn {...} \
  --vpc-subnet-ids subnet-xxx subnet-yyy

4. Azure Database for PostgreSQL - Connection Pooling

Azure propose également un pooling intégré.

5. Supabase / Neon (Serverless PostgreSQL)

Ces services cloud incluent du connection pooling natif optimisé pour serverless.

Partie 7 : Bonnes pratiques

1. Fermer les connexions correctement

# ❌ MAUVAIS : Connection leak
def get_users():
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    return cursor.fetchall()
    # Connexion jamais fermée !

# ✅ BON : Utiliser context manager
def get_users():
    with psycopg2.connect(...) as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM users")
            return cursor.fetchall()
    # Connexion fermée automatiquement

2. Configurer les timeouts

# Application
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    connect_timeout=5,      # Timeout connexion
    options="-c statement_timeout=30000"  # Timeout requête (30s)
)

-- PostgreSQL
-- Dans postgresql.conf
idle_in_transaction_session_timeout = 60000  # 60 secondes  
statement_timeout = 30000  # 30 secondes

# PgBouncer
[pgbouncer]
query_timeout = 30  
query_wait_timeout = 5  
idle_transaction_timeout = 60

3. Éviter les connexions long-lived

# ❌ MAUVAIS : Connexion gardée toute la durée de vie de l'app
class MyApp:
    def __init__(self):
        self.conn = psycopg2.connect(...)  # Garde la connexion

    def do_something(self):
        cursor = self.conn.cursor()
        # ...

# ✅ BON : Connexion par requête (avec pool)
class MyApp:
    def __init__(self):
        self.pool = create_pool(...)

    def do_something(self):
        with self.pool.get_connection() as conn:
            cursor = conn.cursor()
            # ...
        # Connexion rendue au pool

4. Dimensionner en fonction de la charge

Règle empirique :

Connexions PostgreSQL = Nombre de cœurs CPU × 2 à 4

Exemples :
- Serveur 4 cœurs : 10-15 connexions
- Serveur 8 cœurs : 20-30 connexions
- Serveur 16 cœurs : 40-60 connexions

Validation : Surveiller CPU et load average

Si CPU < 70% : Peut augmenter connexions
Si CPU > 90% : Trop de connexions, réduire

5. Séparer les workloads

# PgBouncer : Différents pools pour différents usages

[databases]
# API web : Transaction pooling, petit pool
api_db = host=localhost dbname=mydb pool_size=25 pool_mode=transaction

# Background jobs : Session pooling, pool moyen
jobs_db = host=localhost dbname=mydb pool_size=15 pool_mode=session

# Analytics : Session pooling, grand pool
analytics_db = host=replica dbname=mydb pool_size=50 pool_mode=session

6. Tester sous charge

# Utiliser pgbench pour simuler charge
pgbench -h localhost -p 6432 -U app_user -c 100 -j 4 -T 300 mydb

# -c 100 : 100 clients simultanés
# -j 4 : 4 threads
# -T 300 : Test de 5 minutes

Partie 8 : Troubleshooting

Problème 1 : "too many clients already"

Symptôme :

FATAL: sorry, too many clients already

Diagnostic :

-- Voir les connexions actuelles
SELECT count(*), state  
FROM pg_stat_activity  
GROUP BY state;  

SHOW max_connections;

Solutions :

Immédiat : Tuer les connexions idle

SELECT pg_terminate_backend(pid)  
FROM pg_stat_activity  
WHERE state = 'idle'  
  AND now() - state_change > interval '10 minutes';

Court terme : Augmenter max_connections

-- Dans postgresql.conf
max_connections = 200  # Au lieu de 100

-- Redémarrer
sudo systemctl restart postgresql

Long terme : Implémenter PgBouncer

# Permet 10000 clients avec seulement 25 connexions PostgreSQL
max_client_conn = 10000  
default_pool_size = 25

Problème 2 : Clients en attente (cl_waiting > 0)

Symptôme dans PgBouncer :

SHOW POOLS;
# cl_waiting > 0 constamment

Causes :

Pool trop petit
Requêtes lentes monopolisent connexions
Transactions non fermées

Diagnostic :

-- Voir les requêtes actives longues
SELECT pid, now() - query_start as duration, query  
FROM pg_stat_activity  
WHERE state = 'active'  
ORDER BY duration DESC  
LIMIT 10;

Solutions :

Augmenter pool_size

default_pool_size = 50  # Au lieu de 25

Optimiser les requêtes lentes (voir chapitre Query Tuning)
Configurer timeouts

query_timeout = 30  
query_wait_timeout = 5

Problème 3 : Connection leaks

Symptôme : Nombre de connexions augmente continuellement.

Diagnostic :

# Script de test pour détecter leaks
import psycopg2  
import time  

for i in range(100):
    conn = psycopg2.connect(...)
    # Oublier de fermer : conn.close()
    time.sleep(1)

# Résultat : 100 connexions ouvertes !

Solution : Toujours utiliser context managers ou try/finally

# ✅ BON
try:
    conn = psycopg2.connect(...)
    # Utiliser conn
finally:
    conn.close()

# Ou mieux : context manager
with psycopg2.connect(...) as conn:
    # Utiliser conn
    pass
# Fermeture automatique

Problème 4 : PgBouncer ne démarre pas

Erreurs communes :

# Erreur : permission denied
# Solution : Vérifier les permissions
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/*  
sudo chmod 600 /etc/pgbouncer/userlist.txt  

# Erreur : could not connect to server
# Solution : Vérifier pg_hba.conf
# Ajouter dans PostgreSQL /etc/postgresql/*/main/pg_hba.conf :
host    all    all    127.0.0.1/32    scram-sha-256

# Recharger PostgreSQL
sudo systemctl reload postgresql

Partie 9 : Monitoring et alertes

Métriques critiques à surveiller

1. Nombre de connexions

-- Alerte si > 80% de max_connections
SELECT
    current_setting('max_connections')::int as max,
    count(*) as current,
    round(100.0 * count(*) / current_setting('max_connections')::int, 2) as percent
FROM pg_stat_activity;

Seuil d'alerte : > 80%

2. Connexions idle in transaction

-- Alerte si > 10 connexions idle in transaction
SELECT count(*)  
FROM pg_stat_activity  
WHERE state = 'idle in transaction';

Seuil d'alerte : > 10 connexions

3. PgBouncer : Clients en attente

-- Dans console PgBouncer
SHOW POOLS;

-- Alerte si cl_waiting > 0 pendant > 1 minute

Dashboard Grafana

Requêtes Prometheus :

# Nombre de connexions PostgreSQL
pg_stat_activity_count

# Connexions par état
pg_stat_activity_count{state="active"}  
pg_stat_activity_count{state="idle"}  
pg_stat_activity_count{state="idle in transaction"}  

# PgBouncer : Clients en attente
pgbouncer_pools_cl_waiting

# PgBouncer : Connexions serveur
pgbouncer_pools_sv_active  
pgbouncer_pools_sv_idle

Alertes Prometheus

groups:
  - name: postgresql_connections
    rules:
      - alert: PostgreSQLTooManyConnections
        expr: pg_stat_activity_count / pg_settings_max_connections > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL connections above 80%"

      - alert: PostgreSQLIdleInTransaction
        expr: pg_stat_activity_count{state="idle in transaction"} > 10
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Too many idle in transaction connections"

      - alert: PgBouncerClientsWaiting
        expr: pgbouncer_pools_cl_waiting > 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PgBouncer has clients waiting for connections"

Partie 10 : Checklist de déploiement

Avant la mise en production

Après la mise en production

Surveiller métriques pendant 48h
Vérifier absence de cl_waiting
Vérifier latence acceptable
Ajuster pool_size si nécessaire
Vérifier absence de connection leaks
Documenter toute anomalie

Configuration de référence

PostgreSQL (postgresql.conf)

# CONNEXIONS
max_connections = 100           # Réduit (PgBouncer gère)  
superuser_reserved_connections = 5  

# TIMEOUTS
idle_in_transaction_session_timeout = 60000  # 60 secondes  
statement_timeout = 30000                    # 30 secondes  

# Pas besoin de plus avec PgBouncer

PgBouncer (pgbouncer.ini)

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
# Connection pooling
listen_addr = 0.0.0.0  
listen_port = 6432  
pool_mode = transaction  
auth_type = scram-sha-256  
auth_file = /etc/pgbouncer/userlist.txt  

# Pool sizing (adapter selon CPU)
max_client_conn = 10000  
default_pool_size = 25      # Pour serveur 8 cœurs  
min_pool_size = 5  
reserve_pool_size = 5  
reserve_pool_timeout = 3  

# Timeouts
query_timeout = 30  
query_wait_timeout = 5  
client_idle_timeout = 600   # 10 minutes  
server_idle_timeout = 600  
idle_transaction_timeout = 60  

# Performance
max_db_connections = 100  
server_check_delay = 30  
server_check_query = SELECT 1  

# Logging
log_connections = 1  
log_disconnections = 1  
log_pooler_errors = 1  
admin_users = postgres  
stats_users = postgres, monitoring

Application (Python exemple)

# config.py
DATABASE_CONFIG = {
    'host': 'localhost',
    'port': 6432,  # PgBouncer, pas PostgreSQL direct !
    'database': 'mydb',
    'user': 'app_user',
    'password': 'secret',
    'connect_timeout': 5,
    'options': '-c statement_timeout=30000'
}

# app.py
from psycopg2 import pool

# Créer un pool applicatif (optionnel avec PgBouncer)
app_pool = pool.SimpleConnectionPool(5, 20, **DATABASE_CONFIG)

def get_users():
    conn = app_pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM users")
            return cur.fetchall()
    finally:
        app_pool.putconn(conn)

Résumé des points clés

✅ Connection storms : Saturation par trop de connexions simultanées

✅ PostgreSQL = process-per-connection : Chaque connexion coûte ~10 MB RAM + CPU

✅ Limite pratique : ~200-300 connexions sans pooling

✅ Connection pooling : Partage intelligent des connexions

✅ PgBouncer : Solution de référence, léger et performant

✅ Transaction pooling : Mode recommandé pour web apps (quelques limitations)

✅ Dimensionnement : pool_size = CPU × 2 à 4

✅ Monitoring : Surveiller cl_waiting, connections count, idle in transaction

✅ Timeouts : Configuration critique pour éviter connexions zombies

✅ Tests de charge : Valider avant production avec pgbench

Conclusion

Le connection pooling est essentiel pour toute application PostgreSQL en production. Sans pooling :

Vous êtes limité à ~200-300 utilisateurs simultanés
Chaque connexion consomme des ressources précieuses
Les connection storms peuvent faire tomber votre base

Avec PgBouncer :

Vous pouvez gérer 10,000+ utilisateurs avec 25 connexions PostgreSQL
Overhead minimal (~0.1ms de latence)
Configuration simple et maintenance facile
Résilience accrue face aux pics de charge

L'investissement temps pour configurer PgBouncer (1-2 heures) est largement compensé par :

Économies de ressources (RAM, CPU)
Meilleure scalabilité
Stabilité accrue en production
Coûts d'infrastructure réduits

PgBouncer n'est pas une optimisation optionnelle : c'est un composant standard de toute infrastructure PostgreSQL professionnelle.

Fin du chapitre 19.4 - Troubleshooting et Crises

⏭️ Disaster Recovery (DR)

FilesExpand file tree

04.6-connection-storms-pooling.md

Latest commit

History

04.6-connection-storms-pooling.md

File metadata and controls

19.4.6. Connection storms et pooling

Introduction : Quand les connexions deviennent un problème

Partie 1 : Comprendre le problème des connexions

Comment PostgreSQL gère les connexions

Le coût d'une connexion

La limite théorique

Qu'est-ce qu'un connection storm ?

Symptômes d'un connection storm

1. Erreurs de connexion

2. Performances dégradées

3. Système surchargé

Partie 2 : Détecter et diagnostiquer

Monitoring des connexions

Vue d'ensemble des connexions

Connexions par utilisateur et application

Identifier les connexions problématiques

Visualisation graphique

Alertes proactives

Partie 3 : Connection Pooling - La solution

Qu'est-ce que le connection pooling ?

Avantages du pooling

Types de pooling

1. Session Pooling (par session)

2. Transaction Pooling (par transaction)

3. Statement Pooling (par requête)

Partie 4 : PgBouncer - Le pooler de référence

Qu'est-ce que PgBouncer ?

Installation de PgBouncer

Configuration de base

Fichier pgbouncer.ini

Fichier userlist.txt (authentification)

Démarrer PgBouncer

Se connecter via PgBouncer

Console d'administration PgBouncer

SHOW POOLS (exemple de résultat)

SHOW STATS (exemple de résultat)

Partie 5 : Configuration optimale

Dimensionner le pool

Configuration par base de données

Transaction pooling : Limitations et solutions

Problème : Prepared Statements

Problème : Temporary Tables

Problème : Session variables

Monitoring PgBouncer

Métriques à surveiller

Exporter vers Prometheus

Partie 6 : Alternatives et solutions complémentaires

1. pgpool-II

2. Connection Pooling intégré aux applications

Python (psycopg2)

Node.js (node-postgres)

Java (HikariCP)

3. AWS RDS Proxy (Cloud)

4. Azure Database for PostgreSQL - Connection Pooling

5. Supabase / Neon (Serverless PostgreSQL)

Partie 7 : Bonnes pratiques

1. Fermer les connexions correctement

2. Configurer les timeouts

3. Éviter les connexions long-lived

4. Dimensionner en fonction de la charge

5. Séparer les workloads

6. Tester sous charge

Partie 8 : Troubleshooting

Problème 1 : "too many clients already"

Problème 2 : Clients en attente (cl_waiting > 0)

Problème 3 : Connection leaks

Problème 4 : PgBouncer ne démarre pas

Partie 9 : Monitoring et alertes

Métriques critiques à surveiller

1. Nombre de connexions

2. Connexions idle in transaction

3. PgBouncer : Clients en attente

Dashboard Grafana

Alertes Prometheus