10 ans d’améliorations de l’optimiseur de PostgreSQL

Les améliorations de l’optimiseur de PostgreSQL sur 10 ans

• En tant que chercheur en optimisation de requêtes, l’auteur a utilisé au cours des dix dernières années l’optimiseur de requêtes open source sophistiqué de PostgreSQL dans ses travaux
• Après dix ans passés à travailler sur les bases de données, il s’est demandé à quel point PostgreSQL s’était amélioré
• Il existait de nombreux changelogs et avis, mais aucune comparaison empirique solide n’a été trouvée ; il a donc décidé d’exécuter lui-même le Join Order Benchmark (JOB) sur PostgreSQL 8 à 16
• Pour chaque version de la base de données, la latence des requêtes au 90e centile a été mesurée

Configuration de l’environnement de test

• Chaque version de PostgreSQL a été compilée avec GCC 13.2 dans un conteneur Docker sous Arch Linux
• Pour mesurer la qualité de l’optimiseur de requêtes, shared_buffers a été réglé à 8GB (suffisamment grand pour contenir l’intégralité de la base)
• work_mem a été fixé à 8MB pour toutes les versions
• Chaque requête a été exécutée une première fois pour réchauffer le cache, puis cinq fois supplémentaires, et la latence médiane a été retenue

Améliorations globales des performances

• Les performances en queue de distribution de PostgreSQL se sont nettement améliorées, mais les versions 13 à 16 ont été globalement stables
• En comparant les versions 8 et 16, l’optimiseur de PostgreSQL a presque divisé par deux la latence en queue sur les dix dernières années
• Il est possible d’examiner la distribution complète des requêtes (voir l’échelle logarithmique)

Quantifier les progrès via une analyse de régression

• Une analyse de régression permet de vérifier si la pente de baisse de la latence est significative et de quantifier le gain apporté par chaque version de PostgreSQL
• En effectuant une régression entre le numéro de version majeure de PostgreSQL et la latence des requêtes, chaque nouvelle version majeure de PostgreSQL apporte en moyenne un gain de performance de 15 % sur le Join Order Benchmark
• Cependant, un modèle linéaire est sans doute un mauvais indicateur pour mesurer ces évolutions

Points supplémentaires à considérer

• Bien sûr, toutes ces améliorations ne viennent pas uniquement de l’optimiseur de requêtes. Les améliorations du moteur d’exécution, des workers parallèles à la compilation just-in-time (JIT), y contribuent également
• Il serait aussi intéressant d’examiner comment le plan d’exécution de chaque requête du JOB a évolué au fil des années

Points clés

• Mettez à niveau votre base de données ! Passer de PostgreSQL 8 à 16 peut fortement réduire la latence en queue de votre charge de travail
• Les chercheurs doivent garder à l’esprit que PostgreSQL est une cible mouvante
  • Les recherches sur l’optimisation de requêtes apprise ont été comparées à différentes versions de PostgreSQL au fil du temps
  • Si une ancienne technique améliore PostgreSQL de 30 % et une technique récente de 25 %, cela peut simplement signifier que la technique récente est comparée à une version de PostgreSQL plus performante

L’avis de GN⁺

• PostgreSQL a continuellement amélioré ses performances, mais l’ampleur des gains diminue dans les versions récentes. Cela peut s’expliquer par le fait qu’un niveau d’optimisation déjà important a été atteint. Les progrès futurs devraient sans doute se concentrer sur des domaines plus ciblés

• Les gains de performance ne viennent pas uniquement de l’optimiseur de requêtes, mais aussi des améliorations du moteur d’exécution. Des optimisations ont été apportées sur divers plans, comme le parallélisme ou la compilation JIT

• Cette expérience est limitée au Join Order Benchmark ; dans un contexte réel, les gains de performance peuvent varier selon la charge de travail. Il est préférable d’effectuer des benchmarks adaptés à son propre usage

• Les chercheurs doivent prendre en compte l’évolution des versions de PostgreSQL. Même avec le même algorithme, le gain relatif peut varier selon la version de PostgreSQL utilisée comme référence

• Si vous utilisez une ancienne version de PostgreSQL, une mise à niveau mérite d’être sérieusement envisagée. Les versions récentes affichent des gains de performance marqués par rapport à celles d’il y a dix ans. Il faut bien sûr tenir compte des éventuels problèmes de compatibilité liés à la migration

[GN⁺]

Commentaires Hacker News

Résumé :

• Pour bien résoudre les problèmes d’optimisation, les données sur les coûts sont essentielles. PostgreSQL a encore une large marge de progression, notamment par le manque de données sur la latence des syscall et de statistiques sur les clés étrangères.
• Dans le cas des requêtes de grande taille, il faudrait introduire des techniques comme le deferred planning, qui permet de modifier le plan pendant l’exécution.
• L’apprentissage automatique est pertinent pour améliorer les modèles de prédiction des coûts. En revanche, l’utiliser directement pour établir un plan de requête n’est pas approprié.
• Définir un shared buffer très grand pour charger toutes les données en mémoire et faire des benchmarks ainsi rend difficile l’évaluation correcte des performances réelles de l’optimiseur.
• Le compilateur JIT provoque encore souvent uniquement une dégradation des performances.
• Comme la numérotation des versions de PostgreSQL a changé à partir de la version 10, il pourrait aussi être intéressant d’analyser l’évolution des performances en considérant les versions 8.x et 9.x comme des versions majeures.
• Les graphiques présentés à eux seuls ne permettent pas de confirmer clairement une tendance d’amélioration des performances. La tail latency semble s’être améliorée, mais le reste peut varier selon les cas.
• Créer un excellent optimiseur est un défi considérable.
• On se demande si l’optimisation des requêtes se situe au niveau SQL ou au niveau algorithmique.