10 años de mejoras en el optimizador de PostgreSQL

Mejoras en 10 años del optimizador de PostgreSQL

• Como investigador en optimización de consultas, durante la última década ha utilizado el sofisticado optimizador de consultas de código abierto de PostgreSQL para su investigación
• Le surgió la curiosidad de cuánto había mejorado PostgreSQL en los 10 años transcurridos desde que empezó a trabajar con bases de datos
• Había muchos registros de cambios y opiniones, pero no encontró una comparación empírica sólida, así que decidió ejecutar directamente el Join Order Benchmark (JOB) desde PostgreSQL 8 hasta 16
• Para cada versión de la base de datos, registró la latencia de consulta del percentil 90

Configuración del entorno de prueba

• Compiló cada versión de PostgreSQL usando GCC 13.2 dentro de un contenedor Docker en Arch Linux
• Para medir la calidad del optimizador de consultas, configuró shared_buffers en 8GB (lo bastante grande como para contener toda la base de datos)
• Configuró work_mem en 8MB para todas las versiones
• Cada consulta se ejecutó una vez para calentar la caché y luego 5 veces más, registrando la latencia mediana

Mejoras generales de rendimiento

• El rendimiento en la cola de PostgreSQL ha mejorado de forma importante, aunque las versiones 13 a 16 se mantuvieron en general estables
• Al comparar la versión 8 con la 16, el optimizador de PostgreSQL casi redujo a la mitad la latencia de cola en los últimos 10 años
• Se puede examinar la distribución completa de las consultas (teniendo en cuenta la escala logarítmica)

Cuantificación de las mejoras mediante análisis de regresión

• Se puede usar análisis de regresión para verificar si la pendiente descendente de la latencia es significativa y cuantificar cuánta mejora aporta cada versión de PostgreSQL
• Al hacer una regresión entre el número de versión principal de PostgreSQL y la latencia de consulta, cada nueva versión principal de PostgreSQL aporta en promedio una mejora de rendimiento del 15% en el Join Order Benchmark
• Sin embargo, el modelo lineal es, probablemente, una mala medida para cuantificar el cambio

Consideraciones adicionales

• Por supuesto, no todas estas mejoras se deben al optimizador de consultas. También influyen las mejoras en el motor de ejecución, desde workers en paralelo hasta compilación just-in-time (JIT)
• También sería interesante investigar cómo han cambiado con los años los planes de consulta de cada consulta de JOB

Puntos clave

• ¡Actualiza tu base de datos! Pasar de PostgreSQL 8 a 16 puede mejorar significativamente la latencia de cola de tu carga de trabajo
• Los investigadores deben tener en cuenta que PostgreSQL es un objetivo en movimiento
  • La investigación sobre optimización de consultas aprendida ha venido comparándose con distintas versiones de PostgreSQL a lo largo del tiempo
  • Que una técnica anterior mejore PostgreSQL en un 30% y una técnica más reciente lo mejore en un 25% no significa necesariamente que la más reciente sea peor; puede estar comparándose con un PostgreSQL más potente

Opinión de GN⁺

• PostgreSQL ha seguido mejorando su rendimiento, pero en las versiones recientes el margen de mejora se ha reducido. Esto podría deberse a que ya se han realizado optimizaciones considerables. Es probable que las mejoras futuras se enfoquen en áreas más específicas

• No solo el optimizador de consultas, sino también las mejoras en el motor de ejecución están contribuyendo al aumento del rendimiento. Se han realizado optimizaciones en distintos frentes, como el procesamiento en paralelo y la compilación JIT

• Este experimento se limita al Join Order Benchmark, por lo que el efecto de mejora en rendimiento en trabajos reales puede variar según la carga de trabajo. Sería recomendable realizar benchmarks ajustados a las características de cada entorno

• Los investigadores deben considerar los cambios de versión de PostgreSQL. Incluso con el mismo algoritmo, el aumento relativo de rendimiento puede variar según la versión de PostgreSQL usada como referencia

• Si todavía se está usando una versión antigua de PostgreSQL, vale la pena considerar seriamente una actualización. Las versiones actuales han logrado mejoras de rendimiento notables frente a las de hace 10 años. Por supuesto, habrá que considerar temas como la compatibilidad derivados de la actualización

[GN⁺]

Opiniones de Hacker News

Resumen:

• Para resolver bien los problemas de optimización, son importantes los datos sobre costos. PostgreSQL todavía tiene mucho margen de mejora. En particular, faltan datos sobre latencia de syscalls y estadísticas de foreign keys.
• En el caso de consultas grandes, hace falta introducir técnicas como deferred planning, que permiten modificar el plan durante la ejecución.
• El aprendizaje automático es adecuado para mejorar los modelos de predicción de costos. No es apropiado usar aprendizaje automático para generar directamente planes de consulta.
• Hacer benchmarks con un shared buffer grande, subiendo todos los datos a memoria, dificulta evaluar correctamente el rendimiento real del optimizador.
• El compilador JIT todavía suele provocar solo degradación de rendimiento en muchos casos.
• Como la numeración de versiones de PostgreSQL cambió a partir de la versión 10, también sería interesante analizar la evolución del rendimiento considerando las versiones 8.x y 9.x como versiones major.
• Solo con los gráficos presentados es difícil confirmar con claridad la tendencia de mejora del rendimiento. La tail latency parece haber mejorado, pero el resto puede variar en cada caso.
• Crear un optimizador excelente es una tarea bastante desafiante.
• Surge la duda de si la optimización de consultas se da a nivel de SQL o a nivel de algoritmos.