Configurando o Pool de Conexões do PostgreSQL com PgBouncer para Aplicações de Alto Tráfego

Introdução

Quando bancos de dados PostgreSQL enfrentam altos volumes de conexão, o desempenho pode cair rapidamente. Cada conexão com o banco de dados consome recursos do sistema, e o PostgreSQL possui limites práticos para conexões simultâneas. O PgBouncer, um pooler de conexões leve, resolve esse problema mantendo um pool de conexões de banco de dados e distribuindo-as de forma eficiente para as aplicações clientes.

Por que o Pool de Conexões é Importante

O Problema das Conexões

Sobrecarga de Recursos: Cada processo de backend do PostgreSQL consome 5-10MB de memória
Limites de Conexão: O max_connections padrão é tipicamente 100-200
Custo de Inicialização: Criar novas conexões leva de 1-5ms cada
Troca de Contexto (Context Switching): Muitos processos causam lentidão na CPU (CPU thrashing)

Benefícios do PgBouncer

Reduz a contagem de conexões com o banco de dados em 10-100x
Permite milhares de conexões de clientes com sobrecarga mínima
Fornece enfileiramento de conexões durante picos de carga
Suporta múltiplos modos de pool para diferentes casos de uso

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

Instalando o PgBouncer

No Ubuntu/Debian:

sudo apt update
sudo apt install pgbouncer

No CentOS/RHEL:

sudo yum install pgbouncer

No macOS:

brew install pgbouncer

Estrutura de Diretórios

/etc/pgbouncer/
  ├── pgbouncer.ini        # Configuração principal
  └── userlist.txt         # Credenciais de autenticação

Configuração do Arquivo pgbouncer.ini

Configuração Básica

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

[pgbouncer]
; Modo de pool de conexões
pool_mode = transaction

; Conexões máximas
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; Rede
listen_addr = 0.0.0.0
listen_port = 6432

; Autenticação
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Logs
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Desempenho
max_prepared_statements = 0

Entendendo os Modos de Pool

1. Session Pooling (pool_mode = session)

Comportamento: Conexão atribuída ao cliente durante toda a sessão
Caso de uso: Aplicações que usam tabelas temporárias, prepared statements
Eficiência: Baixa (proporção de conexão 1:1)

pool_mode = session

2. Transaction Pooling (pool_mode = transaction) - Recomendado

Comportamento: Conexão retornada ao pool após cada transação
Caso de uso: A maioria das aplicações web com transações curtas
Eficiência: Alta (redução de 10-100x)

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

3. Statement Pooling (pool_mode = statement)

Comportamento: Conexão retornada após cada instrução (statement)
Caso de uso: Consultas simples de leitura sem transações
Eficiência: Máxima (porém muito restritiva)

pool_mode = statement
; Use com cautela - quebra transações de múltiplas instruções

Configuração de Autenticação

Criando o userlist.txt

O PgBouncer requer um arquivo de autenticação separado. Gere o hash MD5 e adicione ao userlist.txt.

Exemplo de userlist.txt:

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

Usando auth_query do PostgreSQL (Avançado)

Consulte o PostgreSQL diretamente para autenticação:

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

Configuração Ideal para Alto Tráfego

Dimensionando o Pool de Conexões

Fórmula para dimensionamento:

default_pool_size = (num_cores × 2) + effective_spindle_count

Para um servidor de 4 núcleos com SSD:

default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000

Configuração Completa de Produção

[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

; Limites de conexão
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

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

; Segurança
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

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

; Desempenho
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

String de Conexão da Aplicação

Antes do PgBouncer

# Conexão direta com PostgreSQL
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

Após o PgBouncer

# Conexão através do PgBouncer
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

Monitoramento e Gerenciamento

Comandos do Console Administrativo

Conecte-se ao console administrativo do PgBouncer:

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Comandos essenciais:

-- Mostrar estatísticas do pool
SHOW POOLS;

-- Mostrar conexões ativas
SHOW CLIENTS;
SHOW SERVERS;

-- Mostrar configuração
SHOW CONFIG;

-- Recarregar configuração
RELOAD;

Solução de Problemas Comuns

Problema 1: "no more connections allowed"

Solução:

max_client_conn = 5000
default_pool_size = 50

Problema 2: Contagem alta de cl_waiting

Soluções:

Aumentar o tamanho do pool
Otimizar consultas lentas
Adicionar pool de reserva

Problema 3: Erros de Prepared Statement

Solução:

max_prepared_statements = 0

Exemplos de Impacto no Desempenho

Antes do PgBouncer

500 requisições simultâneas → 500 conexões PostgreSQL
Carga do banco: 95% CPU, 8GB RAM
Tempo de resposta: 250ms (média)

Após o PgBouncer

500 requisições simultâneas → 25 conexões PostgreSQL
Carga do banco: 35% CPU, 1GB RAM
Tempo de resposta: 80ms (média)
Resultado: 3x mais rápido, 70% menos uso de recursos

Conclusão

O PgBouncer é essencial para escalar aplicações PostgreSQL. Ele reduz a sobrecarga de conexão em mais de 90%, suporta de 10 a 100 vezes mais clientes e melhora drasticamente os tempos de resposta. Comece com o modo de pool de transação e ajuste com base no monitoramento.