Performance Tuning em PostgreSQL: Roteiro Prático para Achar a Causa Raiz Antes de Mexer em Parâmetro

Performance tuning em PostgreSQL costuma começar do jeito errado: alguém sobe shared_buffers, muda work_mem às cegas ou sai criando índice em cima de índice, na esperança de que algum parâmetro "resolva" a lentidão. Na prática, tuning de PostgreSQL é um processo de eliminação — query lenta, falta de índice, I/O saturado e memória mal dimensionada têm sintomas parecidos (tudo fica "lento"), mas exigem diagnóstico e correção diferentes. Este roteiro cobre as quatro frentes mais comuns, na ordem em que vale investigar.

1. Identifique as queries que mais pesam, não as que "parecem" lentas

Antes de otimizar qualquer coisa, é preciso saber o que de fato consome tempo no banco. A extensão pg_stat_statements agrega estatísticas de execução por query normalizada (parâmetros diferentes, mesmo texto de query, mesma linha na view).

Se ainda não estiver habilitada:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Ela também precisa estar carregada em shared_preload_libraries, o que exige reinício do servidor — não é algo que se ativa só com CREATE EXTENSION se ainda não estiver na lista de bibliotecas pré-carregadas. Confira antes:

SHOW shared_preload_libraries;

Com a extensão ativa, as queries que mais consomem tempo total de execução (não apenas tempo médio) aparecem assim:

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Em versões PostgreSQL 12 ou anteriores, as colunas se chamam total_time e mean_time (sem o sufixo _exec_) — confirme o nome exato na sua versão antes de rodar a query, porque o erro de coluna inexistente é comum nesse ponto.

Ordenar por total_exec_time (impacto agregado) costuma ser mais útil do que ordenar por mean_exec_time (média por execução): uma query rápida chamada um milhão de vezes pode pesar mais no servidor do que uma query lenta chamada raramente.

2. Entenda o plano de execução antes de criar índice

Depois de identificar a query candidata, o passo seguinte é o plano de execução real, não o estimado:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

A opção BUFFERS mostra quantos blocos foram lidos do cache (shared hit) e quantos precisaram vir de disco (shared read) — é o dado mais direto para saber se o gargalo é I/O ou processamento. Sinais que costumam indicar problema real no plano:

• Seq Scan em tabela grande com um Filter que descarta a maior parte das linhas: candidato claro a índice.

• Estimativa de linhas muito diferente da contagem real (rows=X estimado vs. actual rows=Y): estatísticas desatualizadas — geralmente resolvido com ANALYZE na tabela, não com índice novo.

• Nested Loop com muitas iterações sobre uma tabela sem índice na coluna de junção: geralmente resolvido com índice na FK.

Só depois de ler o plano real é que faz sentido decidir se falta índice, se a query precisa ser reescrita, ou se é caso de ANALYZE.

3. Cruze com as estatísticas de uso de índice por tabela

A view pg_stat_user_tables mostra, por tabela, quantos scans sequenciais e quantos scans por índice aconteceram:

SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

Tabelas grandes com seq_scan alto e idx_scan baixo (ou zero) são candidatas a índice ausente — mas vale cruzar com o padrão de acesso real da aplicação: se a tabela é pequena, um scan sequencial pode ser mais barato que usar índice, e o planner do PostgreSQL normalmente já faz essa escolha corretamente.

A mesma view também expõe n_dead_tup (tuplas mortas aguardando limpeza) e last_autovacuum. Tabela com n_dead_tup alto e crescendo, com idx_scan OK mas performance piorando ao longo do tempo, costuma ser sintoma de autovacuum não acompanhando o volume de updates/deletes — não de falta de índice.

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Por padrão, o autovacuum dispara em uma tabela quando o número de tuplas mortas ultrapassa autovacuum_vacuum_threshold (padrão 50) mais autovacuum_vacuum_scale_factor (padrão 0,2, ou seja 20% do tamanho da tabela) multiplicado pelo total de tuplas. Em tabelas muito grandes, esse percentual fixo de 20% pode significar milhões de linhas mortas acumuladas antes do autovacuum disparar — nesses casos, é comum reduzir autovacuum_vacuum_scale_factor por tabela (via ALTER TABLE ... SET) em vez de mudar o parâmetro global.

4. Só então revise memória — e com base em medição, não em regra de bolso

Ajustar parâmetros de memória sem antes eliminar problema de query/índice/vacuum costuma mascarar o sintoma por um tempo e nada mais. Com o diagnóstico acima feito, os três parâmetros mais relevantes:

• shared_buffers: memória dedicada ao cache de páginas do PostgreSQL. Um ponto de partida comum citado pela comunidade é algo em torno de 25% da RAM do servidor em uma instância dedicada — não há um valor universal correto, e vale medir a taxa de acerto de cache (via pg_stat_statements/BUFFERS do passo 2) antes e depois de qualquer mudança.

• work_mem: memória usada por operação de ordenação/hash antes de gravar em disco (arquivos temporários). Se o plano do passo 2 mostrar Sort Method: external merge Disk em vez de quicksort (in memory), é sinal de que work_mem pode estar baixo para aquela operação — mas atenção: esse valor é por operação, não por conexão, então subir demais pode causar pressão de memória com muitas conexões simultâneas.

• effective_cache_size: não aloca memória, apenas informa ao planner uma estimativa de quanto cache de disco (SO + PostgreSQL) está disponível, influenciando a escolha entre índice e scan sequencial. Um valor conservador citado pela comunidade é cerca de metade da RAM disponível.

Os valores acima são pontos de partida amplamente citados pela comunidade PostgreSQL, não recomendações fixas da documentação oficial para todo ambiente — o dimensionamento correto depende de workload, tamanho de dataset e RAM disponível, e deve ser validado medindo o comportamento antes/depois no seu ambiente. Obs: se o ambiente for gerenciado (RDS/Aurora/Cloud SQL), confirme se esses parâmetros estão sob controle do parameter group do provedor antes de tentar alterá-los diretamente na sessão.

Validação

Depois de qualquer mudança — índice novo, ANALYZE, ajuste de autovacuum ou de memória —, feche o ciclo comparando o antes/depois nos mesmos pontos usados no diagnóstico:

-- reseta as estatísticas agregadas para medir o efeito da mudança isoladamente
SELECT pg_stat_statements_reset();

Depois de um período representativo de uso, repita a consulta do passo 1 e confirme que total_exec_time/mean_exec_time da query alvo caíram, e repita o EXPLAIN (ANALYZE, BUFFERS) para confirmar que o plano mudou como esperado (menos shared read, scan por índice em vez de sequencial, ordenação em memória em vez de disco). Mudança sem medição de antes/depois é achismo, mesmo quando "parece" ter melhorado.

Precisa de apoio para diagnosticar ou revisar a performance do seu ambiente PostgreSQL? Fale com a nossa equipe — contato: contato@siltechconsult.com.br — www.siltechconsult.com.br