PostgreSQL 11.0

64
22
oct.
2018
Base de données

PostgreSQL est un système de gestion de base de données relationnelle. La version 11 est sortie ce 18 octobre 2018.

Logo PostgreSQL

Les principales nouveautés, détaillées en seconde partie de la dépêche, se sont concentrées sur la gestion des bases ayant un très gros volume de données.

Amélioration du partitionnement

PostgreSQL permet déjà le partitionnement depuis longtemps, il s’agit de stocker dans plusieurs tables distinctes des données qui sont logiquement dans une seule table. L’utilisateur de la base ne voit pas cette répartition et cela permet de stocker des données sur plusieurs systèmes de fichiers afin d’avoir des caractéristiques différentes en fonction des données. Par exemple, les données récentes sont stockées sur SSD et les données plus anciennes sont stockées sur disque dur.

Il était possible de faire des partitions sur des listes de données ou sur des intervalles. Il est maintenant possible de faire des partitions sur un hash de la clef afin de répartir les données de manière aléatoire. Ceci permet de répartir les opérations de lecture et écriture sur plusieurs stockages.

Il est aussi possible d’avoir une table catch‐all pour les partitions afin de récupérer les données qui ne se retrouvent dans aucune des autres tables. Les clefs primaires et étrangères ainsi que les index et les déclencheurs (triggers) peuvent être maintenant déclarés au niveau de la table principale et seront répercutés sur toutes les tables membres de la partition. PostgreSQL peut aussi dorénavant changer les données de partition automatiquement si la clef de répartition est modifiée.

Les performances de lecture des tables partitionnées ont aussi été améliorées avec une nouvelle stratégie d’élimination des partitions. La fonctionnalité UPSERT est maintenant disponible pour les tables partitionnées.

Disponibilité des transactions dans les procédures stockées

Les procédures stockées sont disponibles dans PostgreSQL depuis longtemps, mais il n’était pas possible, jusqu’à présent, d’y créer des transactions. C’est désormais possible. Voici un exemple avec PL/Python :

CREATE PROCEDURE transaction_test1()
LANGUAGE plpythonu
AS $$
for i in range(0, 10):
    plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i)
    if i % 2 == 0:
        plpy.commit()
    else:
        plpy.rollback()
$$;

CALL transaction_test1();

Amélioration des performances

Parallélisme

Plusieurs opérations peuvent maintenant êtres faites en parallèle pour profiter des nombreux cœurs des processeurs actuels. Ainsi, les tables partitionnées peuvent être lues en parallèle mais aussi lorsqu’une requête SELECT inclut une clause UNION.

La création d’index est aussi parallélisée, ainsi que d’autres commande DDL (Data Definition Language) comme CREATE TABLE ou CREATE MATERIALIZED VIEW.

Compilation à la volée

Un compilateur à la volée (Just in Time) optionnel est maintenant disponible. Il permet d’optimiser les requêtes complexes pour améliorer leur vitesse d’exécution. PostgreSQL utilise pour cela le compilateur LLVM. Pour l’activer, il faut ajouter jit = on dans le fichier de configuration ou bien définir la variable de session SET jit = on.

Simplification = rapidité

Il n’est plus nécessaire de réécrire toute la table quand l’appel ALTER TABLE … ADD COLUMN … DEFAULT … avec une valeur par défaut qui n’est pas NULL est fait. Cela améliore beaucoup la vitesse d’exécution de cette commande, si la table est de taille importante.

Expérience utilisateur

Les mots clefs quit et exit sont maintenant reconnus par l’interface en ligne de commande (psql) pour terminer le processus, en plus de \q (enfin !).

Aller plus loin

  • # Github

    Posté par (page perso) . Évalué à 10. Dernière modification le 22/10/18 à 12:30.

    Bon timing pour cette dépêche avec GitHub qui est indisponible à cause de MySQL.

    ➡️🚪

    « Rappelez-vous toujours que si la Gestapo avait les moyens de vous faire parler, les politiciens ont, eux, les moyens de vous faire taire. » Coluche

    • [^] # Re: Github

      Posté par . Évalué à 10.

      Tu es sûr que microsoft n’a pas remplacé MySql par access ?

      ---->[]

  • # Partitionnement

    Posté par (page perso) . Évalué à 10.

    Ces nouvelles fonctionnalités autour du partitionnement sont toujours bonnes à pendre !

    Le partitionnement avec Postgresql est en fait un corollaire de la fonctionnalité des tables héritées. Cela a l'avantage que c'est plus générique et donc (comme toujours avec PG) très cohérent, mais le désavantage qu'il faut beaucoup de travail pour obtenir une solution de partitionnement complète: gérer l'insertion dans la table appropriée, gérer la création de nouvelles tables au fur et à mesure du temps dans le cas de partitions historiques, création d'index, maintenance (vacuum) des tables…

    À noter d'autres avantages du partitionnement : les routines un peu lourdes, comme une modification de la table, un ajout d'index ou le vacuum, peuvent être faites partition par partition, parfois même en parallèle, ce qui transforme une opération de maintenance de 48 heures en une opération de 2 heures, et permet de profiter de son week-end.

    Les requêtes bénéficient également du partitionnement, en sélectionnant dès la phase de planification les sous-tables appropriées. Attention cependant ! Dans le cas du partitionnement historique, utiliser "now()" ou "current_date" n'éliminera pas les partitions non pertinentes. En effet, au moment de la planification, la requête ne connaît pas la date (ou l'heure) courante, puisque le plan pourrait être réutilisé ultérieurement, et donc inclut toutes les partitions. Il faut donc bien coder ses conditions de dates en dur, par exemple via le script qui lance la requête.

    • [^] # Re: Partitionnement

      Posté par . Évalué à 6. Dernière modification le 25/10/18 à 01:00.

      mais le désavantage qu'il faut beaucoup de travail pour obtenir une solution de partitionnement complète: gérer l'insertion dans la table appropriée, gérer la création de nouvelles tables au fur et à mesure du temps dans le cas de partitions historiques, création d'index,

      Justement la nouvelle mouture simplifie tout ça, les insertions sont gérées automatiquement, un index unique global sur la table mère est maintenant possible et référence les valeurs des partitions.
      Plus de CASE dans le trigger pour aiguiller les insertions et plombant les performances au passage.

      Je concède qu'il faut toujours gérer les partitions supplémentaires mais un tuple dont la valeur de la clef de partitionnement changerait au point de devoir changer de partition, se trouve maintenant déplacée automatiquement.

      ps: Je n'ai pas compris pourquoi tu veux lyncher les nouvelles fonctionnalités du partitionnement…

      • [^] # Re: Partitionnement

        Posté par (page perso) . Évalué à 6.

        ps: Je n'ai pas compris pourquoi tu veux lyncher les nouvelles fonctionnalités du partitionnement…

        Au contraire, je les révère, et leur donne un satisfecit :)

        Mon commentaire s'adresse plutôt à la situation courante, et je vois donc avec joie que beaucoup de soucis ne seront plus qu'un mauvais souvenir. En particulier, si je comprends bien, il y a maintenant 2 phases d'élimination de partitions, à la planification et à l’exécution, ce qui permet d'éliminer les problèmes avec now() et current_date que j'évoquais.

        J'ai donc hâte de mettre à jour mes bases, même si c'est pas pour tout de suite.

  • # Au passage : contribution

    Posté par . Évalué à 1.

    Au passage, je vous informe de la mise en ligne d'un document
    sur les nouvelles fonctionnalités de PostgreSQL 11 :

    https://github.com/dalibo/workshops/blob/master/fr/110-postgresql_11.md

    (sous licence Postgresql).

Suivre le flux des commentaires

Note : les commentaires appartiennent à ceux qui les ont postés. Nous n'en sommes pas responsables.