Uma query ruim pode derrubar o Postgres inteiro? O perigo escondido no work_mem

Desenvolvimento Web · "Loop"

Uma consulta lenta costuma parecer um problema localizado: ela demora, recebe timeout e a aplicação segue atendendo o restante. Só que algumas queries conseguem pressionar a memória até afetar outras conexões, matar processos e, dependendo da proteção oferecida pelo ambiente, empurrar o PostgreSQL inteiro para uma recuperação após crash.

O ponto mais traiçoeiro é que work_mem não funciona como um teto de memória por consulta. A documentação do PostgreSQL define o parâmetro como um limite base por operação de sort ou hash antes que a execução comece a usar arquivos temporários. Uma mesma query pode ter várias dessas operações, executadas por vários processos, enquanto outras sessões fazem o mesmo.

Quatro megabytes podem virar muito mais

O valor padrão de work_mem é 4 MB. Isso não significa que uma consulta receberá no máximo 4 MB. Um plano pode conter um sort para ORDER BY, outro para um merge join, uma agregação por hash e uma tabela de hash para o join. Cada nó elegível tem seu próprio orçamento.

Operações de hash ainda recebem outro multiplicador. O limite usado por elas é calculado a partir de work_mem * hash_mem_multiplier, cujo padrão atual é 2. Com as configurações padrão, uma única hash table pode chegar a aproximadamente 8 MB antes de derramar parte do trabalho para o disco.

SHOW work_mem;
SHOW hash_mem_multiplier;

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(total)
FROM orders
GROUP BY customer_id
ORDER BY sum(total) DESC;

O EXPLAIN ANALYZE ajuda a encontrar Sort Method, Memory Usage, quantidade de batches e uso de disco. Ele deve ser usado com cuidado em produção porque executa a consulta de verdade. Para queries que alteram dados, a inspeção exige ainda mais cautela e uma estratégia explícita de transação.

Paralelismo multiplica o orçamento

Quando o planner escolhe um plano paralelo, os nós abaixo de Gather ou Gather Merge são executados por workers. O processo líder também pode participar do trabalho. Como cada worker é um processo separado, limites como work_mem são aplicados individualmente.

A documentação chama atenção para esse efeito: uma consulta com quatro workers pode consumir até cinco vezes os recursos de uma execução sem paralelismo, contando o líder. Isso não torna a execução paralela ruim. Ela pode reduzir bastante a latência, mas precisa entrar na conta de capacidade do servidor.

Agora coloque concorrência nessa equação. Dez conexões executando planos com múltiplos sorts, hashes e workers não compartilham um único balde de work_mem. É por isso que aumentar o parâmetro globalmente para eliminar arquivos temporários pode trocar um gargalo de I/O por picos perigosos de RAM.

Nem toda estrutura consegue escapar para o disco

Muitas operações conseguem dividir dados em lotes e continuar no disco quando a memória fica curta. Outras estruturas internas precisam manter o estado durante toda a consulta. Um teste publicado pela ClickHouse em 28 de setembro explorou um desses casos usando uma CTE recursiva com UNION.

Para remover duplicatas entre iterações, a execução mantém uma tabela com valores já visitados. No cenário construído para o benchmark, essa estrutura crescia até cerca de 1 GiB por consulta e não obedecia a um mecanismo útil de spill baseado em work_mem. Com várias conexões concorrentes, o teste conseguiu diferenciar ambientes que encerravam consultas, derrubavam sessões ou deixavam o sistema operacional matar processos por falta de memória.

O resultado merece contexto: o artigo foi produzido por um fornecedor de PostgreSQL gerenciado e seu próprio produto venceu a comparação. A metodologia e a explicação do executor são úteis, mas os números não devem virar um ranking universal. Configuração da instância, supervisores, limites do sistema operacional, armazenamento e escolhas do provedor mudam bastante o comportamento.

Por que um processo morto pode reiniciar o cluster

O PostgreSQL usa processos separados para atender conexões. Se um backend termina de forma anormal, o postmaster precisa assumir que a memória compartilhada pode ter ficado inconsistente. O caminho seguro é encerrar os demais processos e iniciar a recuperação. Para a aplicação, uma query problemática deixa de ser apenas uma requisição com erro e passa a derrubar conexões saudáveis.

Serviços gerenciados tentam evitar esse estágio com estratégias diferentes. Alguns encerram a consulta, outros matam a sessão antes que o kernel intervenha, e há ambientes que colocam limites mais conservadores para preservar o cluster. Essa camada de proteção é uma característica operacional importante, mas raramente aparece na primeira tela de comparação entre provedores.

Como reduzir o raio de explosão

Não existe um número mágico para work_mem. O valor adequado depende dos planos, da concorrência e da memória disponível. Ainda assim, algumas práticas deixam o risco mais visível:

  • Dimensione pela concorrência: considere operações por plano, workers paralelos e número de sessões simultâneas, não apenas uma query isolada.
  • Evite aumentar o valor global por reflexo: uma configuração maior pode ser útil em uma sessão de relatório e perigosa como padrão de toda a aplicação.
  • Observe spills e planos: arquivos temporários, sorts externos, batches de hash e mudanças de plano conforme os dados crescem são sinais úteis.
  • Coloque limites no trabalho: statement_timeout contém duração, enquanto pool de conexões e limites do ambiente ajudam a controlar concorrência e memória. Nenhum deles substitui os demais.
  • Teste com volume parecido com produção: um plano barato com milhares de linhas pode mudar de forma quando a tabela chega a milhões.
  • Entenda a proteção do provedor: pergunte o que acontece quando uma query excede a RAM e quanto tempo o serviço leva para se recuperar.

Também vale separar perfis de carga. Endpoints transacionais, relatórios, jobs e consultas administrativas não precisam usar o mesmo pool nem as mesmas configurações. Permitir que uma tela de análise dispute memória sem controle com o tráfego principal é uma decisão arquitetural, mesmo quando aconteceu por falta de decisão.

A lição prática não é desconfiar de toda CTE recursiva ou desligar o paralelismo. É parar de tratar work_mem como a memória máxima de uma query. Quando o banco está sob concorrência, a unidade real de consumo aparece espalhada pelo plano, pelos workers e pelas conexões. A configuração só fica segura quando essa multiplicação entra na conta.

Faça a conta por operação concorrente

Uma estimativa conservadora começa com conexões ativas × operações de memória por plano × work_mem e depois considera workers paralelos. Não use max_connections × work_mem como previsão exata, mas use o exercício para perceber se uma configuração global cabe no pior pico plausível.

40 consultas concorrentes
× 3 nós de sort/hash
× 2 workers paralelos
× 64 MB de work_mem
= 15 GB de orçamento potencial

O valor real depende do plano e alguns nós não atingem o limite, mas a conta impede tratar 64 MB como custo da conexão inteira. Compare o resultado com RAM disponível depois de shared_buffers, sistema operacional e outros processos.

Ajuste localmente e deixe evidência

Para uma consulta conhecida, prefira SET LOCAL work_mem dentro da transação, ou uma role específica, em vez de elevar o cluster inteiro. Ative log_temp_files com um limiar útil, acompanhe pg_stat_statements e compare EXPLAIN (ANALYZE, BUFFERS) com cuidado em ambiente seguro.

Memória maior não corrige uma consulta que faz trabalho desnecessário. Um N+1 pode multiplicar operações pequenas; uma paginação por offset profundo obriga o banco a percorrer linhas que serão descartadas. Primeiro reduza o trabalho, depois dê memória ao que restou.

Fontes

Leia também