Os 5 Principais Erros na Solução de Problemas do PostgreSQL e Como Evitá-los

A maioria dos incidentes com PostgreSQL não começa com algo exótico. Eles começam com um endpoint lento, uma fila de sessões bloqueadas, uma tabela que cresceu mais rápido que o esperado ou um alerta de disco da partição WAL no pior momento possível. A parte difícil não é saber que o PostgreSQL tem índices, autovacuum, configurações de memória, locks e WAL. A parte difícil é saber qual deles importa agora e evitar as correções que pioram o próximo incidente.

Os erros de solução de problemas do PostgreSQL abaixo são os que vejo com mais frequência em operações reais. Eles não são apenas dicas de "ajuste este parâmetro". Cada um inclui o sintoma, a armadilha e uma maneira mais segura de raciocinar sobre o problema antes de alterar a produção.

Armadilha 1: Deficiência e Mau Uso de Índices

Uma das causas mais frequentes de desempenho lento no PostgreSQL é a indexação deficiente. Muitos DBAs dependem apenas de índices de chave primária criados automaticamente, não considerando padrões de consulta específicos, resultando em varreduras sequenciais frequentes e caras em vez de varreduras de índice eficientes.

Diagnóstico: Varreduras Sequenciais

Quando uma consulta tem desempenho ruim, comece pelo plano de execução. Use EXPLAIN simples primeiro se a consulta alterar dados ou puder ser executada por muito tempo. Use EXPLAIN (ANALYZE, BUFFERS) quando puder executá-la com segurança e precisar de tempo real e comportamento de E/S.

EXPLAIN ANALYZE
SELECT * FROM user_data WHERE last_login > '2023-10-01' AND status = 'active';

Evitando a Armadilha: Índices Compostos e Parciais

Se a consulta usa várias colunas na cláusula WHERE, um índice composto pode ajudar, mas a ordem das colunas depende da forma da consulta. Filtros de igualdade geralmente vêm antes de filtros de intervalo. Para uma consulta como WHERE status = 'active' AND last_login > ..., um índice em (status, last_login) é geralmente mais útil do que (last_login, status) porque o PostgreSQL pode restringir a um status e depois escanear o intervalo de datas. Para ORDER BY last_login DESC LIMIT 50, o melhor índice pode ser diferente.

Além disso, considere índices parciais para colunas que só precisam de indexação quando atendem a critérios específicos. Isso reduz o tamanho do índice e acelera a criação e manutenção do índice.

-- Cria um índice composto para a consulta de exemplo acima
CREATE INDEX idx_user_login_status ON user_data (status, last_login);

-- Cria um índice parcial apenas para usuários ativos
CREATE INDEX idx_active_users_email ON user_data (email) WHERE status = 'active';

Não descarte um índice apenas porque idx_scan é zero hoje. As estatísticas são redefinidas após reinicializações e redefinições manuais, e alguns índices existem para trabalhos raros, mas importantes. Uma revisão mais segura se parece com isso:

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Se um índice é grande, não usado durante um ciclo completo de negócios e não está respaldando uma restrição, é um candidato para remoção. Em sistemas ocupados, use DROP INDEX CONCURRENTLY para que leituras e gravações normais não sejam bloqueadas durante toda a operação.

Armadilha 2: Negligenciar o Daemon Autovacuum

O PostgreSQL usa o Controle de Concorrência Multiversão (MVCC). Atualizações e exclusões deixam versões antigas de linhas para trás até que o vacuum possa limpá-las. O Autovacuum não é uma manutenção opcional; é parte da operação normal do banco de dados. Ele remove tuplas mortas, atualiza as estatísticas do planejador através do autoanalyze e evita o wraparound do ID da transação.

Diagnóstico: Inchaço Excessivo

Ignorar o autovacuum leva ao inchaço da tabela, onde os sistemas de arquivos retêm espaço não utilizado, diminuindo significativamente as varreduras sequenciais. Se o autovacuum não consegue acompanhar o alto tráfego de gravação, o consumo de XID acelera.

Sintoma comum: alta espera de E/S, arquivos de tabela crescendo, estimativas de linhas desatualizadas e tamanhos de tabela que continuam subindo mesmo que a contagem de linhas ativas esteja praticamente estável.

Primeiras verificações úteis:

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Evitando a Armadilha: Ajustando o Autovacuum

Muitas equipes mantêm os padrões e depois descobrem que os padrões não são agressivos o suficiente para uma ou duas tabelas de alta rotatividade. Ajuste essas tabelas diretamente em vez de tornar todo o cluster ruidoso.

As configurações a entender primeiro são:

1. autovacuum_vacuum_scale_factor: a fração da tabela que deve mudar antes que o vacuum seja acionado. Tabelas grandes geralmente precisam de um valor mais baixo.
2. autovacuum_vacuum_threshold: o limite fixo de linhas adicionado ao cálculo do fator de escala.
3. autovacuum_vacuum_cost_delay e autovacuum_vacuum_cost_limit: controles de limitação. Tornar o autovacuum mais rápido pode aumentar a pressão de E/S, então monitore o sistema após alterá-los.

Ajuste-os globalmente no postgresql.conf ou por tabela usando os parâmetros de armazenamento, garantindo que o autovacuum seja executado com agressividade suficiente para gerenciar tabelas de alta rotatividade.

ALTER TABLE high_churn_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000
);

A armadilha é desabilitar o autovacuum porque ele apareceu durante um problema de desempenho. Se o autovacuum está constantemente visível, isso geralmente significa que ele está tentando acompanhar a rotatividade de gravação. Trate isso como um sinal de capacidade e ajuste, não como prova de que o autovacuum é a causa.

Armadilha 3: O Dilema do shared_buffers e work_mem

Configurar incorretamente a alocação de memória é uma armadilha comum que impacta diretamente o desempenho de E/S do banco de dados. Dois parâmetros dominam esta área: shared_buffers (armazenamento em cache de blocos de dados) e work_mem (memória usada para operações de classificação e hash dentro de uma sessão).

Diagnóstico: Alta E/S de Disco e Derramamentos

Se shared_buffers for muito pequeno para a carga de trabalho, o PostgreSQL depende mais do cache do sistema operacional e do armazenamento. Se work_mem for muito pequeno, as operações de classificação e hash derramam arquivos temporários para o disco. Se work_mem for muito grande globalmente, uma rajada de consultas concorrentes pode esgotar a memória.

Para verificar derramamentos de disco, use EXPLAIN ANALYZE. Procure por linhas indicando:

Sort Method: external merge Disk: 1234kB

Evitando a Armadilha: Alocação Estratégica de Memória

1. shared_buffers

Um ponto de partida comum para shared_buffers é cerca de 25% da RAM do sistema, mas não é uma regra universal. Instâncias menores, limites de memória de contêiner, cargas de trabalho mistas e plataformas de banco de dados gerenciadas podem alterar o valor correto. O PostgreSQL também se beneficia do cache de página do sistema operacional, então dar toda a memória para shared_buffers geralmente é um erro.

2. work_mem

Este parâmetro é específico da sessão. Uma armadilha comum é definir um work_mem global alto, que, quando multiplicado por centenas de conexões concorrentes, pode esgotar rapidamente a RAM do sistema, levando a swapping e travamentos. Em vez disso, defina um padrão global conservador e use SET work_mem para aumentá-lo para sessões específicas que executam relatórios complexos ou trabalhos em lote.

# Exemplo postgresql.conf
shared_buffers = 12GB   # Assumindo 48GB de RAM total
work_mem = 4MB          # Padrão global conservador

Para um trabalho de relatório, defina-o apenas para essa sessão ou transação:

BEGIN;
SET LOCAL work_mem = '128MB';
-- execute a consulta de relatório
COMMIT;

Lembre-se de que uma única consulta pode usar work_mem mais de uma vez. Uma consulta paralela com vários nós de classificação ou hash pode multiplicar a memória real usada.

Armadilha 4: Ignorar Consultas de Longa Duração e Locks

Consultas mal escritas ou erros de aplicação podem levar a conexões que permanecem ativas por horas, consumindo recursos e, pior, mantendo locks transacionais que bloqueiam outros processos. Não monitorar e gerenciar essas consultas é um grande risco de estabilidade.

Diagnóstico: Monitorando Sessões Ativas

Use a visão pg_stat_activity para identificar rapidamente consultas de longa duração, o SQL específico que estão executando e seu estado atual (por exemplo, aguardando lock, ativo).

SELECT pid, usename, client_addr, backend_start, state, query_start, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';

Para esperas de lock, inclua os PIDs bloqueadores:

SELECT a.pid,
       a.usename,
       a.state,
       now() - a.query_start AS age,
       pg_blocking_pids(a.pid) AS blocked_by,
       a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY age DESC;

Evitando a Armadilha: Timeouts e Terminação

Implemente timeouts de sessão e declaração para encerrar automaticamente processos descontrolados antes que causem danos significativos.

1. statement_timeout: O tempo máximo que uma única declaração pode ser executada antes de ser cancelada. Deve ser definido globalmente ou por conexão de aplicação.
2. lock_timeout: O tempo máximo que uma declaração espera por um lock antes de abandonar a tentativa.

Para mitigação imediata, você pode encerrar um processo problemático usando seu ID de Processo (PID) identificado em pg_stat_activity:

-- Define um timeout de declaração global de 10 minutos (600000 ms)
ALTER SYSTEM SET statement_timeout = '600s';

-- Encerra uma consulta específica usando seu PID
SELECT pg_terminate_backend(12345);

Prefira pg_cancel_backend(pid) primeiro quando a consulta é meramente cara. Ele cancela a declaração atual, mas mantém a sessão ativa. Use pg_terminate_backend(pid) quando a sessão está ociosa em transação, mantendo locks ou não respondendo ao cancelamento. Encerrar o backend errado pode reverter o trabalho que a aplicação ainda espera concluir, então capture a consulta, usuário, endereço do cliente e relação de bloqueio antes de agir.

Armadilha 5: Gerenciamento Pobre de WAL e Planejamento de Capacidade de Disco

O PostgreSQL depende do Write-Ahead Logging (WAL) para durabilidade e replicação. Os segmentos de WAL se acumulam rapidamente durante tráfego intenso de gravação. Uma armadilha operacional comum é não monitorar o uso de espaço em disco relacionado aos arquivos WAL ou definir parâmetros agressivos de WAL sem planejamento de armazenamento adequado.

Diagnóstico: Parada do Banco de Dados

O sintoma mais grave do gerenciamento pobre de WAL é o banco de dados ficar sem espaço na partição que contém pg_wal. Isso geralmente acontece quando o arquivamento falha, um standby está inativo ou um slot de replicação está retendo WAL para um consumidor que não existe mais.

Evitando a Armadilha: Dimensionamento e Arquivamento

1. Controlando o Tamanho do WAL

O parâmetro max_wal_size é um alvo de checkpoint, não uma cota de disco rígida. O PostgreSQL pode excedê-lo quando o WAL precisa ser retido para arquivamento, replicação ou recuperação. Definir muito baixo leva a checkpoints frequentes e E/S extra. Definir mais alto pode suavizar a pressão do checkpoint, mas você ainda precisa de monitoramento de disco e de arquivamento.

# Exemplo postgresql.conf
# Aumente para reduzir a frequência de checkpoint sob carga pesada
max_wal_size = 4GB 
min_wal_size = 512MB

2. Estratégia de Arquivamento

Se o arquivamento WAL (archive_mode = on) estiver habilitado para recuperação point-in-time (PITR) ou replicação, o processo de arquivamento deve ser confiável. Se o destino do arquivamento (por exemplo, armazenamento em rede) se tornar inacessível, o PostgreSQL continuará retendo os segmentos, eventualmente enchendo o disco local. Certifique-se de que o monitoramento esteja em vigor para alertar os DBAs se as falhas de archive_command persistirem.

Verifique também os slots de replicação:

SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

Um slot inativo com WAL retido crescente é uma das maneiras mais rápidas de encher um primário.

Uma Ordem Prática de Solução de Problemas

Quando você está sob pressão, use uma ordem fixa para não perseguir sintomas aleatoriamente:

1. Verifique o espaço em disco, especialmente o diretório de dados, pg_wal e locais de arquivos temporários.
2. Verifique as sessões ativas e bloqueadores em pg_stat_activity.
3. Verifique se o plano da consulta lenta está realmente fazendo o que você pensa com EXPLAIN (ANALYZE, BUFFERS).
4. Verifique a rotatividade da tabela, tuplas mortas e histórico do autovacuum.
5. Verifique o arquivamento WAL, lag de replicação e retenção de slot.
6. Altere uma coisa de cada vez e mantenha as evidências de antes/depois.

O maior erro na solução de problemas do PostgreSQL é tratar todo incidente como um problema de ajuste. Às vezes, a correção certa é um índice. Às vezes, é um timeout ausente. Às vezes, é um slot de replicação travado. O banco de dados geralmente fornece evidências suficientes; a disciplina é ler essas evidências antes de girar botões.