Otimização de consulta de banco de dados: índices, planos de execução e particionamento

Um único índice ausente pode transformar uma consulta de 2 milissegundos em uma verificação de tabela de 20 segundos. À medida que seu banco de dados cresce de milhares para milhões de linhas, a diferença entre uma consulta otimizada e uma não otimizada é a diferença entre um aplicativo responsivo e um que atinge o tempo limite sob carga. A otimização do banco de dados oferece o maior retorno em tempo de engenharia de qualquer trabalho de desempenho que você possa realizar.

Principais conclusões

• EXPLAIN ANALYZE é sua ferramenta de diagnóstico mais poderosa - aprenda a ler os planos de execução antes de otimizar qualquer coisa
• Escolha os tipos de índice estrategicamente: árvore B para igualdade e intervalo, GIN para texto completo e JSONB, índices parciais para subconjuntos filtrados
• Consultas N+1 são o fator destruidor de desempenho mais comum em aplicativos baseados em ORM – detecte-as antecipadamente com o registro de consultas
• O particionamento de tabelas torna-se essencial quando as tabelas excedem de 10 a 50 milhões de linhas, reduzindo o tempo de planejamento de consultas e permitindo o gerenciamento eficiente do ciclo de vida dos dados

---

Lendo Planos de Execução com EXPLAIN ANALYZE

Antes de otimizar qualquer consulta, você deve entender como o PostgreSQL a executa atualmente. EXPLAIN ANALYZE executa a consulta e mostra o plano de execução real com dados de tempo real.

Uma saída EXPLAIN ANALYZE básica mostra a estratégia escolhida pelo planejador, as contagens de linhas estimadas versus reais e o tempo gasto em cada etapa. As principais métricas nas quais focar são:

• Seq Scan – o banco de dados lê todas as linhas da tabela. Aceitável para tabelas pequenas (menos de 10.000 linhas), mas sinal de alerta para tabelas maiores.
• Index Scan – o banco de dados usa um índice para encontrar linhas correspondentes de forma eficiente. Isto é o que você deseja para consultas filtradas em tabelas grandes.
• Verificação somente do índice - o banco de dados responde à consulta inteiramente a partir do índice, sem tocar na tabela. O tipo de verificação mais rápido.
• Nested Loop – une tabelas examinando a tabela interna uma vez por linha na tabela externa. Eficiente quando a varredura interna usa um índice.
• Hash Join – cria uma tabela hash de um lado da junção e, em seguida, investiga-a com o outro. Eficiente para conjuntos de resultados maiores.
• Classificar -- uma etapa de classificação explícita, geralmente para ORDER BY. Fique atento às classificações que vazam para o disco (indicadas por "Método de classificação: mesclagem externa").

O que procurar

O sinal mais importante em um plano de execução é a lacuna entre as linhas estimadas e reais. Quando o PostgreSQL estima 10 linhas, mas encontra 100.000, ele escolheu o plano errado. Isso acontece quando as estatísticas da tabela estão desatualizadas – execute ANALYZE na tabela para atualizá-las.

Fique atento a varreduras sequenciais em tabelas grandes, classificações sem índices e loops aninhados com varreduras sequenciais na tabela interna. Cada um desses padrões indica um índice ausente ou uma consulta que precisa ser reescrita.

---

Tipos de índice e quando usá-los

O PostgreSQL oferece vários tipos de índice, cada um otimizado para diferentes padrões de consulta. Escolher o tipo certo é fundamental – um índice GIN em uma coluna que precisa apenas de verificações de igualdade desperdiça armazenamento e retarda gravações sem melhorar as leituras.

Índices de árvore B

Árvore B é o tipo de índice padrão e mais versátil. Ele suporta igualdade (=), intervalo (<, >, BETWEEN), classificação (ORDER BY) e correspondência de padrão de prefixo (LIKE 'abc%'). Para a maioria das colunas nas cláusulas WHERE, JOIN e ORDER BY, um índice de árvore B é a escolha correta.

Índices compostos combinam várias colunas em uma única árvore B. A ordem das colunas é importante: o índice on (status,created_at) suporta com eficiência a filtragem de consultas apenas por status ou por status ecreated_at, mas não apenas porcreate_at. Coloque a coluna mais seletiva primeiro e a coluna usada para filtragem de intervalo por último.

Índices GIN

Os índices GIN são excelentes na pesquisa em valores compostos. Eles são essenciais para pesquisa de texto completo (colunas tsvector), consultas de contenção JSONB (@>, ?) e consultas de sobreposição de array (&&, @>). Os índices GIN são maiores e mais lentos para atualizar do que os índices de árvore B, portanto, use-os somente onde a árvore B não puder atender ao padrão de consulta.

Para colunas JSONB que armazenam atributos flexíveis, um índice GIN na coluna inteira dá suporte a qualquer consulta baseada em chave. Para colunas onde você consulta apenas chaves específicas, um índice de árvore B em uma coluna ou expressão gerada é mais eficiente.

Índices Parciais

Índices parciais apenas indexam linhas que correspondem a uma condição WHERE. Eles são poderosos para tabelas em que as consultas filtram consistentemente um pequeno subconjunto de dados.

Por exemplo, se sua tabela de pedidos tiver 10 milhões de linhas, mas você consultar quase exclusivamente pedidos ativos (5% da tabela), um índice parcial em (customer_id,created_at) WHERE status = 'active' será 20x menor que um índice completo e igualmente rápido para suas consultas reais.

---

Detectando e corrigindo consultas N+1

O problema de consulta N+1 é o problema de desempenho mais comum em aplicações que usam ORMs. Ocorre quando o código carrega uma lista de N registros e, em seguida, executa uma consulta adicional por registro para carregar dados relacionados, resultando em N+1 consultas totais em vez de 1-2.

Como acontecem as consultas N+1

Considere carregar uma lista de pedidos com os nomes dos clientes. Uma implementação ingênua carrega a lista de pedidos (1 consulta) e, para cada pedido, carrega o cliente (N consultas). Com 100 pedidos, isso gera 101 viagens de ida e volta ao banco de dados. A 1 ms por consulta, isso equivale a 101 ms – mas sob carga simultânea com contenção do pool de conexões, pode facilmente chegar a 500 ms ou mais.

Métodos de detecção

1. Registro de consultas - habilite temporariamente o registro de consultas do PostgreSQL e procure consultas idênticas repetidas com valores de parâmetros diferentes
2. Registro em nível de ORM – Drizzle ORM, Prisma e TypeORM suportam registro de consulta que mostra cada instrução SQL executada
3. Ferramentas APM – Datadog, New Relic e Sentry podem agrupar consultas por endpoint e destacar padrões N+1 automaticamente
4. pg_stat_statements – esta extensão do PostgreSQL rastreia estatísticas de execução de consulta e revela modelos de consulta idênticos executados com frequência

Corrigindo consultas N+1

A correção depende do seu ORM e padrão de consulta:

• Carregamento rápido - diz ao ORM para carregar dados relacionados na consulta inicial usando JOINs. No Drizzle, use a opção with nos construtores de consulta.
• Carregamento em lote - colete todos os IDs de chave estrangeira e, em seguida, carregue os registros relacionados em uma única consulta WHERE id IN (...). Este é o padrão DataLoader.
• Desnormalização – para casos de uso com muita leitura, armazene os dados relacionados diretamente no registro pai. Troque complexidade de gravação por desempenho de leitura.

---

Técnicas de reescrita de consulta

Às vezes, a consulta em si precisa de reestruturação, e não apenas de índices melhores.

Subconsulta para conversão JOIN

As subconsultas correlacionadas são executadas uma vez por linha na consulta externa. Convertê-los em JOINs permite que o PostgreSQL use estratégias de junção mais eficientes.

Em vez de selecionar pedidos com uma subconsulta que procura a data do último pedido por cliente, reescreva-a como JOIN com uma tabela derivada ou uma função de janela. A versão JOIN permite que o PostgreSQL escolha entre loop aninhado, hash join e merge join com base na distribuição de dados.

Expressões de tabela comuns (CTEs)

No PostgreSQL 12 e versões posteriores, os CTEs são embutidos por padrão, o que significa que o otimizador pode enviar predicados para eles. Use CTEs para facilitar a leitura sem se preocupar com barreiras de desempenho. Para casos em que você deseja explicitamente a materialização (para evitar a reexecução de subconsultas caras), adicione a palavra-chave MATERIALIZED.

Funções de janela vs GROUP BY

Quando você precisa de linhas de detalhes e agregações, as funções de janela evitam a necessidade de autojunção ou subconsulta. Calcular um total acumulado, classificar dentro de grupos ou comparar cada linha com a média do grupo são mais eficientes com funções de janela do que com subconsultas correlacionadas.

---

Estratégias de particionamento de tabelas

Quando as tabelas crescem além de 10 a 50 milhões de linhas, mesmo as consultas bem indexadas ficam mais lentas devido à profundidade do índice, à sobrecarga de vácuo e à complexidade do planejador. O particionamento divide uma tabela grande em partes físicas menores, mantendo uma única interface de tabela lógica.

Tipos de partição

Particionamento de intervalo por data

O padrão mais comum é o particionamento mensal por uma coluna de carimbo de data/hora. Os dados de cada mês residem em sua própria partição. Consultas que filtram por data verificam automaticamente apenas as partições relevantes (remoção de partição).

Benefícios do particionamento baseado em tempo:

• Desempenho de consulta – consultas de dados recentes verificam apenas partições recentes
• Manutenção -- VACUUM e ANALYZE são executados mais rapidamente em partições menores
• Ciclo de vida dos dados – a eliminação de partições antigas é instantânea em comparação com a exclusão de milhões de linhas
• Eficiência de backup – faça backup apenas de partições recentes para recuperação pontual

Considerações sobre particionamento

O particionamento adiciona complexidade. Cada consulta deve incluir a chave de partição em sua cláusula WHERE para que a remoção da partição funcione. As restrições exclusivas devem incluir a chave de partição. As chaves estrangeiras que fazem referência a tabelas particionadas têm limitações. Inicie o particionamento somente quando tiver medido que o tamanho da tabela está causando degradação do desempenho.

---

Ajuste de configuração do PostgreSQL

A configuração padrão do PostgreSQL é conservadora, projetada para ser executada em hardware mínimo. As cargas de trabalho de produção se beneficiam do ajuste dos principais parâmetros.

Essas configurações devem ser ajustadas para seu hardware e carga de trabalho específicos. Monitore pg_stat_bgwriter, pg_stat_activity e pg_stat_user_tables para validar se as alterações melhoram o desempenho.

---

Perguntas frequentes

Quantos índices uma tabela deve ter?

Não há limite fixo, mas cada índice retarda as operações INSERT, UPDATE e DELETE porque o índice deve ser mantido. Uma boa regra é criar índices para colunas que aparecem nas cláusulas WHERE, JOIN ON e ORDER BY de suas consultas mais frequentes. Use pg_stat_user_indexes para encontrar índices não utilizados que podem ser eliminados.

Devo usar UUID ou chaves primárias inteiras para desempenho?

Chaves primárias inteiras (BIGSERIAL) são mais rápidas para junções e indexação porque são menores (8 bytes versus 16 bytes) e ordenadas naturalmente. Os UUIDs fornecem exclusividade global sem coordenação, o que é importante para sistemas distribuídos. Para a maioria dos aplicativos, use UUIDs para identificadores externos e números inteiros para junções internas.

Quando devo mudar de um único banco de dados para ler réplicas?

Quando a carga de trabalho de leitura excede 70-80% da capacidade do seu banco de dados ou quando as consultas de relatórios competem com consultas transacionais por recursos. As réplicas de leitura lidam com a carga de leitura enquanto a primária se concentra nas gravações. Isso normalmente é necessário entre 5.000 e 10.000 usuários simultâneos para um aplicativo da Web típico.

Como lidar com consultas lentas na produção sem tempo de inatividade?

Crie índices com a opção CONCURRENTLY para evitar o bloqueio da tabela. Use pg_stat_statements para identificar as consultas mais lentas. Implante otimizações de consulta por trás de sinalizadores de recursos. Para alterações de esquema que reescrevem tabelas, use ferramentas como pg_repack para reorganizar tabelas sem bloquear.

---

O que vem a seguir

A otimização do banco de dados é a base do desempenho da plataforma. Comece habilitando pg_stat_statements, identifique suas consultas mais lentas e trabalhe nelas sistematicamente com EXPLAIN ANALYZE. Adicione índices ausentes, corrija padrões N+1 e considere o particionamento para suas tabelas maiores.

Para uma visão mais ampla do desempenho, consulte nosso guia de pilares sobre escalando sua plataforma de negócios desde a inicialização até a empresa. Para saber mais sobre a próxima camada de otimização, leia nosso guia sobre estratégias de cache com Redis, CDN e cache HTTP.

ECOSIRE fornece otimização especializada de banco de dados para plataformas baseadas em PostgreSQL, incluindo Odoo ERP e aplicativos personalizados. Entre em contato conosco para uma auditoria de desempenho de banco de dados.

---

Publicado por ECOSIRE — ajudando empresas a escalar com soluções baseadas em IA em Odoo ERP, Shopify eCommerce e OpenClaw AI.