Monitoramento de Consultas Ativas: Usando pg_stat_activity para Ajuste de Desempenho

Quando seu banco de dados desacelera repentinamente, você precisa saber o que o PostgreSQL está fazendo agora. pg_stat_activity mostra consultas ativas, sessões ociosas, esperas por bloqueio e transações abertas, permitindo distinguir uma consulta lenta de uma bloqueada.

Use-o durante incidentes, mas também mantenha algumas consultas salvas para verificações de rotina. Os exemplos abaixo focam em sistemas PostgreSQL onde você tem permissão para ler a atividade das sessões que precisa inspecionar.

Entendendo a Visão pg_stat_activity

pg_stat_activity é uma visão dinâmica do sistema com uma linha para cada processo do servidor conectado ao cluster de banco de dados. Isso inclui backends de clientes, workers em segundo plano e sessões que estão ociosas, mas ainda conectadas.

Monitorar essa visão permite ver exatamente o que o banco de dados está fazendo agora, tornando-a inestimável para depurar quedas repentinas de desempenho ou diagnosticar problemas de contenção que são muito transitórios para serem capturados efetivamente por arquivos de log típicos.

Colunas Chave para Análise de Desempenho

Embora pg_stat_activity contenha dezenas de colunas, as seguintes são essenciais ao diagnosticar problemas de desempenho:

Nome da Coluna	Descrição	Relevância para Ajuste
pid	ID do processo do backend.	Necessário para cancelar ou encerrar sessões.
datname	Nome do banco de dados ao qual este backend está conectado.	Ajuda a delimitar o monitoramento em ambientes com múltiplos bancos.
usename	Usuário que iniciou a conexão.	Identifica atividade específica de aplicação ou usuário.
application_name	Nome da aplicação que está se conectando (se definido pelo cliente).	Excelente para identificar conexões de microsserviços específicos.
state	Status atual da atividade (ex.: active, idle, idle in transaction).	Indicador central do que o backend está fazendo.
query	A consulta atual, ou a última consulta para sessões ociosas. A visibilidade pode ser limitada por privilégios e configurações.	Identifica a instrução SQL envolvida.
query_start	Timestamp de quando a execução da consulta atual começou.	Usado para calcular a duração da consulta.
wait_event_type & wait_event	Detalhes sobre o que o processo está esperando (ex.: aquisição de bloqueio, E/S).	Crítico para diagnosticar contenção e bloqueio.

Casos de Uso Práticos de Monitoramento

O verdadeiro poder do pg_stat_activity vem da filtragem dos dados para responder a perguntas específicas de desempenho.

Visualizar Todas as Consultas Ativas

Para ver apenas os processos que estão executando uma instrução (não ociosos), filtre a visão pela coluna state.

-- Visualizar todas as consultas atualmente em execução
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Identificar Consultas de Longa Duração

Identificar consultas que estão sendo executadas por mais tempo que o esperado é frequentemente o primeiro passo no ajuste de desempenho. Essas consultas podem consumir recursos, causar picos de E/S ou manter bloqueios.

Para identificar consultas em execução por mais de um limite específico (ex.: 5 segundos), use subtração de intervalo com now() e query_start.

-- Encontrar consultas em execução por mais de 5 segundos
SELECT
    pid,
    usename,
    datname,
    state,
    (now() - query_start) AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '5 seconds'
ORDER BY duration DESC;

Use um limite que se ajuste à sua carga de trabalho. Uma consulta de checkout de cinco segundos pode ser grave em uma aplicação OLTP, enquanto uma consulta de relatório de cinco minutos pode ser normal se for executada fora do horário comercial.

Diagnosticar Sessões Ociosas em Transação

Uma conexão que está idle in transaction iniciou uma transação, mas não a confirmou ou desfez. Ela está esperando o cliente enviar o próximo comando. Essas sessões podem manter bloqueios e manter versões antigas de linhas visíveis, o que pode atrasar a limpeza pelo autovacuum e contribuir para o inchaço da tabela.

-- Encontrar sessões que estão ociosas, mas mantendo uma transação aberta
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - xact_start AS txn_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY txn_duration DESC;

Se você encontrar sessões mantendo transações abertas por minutos ou horas, verifique o caminho da aplicação que abriu a transação. Causas comuns incluem falta de tratamento de rollback após uma exceção, uma conexão retornada a um pool antes da limpeza, ou uma sessão administrativa interativa deixada aberta.

Analisar Contenção de Bloqueio e Bloqueio

Quando uma consulta trava, muitas vezes está esperando por um bloqueio mantido por outro processo. A visão pg_stat_activity, combinada com pg_locks, é crucial para diagnosticar contenção.

Para encontrar sessões que estão atualmente esperando por um recurso (um bloqueio, E/S, etc.), observe a coluna wait_event. Se uma sessão está bloqueada, seu wait_event_type frequentemente será Lock.

-- Identificar processos atualmente bloqueados por um bloqueio
SELECT
    a.pid,
    a.usename,
    a.query_start,
    a.query,
    a.wait_event,
    a.wait_event_type
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND a.wait_event IS NOT NULL
ORDER BY a.query_start;

Para uma visão rápida de "quem está bloqueando quem", o PostgreSQL também fornece pg_blocking_pids().

-- Mostrar sessões bloqueadas e as sessões que as bloqueiam
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query
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';

Se isso retornar uma solicitação web bloqueada aguardando uma longa atualização administrativa, cancelar a consulta administrativa pode ser mais seguro do que encerrar a sessão da aplicação.

Gerenciando Sessões Problemáticas

Uma vez identificada uma consulta ou sessão problemática usando seu ID de Processo (pid), o PostgreSQL fornece duas funções para gerenciá-la:

Cancelar uma Consulta com pg_cancel_backend

Esta função tenta interromper graciosamente a execução de uma consulta específica. A sessão em si permanece conectada e disponível para consultas futuras.

-- Exemplo: Cancelar a consulta em execução no PID 12345
SELECT pg_cancel_backend(12345);

Encerrar uma Sessão com pg_terminate_backend

Esta função desconecta forçadamente o processo backend do servidor. Se a sessão estava no meio de uma transação, o PostgreSQL irá automaticamente desfazer a transação.

-- Exemplo: Encerrar forçadamente a sessão com PID 54321
SELECT pg_terminate_backend(54321);

Tente pg_cancel_backend primeiro quando a sessão está simplesmente executando uma consulta ruim. Use pg_terminate_backend para sessões que estão travadas, abandonadas ou mantendo uma transação aberta que não pode ser limpa normalmente. Desfazer uma transação grande pode levar tempo e adicionar carga de E/S, então faça isso deliberadamente.

Melhores Práticas para Monitoramento

Filtrar Agressivamente

Evite SELECT * FROM pg_stat_activity como seu hábito padrão de produção. A saída é ruidosa, e o texto da query pode expor valores sensíveis se suas aplicações enviarem literais em vez de parâmetros de ligação. Selecione as colunas que você precisa e filtre por state, datname, application_name ou duração.

Usar Ferramentas para Monitoramento Automatizado

Verificações manuais são úteis durante um incidente, mas tendências pertencem ao monitoramento. Acompanhe sessões ativas, sessões em espera, transações longas e contagens de idle in transaction em seu painel do PostgreSQL.

Configurar Log de Instruções

Combine monitoramento em tempo real com dados históricos. Configure parâmetros como log_min_duration_statement para registrar consultas que excedem um certo limite, fornecendo dados para análise mesmo após a consulta terminar a execução.

Conclusão

Mantenha três verificações salvas: consultas ativas ordenadas por duração, transações ociosas ordenadas por idade da transação e sessões bloqueadas com seus bloqueadores. Quando o PostgreSQL parecer lento, essas visões dirão se você deve ajustar SQL, corrigir o tratamento de transações ou limpar uma sessão bloqueadora.