Monitorando transações ativas em tempo real com pg_stat_activity

pg_stat_activity é tipo uma janela em tempo real que te ajuda a entender o que tá rolando no seu banco de dados nesse exato momento. Na aula passada a gente viu o básico, agora bora mergulhar mais fundo nesse recurso poderoso.

Exemplo de query básica pra pg_stat_activity:

SELECT * 
FROM pg_stat_activity;

Essa query vai mostrar todas as conexões ativas e as queries que tão rolando. Massa! Mas vai vir dado demais, e você pode perder um tempão olhando tudo. Por isso, é melhor filtrar só o que realmente importa.

Campos principais no pg_stat_activity

Bora dar uma olhada nos campos chave que vão te ajudar além dos que você já conhece. query_start mostra quando a query começou a rodar, o que é crucial pra achar operações demoradas. pid traz o ID do processo da conexão — isso é útil pra gerenciar (tipo finalizar) a conexão. state_change mostra quando o estado atual da conexão foi definido, o que é ótimo pra analisar estados problemáticos que ficam travados por muito tempo.

Exemplo de seleção dos processos ativos:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

Como rastrear queries demoradas?

Imagina que você é o DBA e, do nada, a carga do servidor foi lá pra cima. E agora? Primeiro, tem que descobrir qual query tá sugando todos os recursos. Bora usar o pg_stat_activity pra achar essas queries “fominhas”.

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

Essa query mostra todas as queries que tão rodando há mais de 10 segundos. Ajusta esse intervalo do jeito que você quiser.

Finalizando queries problemáticas

Bora ver como se livrar das queries que já tão rodando faz tempo demais e tão atrapalhando o banco. Usa a função pg_terminate_backend() pra finalizar o processo na marra.

Exemplo de finalização de um processo com PID específico:

SELECT pg_terminate_backend(12345);

Onde 12345 é o ID do processo (campo pid) que você pegou do pg_stat_activity.

Importante: Finalizar um processo pode causar rollback se a transação não terminar direito, então vai com calma.

Agora, se você quiser finalizar automaticamente todos os processos “pendurados”, tipo transações idle, pode rodar esse bloco PL/pgSQL. Como você já manja de programação, loop (laço) é aquela estrutura que repete um conjunto de comandos até acabar os dados ou até uma condição ser satisfeita:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Essa solução dinâmica faz uma limpeza nos processos problemáticos. O FOR passa por cada linha do resultado da query e finaliza o processo pra cada PID encontrado.

Daqui a pouco a gente vai estudar PL/pgSQL, segura aí que tá chegando :P

Filtrando pelo estado das transações

Às vezes você não quer só achar query ativa, mas também entender quais conexões tão em estados especiais, tipo idle ou idle in transaction. Isso pode te ajudar a achar problemas antes deles virarem dor de cabeça.

Exemplo de query pra achar transações que tão em idle in transaction:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

O campo state_change mostra quando esse estado foi definido. Assim, você acha transações que tão paradas faz tempo, não fazem nada útil, mas podem travar recursos do banco.

Aplicação prática

Monitorando queries longas em produção: você pode configurar um monitoramento regular das queries que passam de um certo tempo e mandar alerta no Slack, Telegram ou qualquer outra ferramenta de notificação. Assim, você reage rapidinho a problemas de performance.

Análise de queries durante incidentes: se o servidor começar a engasgar, a primeira coisa é olhar o pg_stat_activity pra achar o motivo. Isso tem que ser seu protocolo padrão pra problemas de performance.

Manutenção do banco de dados: analisar o pg_stat_activity com frequência te ajuda a achar queries ineficientes e otimizar elas (tipo criando índices ou reescrevendo as queries).

Quando o assunto é monitoramento, os erros podem rolar por causa de filtragem errada ou interpretação errada dos dados. Por exemplo, se você filtra só pelo estado active, pode deixar passar queries que tão em idle in transaction, e elas também podem travar recursos. Outro erro é finalizar processos agressivamente demais, o que pode causar rollback indesejado e perda de dados. Sempre analisa o contexto antes de tomar decisões radicais.

Técnicas extras de monitoramento

Pra monitoramento mais avançado, dá pra criar queries mais complexas que mostram estatísticas por usuário, banco de dados ou tipo de query. Por exemplo, dá pra ver quanto tempo em média cada usuário gasta nas queries, ou achar quais bancos têm mais conexões ativas.

Também é massa configurar o log automático de queries longas nos arquivos de log do PostgreSQL, usando os parâmetros de configuração log_min_duration_statement e log_statement. Isso te ajuda a analisar problemas de performance depois e achar padrões no comportamento das aplicações.