10 anos de melhorias no otimizador do PostgreSQL

Melhorias no otimizador do PostgreSQL ao longo de 10 anos

• Como pesquisador em otimização de consultas, venho usando ao longo dos últimos 10 anos o sofisticado otimizador de consultas open source do PostgreSQL em minhas pesquisas
• Fiquei curioso para saber o quanto o PostgreSQL melhorou nesses 10 anos desde que comecei a trabalhar com bancos de dados
• Havia muitos registros de mudanças e opiniões, mas não encontrei uma comparação empírica robusta, então decidi executar eu mesmo o Join Order Benchmark (JOB) do PostgreSQL 8 ao 16
• Para cada versão do banco de dados, foi registrada a latência de consulta no percentil 90

Configuração do ambiente de teste

• Cada versão do PostgreSQL foi compilada com GCC 13.2 dentro de um contêiner Docker no Arch Linux
• Para medir a qualidade do otimizador de consultas, shared_buffers foi definido como 8GB (grande o suficiente para manter todo o banco de dados)
• work_mem foi definido como 8MB para todas as versões
• Cada consulta foi executada uma vez para aquecer o cache e depois mais 5 vezes, registrando-se a latência mediana

Melhoria geral de desempenho

• O desempenho de cauda do PostgreSQL melhorou bastante, mas as versões 13 a 16 foram, em geral, estáveis
• Comparando a versão 8 com a 16, o otimizador do PostgreSQL reduziu quase pela metade a latência de cauda ao longo dos últimos 10 anos
• É possível examinar a distribuição completa das consultas (observe a escala logarítmica)

Quantificando as melhorias com análise de regressão

• Com análise de regressão, é possível verificar se a inclinação de queda da latência é significativa e quantificar quanto de melhoria cada versão do PostgreSQL trouxe
• Ao fazer regressão do número da versão principal do PostgreSQL em relação à latência de consulta, cada nova versão principal do PostgreSQL traz, em média, um ganho de desempenho de 15% no Join Order Benchmark
• No entanto, um modelo linear é discutivelmente uma métrica ruim para medir essa mudança

Considerações adicionais

• Claro, nem todas essas melhorias se devem ao otimizador de consultas. Melhorias no mecanismo de execução, de workers paralelos a compilação just-in-time (JIT), também têm papel nisso
• Também seria interessante investigar como o plano de execução de cada consulta do JOB mudou ao longo dos anos

Pontos principais

• Atualize seu banco de dados! Migrar do PostgreSQL 8 para o 16 pode melhorar bastante a latência de cauda da sua carga de trabalho
• Pesquisadores devem ter em mente que o PostgreSQL é um alvo em movimento
  • Pesquisas sobre otimização de consultas aprendida têm comparado diferentes versões do PostgreSQL ao longo do tempo
  • O fato de uma técnica anterior melhorar o PostgreSQL em 30% e uma técnica mais recente melhorar em 25% não significa necessariamente que a técnica mais recente seja pior; ela pode estar sendo comparada com um PostgreSQL mais poderoso

Opinião do GN⁺

• O PostgreSQL vem melhorando continuamente o desempenho, mas nas versões mais recentes o ritmo de avanço parece menor. Isso pode ser porque boa parte da otimização importante já foi feita. Melhorias futuras provavelmente devem se concentrar em áreas mais específicas

• Não é apenas o otimizador de consultas que contribui para o ganho de desempenho; melhorias no mecanismo de execução também têm impacto. Há otimizações sendo feitas em vários aspectos, como processamento paralelo e compilação JIT

• Este experimento é limitado ao Join Order Benchmark, então o efeito de melhoria de desempenho em ambientes reais pode variar conforme a carga de trabalho. Vale a pena rodar benchmarks adequados às características do seu próprio ambiente

• Pesquisadores precisam considerar as mudanças de versão do PostgreSQL. Mesmo com o mesmo algoritmo, o ganho relativo de desempenho pode variar dependendo da versão do PostgreSQL usada como base de comparação

• Se você ainda usa uma versão antiga do PostgreSQL, vale considerar seriamente um upgrade. Em comparação com versões de 10 anos atrás, as versões atuais apresentam melhorias de desempenho bem expressivas. Claro, é preciso também levar em conta questões como compatibilidade na migração

[GN⁺]

Comentários do Hacker News

Resumo:

• Para resolver bem problemas de otimização, dados sobre custos são importantes. O PostgreSQL ainda tem bastante espaço para melhorias. Em especial, faltam dados como latência de syscall e estatísticas de chaves estrangeiras.
• No caso de consultas de grande porte, é necessário introduzir técnicas como deferred planning, que permitem ajustar o plano durante a execução.
• O uso de aprendizado de máquina é apropriado para melhorar modelos de previsão de custo. Usá-lo diretamente para montar planos de consulta não parece adequado.
• Fazer benchmark com o shared buffer grande o suficiente para manter todos os dados na memória dificulta avaliar corretamente o desempenho real do otimizador.
• O compilador JIT ainda costuma, em muitos casos, apenas causar perda de desempenho.
• Como a numeração de versões do PostgreSQL mudou a partir da versão 10, também seria interessante analisar a evolução de desempenho considerando as versões 8.x e 9.x como versões major.
• Só com os gráficos apresentados, é difícil confirmar com clareza a tendência de melhora de desempenho. A tail latency parece ter melhorado, mas o restante pode variar caso a caso.
• Criar um excelente otimizador é um desafio considerável.
• Fica a dúvida se a otimização de consultas está no nível do SQL ou no nível dos algoritmos.