Connection pooling no PostgreSQL com PgBouncer: quando e por que configurar

O problema de conexões no PostgreSQL

Diferente de outros bancos, o PostgreSQL cria um processo filho para cada conexão. Cada processo consome em torno de 5-10MB de RAM e tem overhead de CPU. Com uma aplicação que cresce horizontalmente, o número de conexões pode explodir rapidamente.

Exemplo:

10 instâncias da aplicação
Pool de 20 conexões por instância
Total: 200 conexões abertas simultaneamente

Com PostgreSQL rodando em uma instância de 2GB, 200 conexões já consomem mais de 1GB só com o overhead de conexão: antes de qualquer dado em memória.

O max_connections padrão do PostgreSQL é 100. Fácil estourar.

O que é o PgBouncer

PgBouncer é um connection pooler leve que fica entre a aplicação e o PostgreSQL:

App (instância 1) ──┐
App (instância 2) ──┤── PgBouncer ──→ PostgreSQL (20 conexões reais)
App (instância 3) ──┘
(30 conexões por app)

As aplicações abrem conexões com o PgBouncer. O PgBouncer mantém um pool menor de conexões reais com o PostgreSQL. 90 conexões de aplicação podem ser atendidas por 20 conexões reais.

Modos de pool

O PgBouncer tem três modos, com trade-offs importantes:

Session pooling (padrão)

pool_mode = session

A conexão do cliente é associada a uma conexão real pelo tempo da sessão inteira. Pouca reutilização: quase não resolve o problema de conexões.

Única vantagem: compatibilidade total com todos os recursos do PostgreSQL (prepared statements, SET, LISTEN/NOTIFY, etc.).

Transaction pooling (recomendado para a maioria)

pool_mode = transaction

A conexão real é emprestada apenas durante uma transação. Entre transações, fica disponível para outros clientes. Alta reutilização.

Limitações importantes:

Prepared statements pelo client não funcionam (use prepared_statements = 0 ou disable_prepared_statement na lib)
SET LOCAL e SET com efeito de sessão não persistem entre transações
LISTEN/NOTIFY não funciona
Advisory locks de sessão não funcionam

Statement pooling

pool_mode = statement

Pool por statement individual. Mais reutilização, mas transações multi-statement não funcionam. Use apenas para workloads muito específicas.

Configuração básica do PgBouncer

# pgbouncer.ini
 
[databases]
myapp = host=postgres-host port=5432 dbname=myapp
 
[pgbouncer]
listen_port = 5432
listen_addr = 0.0.0.0
 
pool_mode = transaction
max_client_conn = 1000   # conexões máximas de clientes
default_pool_size = 20   # conexões reais por banco/usuário
min_pool_size = 5        # mínimo de conexões ativas
reserve_pool_size = 5    # conexões extras em caso de pico
 
# Timeouts
server_idle_timeout = 600       # fecha conexões reais ociosas após 10min
client_idle_timeout = 0         # não fecha conexões de cliente por ociosidade
server_connect_timeout = 15     # timeout para conectar ao PostgreSQL
 
# Autenticação
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
 
# Logging
log_connections = 0    # não logar cada conexão (alto volume)
log_disconnections = 0
log_pooler_errors = 1

Configurando o pool na aplicação

Com PgBouncer em transaction mode, ajuste o pool da aplicação:

// knex config (Node.js)
const knex = Knex({
  client: "pg",
  connection: {
    host: process.env.PGBOUNCER_HOST,
    port: 5432,
    database: "myapp",
    user: "app",
    password: process.env.DB_PASSWORD,
    // Desativa prepared statements: incompatível com transaction mode
    statement_timeout: 30000,
  },
  pool: {
    min: 2,
    max: 10,              // por instância
    acquireTimeoutMillis: 30000,
    idleTimeoutMillis: 600000,
    // Prepared statements precisam ser desativados
    afterCreate: (conn: any, done: Function) => {
      conn.query("SET statement_timeout = 30000", (err: Error) => done(err, conn));
    },
  },
});

Para Prisma:

# .env
DATABASE_URL="postgresql://user:pass@pgbouncer:5432/myapp?pgbouncer=true&connection_limit=10"

O parâmetro pgbouncer=true desativa prepared statements no cliente do Prisma.

Monitorando o PgBouncer

-- Conecte ao banco de admin do PgBouncer
-- psql -h pgbouncer-host -p 5432 -U pgbouncer pgbouncer
 
-- Pools ativos
SHOW POOLS;
-- database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | maxwait
 
-- Clientes conectados
SHOW CLIENTS;
 
-- Conexões reais com o PostgreSQL
SHOW SERVERS;
 
-- Estatísticas de uso
SHOW STATS;

Alertas que valem configurar:

cl_waiting > 0 por mais de 5s: pool esgotado, considere aumentar default_pool_size
maxwait > 1000ms: clientes esperando muito por conexão
sv_idle muito alto: pool superdimensionado, reduza

PgBouncer no Kubernetes

# pgbouncer-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
spec:
  replicas: 2  # HA: dois bouncer
  template:
    spec:
      containers:
        - name: pgbouncer
          image: pgbouncer/pgbouncer:1.22
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRESQL_HOST
              value: postgres-service
            - name: POOL_MODE
              value: transaction
            - name: MAX_CLIENT_CONN
              value: "1000"
            - name: DEFAULT_POOL_SIZE
              value: "25"

Com dois PgBouncers, configure o connection string das aplicações para um load balancer ou use ambos no pool:

DATABASE_URL="postgresql://user:pass@pgbouncer-svc:5432/myapp?pgbouncer=true"

Quando não usar PgBouncer

Long-running queries: se suas queries demoram minutos, o transaction pooling não oferece muita reutilização
LISTEN/NOTIFY: use connection dedicada fora do pool para isso
Prepared statements em nível de sessão: use session mode ou desative completamente
Poucos clientes: se você tem 2 instâncias com pool de 5, não precisa de PgBouncer

Conclusão

PgBouncer é essencial quando você escala horizontalmente e o número de conexões começa a pressionar o PostgreSQL. Transaction pooling é o modo correto para a maioria das aplicações web: com a ressalva de desativar prepared statements no cliente.

A configuração mais importante é default_pool_size: dimensione baseado no número de workers do PostgreSQL que você quer utilizar, não no número de clientes que você tem.