Configurazione del Pooling delle Connessioni PostgreSQL con PgBouncer per Applicazioni ad Alto Traffico

Introduzione

Quando i database PostgreSQL affrontano elevate volumetrie di connessioni, le prestazioni possono degradare rapidamente. Ogni connessione al database consuma risorse di sistema e PostgreSQL ha limiti pratici sulle connessioni concorrenti. PgBouncer, un pooler di connessioni leggero, risolve questo problema mantenendo un pool di connessioni al database e distribuendole in modo efficiente alle applicazioni client.

Perché il Pooling delle Connessioni è Importante

Il Problema delle Connessioni

Overhead delle Risorse: Ogni processo backend PostgreSQL consuma 5-10MB di memoria
Limiti delle Connessioni: Il valore predefinito di max_connections è tipicamente 100-200
Costo di Avvio: La creazione di nuove connessioni richiede 1-5ms ciascuna
Context Switching: Troppi processi causano il thrashing della CPU

Vantaggi di PgBouncer

Riduce il conteggio delle connessioni al database di 10-100 volte
Abilita migliaia di connessioni client con overhead minimo
Fornisce la messa in coda delle connessioni durante i picchi di carico
Supporta molteplici modalità di pooling per diversi casi d'uso

Installazione e Configurazione di Base

Installazione di PgBouncer

Su Ubuntu/Debian:

sudo apt update
sudo apt install pgbouncer

Su CentOS/RHEL:

sudo yum install pgbouncer

Su macOS:

brew install pgbouncer

Struttura della Directory

/etc/pgbouncer/
  ├── pgbouncer.ini        # Configurazione principale
  └── userlist.txt         # Credenziali di autenticazione

Configurazione del File

Configurazione Base di pgbouncer.ini

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

[pgbouncer]
; Modalità di pooling delle connessioni
pool_mode = transaction

; Connessioni massime
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; Rete
listen_addr = 0.0.0.0
listen_port = 6432

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

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Prestazioni
max_prepared_statements = 0

Comprensione delle Modalità di Pooling

1. Pooling di Sessione (pool_mode = session)

Comportamento: La connessione viene assegnata al client per l'intera sessione
Caso d'uso: Applicazioni che utilizzano tabelle temporanee, istruzioni preparate
Efficienza: Bassa (rapporto di connessione 1:1)

pool_mode = session

2. Pooling di Transazione (pool_mode = transaction) - Consigliata

Comportamento: La connessione viene restituita al pool dopo ogni transazione
Caso d'uso: La maggior parte delle applicazioni web con transazioni brevi
Efficienza: Alta (riduzione di 10-100 volte)

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

3. Pooling di Istruzione (pool_mode = statement)

Comportamento: La connessione viene restituita dopo ogni istruzione
Caso d'uso: Query di sola lettura semplici senza transazioni
Efficienza: Massima (ma molto restrittiva)

pool_mode = statement
; Usare con cautela - rompe le transazioni multi-istruzione

Configurazione dell'Autenticazione

Creazione di userlist.txt

PgBouncer richiede un file di autenticazione separato. Genera l'hash MD5 e aggiungilo a userlist.txt.

Esempio di userlist.txt:

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

Utilizzo di auth_query di PostgreSQL (Avanzato)

Interroga PostgreSQL direttamente per l'autenticazione:

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

Configurazione Ottimale per Alto Traffico

Dimensionamento del Pool di Connessioni

Formula per il dimensionamento del pool:

default_pool_size = (num_cores × 2) + effective_spindle_count

Per un server a 4 core con SSD:

default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000

Configurazione di Produzione Completa

[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

; Limiti delle connessioni
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

; Rete
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192

; Sicurezza
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

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

; Prestazioni
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

Stringa di Connessione dell'Applicazione

Prima di PgBouncer

# Connessione PostgreSQL diretta
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

Dopo di PgBouncer

# Connessione attraverso PgBouncer
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

Monitoraggio e Gestione

Comandi della Console Admin

Connetti alla console admin di PgBouncer:

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Comandi essenziali:

-- Mostra le statistiche del pool
SHOW POOLS;

-- Mostra le connessioni attive
SHOW CLIENTS;
SHOW SERVERS;

-- Mostra la configurazione
SHOW CONFIG;

-- Ricarica la configurazione
RELOAD;

Risoluzione dei Problemi Comuni

Problema 1: "no more connections allowed"

Soluzione:

max_client_conn = 5000
default_pool_size = 50

Problema 2: Conteggio cl_waiting Elevato

Soluzioni:

Aumenta la dimensione del pool
Ottimizza le query lente
Aggiungi un pool di riserva

Problema 3: Errori delle Istruzioni Preparate

Soluzione:

max_prepared_statements = 0

Esempi dell'Impatto sulle Prestazioni

Prima di PgBouncer

500 richieste concorrenti → 500 connessioni PostgreSQL
Carico del database: 95% CPU, 8GB RAM
Tempo di risposta: 250ms medio

Dopo di PgBouncer

500 richieste concorrenti → 25 connessioni PostgreSQL
Carico del database: 35% CPU, 1GB RAM
Tempo di risposta: 80ms medio
Risultato: 3 volte più veloce, 70% in meno di utilizzo delle risorse

Conclusione

PgBouncer è essenziale per scalare le applicazioni PostgreSQL. Riduce l'overhead delle connessioni del 90%+, supporta 10-100 volte più client e migliora drasticamente i tempi di risposta. Inizia con la modalità di pooling di transazione e regola in base al monitoraggio.