O banco de dados é frequentemente o maior gargalo de performance de sistemas web corporativos. À medida que o volume de registros na base de dados cresce, consultas SQL mal otimizadas (as chamadas Slow Queries) começam a travar conexões no servidor, elevando o uso de CPU para 100% e gerando lentidão para os usuários finais.
Neste artigo, apresentamos as principais técnicas de engenharia de banco de dados para analisar e otimizar a velocidade de consultas no PostgreSQL.
1. Localizando Slow Queries com pg_stat_statements
O primeiro passo é mapear quais consultas são de fato as vilãs de lentidão da aplicação. Para isso, ativamos a extensão nativa pg_stat_statements.
Ativação:
No arquivo de configuração postgresql.conf, adicione:
shared_preload_libraries = 'pg_stat_statements'Reinicie o PostgreSQL e crie a extensão no banco da aplicação:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;A consulta abaixo lista as dez instruções que mais consumiram tempo acumulado. No PostgreSQL 13 ou mais novo as colunas são total_exec_time e mean_exec_time. No 12, troque por total_time e mean_time.
SELECT
round(total_exec_time::numeric, 1) AS total_ms,
calls,
round(mean_exec_time::numeric, 1) AS mean_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;total_ms alto com calls alto é a consulta que vale o índice. mean_ms alto com poucos calls é um relatório pontual, não o gargalo do dia.
2. Decifrando o Plano de Execução com EXPLAIN ANALYZE
Com a consulta da lista, rode o plano de verdade. BUFFERS mostra se a leitura veio do disco:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM pedidos
WHERE cliente_id = 42
ORDER BY data_criacao DESC
LIMIT 20;O PostgreSQL devolve o plano, não as linhas. Um Seq Scan em pedidos com shared read alto é a tabela inteira no disco. Depois do índice da seção seguinte, o mesmo comando passa a mostrar Index Scan.
O que procurar no relatório:
- Seq Scan (Sequential Scan): Indica que o banco de dados teve que ler toda a tabela, linha por linha, no disco. Em tabelas com milhões de registros, isso é catastrófico.
- Index Scan: Significa que a busca utilizou um índice indexado na memória para achar o registro instantaneamente, que é o cenário ideal.
3. Criando Índices Inteligentes (B-Tree e Índices Compostos)
Para eliminar os Sequential Scans nas cláusulas WHERE, criamos índices nos campos mais buscados.
Exemplo:
Se o sistema realiza buscas frequentes combinando cliente_id e data_criacao: CREATE INDEX idx_pedidos_cliente_data ON pedidos (cliente_id, data_criacao DESC);
Evite o excesso: Cada novo índice criado acelera as consultas de leitura (SELECT), mas atrasa as operações de gravação (INSERT/UPDATE), pois o PostgreSQL precisa remontar a árvore de índices a cada alteração de registro.
4. Calibração de Memória Compartilhada
A configuração padrão do PostgreSQL é conservadora para rodar em hardware modesto. Para produção, calibre parâmetros fundamentais em seu postgresql.conf:
shared_buffers: Quantidade de memória RAM dedicada para o cache de dados. Configure para 25% a 40% da memória total disponível no servidor.work_mem: Memória usada para operações de ordenação interna (ORDER BY, DISTINCT) por conexão. Elevar de forma moderada evita gravação temporária de dados em disco durante ordenações.
Conclusão
Manter um banco de dados saudável exige monitoramento constante do plano de execução e das métricas de memória do servidor. Ao aplicar índices compostos planejados e ajustar as variáveis de buffers do PostgreSQL, sua aplicação corporativa operará de forma muito mais suave e econômica.