Como monitorar saúde de conexões de banco com pg_stat_activity

1. Introdução ao pg_stat_activity e sua importância

O pg_stat_activity é uma das views de sistema mais valiosas do PostgreSQL, fornecendo uma visão em tempo real de todas as conexões ativas no banco de dados. Cada linha representa uma sessão ou processo backend, contendo informações detalhadas sobre o que cada conexão está executando, seu estado atual e há quanto tempo está ativa.

Monitorar a saúde das conexões de banco é fundamental para garantir a estabilidade e performance de aplicações que dependem do PostgreSQL. Problemas comuns como deadlocks, queries lentas, conexões órfãs e transações abandonadas podem ser detectados precocemente através da análise desta view. Uma conexão mal comportada pode consumir recursos preciosos, bloquear outras operações e até mesmo levar o banco à exaustão de conexões disponíveis.

A saúde geral do banco está diretamente relacionada ao gerenciamento adequado das conexões. Sem monitoramento, uma única transação ociosa pode escalar para um incidente de produção, afetando centenas de usuários.

2. Estrutura e campos principais da view

A view pg_stat_activity contém dezenas de colunas, mas algumas são essenciais para o diagnóstico:

-- Identificação básica das sessões
SELECT 
    datname,           -- Nome do banco de dados
    pid,               -- ID do processo backend
    usename,           -- Nome do usuário
    application_name,  -- Nome da aplicação (ex: psql, pgAdmin)
    client_addr,       -- Endereço IP de origem
    client_port        -- Porta de origem
FROM pg_stat_activity;

Os campos mais importantes para monitoramento de saúde são:

• state: Indica o estado atual da conexão (active, idle, idle in transaction, disabled)
• query: A consulta SQL sendo executada ou a última consulta executada
• query_start: Timestamp de quando a consulta atual começou
• state_change: Timestamp da última mudança de estado
• wait_event e wait_event_type: Informam se a sessão está esperando por algum recurso (lock, IO, rede)

3. Identificando conexões problemáticas

Conexões em estado idle in transaction

Este é um dos estados mais perigosos para a saúde do banco. Uma conexão que iniciou uma transação mas não a finalizou (commit ou rollback) mantém locks ativos, impedindo que outras operações prossigam.

-- Encontrar transações ociosas perigosas
SELECT 
    pid,
    usename,
    datname,
    state,
    query,
    query_start,
    state_change,
    age(now(), query_start) AS duracao_transacao
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY query_start;

Queries ativas por tempo excessivo

Consultas que permanecem no estado active por muito tempo podem indicar problemas de performance, falta de índices ou bloqueios.

-- Queries ativas há mais de 5 minutos
SELECT 
    pid,
    usename,
    datname,
    query,
    query_start,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '5 minutes'
ORDER BY query_start;

Diagnóstico de esperas com wait_event

Os campos wait_event_type e wait_event são cruciais para identificar gargalos:

• Lock: Espera por locks de tabela, linha ou transação
• IO: Espera por operações de disco (leitura/escrita)
• Client: Espera por dados do cliente
• Activity: Espera por atividades internas do PostgreSQL

4. Consultas práticas para diagnóstico

Listar conexões totais e por banco de dados

-- Total de conexões por banco
SELECT 
    datname,
    count(*) AS total_conexoes,
    count(*) FILTER (WHERE state = 'active') AS ativas,
    count(*) FILTER (WHERE state = 'idle') AS ociosas,
    count(*) FILTER (WHERE state = 'idle in transaction') AS transacoes_ociosas
FROM pg_stat_activity
GROUP BY datname
ORDER BY total_conexoes DESC;

Encontrar queries bloqueadas e bloqueadoras

A função pg_blocking_pids() retorna os PIDs das sessões que estão bloqueando uma determinada conexão:

-- Identificar bloqueios ativos
SELECT
    blocked.pid AS pid_bloqueado,
    blocked.usename AS usuario_bloqueado,
    blocked.query AS query_bloqueada,
    blocking.pid AS pid_bloqueador,
    blocking.usename AS usuario_bloqueador,
    blocking.query AS query_bloqueadora,
    blocked.wait_event,
    blocked.wait_event_type
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking 
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Detectar conexões ociosas por mais de X minutos

-- Conexões ociosas há mais de 30 minutos
SELECT 
    pid,
    usename,
    datname,
    client_addr,
    application_name,
    state_change,
    age(now(), state_change) AS tempo_ocioso
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - interval '30 minutes'
ORDER BY state_change;

5. Métricas de saúde e alertas

Número de conexões ativas vs. max_connections

Uma métrica crítica é a proporção de conexões utilizadas em relação ao limite máximo:

-- Percentual de uso de conexões
SELECT 
    current_connections,
    max_connections,
    round(100.0 * current_connections / max_connections, 2) AS percentual_uso
FROM (
    SELECT 
        count(*) AS current_connections,
        (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections
    FROM pg_stat_activity
) sub;

Monitoramento de state_change

A frequência de mudanças de estado pode indicar padrões anormais:

-- Conexões que mudaram de estado nos últimos 10 minutos
SELECT 
    count(*) AS mudancas_estado,
    state,
    count(*) FILTER (WHERE state_change > now() - interval '10 minutes') AS recentes
FROM pg_stat_activity
GROUP BY state;

Integração com ferramentas externas

Para ambientes de produção, é recomendado exportar estas métricas para sistemas de monitoramento:

• Prometheus: Utilize o postgres_exporter para coletar métricas do pg_stat_activity
• Zabbix: Crie itens de monitoramento customizados com queries SQL
• Grafana: Configure dashboards com alertas baseados em thresholds

6. Ações corretivas baseadas no monitoramento

Encerrar conexões problemáticas com pg_terminate_backend

Quando uma conexão precisa ser removida imediatamente:

-- Encerrar uma conexão específica pelo PID
SELECT pg_terminate_backend(12345);

-- Encerrar todas as transações ociosas há mais de 1 hora
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < now() - interval '1 hour';

Estratégias para matar queries lentas

-- Encerrar queries ativas há mais de 30 minutos
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '30 minutes'
  AND query !~ '^autovacuum:';  -- Não matar autovacuum

Ajuste de parâmetros de segurança

Para prevenir problemas recorrentes, configure limites automáticos:

-- No postgresql.conf ou ALTER SYSTEM
-- Tempo máximo para transações ociosas (5 minutos)
idle_in_transaction_session_timeout = '5min'

-- Tempo máximo para execução de queries (2 minutos)
statement_timeout = '2min'

-- Tempo máximo para conexões ociosas (1 hora)
idle_session_timeout = '1h'

7. Boas práticas e automação

Criação de scripts periódicos para snapshots

Automatize a coleta de dados para análise histórica:

-- Script para capturar snapshot a cada 5 minutos
CREATE TABLE IF NOT EXISTS connection_snapshots (
    snapshot_time timestamptz DEFAULT now(),
    datname text,
    total_connections int,
    active_connections int,
    idle_in_transaction int,
    blocked_queries int,
    max_connections int
);

INSERT INTO connection_snapshots
SELECT 
    now(),
    datname,
    count(*) AS total,
    count(*) FILTER (WHERE state = 'active') AS active,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_tx,
    (SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock') AS blocked,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections')
FROM pg_stat_activity
GROUP BY datname;

Integração com sistemas de logging

Configure logs específicos para eventos de conexão:

-- No postgresql.conf
log_connections = on
log_disconnections = on
log_duration = on
log_statement = 'ddl'  -- Loga apenas DDL por segurança
log_lock_waits = on
deadlock_timeout = '1s'

Recomendações para ambientes de produção

1. Limites por aplicação: Use connection pools como PgBouncer para gerenciar conexões
2. Pool de conexões: Configure max_connections com folga (tipicamente 20-30% acima do pico esperado)
3. Timeout global: Sempre defina statement_timeout e idle_in_transaction_session_timeout
4. Alertas proativos: Configure alertas quando 80% das conexões estiverem em uso
5. Revisão periódica: Analise semanalmente as queries mais lentas e conexões órfãs

-- Dashboard de alertas recomendado
SELECT 
    'CRITICAL' AS nivel,
    'Conexoes proximas do limite' AS alerta,
    count(*) || '/' || (SELECT setting FROM pg_settings WHERE name = 'max_connections') AS detalhe
FROM pg_stat_activity
HAVING count(*) > (SELECT setting::int * 0.8 FROM pg_settings WHERE name = 'max_connections')

UNION ALL

SELECT 
    'WARNING',
    'Transacoes ociosas antigas',
    count(*) || ' transacoes ha mais de 30 min'
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < now() - interval '30 minutes'
HAVING count(*) > 0;

Referências

• Documentação oficial do PostgreSQL: pg_stat_activity — Referência completa sobre a view pg_stat_activity, incluindo todos os campos e exemplos de uso
• PostgreSQL Monitoring: Understanding pg_stat_activity — Artigo técnico da CyberTec explicando como interpretar cada campo e diagnosticar problemas comuns
• pg_stat_activity: The Swiss Army Knife of PostgreSQL Monitoring — Guia prático da Citus Data com exemplos de consultas para troubleshooting
• PostgreSQL Connection Pooling and Monitoring Best Practices — Artigo da SeveralNines sobre boas práticas de gerenciamento de conexões e monitoramento
• Using pg_stat_activity for Performance Troubleshooting — Tutorial da Percona focado em identificação de queries lentas e bloqueios
• PostgreSQL Monitoring with Prometheus and Grafana — Guia oficial da Grafana sobre como integrar métricas do PostgreSQL com dashboards
• PostgreSQL Configuration: timeout parameters explained — Documentação oficial sobre parâmetros de timeout como statement_timeout e idle_in_transaction_session_timeout