Aumentando a Escalabilidade do PostgreSQL: Implementando o Pooling de Conexões com PgBouncer

O PostgreSQL usa um processo de backend por conexão de cliente. Esse modelo é confiável, mas se torna caro quando um aplicativo web abre centenas ou milhares de conexões majoritariamente ociosas.

O PgBouncer fica entre seu aplicativo e o PostgreSQL, mantendo um pool menor de conexões de servidor e permitindo que muitos clientes as reutilizem. O benefício é uma menor sobrecarga de conexão e um uso de memória do banco de dados mais previsível.

O Gargalo: Sobrecarga de Conexão Nativa do PostgreSQL

O PostgreSQL utiliza um modelo dedicado de processo por conexão. Embora seja altamente estável e garanta isolamento, essa arquitetura introduz uma sobrecarga significativa sob estresse:

1. Consumo de Recursos: Cada nova conexão exige que o servidor crie um novo processo de backend, consumindo memória e CPU. Centenas ou milhares de conexões ociosas retêm RAM desnecessariamente.
2. Estabelecimento Lento: Estabelecer uma nova conexão envolve handshake de rede, autenticação e inicialização do processo, adicionando latência mensurável às requisições do aplicativo, especialmente aquelas que abrem e fecham conexões com frequência.
3. Limites de Escalabilidade: Essas demandas de recursos impõem um teto efetivo no número de conexões simultâneas que o servidor PostgreSQL pode lidar realisticamente antes que o desempenho entre em colapso.

Apresentando o PgBouncer: O Proxy Leve

O PgBouncer atua como um servidor proxy leve posicionado entre os aplicativos cliente e o servidor de banco de dados PostgreSQL. Sua função principal é manter um número fixo e persistente de conexões abertas com o backend PostgreSQL, agrupando e reutilizando essas conexões para requisições transitórias de clientes do aplicativo.

Essa abordagem oferece dois benefícios críticos:

1. Sobrecarga Reduzida: O servidor PostgreSQL vê apenas o pool fixo de conexões mantidas pelo PgBouncer, eliminando o custoso ciclo de criação de processo por conexão para requisições de clientes recebidas.
2. Aumento de Throughput: Ao reutilizar conexões estabelecidas, o PgBouncer minimiza o tempo de autenticação e inicialização da conexão, resultando em um throughput significativamente maior do aplicativo e menor latência.

Entendendo os Modos de Pooling do PgBouncer

A eficiência do PgBouncer depende fortemente do modo de pooling escolhido. O PgBouncer oferece três modos fundamentais, cada um adequado para diferentes arquiteturas de aplicativos e necessidades de concorrência.

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

O pooling de sessão é o modo padrão e mais seguro. Uma vez que um cliente se conecta, o PgBouncer dedica uma conexão de servidor do pool a esse cliente até que ele se desconecte. A conexão é retornada ao pool apenas quando o cliente encerra explicitamente sua sessão.

• Caso de Uso: Aplicativos que dependem fortemente de recursos específicos de sessão (ex.: declarações preparadas, tabelas temporárias, comandos SET para variáveis personalizadas).
• Prós: Mais seguro, totalmente compatível com todos os recursos do PostgreSQL.
• Contras: Menos eficiente em termos de pooling, pois as conexões são mantidas mesmo durante o tempo ocioso do cliente.

2. Pooling de Transação (pool_mode = transaction)

O pooling de transação é geralmente recomendado para aplicativos web de alto tráfego, particularmente aqueles que usam APIs sem estado. Uma conexão de servidor é dedicada a um cliente apenas durante a duração de uma única transação (BEGIN até COMMIT/ROLLBACK). Assim que a transação termina, a conexão é imediatamente retornada ao pool para ser reutilizada por outro cliente em espera.

• Caso de Uso: Transações curtas e frequentes comuns em sistemas OLTP e microsserviços.
• Prós: Utilização altamente eficiente dos recursos do servidor.
• Contras: Exige que os aplicativos gerenciem as transações com cuidado. Mudanças de estado no nível da sessão (ex.: SET extra_float_digits = 3) serão perdidas entre transações ou vazarão para outros clientes.

Aviso sobre Pooling de Transação

No pooling de transação, evite estado de sessão como tabelas temporárias, alterações de SET no nível da sessão, locks de sessão e declarações preparadas de longa duração. O PgBouncer redefine as conexões do servidor entre clientes, mas o pooling de transação ainda exige testes de compatibilidade com o aplicativo.

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

O pooling de declaração é o modo mais agressivo. Uma conexão de servidor é retornada ao pool após a execução de cada declaração individual. Este modo efetivamente impede o uso de transações com múltiplas declarações e é altamente restritivo.

• Caso de Uso: Cargas de trabalho altamente especializadas e somente leitura, onde transações são explicitamente proibidas ou desnecessárias.
• Prós: Maximiza a reutilização de conexões.
• Contras: Quebra todas as transações. Adequado apenas para ambientes onde é garantido que transações não serão usadas.

Configuração e Instalação Inicial do PgBouncer

1. Instalação

O PgBouncer geralmente está disponível nos repositórios de distribuição padrão:

# No Debian/Ubuntu
sudo apt update && sudo apt install pgbouncer

# No RHEL/CentOS
sudo dnf install pgbouncer

2. Arquivos de Configuração

O PgBouncer depende principalmente de dois arquivos de configuração, tipicamente localizados em /etc/pgbouncer/:

• pgbouncer.ini: Configuração principal, definindo bancos de dados, limites de pool e modos de operação.
• userlist.txt: Define os usuários e senhas que o PgBouncer usa para autenticar no servidor PostgreSQL.

3. Definindo Usuários (userlist.txt)

Por segurança, o PgBouncer não lê diretamente a tabela pg_authid do PostgreSQL. Você deve definir manualmente os usuários com os quais ele pode autenticar. Certifique-se de que este arquivo esteja seguro (ex.: pertencente ao usuário pgbouncer e com permissões restritas).

"app_user" "md5<md5-hash>"
"admin_user" "another_hash"

Senhas em texto puro são possíveis com algumas configurações de autenticação, mas prefira autenticação hash ou mais forte onde suas versões do PgBouncer e PostgreSQL suportarem. Para autenticação MD5 legada, o valor armazenado é md5 mais o hash MD5 da senha e nome de usuário, não apenas a senha.

4. Configurando pgbouncer.ini

O arquivo pgbouncer.ini define o comportamento do pooler. Abaixo está um exemplo adaptado para uma configuração comum de aplicativo web usando pooling de transação.

[databases]
# Definição da string de conexão do cliente:
# <nome do banco> = host=<ip_servidor_pg> port=<porta_pg> dbname=<nome_bd> user=<usuário_auth_pgbouncer>
myappdb = host=10.0.0.5 port=5432 dbname=productiondb user=pgbouncer_service

[pgbouncer]

; Configuração de Escuta
listen_addr = *
listen_port = 6432

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

; Modo de Pooling (Definir com base nas necessidades do aplicativo)
pool_mode = transaction
; DISCARD ALL é a query de reset padrão para pooling de sessão.
; No pooling de transação, teste com cuidado antes de confiar no estado da sessão.
server_reset_query = DISCARD ALL

; Limites e Tamanhos de Conexão
; Máximo de conexões de cliente para o PgBouncer
max_client_conn = 1000

; Máximo de conexões que o PgBouncer mantém abertas por banco de dados (tamanho do pool)
default_pool_size = 20

; Número máximo de conexões permitidas no pool no geral em todos os bancos de dados
max_db_connections = 100

; Quando o pool estiver esgotado, reservar este número de slots
reserve_pool_size = 5

; Logging e Administração
admin_users = postgres, admin_user
stats_users = postgres

Monitoramento e Administração

O PgBouncer expõe um pseudo-banco de dados chamado pgbouncer que permite aos administradores monitorar o status, estatísticas e conexões do pooler em tempo real. Você se conecta à porta de escuta do PgBouncer (ex.: 6432) usando um dos admin_users definidos.

psql -p 6432 -U admin_user pgbouncer

Comandos administrativos chave:

Comando	Descrição	Nota de Uso
SHOW STATS;	Exibe estatísticas de conexão (requisições, bytes, duração total).	Útil para análise de desempenho.
SHOW POOLS;	Mostra o estado dos pools para todos os bancos de dados configurados.	Monitore cl_active, sv_active, sv_idle.
SHOW CLIENTS;	Lista todas as conexões de clientes conectadas ao PgBouncer.
RELOAD;	Tenta recarregar a configuração sem interromper as conexões.
PAUSE;	Para de aceitar novas consultas, aguarda as transações atuais terminarem.	Usado antes de manutenção ou atualização do PgBouncer.

Dicas de Escalabilidade

1. Posicionamento: Instale o PgBouncer no mesmo servidor que seu aplicativo ou em uma máquina dedicada e altamente otimizada para rede, para minimizar a latência entre o aplicativo e o pooler.
2. Dimensionamento do Pool: O default_pool_size deve ser definido para um número razoável (geralmente 10-50), que é tipicamente muito menor que o número de conexões permitidas no próprio servidor PostgreSQL. Um tamanho de pool excessivo anula o propósito do pooling.
3. Limites de Cliente: Use max_client_conn para evitar que tempestades de conexão sobrecarreguem o próprio PgBouncer. Isso atua como um limitador robusto no front-end.

Conclusão

O PgBouncer ajuda mais quando seu aplicativo tem muitas conexões de curta duração ou ociosas. Escolha o modo de pooling menos agressivo que seu aplicativo possa tolerar, mantenha os pools do servidor PostgreSQL pequenos o suficiente para proteger o banco de dados e observe SHOW POOLS; para clientes em espera antes que os usuários sintam a lentidão.