Connection limits e resource governance

1. Fundamentos de Gerenciamento de Conexões

Connection limits são restrições impostas ao número máximo de conexões simultâneas que um banco de dados pode aceitar. Esses limites são críticos porque cada conexão consome recursos do sistema: memória RAM para manter o estado da sessão, CPU para processar queries, e descritores de arquivo no sistema operacional.

Quando conexões excessivas são permitidas, o banco pode sofrer degradação severa de desempenho. O sistema operacional pode ficar sem recursos para alocar novas conexões, queries competem por CPU e memória, e o banco pode até ficar indisponível. É fundamental entender a diferença entre:

  • Conexões ativas: executando queries no momento
  • Conexões ociosas: estabelecidas mas sem atividade
  • Conexões em espera: aguardando liberação de recursos (locks, buffers)

2. Configuração de Connection Limits no PostgreSQL

O PostgreSQL oferece múltiplas camadas para controle de conexões. O parâmetro principal é max_connections, definido no postgresql.conf:

max_connections = 100

Para calcular um valor adequado, considere que cada conexão reserva aproximadamente 2-10 MB de RAM. Um servidor com 8 GB de RAM pode suportar cerca de 200-400 conexões, dependendo de outras cargas.

Limites por usuário são configurados com:

ALTER USER app_user CONNECTION LIMIT 20;

Limites por banco de dados:

ALTER DATABASE meu_banco CONNECTION LIMIT 50;

No pg_hba.conf, é possível restringir por origem (IP, rede) e método de autenticação:

# Bloquear conexões de uma rede específica
host    all    all    192.168.1.0/24    reject
# Permitir apenas 10 conexões simultâneas de uma aplicação
host    all    app_user    10.0.0.0/8    md5

3. Pooling de Conexões como Estratégia de Governança

Pooling de conexões é essencial para gerenciar recursos em ambientes de alta concorrência. O PgBouncer é a ferramenta mais popular, oferecendo três modos:

Session pooling: mantém a conexão por toda a sessão do cliente (mais recursos)
Transaction pooling: libera a conexão após cada transação (recomendado para web apps)
Statement pooling: libera após cada statement (uso específico)

Configuração básica do PgBouncer:

[databases]
minha_app = host=127.0.0.1 port=5432 dbname=meu_banco

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
max_client_conn = 200
default_pool_size = 25
pool_mode = transaction

O Pgpool-II oferece funcionalidades adicionais como balanceamento de carga e failover automático:

num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0

4. Resource Governance com Workload Management

O monitoramento ativo de conexões usa pg_stat_activity:

SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity
WHERE state != 'idle';

Para controle proativo, funções built-in permitem gerenciar sessões:

-- Cancelar uma query específica
SELECT pg_cancel_backend(12345);

-- Terminar uma conexão
SELECT pg_terminate_backend(12345);

-- Encontrar e matar conexões ociosas há mais de 30 minutos
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND state_change < now() - interval '30 minutes';

Parâmetros de timeout são cruciais para evitar recursos presos:

# postgresql.conf
statement_timeout = '30s'
idle_in_transaction_session_timeout = '5min'
lock_timeout = '10s'

5. Gerenciamento de Memória e CPU por Conexão

Cada conexão pode consumir memória significativa dependendo dos parâmetros. O work_mem é usado por operações de ordenação e hash:

work_mem = '8MB'

Com 100 conexões executando queries simultâneas, o consumo potencial é de 800 MB apenas para work_mem. O shared_buffers é compartilhado entre todas as conexões:

shared_buffers = '2GB'  # 25% da RAM total

Para operações de manutenção (VACUUM, CREATE INDEX):

maintenance_work_mem = '512MB'

O controle de paralelismo evita que conexões monopolizem CPU:

max_parallel_workers_per_gather = 2
max_parallel_workers = 4

Para identificar queries problemáticas:

log_min_duration_statement = '2s'

6. Implementação de Quotas e Limites Avançados

A extensão pg_stat_statements permite análise detalhada por query:

CREATE EXTENSION pg_stat_statements;

SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Função personalizada para limitar conexões por aplicação:

CREATE OR REPLACE FUNCTION check_connection_limit()
RETURNS event_trigger AS $$
DECLARE
    app_name text;
    conn_count integer;
BEGIN
    app_name := current_setting('application_name');

    SELECT COUNT(*) INTO conn_count
    FROM pg_stat_activity
    WHERE application_name = app_name;

    IF conn_count > 10 THEN
        RAISE EXCEPTION 'Limite de conexões excedido para aplicação: %', app_name;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER connection_limit_trigger
ON ddl_command_start
EXECUTE FUNCTION check_connection_limit();

Script para matar conexões ociosas automaticamente:

CREATE OR REPLACE FUNCTION kill_idle_connections()
RETURNS integer AS $$
DECLARE
    killed integer := 0;
BEGIN
    WITH idle_connections AS (
        SELECT pid
        FROM pg_stat_activity
        WHERE state = 'idle'
        AND state_change < now() - interval '1 hour'
        AND pid <> pg_backend_pid()
    )
    SELECT COUNT(*) INTO killed
    FROM (
        SELECT pg_terminate_backend(pid)
        FROM idle_connections
    ) AS sub;

    RETURN killed;
END;
$$ LANGUAGE plpgsql;

7. Monitoramento e Alertas para Resource Governance

Consultas no pg_stat_database fornecem métricas essenciais:

SELECT datname, numbackends, xact_commit, xact_rollback,
       blks_read, blks_hit, conflicts, deadlocks
FROM pg_stat_database;

Configuração de alertas no sistema operacional:

# /etc/security/limits.conf
postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 65536
postgres hard nproc 65536

Ferramentas de monitoramento:

# pgBadger - análise de logs
pgbadger /var/log/postgresql/postgresql.log

# pg_top - monitoramento em tempo real
pg_top -h localhost -p 5432 -U postgres

# Consulta para dashboard de conexões
SELECT
    COUNT(*) FILTER (WHERE state = 'active') AS active,
    COUNT(*) FILTER (WHERE state = 'idle') AS idle,
    COUNT(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction,
    COUNT(*) FILTER (WHERE wait_event_type IS NOT NULL) AS waiting
FROM pg_stat_activity;

8. Boas Práticas e Casos de Uso

Planejamento de capacidade: para cada 100 conexões simultâneas, reserve 1-2 GB de RAM extras além do shared_buffers. Em servidores com 16 GB RAM, um limite de 300-400 conexões é razoável.

Ambientes multi-tenant: use combinação de CONNECTION LIMIT por banco de dados e pooling por aplicação:

ALTER DATABASE tenant_a CONNECTION LIMIT 20;
ALTER DATABASE tenant_b CONNECTION LIMIT 50;

Alta concorrência (web apps): configure PgBouncer com:

default_pool_size = 20
max_client_conn = 500
pool_mode = transaction

Checklist de verificação:
- [ ] max_connections calculado conforme hardware
- [ ] Pooling de conexões implementado (PgBouncer)
- [ ] Timeouts configurados (statement_timeout, idle_in_transaction_session_timeout)
- [ ] pg_stat_statements ativado para análise
- [ ] Alertas configurados para uso > 80% das conexões
- [ ] Monitoramento de conexões ociosas automatizado
- [ ] Limites por usuário/banco definidos
- [ ] work_mem ajustado para evitar estouro de memória

Referências