Introduction

Depuis quelques années, PostgreSQL s’est doté de nombreuses améliorations pour le traitement des grosses volumétries.

Ce premier article va tenter de les lister, nous verrons qu’elles peuvent être de différents ordres :

• Parallélisation
• Amélioration intrinsèque du traitement des requêtes
• Partitionnement
• Méthodes d’accès
• Tâches de maintenance
• Ordres SQL

Afin de conserver de la clarté, l’explication de chaque fonctionnalité restera succincte.

Note : cet article a été écrit durant la phase de développement de la version 11. J’ai intégré des nouveautés de la version 11. Tant que celle-ci n’est pas sortie, ces nouveautés peuvent être retirées.

Je remercie au passage Guillaume Lelarge pour la relecture de cet article ;).

Table des matières

SQL

TABLESAMPLE (9.5)

L’ordre TABLESAMPLE (apparu avec la version 9.5) permet de faire une requête sur un échantillon. Cela permet d’avoir un aperçu du résultat. Comme en statistique, plus l’échantillon sera important, plus le résultat sera proche du résultat réel.

GROUPING SETS (9.5)

Toujours avec la version 9.5, PostgreSQL dispose de nouvelles clauses permettant de faire des agrégations multiples appelées GROUPING SETS. Les nouveaux agrégats sont : GROUPING SETS, ROLLUP, CUBE.

Voir cet article de Depesz : Waiting for 9.5 – Support GROUPING SETS, CUBE and ROLLUP

À noter que la version 10 apporte des gains très significatifs grâce à l’amélioration des fonctions de hachage.

Héritage sur les foreign tables (9.5)

Depuis la version 9.5, il est possible de déclarer des foreign tables comme tables enfants. Il est ainsi possible de distribuer les données sur différents serveurs et d’y accéder depuis une seule instance. Cette technique s’apparente à du sharding.

Voir cet article de Michael Paquier : Postgres 9.5 feature highlight - Scale-out with Foreign Tables now part of Inheritance Trees

Parallélisation

PostgreSQL étant multi-processus, le traitement d’une requête ne se faisait que sur un coeur. On retrouve de nombreux posts où les utilisateurs se plaignent de ce fonctionnement :

• Query parallelization for single connection in Postgres
• Any way to use >1 Core in PostgreSQL for a single Connection/Query?

Depuis la version 9.6, le moteur est capable de mobiliser plusieurs processus (appelé workers) pour le traitement d’une requête. Cette avancée majeure a été l’aboutissement de plusieurs années de travail. Il a fallu mettre en place toute une infrastructure pour permettre au moteur d’utiliser plusieurs processus. Voir cet article de Robert Haas : Parallelism Progress

Parcours séquentiel (9.6)

Depuis la version 9.6, PostgreSQL est capable d’utiliser plusieurs processus pour paralléliser l’opération de lecture d’une table. Cette opération correspond au noeud Parallel Seq Scan.

Parcours d’index (10)

La version 10 a permis d’étendre la parallélisation aux parcours d’index.

Ainsi le moteur est maintenant capable d’utiliser plusieurs processus pour ces types de parcours (uniquement pour les index de type BTree) :

• Index Scan
• Bitmap heap scan
• Index Only Scan

Jointures (9.6, 10, 11)

En plus de la parallélisation des parcours séquentiels, la version 9.6 apportait la possibilité de paralléliser les opérations de jointure aux noeuds suivants :

• Nested-loop
• Hash join

La version 10 a permis d’étendre la parallélisation au noeud de type merge join.

Enfin, la version 11 apporte un gros changement avec le parallel hash join. Avec les versions précédentes, chaque worker devait construire sa propre table de hachage. Il y avait une grosse perte d’efficacité :

• Plusieurs workers faisaient en réalité la même opération (donc utilisation de plus de ressources de type CPU)
• Une même table de hachage existait plusieurs fois en mémoire (donc utilisation de plus de ressources de type IO)

Le parallel hash join permet aux workers de paralléliser le remplissage d’une seule table de hachage, partagée.

L’auteur principal de cette fonctionnalité a écrit un excellent article : Parallel Hash for PostgreSQL

Agrégation (9.6)

Toujours avec la version 9.6, le moteur est capable d’utiliser plusieurs workers pour réaliser des opérations d’agrégation (COUNT, SUM…).

En réalité, chaque worker fait une agrégation partielle (Partial Aggregate), puis un noeud parent se charge de faire l’agrégation finale (Finalize Aggregate).

Union d’ensembles (11)

La version 11 apporte la possibilité de paralléliser les opérations d’union d’ensemble (noeud append), par exemple lors de l’utilisation d’un UNION ou lorsque des tables sont héritées.

Méthodes d’accès

On confond souvent index et méthodes d’accès en employant régulièrement le terme “index”.

En réalité, au sens large, dans le domaine des bases de données, un index est une méthode d’accès.

Index BRIN (9.5)

Depuis la version 9.5, PostgreSQL propose un type d’index particulier : BRIN pour Block Range INdexes.

Ce type d’index contient le résumé d’un ensemble de blocs. Ils sont donc très compacts et tiennent facilement en mémoire.

Ils sont particulièrement adaptés aux fortes volumétries avec des requêtes manipulant un gros volume de données. Attention, il est très important qu’il y ait une forte corrélation entre les données et leur emplacement pour que leur efficacité soit optimale.

J’ai présenté le fonctionnement de ce type d’index lors du PGDay France 2016 à Lille : Index BRIN - Fonctionnement et usages possibles

BLOOM filters (9.6)

Depuis la version 9.6, il est possible d’utiliser des filtres de Bloom. Sans rentrer dans les détails, ce type de structure de donnée permet d’affirmer avec certitude que l’information recherchée ne se trouve pas un ensemble. Inversement, l’information peut (avec une certaine probabilité) se trouver dans un autre ensemble.

L’intérêt des filtres bloom, c’est qu’ils sont très compacts et permettent de répondre à des recherches multi-colonnes à partir d’un seul index.

Voir cet article de Depesz : Waiting for 9.6 – Bloom index contrib module

Partionnement

La partitionnement existait sous forme d’héritage de table. Cependant, cette approche avait l’inconvénient de nécessiter la mise en place de triggers pour router les écritures dans les bonnes tables. L’impact sur les performances était important.

La version 10 intègre une gestion native du partitionnement. Ainsi, il n’est plus nécessaire de mettre en place des triggers. Les opérations de maintenance sont facilitées et les performances sont améliorées.

Types de partitionnement (10, 11)

PostgreSQL supporte le partitionnement par :

• Liste - LIST (10)
• Intervalle - RANGE (10)
• Hachage - HASH (11)

Voir ces articles de Depesz :

• Waiting for PostgreSQL 10 – Implement table partitioning
• Waiting for PostgreSQL 11 – Add hash partitioning

Index (11)

La version 11 apporte également une gestion plus facilitée des index. La création d’un index sur une table partitionnée entraîne sa création sur toutes les partitions. De même, il est possible d’avoir un index unique (à condition qu’il porte sur la clé de partitionnement). Il est également possible d’avoir une clé étrangère sur une table partitionnée.

Exclusion de partition (11)

Le partition pruning consiste à exclure les partitions inutiles. Postgres s’appuie sur les contraintes d’exclusion pour écarter des partitions à la planification.

L’algorithme n’a pas été prévu pour gérer un nombre important de partitions. Sa complexité est linéaire en fonction du nombre de partitions (How many table partitions is too many in Postgres?)

Pour y remédier, la version 11 intègre un nouveau algorithme de recherche bien plus performant : Faster Partition Pruning.

Enfin, le moteur ne pouvait exclure des partitions que lors de la planification. Avec la version 11, le moteur est capable d’exclure une partition durant l’exécution. Cette fonctionnalité s’appelle Runtime Partition Pruning.

Jointures et agrégations (11)

La version 11 apporte de nouveaux algorithmes de jointure et d’agrégation. L’idée est de réaliser les opérations de jointures et d’agrégation partition par partition lors d’une jointure entre deux tables partitionnées (Voir Partition and conquer large data with PostgreSQL 10).

Améliorations intrinsèques

Fonctions de hachage (10)

Les fonctions de hachage ont été améliorées dans la version 10. Ainsi, les opérations d’agrégat (GROUP BY, GROUPING SETS, CUBE…) ainsi que les noeuds type bitmap scans bénéficient de ces améliorations. Le temps d’exécution de certaines requêtes a été divisé par deux!

Amélioration de l’exécuteur (10)

L’exécuteur a été amélioré dans la version 10, il est maintenant plus performant pour le traitement des expressions. Ce thread mentionne des gains très significatifs : Faster Expression Processing

Voir cet extrait du livre PostgreSQL - Architecture et notions avancées de Guillaume Lelarge et Julien Rouhaud.

Amélioration des tris

Abbreviated keys (9.5)

L’algorithme de tri a été revu avec la version 9.5, celui-ci exploite mieux le cache des processeurs. Les gains annoncés étaient entre 40 et 70% (voir Use abbreviated keys for faster sorting of text datums).

Amélioration des tris externes (10)

La version 10 apporte également des gains très significatifs lorsqu’ils sont réalisés sur disque. Certaines requêtes ont vu leur temps d’exécution divisé par deux.

Just-In-time (11)

La version 11 intègre une infrastructure pour le Just-In-Time (JIT) ou littéralement “compilation à la volée”. Le JIT consiste à compiler la requête pour générer un bytecode qui sera exécuté.

À nouveau, les gains annoncés sont impressionnants comme en témoignent ces slides de conférence : JITing PostgreSQL using LLVM

Taches de maintenance

VACUUM FREEZE (9.6)

Avant la version 9.6, un VACUUM FREEZE entraînait la lecture de l’intégralité de la table, même si des lignes avaient déjà été “freezées”. La version 9.6 ajoute une information supplémentaire dans la visibility map afin de savoir si un bloc a déjà été freezé. Cette information permet au moteur de sauter les blocs déjà freezés.

Réduction des parcours d’index lors des opérations de VACUUM (11)

Lors d’une opération de vacuum simple (où le moteur va nettoyer les lignes périmées), le moteur était capable de sauter les blocs où il savait qu’il n’y avait aucune ligne à traiter. Néanmoins il devait quand même parcourir l’index, ce qui peut s’avérer coûteux avec une table volumineuse. La version 11 permet d’éviter ce phénomène.

Un nouveau paramètre fait son apparition : vacuum_cleanup_index_scale_factor.

Le moteur peut éviter le parcours de l’index si deux conditions sont réunies :

• Aucun bloc ne doit être supprimé
• Les statistiques sont bien à jour

Le moteur considère que les statistiques ne sont pas à jour si plus de : [nombre de lignes insérées] > vacuum_cleanup_index_scale_factor * [nombre de lignes dans la table]

Création d’index parallélisée (11)

Lors de la création d’un index, le moteur peut utiliser plusieurs processus pour réaliser l’opération de tri. En fonction du nombre de processus, le temps de création de l’index peut être divisé de 2 à 4 environ.

Pushdown dans les Foreign Data Wrapper (9.6, 10)

Petit rappel, les Foreign Data Wrapper permettent d’accéder à des données externes. C’est l’implémentation dans PostgreSQL de la norme SQL/MED (pour “Management of External Data”).

Un FDW permet d’accéder à tout type de donnée externe pour peu qu’un FDW existe (voir Foreign data wrappers).

La communauté maintient un FDW permettant de se connecter à une instance PostgreSQL : postgres_fdw. Mais ce n’est pas tout, certaines opérations peuvent être “poussées” au serveur distant. C’est ce qu’on appelle le pushdown.

Avant la version 9.6, une opération de tri ou une jointure se traduisait par le rapatriement de toutes les données et le serveur effectuait le tri et/ou la jointure en local.

La version 9.6 inclut le “sort pushdown” et le “join pushdown”. Ainsi, l’opération de tri ou de jointure peut être réalisée par le serveur distant. D’une part, cela décharge le serveur exécutant la requête, d’autre part, le serveur distant va pouvoir utiliser l’algorithme de jointure adéquat ou un index pour le tri des données.

Enfin, la version 10 inclut aussi l’exécution des agrégations et jointures de type FULL JOIN sur le serveur distant.

Futur

Le feature freeze de la version 11 s’est terminé le 7 Avril : PostgreSQL 11 Release Management Team & Feature Freeze

Cela signifie que certaines fonctionnalités n’ont pas été implémentées, certaines étant jugées pas assez matures pour être intégrées. D’autres ne sont encore qu’à l’état de discussion ou de démonstration.

À noter que certaines fonctionnalités peuvent être retirées après le feature freeze si les développeurs considèrent qu’elles ne sont pas suffisamment stables ou que l’implémentation doit être revue.

Par chance, les développeurs des différentes sociétés qui contribuent au développement de Postgres, communiquent sur leur travaux en cours. Cela donne un aperçu des tendances des futures fonctionnalités.

Extension du système de stockage

La communauté travaille pour rendre le système de stockage modulaire (pluggable storage). Ainsi, le moteur pourrait avoir différents moteurs de stockage. Dans les travaux en cours, on peut compter :

• Le stockage colonne (associé à la vectorisation)
• La compression des tables
• Le stockage en mémoire (In-memory)
• zheap : ce moteur permettrait de mettre à jour les enregistrements directement dans les tables. Sans dupliquer les données dans les tables selon le modèle MVCC. Et ainsi, s’affranchir de la fragmentation et des vacuum.

Extension du JIT

L’auteur du JIT prévoit de l’étendre au reste du moteur (agrégation, hachage, tris…).

Dans les pistes évoquées, il y aurait aussi la mise en cache et le partage du bytecode entre toutes les sessions. Cela permettrait d’utiliser le JIT même dans le cas d’un traffic OLTP.

Vectorisation

L’idée de la vectorisation serait de traiter les données par batch afin d’exploiter des instructions SIMD des processeurs.

Couplé à un stockage colonne, les gains peuvent être impressionnants :

• Vectorized Postgres (VOPS extension)

FDW et exécution asynchrone

Le sujet du sharding revient régulièrement. D’une certaine façon, l’usage des FDW répond en partie à ce besoin. Néanmoins, PostgreSQL a encore des progrès à faire dans ce domaine. Par exemple, s’il effectue une requête d’agrégation portant sur plusieurs tables distantes, le moteur doit requêter chaque serveur distant de manière séquentielle. Une piste d’amélioration serait de requêter tous les serveurs distants de manière asynchrone. Ainsi, l’opération serait parallélisée sur tous les serveurs distants. Voir cette présentation FDW-based Sharding Update and Future.

Conclusion

PostgreSQL rocks!

– Guillaume Lelarge

Cet article nous a permis de voir les différentes avancées de PostgreSQL dans le monde des grosses volumétries. Il reste évidemment encore du travail, mais ce qui a déjà été réalisé est important et permet de gérer des bases imposantes.