PostgreSQL lento: por onde começar o diagnóstico

Quando o PostgreSQL fica lento, o primeiro passo é identificar se as sessões estão executando, esperando ou sendo impedidas de começar. Sem essa distinção, criar índice, aumentar CPU e reiniciar o banco viram tentativas caras para sintomas diferentes.

“O banco está lento” descreve uma experiência, não uma causa. Uma consulta pode estar lenta porque lê dados demais; todas as consultas podem esperar por I/O; uma transação pode bloquear centenas de sessões; ou a aplicação pode ter esgotado o pool de conexões. O diagnóstico começa separando esses cenários enquanto o problema ainda está acontecendo.

O que está esperando agora

pg_stat_activity é o ponto de partida porque mostra o estado das sessões e, quando disponível, a consulta em execução. Uma leitura básica é:

SELECT    pid,    usename,    datname,    client_addr,    state,    wait_event_type,    wait_event,    clock_timestamp() - query_start AS query_age,    left(query, 180) AS queryFROM pg_stat_activityWHERE state <> 'idle'ORDER BY query_age DESC NULLS LAST;

O campo state informa o ciclo da sessão. active não significa necessariamente que ela está usando CPU: ela pode estar ativa para o cliente enquanto espera um evento. Quando wait_event_type e wait_event estão preenchidos, a espera explica onde o backend está parado naquele instante.

Uma amostra isolada ajuda a investigar, mas não prova que a causa é recorrente. O ideal é guardar snapshots durante o horário afetado e comparar a duração, o tipo de espera e a consulta envolvida.

Bloqueio antes de índice

Se uma sessão espera por um lock, melhorar o plano dela não remove a sessão que mantém a transação aberta. O primeiro diagnóstico deve encontrar o bloqueador:

SELECT    blocked.pid AS blocked_pid,    blocked.query AS blocked_query,    clock_timestamp() - blocked.query_start AS blocked_for,    blocker.pid AS blocker_pid,    blocker.query AS blocker_query,    clock_timestamp() - blocker.xact_start AS blocker_xact_ageFROM pg_stat_activity AS blockedJOIN pg_locks AS blocked_lock  ON blocked_lock.pid = blocked.pid AND NOT blocked_lock.grantedJOIN pg_locks AS blocker_lock  ON blocker_lock.locktype = blocked_lock.locktype AND blocker_lock.database IS NOT DISTINCT FROM blocked_lock.database AND blocker_lock.relation IS NOT DISTINCT FROM blocked_lock.relation AND blocker_lock.page IS NOT DISTINCT FROM blocked_lock.page AND blocker_lock.tuple IS NOT DISTINCT FROM blocked_lock.tuple AND blocker_lock.virtualxid IS NOT DISTINCT FROM blocked_lock.virtualxid AND blocker_lock.transactionid IS NOT DISTINCT FROM blocked_lock.transactionid AND blocker_lock.classid IS NOT DISTINCT FROM blocked_lock.classid AND blocker_lock.objid IS NOT DISTINCT FROM blocked_lock.objid AND blocker_lock.objsubid IS NOT DISTINCT FROM blocked_lock.objsubid AND blocker_lock.grantedJOIN pg_stat_activity AS blocker ON blocker.pid = blocker_lock.pid;

Uma transação aberta que não está executando consulta ainda pode manter locks e impedir limpeza de versões. Por isso xact_start é tão importante quanto query_start. A consulta que parece parada pode ser apenas a vítima; o bloqueador pode estar em outra aplicação ou em uma sessão esquecida.

Esse é o mesmo princípio descrito em como identificar a query que bloqueia as outras: localizar o mecanismo antes de aplicar uma correção no sintoma.

Plano e cardinalidade

Quando não há bloqueio, o próximo passo é comparar o plano esperado com o plano observado. EXPLAIN (ANALYZE, BUFFERS) mostra o caminho escolhido e o trabalho efetivamente realizado, mas deve ser usado com cuidado em produção: ele executa a consulta e pode alterar dados se for aplicado a comandos de escrita.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT cliente_id, totalFROM pedidosWHERE criado_em >= now() - interval '7 days'ORDER BY criado_em DESC;

Os sinais mais úteis não são “tem ou não tem índice”. São diferenças entre estimativa e realidade, quantidade de blocos lidos, linhas removidas pelo filtro e operações que derramam para disco. Um estimador que esperava poucas linhas e encontrou milhões pode escolher nested loop onde um hash join seria mais adequado.

Antes de criar índice, verifique se as estatísticas estão atualizadas e se a consulta realmente filtra pela coluna que o índice cobriria. Um índice novo também tem custo: ocupa armazenamento, aumenta escrita e pode não ser usado se a seletividade for baixa ou se o predicado não combinar com a ordem das colunas.

I/O e memória sem chute

Se muitas consultas estão ativas e o evento de espera aponta para leitura, observe se o problema é latência do armazenamento, working set maior que a memória disponível ou consultas que fazem varreduras desnecessárias. Aumentar shared_buffers sem medir o comportamento do sistema operacional pode apenas deslocar a pressão.

O EXPLAIN com BUFFERS ajuda a distinguir blocos encontrados em cache de blocos lidos. O histórico de duração e de leituras por consulta ajuda a descobrir se o aumento veio de uma query específica ou de uma mudança geral no volume de dados.

Também vale olhar para checkpoints e escrita de WAL. Picos de escrita agrupados podem elevar a latência percebida por consultas que, isoladamente, não mudaram. A explicação precisa conectar o evento de infraestrutura à janela em que os usuários perceberam a lentidão.

Conexões demais também parecem lentidão

O PostgreSQL usa um processo por conexão. Um número crescente de conexões não significa apenas mais concorrência: significa mais memória e mais trabalho de gerenciamento. Se a aplicação abre conexões acima da capacidade real do banco, as filas podem aparecer no pool antes de qualquer consulta lenta no servidor.

Confira o limite e o uso atual:

SELECT    count(*) AS connections,    stateFROM pg_stat_activityGROUP BY stateORDER BY connections DESC;

O pool deve ter tamanho compatível com CPU, memória, carga e duração das transações. Pooling não corrige consultas lentas, mas evita que cada requisição crie um processo novo. Se o tempo passa esperando uma conexão disponível, o banco pode parecer culpado quando o gargalo está entre a aplicação e o servidor.

Manutenção atrasada

Autovacuum atrasado deixa versões antigas ocupando espaço e aumenta o trabalho de leitura. Em tabelas com muita atualização, o efeito aparece como bloat, estatísticas defasadas e mais páginas para varrer. A saúde do autovacuum deve ser analisada junto com o comportamento da tabela, não apenas pelo número global de workers.

Uma transação antiga pode impedir a remoção dessas versões. Para localizar sessões com transações abertas há muito tempo:

SELECT    pid,    usename,    datname,    state,    xact_start,    clock_timestamp() - xact_start AS transaction_age,    left(query, 180) AS queryFROM pg_stat_activityWHERE xact_start IS NOT NULLORDER BY xact_start;

O problema de manutenção não se resolve simplesmente executando VACUUM em tudo. Primeiro é preciso entender por que a limpeza não acompanha a taxa de mudança e se há uma transação mantendo o horizonte antigo.

Uma sequência que reduz tentativa

Para um incidente de lentidão, a sequência mais útil costuma ser: registrar sessões e esperas; verificar bloqueadores; comparar consultas e planos; medir leituras e escrita; revisar conexões; então avaliar estatísticas e autovacuum. Cada etapa elimina classes de causa antes da próxima mudança.

O registro precisa incluir horário e duração. Sem isso, a análise posterior depende de memória e de relatos como “foi depois do almoço”, que não distinguem sazonalidade, carga de negócio e regressão de plano.

Próximo passo

PostgreSQL lento não é uma categoria de diagnóstico. É o ponto de entrada para descobrir se o tempo está sendo gasto em lock, plano, I/O, conexão ou manutenção.

Se o ambiente apresenta lentidão recorrente e a equipe ainda reage com reinícios ou índices criados no escuro, um diagnóstico gratuito do PostgreSQL ajuda a organizar evidências, riscos e a ordem de correção.