Amélioration pour les paresseux : comment PostgreSQL 12 améliore la performance

PostgreSQL 12, la dernière version de « la meilleure base de données relationnelle open source au monde », sera disponible dans quelques semaines (si tout se passe comme prévu). Cela correspond à un calendrier habituel — une nouvelle version avec de nombreuses nouvelles fonctionnalités est publiée chaque année, et, honnêtement, c'est impressionnant. C'est pourquoi je suis devenu un membre actif de la communauté PostgreSQL.

À mon avis, contrairement aux versions précédentes, PostgreSQL 12 ne contient pas une ou deux fonctionnalités révolutionnaires (comme le partitionnement ou le parallélisme des requêtes). J'ai plaisanté en disant que la principale caractéristique de PostgreSQL 12 est une plus grande stabilité. N'est-ce pas ce dont vous avez besoin lorsque vous gérez des données critiques pour votre entreprise ?

Mais PostgreSQL 12 ne s'arrête pas là : avec les nouvelles fonctionnalités et améliorations, les applications fonctionneront mieux, et il vous suffit de réaliser une mise à niveau !

(Eh bien, peut-être aussi de reconstruire les index, mais dans cette version, ce n'est pas si grave que nous avons l'habitude de le penser.)

Ce serait super — mettre à niveau PostgreSQL et profiter immédiatement d'améliorations significatives sans effort supplémentaire. Il y a quelques années, j'ai analysé la mise à jour de PostgreSQL 9.4 à PostgreSQL 10 et j'ai vu comment l'application s'était accélérée grâce au meilleur parallélisme des requêtes dans PostgreSQL 10. Et surtout, il ne m'a presque rien été demandé (il suffisait de définir un paramètre de configuration max_parallel_workers).

Vous conviendrez qu'il est pratique que les applications fonctionnent mieux immédiatement après une mise à niveau. Et nous faisons de notre mieux pour ravir les utilisateurs, car PostgreSQL en a de plus en plus.

Et comment une simple mise à niveau vers PostgreSQL 12 vous rendra-t-elle heureux ? Je vais vous le raconter maintenant.

Améliorations significatives de l'indexation

Sans indexation, une base de données n'ira pas loin. Comment trouver rapidement des informations autrement ? Le système fondamental d'indexation de PostgreSQL s'appelle l'arbre B. Ce type d'index est optimisé pour les systèmes de stockage.

Nous utilisons simplement l'instruction CREATE INDEX ON some_table (some_column), et PostgreSQL fait le gros du travail pour maintenir la pertinence de l'index pendant que nous insérons, mettons à jour et supprimons constamment des valeurs. Tout fonctionne tout seul, comme par magie.

Mais les index de PostgreSQL ont un problème — ils s'étendent et occupent un espace disque inutile, réduisant ainsi la performance de l'extraction et de la mise à jour des données. Par « gonflement », j'entends le maintien inefficace de la structure d'index. Cela peut être, ou non, lié aux tuples orphelins que supprime VACUUM (merci pour l'information à Peter Geoghegan (Peter Geoghegan)). Le gonflement de l'index est particulièrement visible dans les charges de travail où l'index est activement modifié.

PostgreSQL 12 améliore considérablement le fonctionnement des index en arbre B, et des expériences avec des tests de type TPC-C ont montré que l'espace utilisé n'est maintenant, en moyenne, que 40 % de moins. Nous dépensons désormais moins de temps non seulement pour l'entretien des index en arbre B (c'est-à-dire pour les opérations d'écriture), mais aussi pour l'extraction des données, car les index sont devenus beaucoup plus petits.

Les applications qui mettent à jour activement leurs tables — généralement des applications OLTP (traitement des transactions en temps réel) — utiliseront beaucoup plus efficacement le disque et traiteront les requêtes. Plus il y a d'espace sur le disque, plus la base de données dispose d'espace pour croître sans mettre à niveau l'infrastructure.

Certaines stratégies de mise à niveau nécessitent la reconstruction des index en arbre B pour profiter de ces avantages (par exemple, pg_upgrade ne reconstruit pas automatiquement les index). Dans les versions précédentes de PostgreSQL, la reconstruction de gros index dans des tables entraînait des temps d'arrêt significatifs, car pendant ce temps, aucune modification ne pouvait être apportée. Mais dans PostgreSQL 12, il y a une autre fonctionnalité intéressante : il est désormais possible de reconstruire les index en parallèle avec la commande REINDEX CONCURRENTLY, afin d'éviter complètement le temps d'arrêt.

Dans PostgreSQL 12, il y a également d'autres améliorations de l'infrastructure d'indexation. Une autre chose où la magie a fait son effet est le journal de préécriture, aussi connu sous le nom de WAL (write-ahead log). Le journal de préécriture enregistre chaque transaction dans PostgreSQL en cas de panne et pour la réplication. Les applications l'utilisent pour l'archivage et la récupération à un moment donné. Bien sûr, le journal de préécriture est écrit sur disque, ce qui peut affecter les performances.

Dans PostgreSQL 12, les coûts des enregistrements WAL générés par les index GiST, GIN et SP-GiST lors de la construction de l'index ont été réduits. Cela offre plusieurs avantages tangibles : les enregistrements WAL occupent moins d'espace disque et les données se reproduisent plus rapidement, par exemple lors d'une restauration après un plantage ou lors d'une récupération à un moment donné. Si vous utilisez ces index dans vos applications (par exemple, les applications géospatiales basées sur PostGIS utilisent beaucoup l'index GiST), c'est une autre fonctionnalité qui améliorera considérablement les performances sans effort de votre part.

Le partitionnement - plus, mieux, plus rapide

Dans PostgreSQL 10, le partitionnement déclaratif. Dans PostgreSQL 11, son utilisation est devenue beaucoup plus simple. Dans PostgreSQL 12, le dimensionnement des sections peut être modifié.

Dans PostgreSQL 12, la performance du système de partitionnement s'est considérablement améliorée, surtout si la table contient des milliers de sections. Par exemple, si une requête ne touche que quelques sections d'une table contenant des milliers d'entre elles, elle s'exécutera beaucoup plus rapidement. La performance a été améliorée non seulement pour ce type de requêtes. Vous remarquerez également comment les opérations INSERT dans les tables avec de nombreuses sections ont été accélérées.

L'enregistrement des données via COPY - d'ailleurs, c'est un excellent moyen de charger des données en masse et voici un exemple avec JSON - dans les tables partitionnées de PostgreSQL 12 est également devenu plus efficace. Avec COPY, c'était déjà rapide, mais dans PostgreSQL 12, c'est vraiment rapide.

Grâce à ces avantages, PostgreSQL peut stocker des ensembles de données encore plus grands, et leur extraction est devenue plus simple. Et sans aucun effort de votre part. Si votre application possède de nombreuses sections, par exemple, si elle écrit des données de séries temporelles, une simple mise à niveau améliorera considérablement ses performances.

Et même si cette amélioration n'est pas tout à fait dans la catégorie « nous avons mis à niveau et nous nous réjouissons », dans PostgreSQL 12, il est possible de créer des clés étrangères qui font référence à des tables partitionnées, rendant le travail avec le partitionnement un véritable plaisir.

Les requêtes WITH se sont beaucoup améliorées

Lorsque un patch a été appliqué pour les expressions de table généralisées intégrées (également appelées CTE, également des requêtes WITH), j'étais impatient d'écrire un article sur la joie des développeurs d'applications utilisant PostgreSQL. C'est l'une de ces fonctionnalités qui accélérera l'application. Si, bien sûr, vous utilisez des CTE.

Je remarque souvent que les débutants en SQL aiment utiliser les CTE : si on les écrit d'une certaine manière, on a vraiment l'impression d'écrire un programme impératif. Personnellement, j'aimais réécrire ces requêtes pour m'en passer. sans CTE et améliorer les performances. Tout est différent maintenant.

PostgreSQL 12 permet d'intégrer un certain type de CTE sans effets secondaires (SELECT), qui n'est utilisé qu'une seule fois près de la fin de la requête. Si je tenais des statistiques sur les requêtes avec CTE que je réécrivais, la plupart d'entre elles tomberaient dans cette catégorie. Cela aide les développeurs à écrire un code compréhensible, qui fonctionne désormais aussi rapidement.

De plus, PostgreSQL 12 optimise l'exécution des SQL par lui-même, vous n'avez rien à faire. Et bien que je n'aurai probablement plus besoin d'optimiser ces requêtes, c'est super que PostgreSQL continue à travailler sur l'optimisation des requêtes.

Just-in-Time (JIT) — est maintenant activé par défaut.

Dans les systèmes PostgreSQL 12 prenant en charge LLVM La compilation JIT est activée par défaut. Tout d'abord, vous bénéficiez du support JIT pour certaines opérations internes, et deuxièmement, les requêtes avec des expressions (le plus simple exemple est x + y) dans les listes de sélection (qui se trouvent après SELECT), les agrégats, les expressions avec des clauses WHERE et d'autres peuvent utiliser JIT pour améliorer les performances.

Puisque JIT est activé par défaut dans PostgreSQL 12, la performance s'améliorera par elle-même, mais je conseille de tester l'application dans PostgreSQL 11, où JIT a fait ses débuts, afin de mesurer la performance des requêtes et de déterminer s'il est nécessaire d'ajuster quelque chose.

Et qu'en est-il des autres nouvelles fonctionnalités de PostgreSQL 12 ?

PostgreSQL 12 offre une multitude de nouvelles fonctionnalités intéressantes — de la possibilité d'explorer des données JSON à l'aide d'expressions SQL/JSON standard à l'authentification multifactorielle avec l'option clientcert=verify-full, des colonnes générées et bien d'autres encore. Il y a de quoi écrire un article séparé.

Tout comme PostgreSQL 10, PostgreSQL 12 améliorera les performances globales immédiatement après la mise à niveau. Vous aurez, bien sûr, votre propre façon de faire — testez l'application dans des conditions similaires dans un système de production avant d'activer les améliorations, comme je l'ai fait avec PostgreSQL 10. Même si PostgreSQL 12 est déjà plus stable que je ne l'aurais pensé, ne négligez pas de tester soigneusement les applications avant de les mettre en production.

Source : habr.com