Configurando Pooling de Conexões PostgreSQL com PgBouncer para Aplicações de Alto Tráfego

Quando bancos de dados PostgreSQL enfrentam altos volumes de conexão, o desempenho pode degradar rapidamente. Cada conexão de cliente mapeia para um processo backend do PostgreSQL, então uma aplicação web movimentada pode gastar muita memória e CPU apenas mantendo sessões abertas. O pool de conexões PgBouncer reduz essa pressão permitindo que muitas conexões de cliente reutilizem um conjunto menor de conexões de servidor.

Por que o Pooling de Conexões é Importante

O Problema da Conexão

Sobrecarga de recursos: Cada conexão PostgreSQL tem um processo backend e sobrecarga de memória.
Limites de conexão: max_connections é finito, e aumentá-lo demais pode tornar o banco de dados menos estável em vez de mais rápido.
Custo de inicialização: Criar novas conexões de banco de dados adiciona latência.
Troca de contexto: Muitos processos backend ativos podem desperdiçar CPU.

Benefícios do PgBouncer

Permite que muitos clientes de aplicação compartilhem menos conexões de servidor PostgreSQL.
Enfileira clientes quando o pool está ocupado em vez de sobrecarregar o banco de dados.
Suporta modos de pooling de sessão, transação e declaração.
Fornece comandos operacionais como SHOW POOLS, SHOW CLIENTS e RELOAD.

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

Locais Comuns de Arquivos

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

Configuração do Arquivo de Configuração

Configuração Básica do pgbouncer.ini

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

[pgbouncer]
; Modo de pooling 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

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Desempenho
max_prepared_statements = 0

Os padrões do pacote variam por distribuição, então verifique o arquivo de serviço instalado e o caminho de configuração após a instalação. Em muitos pacotes Linux, /etc/pgbouncer/pgbouncer.ini é o arquivo principal.

Entendendo os Modos de Pooling

1. Pooling de Sessão (pool_mode = session)

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

pool_mode = session

2. Pooling de Transação (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, quando sua aplicação não depende do estado da sessão

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

O pooling de transação é um padrão forte para aplicações web de requisição/resposta, mas pode quebrar suposições em torno de recursos no nível da sessão. Tenha cuidado com tabelas temporárias, variáveis de sessão, locks consultivos mantidos fora de uma transação, LISTEN/NOTIFY e declarações preparadas no nível do driver, a menos que você os tenha testado com sua versão e configurações do PgBouncer.

3. Pooling de Declaração (pool_mode = statement)

Comportamento: Conexão retornada após cada declaração
Caso de Uso: Consultas simples somente leitura sem transações
Eficiência: Máxima (mas muito restritiva)

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

Configuração de Autenticação

Criando 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"

Para senhas MD5 no estilo PostgreSQL, o valor é md5 mais o hash MD5 de senha + nome_de_usuário. Não cole hashes falsos em produção; gere entradas a partir de seus nomes de usuário e senhas reais, ou use um método de autenticação mais seguro suportado pelo seu ambiente.

Usando auth_query do PostgreSQL (Avançado)

O PgBouncer pode consultar o PostgreSQL pelas credenciais do usuário quando um usuário não é encontrado em auth_file, mas isso requer um auth_user no qual o PgBouncer possa fazer login. Um exemplo mínimo se parece com isso:

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

Limite os privilégios do usuário de autenticação e siga as orientações da sua versão do PostgreSQL. Muitas equipes usam uma função SECURITY DEFINER em vez de conceder acesso direto aos dados de senha do catálogo.

Configuração Ideal para Alto Tráfego

Dimensionando o Pool de Conexões

Não existe uma fórmula universal para o tamanho do pool. Comece com um valor conservador baseado no número de consultas que seu banco de dados pode executar ativamente bem, depois ajuste com base em métricas reais.

Para uma aplicação web típica, você pode começar aqui e ajustar:

default_pool_size = 25
reserve_pool_size = 5
max_client_conn = 1000

Monitore o CPU do PostgreSQL, latência de consulta, esperas de lock e a contagem cl_waiting do PgBouncer. Se os clientes esperam enquanto o banco de dados está ocioso, o pool pode ser muito pequeno. Se o banco de dados está saturado, aumentar o pool pode piorar as coisas.

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

; Logging
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

max_prepared_statements = 0 desabilita o rastreamento de declarações preparadas do PgBouncer. Versões recentes do PgBouncer podem suportar declarações preparadas no nível do protocolo no pooling de transação quando isso é configurado acima de zero, mas você deve testar seu driver e carga de trabalho antes de habilitá-lo.

String de Conexão da Aplicação

Antes do PgBouncer

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

Depois do PgBouncer

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

Monitoramento e Gerenciamento

Comandos do Console de Administração

Conecte-se ao console de administração 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"

Isso pode significar que o PgBouncer está rejeitando conexões de cliente ou o PostgreSQL está rejeitando conexões de servidor. Verifique onde o erro aparece.

Possíveis alterações no lado do PgBouncer:

max_client_conn = 5000
default_pool_size = 50

Antes de aumentar ambos, confirme se o limite de descritores de arquivo do seu SO e o max_connections do PostgreSQL podem suportar os novos totais. O próprio PgBouncer também precisa de descritores de arquivo suficientes para sockets de cliente e servidor.

Problema 2: Alta Contagem de cl_waiting

Soluções:

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

Problema 3: Erros de Declaração Preparada

Se sua aplicação ou driver usa declarações preparadas e você está no modo de pooling de transação, você pode ver erros quando uma declaração posterior cai em uma conexão de servidor diferente. As opções incluem:

Desabilitar declarações preparadas no lado do driver.
Usar pooling de sessão para essa carga de trabalho.
Testar o suporte a declarações preparadas do PgBouncer com um valor positivo de max_prepared_statements em uma versão recente do PgBouncer.

Configuração conservadora:

max_prepared_statements = 0

Exemplo Prático de Implantação

Imagine que seus servidores de aplicação podem abrir centenas de requisições HTTP simultâneas, mas o banco de dados funciona melhor com algumas dezenas de consultas ativas. Aponte a aplicação para o PgBouncer na porta 6432, defina max_client_conn alto o suficiente para picos de clientes e mantenha default_pool_size próximo ao número de conexões de banco de dados que você realmente deseja ativas para esse par banco de dados/usuário.

Em seguida, valide com:

SHOW POOLS;
SHOW STATS;

Se cl_waiting permanecer acima de zero durante o tráfego normal, investigue consultas lentas antes de simplesmente aumentar o tamanho do pool. O PgBouncer protege o PostgreSQL de tempestades de conexão, mas não torna SQL lento barato.

Conclusão Prática

Comece com pooling de transação para cargas de trabalho web sem estado, mantenha os tamanhos dos pools intencionalmente pequenos e ajuste com base nas métricas do PgBouncer e PostgreSQL. Se sua aplicação depende do comportamento da sessão ou de declarações preparadas, teste esses caminhos antes de colocar o PgBouncer na frente do tráfego de produção.