PgBouncer: connection pooling para PostgreSQL

# Instalar PgBouncer:
sudo apt install pgbouncer -y

# Verificar versão:
pgbouncer --version

# Arquivos de configuração:
# /etc/pgbouncer/pgbouncer.ini  — configuração principal
# /etc/pgbouncer/userlist.txt   — usuários e senhas

# pgbouncer.ini — configuração básica:
sudo tee /etc/pgbouncer/pgbouncer.ini << 'EOF'
[databases]
# Mapear banco "producao" para o PostgreSQL local:
producao = host=127.0.0.1 port=5432 dbname=producao

[pgbouncer]
listen_port = 5433          # porta do PgBouncer (diferente do Postgres)
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

# Modo de pooling:
pool_mode = transaction     # recomendado para APIs

# Limites de conexões:
max_client_conn = 1000      # conexões de clientes (aplicações)
default_pool_size = 20      # conexões para o PostgreSQL por banco

# Timeouts:
client_idle_timeout = 600
server_idle_timeout = 600
EOF

# Criar arquivo de usuários:
# Gerar hash da senha (md5):
echo -n "senhaPGbouncer" | md5sum
# Formato: "usuario" "md5HASH"
echo '"app_user" "md5HASH_AQUI"' | sudo tee /etc/pgbouncer/userlist.txt

sudo systemctl enable pgbouncer
sudo systemctl restart pgbouncer

# pgbouncer.ini — comparação dos modos:

# SESSION pooling (menos eficiente):
# pool_mode = session
# Uma conexão do PostgreSQL fica alocada por toda a sessão do cliente.
# Compatível com TODAS as funcionalidades do PostgreSQL.
# Use para: aplicações que usam SET, prepared statements nomeados, cursors.

# TRANSACTION pooling (recomendado para APIs):
# pool_mode = transaction
# A conexão é liberada ao pool após cada COMMIT/ROLLBACK.
# Uma conexão do PostgreSQL serve múltiplos clientes (round-robin).
# NÃO compatível com: SET SESSION, prepared statements nomeados (use $1 params),
# LISTEN/NOTIFY, cursors que vivem entre transações.
# Use para: APIs REST stateless, onde cada requisição é uma transação.

# STATEMENT pooling (mais restritivo):
# pool_mode = statement
# Libera a conexão após cada statement (cada query).
# NÃO compatível com transações multi-statement.
# Use para: SELECT simples sem transações.

# Para Node.js com pg ou Prisma: transaction mode é ideal.
# Configurar no DATABASE_URL:
# postgresql://usuario:senha@localhost:5433/producao
#                                       ^^^^ porta do PgBouncer (não 5432)

# Preparar a aplicação para transaction mode:
# Não usar: SET search_path, PREPARE nome_stmt, DECLARE cursor
# Usar sempre: prepared statements sem nome ($1, $2) ou queries inline

// Com PgBouncer, reduzir o pool da aplicação:
import { Pool } from 'pg'

const db = new Pool({
  connectionString: process.env.DATABASE_URL,
  // PgBouncer já faz pooling — pool da app deve ser pequeno:
  max: 5,              // máximo de 5 conexões simultâneas por instância
  idleTimeoutMillis: 10_000,
  connectionTimeoutMillis: 5_000,
  // IMPORTANTE para transaction mode:
  // Não usar prepared statements nomeados:
  statement_timeout: 30_000,
})

// Prisma — adicionar pgbouncer=true no connection string:
// DATABASE_URL="postgresql://user:pass@localhost:5433/db?pgbouncer=true"
// O Prisma desativa prepared statements nomeados automaticamente

// Drizzle ORM — sem configuração especial necessária
// (usa prepared statements sem nome por padrão)

// TypeORM — desativar prepared statements:
// const ds = new DataSource({
//   type: 'postgres',
//   url: process.env.DATABASE_URL,
//   extra: { prepareCached: false }
// })

// Verificar conexões ativas no PgBouncer:
// psql -h localhost -p 5433 -U pgbouncer pgbouncer -c "SHOW POOLS;"
// psql -h localhost -p 5433 -U pgbouncer pgbouncer -c "SHOW CLIENTS;"
// psql -h localhost -p 5433 -U pgbouncer pgbouncer -c "SHOW SERVERS;"

# docker-compose.yml — PostgreSQL + PgBouncer:
services:
  postgres:
    image: postgres:16-alpine
    restart: always
    environment:
      POSTGRES_DB: producao
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - pg_data:/var/lib/postgresql/data

  pgbouncer:
    image: edoburu/pgbouncer:latest
    restart: always
    ports:
      - "127.0.0.1:5433:5432"
    environment:
      DATABASE_URL: "postgres://app_user:${POSTGRES_PASSWORD}@postgres:5432/producao"
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 25
      AUTH_TYPE: scram-sha-256
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  pg_data:

# A aplicação conecta em localhost:5433 (PgBouncer)
# PgBouncer conecta em postgres:5432 (PostgreSQL interno)
# DATABASE_URL=postgresql://app_user:senha@localhost:5433/producao

# Conectar ao console admin do PgBouncer:
psql -h 127.0.0.1 -p 5433 -U pgbouncer pgbouncer

# Comandos úteis no console admin:
SHOW POOLS;
-- pool_mode | cl_active | cl_waiting | sv_active | sv_idle | sv_used
-- cl_active:  clientes com conexão ativa no PostgreSQL
-- cl_waiting: clientes esperando por uma conexão (GARGALO!)
-- sv_active:  conexões do PostgreSQL em uso
-- sv_idle:    conexões do PostgreSQL ociosas no pool

SHOW CLIENTS;   -- todas as conexões de clientes
SHOW SERVERS;   -- todas as conexões para o PostgreSQL
SHOW STATS;     -- estatísticas de uso (requests/s, latência)

# Métricas via Prometheus (pgbouncer_exporter):
docker run -d --name pgbouncer-exporter   -p 127.0.0.1:9127:9127   -e DATABASE_URL="postgresql://pgbouncer:senha@localhost:5433/pgbouncer"   prometheuscommunity/pgbouncer-exporter

# prometheus.yml — adicionar scrape:
# - job_name: pgbouncer
#   static_configs:
#     - targets: ['localhost:9127']

# Alertas importantes:
# cl_waiting > 0 por mais de 30s → pool insuficiente → aumentar default_pool_size
# sv_active ≈ default_pool_size → pool saturado → aumentar ou otimizar queries