Connection pooling: PgBouncer e configuração

1. Fundamentos do Connection Pooling

Em bancos PostgreSQL, cada nova conexão exige um handshake TCP completo, autenticação e fork de um processo filho pelo postmaster. Esse custo, embora aceitável para algumas conexões, torna-se proibitivo em aplicações web com centenas de requisições simultâneas. Uma conexão efêmera — aberta e fechada a cada operação — pode consumir de 2 a 5 segundos de overhead, inviabilizando tempos de resposta aceitáveis.

O connection pooling resolve esse gargalo mantendo um conjunto de conexões persistentes com o banco. Em vez de criar uma nova conexão para cada requisição, o pooler reutiliza conexões já estabelecidas. Isso reduz drasticamente o tempo de latência e o consumo de recursos no servidor PostgreSQL, que tem um limite rígido de conexões simultâneas (max_connections).

2. Arquitetura e Modos de Operação do PgBouncer

PgBouncer é um pooler de conexões leve, escrito em C, que opera em três modos principais:

Modo Session: Cada sessão cliente recebe uma conexão dedicada do pool. Ideal para aplicações que mantêm sessões longas com o banco, como ferramentas administrativas. O isolamento é total, mas o número de conexões pode crescer rapidamente.

Modo Transaction (recomendado): As conexões são compartilhadas entre transações. Quando uma transação termina (COMMIT/ROLLBACK), a conexão é liberada para outro cliente. Esse modo oferece o melhor equilíbrio entre desempenho e isolamento. Prepared statements precisam ser explicitamente desalocados (DEALLOCATE) ou redefinidos por sessão.

Modo Statement: A conexão é liberada após cada comando SQL individual. Não há garantia de atomicidade entre comandos, tornando-o inadequado para transações multi-comando. Útil apenas para consultas simples e independentes.

A escolha do modo impacta diretamente o comportamento de transações e prepared statements. Em modo transaction, por exemplo, uma transação aberta segura a conexão até seu fim, bloqueando outros clientes.

3. Instalação e Configuração Básica

A instalação via pacotes é a abordagem mais prática:

# Debian/Ubuntu
apt-get install pgbouncer

# RHEL/CentOS
yum install pgbouncer

O arquivo principal de configuração é pgbouncer.ini. Sua estrutura básica:

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

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 100

Parâmetros essenciais:
- listen_addr e listen_port: define onde o PgBouncer escuta (padrão: 6432)
- auth_type: método de autenticação (md5, scram-sha-256, trust)
- auth_file: arquivo com pares "username" "password"
- pool_mode: session, transaction ou statement
- default_pool_size: número de conexões mantidas por banco
- max_client_conn: limite máximo de clientes simultâneos

4. Gerenciamento de Pool Interno

O PgBouncer oferece controle fino sobre o pool:

[pgbouncer]
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3.0
server_idle_timeout = 300
server_lifetime = 3600
query_timeout = 30
  • pool_size: conexões máximas por banco no pool
  • min_pool_size: conexões mínimas mantidas aquecidas
  • reserve_pool_size: conexões extras quando o pool principal esgota
  • server_idle_timeout: tempo máximo de inatividade antes de fechar uma conexão
  • server_lifetime: tempo máximo de vida de uma conexão (evita acumular conexões velhas)
  • query_timeout: tempo máximo de execução de uma query

Para monitorar o pool, use comandos administrativos:

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

SHOW POOLS exibe o estado de cada pool: conexões ativas, ociosas, em espera (wait). SHOW STATS fornece métricas de hits e misses no pool.

5. Integração com Aplicações e Frameworks

A string de conexão deve apontar para o PgBouncer, não diretamente para o PostgreSQL:

# String de conexão padrão
postgresql://user:password@localhost:6432/mydb

Exemplo em Python com psycopg2:

import psycopg2
conn = psycopg2.connect(
    host="localhost",
    port=6432,
    dbname="mydb",
    user="appuser",
    password="secret"
)

Exemplo em Node.js com node-postgres:

const { Pool } = require('pg');
const pool = new Pool({
    host: 'localhost',
    port: 6432,
    database: 'mydb',
    user: 'appuser',
    password: 'secret',
    max: 10
});

Atenção com prepared statements em modo transaction: O PgBouncer não gerencia prepared statements entre transações. É necessário desalocá-los explicitamente:

-- Em modo transaction, após usar um prepared statement:
DEALLOCATE stmt_name;

6. Autenticação, TLS e Segurança

O arquivo userlist.txt contém os pares de credenciais:

"appuser" "supersecret"
"admin" "adminpass"

Para autenticação SCRAM-SHA-256, o formato é:

"appuser" "SCRAM-SHA-256$<iterations>:<salt>$<stored_key>:<server_key>"

Configuração de TLS:

[pgbouncer]
client_tls_sslmode = require
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_cert_file = /etc/pgbouncer/server.crt
server_tls_sslmode = require
server_tls_key_file = /etc/pgbouncer/server.key
server_tls_cert_file = /etc/pgbouncer/server.crt

Para limitar acesso por IP, use listen_addr combinado com regras de firewall (iptables, ufw).

7. Monitoramento e Troubleshooting

Os logs do PgBouncer são configurados em pgbouncer.ini:

logfile = /var/log/pgbouncer/pgbouncer.log
stats_period = 60
verbose = 3

Níveis de log: stats (métricas periódicas), debug (detalhado), notice (eventos normais).

Comandos administrativos via console (conecte-se como admin):

RELOAD;      -- Recarrega configuração
PAUSE;       -- Pausa o pool (drena conexões)
RESUME;      -- Retoma operação
SHUTDOWN;    -- Encerra o PgBouncer

Cenários comuns de troubleshooting:

  • Estouro de max_client_conn: clientes recebem erro "too many clients". Aumente o limite ou otimize o pool.
  • Vazamento de transações longas: uma transação não finalizada segura a conexão. Monitore com SHOW POOLS e veja conexões ativas.
  • Conexões esperando (wait): indica que o pool está cheio. Ajuste default_pool_size e reserve_pool_size.

8. Boas Práticas e Otimização

Dimensionamento do pool: Calcule default_pool_size como uma fração de max_connections do PostgreSQL. Por exemplo, se max_connections = 200, use default_pool_size = 50 para deixar margem para conexões diretas.

Combinação com Read Replicas: Crie pools separados para leitura e escrita:

[databases]
mydb_write = host=192.168.1.10 port=5432 dbname=mydb
mydb_read = host=192.168.1.11 port=5432 dbname=mydb

Estratégias de failover: Use DNS round-robin ou ferramentas como keepalived para apontar para múltiplas instâncias do PgBouncer. Configure health checks para detectar falhas.

Cache e conexões ociosas: Conexões aquecidas no pool mantêm o cache de buffer do PostgreSQL aquecido. Ajuste server_idle_timeout para equilibrar reciclagem e cache.

Monitoramento contínuo: Configure alertas para métricas como:
- wait > 0 por mais de 5 segundos
- pool_hits / pool_misses < 90%
- Conexões ativas próximas a default_pool_size

Referências