IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PostgreSQL Discussion :

PostgreSQL 12 est disponible et introduit les « colonnes calculées »


Sujet :

PostgreSQL

  1. #1
    Chroniqueur Actualités

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Mars 2013
    Messages
    8 382
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Mars 2013
    Messages : 8 382
    Points : 196 418
    Points
    196 418
    Par défaut PostgreSQL 12 est disponible et introduit les « colonnes calculées »
    PostgreSQL 12 est disponible et apporte des améliorations sur la performance des requêtes,
    cette version introduit les « colonnes calculées » et supporte les colonnes générées stockées

    Le groupe de développement de PostgreSQL a annoncé la disponibilité de PostgreSQL 12, la dernière version de la base de données open source. Il assure que PostgreSQL 12 inclut des améliorations notables sur la performance des requêtes, particulièrement sur les gros volumes de données et sur l'utilisation générale de l'espace disque. Cette version offre aux développeurs d'applications de nouvelles fonctionnalités comme le support des expressions SQL/JSON path, des optimisations sur l'exécution des requêtes « common tables expression » (WITH) et l'ajout des colonnes calculées.Concernant cette dernière partie, rappelons que WITH fournit une façon d'écrire les sous-requêtes pour utilisation dans une requête SELECT plus étendue. Les sous-requêtes, qui sont souvent appelées des expressions communes de tables (Common Table Expressions ou CTE), peuvent être considérées comme la déclaration d'une table temporaire n'existant que pour la requête. Une utilisation de cette fonctionnalité est de découper des requêtes complexes en parties plus simples.

    La communauté PostgreSQL poursuit les objectifs d'extensibilité et de robustesse de PostgreSQL en y incluant plusieurs ajouts à l'internationalisation et l'authenfication et en simplifiant l'administration de PostgreSQL.

    Cette version introduit également l'interface de stockage connectable permettant de développer sa propre méthode de stockage des données.

    « La communauté des développeurs de PostgreSQL a ajouté à PostgreSQL 12 des fonctionnalités qui apportent de la performance et des gains dans la gestion de l'espace disque que nos clients peuvent mettre en œuvre avec un minimum d'effort. Cette version apporte également une authentification de niveau entreprise, des fonctionnalités d'administration et le support de SQL/JSON. » déclare Dave Page, membre du noyau des développeurs du PostgreSQL Global Development Group. « Cette version poursuit l'objectif de simplifier la gestion des bases de données, quelle que soit la charge de travail, grande ou petite. Elle contribue également à consolider la réputation de flexibilité, sûreté et stabilité de PostgreSQL dans des environnements de production ».

    Nom : What-is-PostgreSQL.png
Affichages : 120785
Taille : 13,9 Ko

    Amélioration globale des performances

    PostgreSQL 12 apporte des améliorations de performance et de maintenance significatives à la fois au niveau du système d'indexation et du partitionnement.

    Les index B-tree, type d'indexation standard de PostgreSQL, ont été optimisés avec PostgreSQL 12 pour gérer les charges de travail où ces index sont fréquemment modifiés. L'équipe note que lors d'un test de performance TPC-C sur PostgreSQL 12, elle a pu mesurer une réduction moyenne de 40% de l'espace mémoire utilisé et un gain général de performance sur les requêtes.

    Des améliorations significatives ont également été mesurées sur les requêtes sur les tables partitionnées, particulièrement sur des tables avec des milliers de partitions pour lesquelles seul un sous-ensemble limité de données devait être récupéré. PostgreSQL 12 améliore également la performance lors de l'ajout de données dans les tables partitionnées avec INSERT et COPY. Enfin, cette version permet d'attacher une nouvelle partition à une table sans bloquer les requêtes.

    Il y a d'autres améliorations de l'indexation dans PostgreSQL 12 qui jouent sur les performances globales. L'équipe évoque entre autres l'abaissement du surcoût de génération des index GiST, GIN et SP-GiST dans les WAL, la possibilité de créer des index couvrants (la clause INCLUDE) sur les index GiST, la possibilité d'effectuer des requêtes de recherche des K plus proches voisins (KNN) avec l'opérateur de distance (<->) à l'aide d'index SP-GiST, et la commande CREATE STATISTICS qui supporte désormais les statistiques « most-common value » (MCV) pour produire de meilleurs plans de requête lorsque des colonnes distribuées de manière non uniforme sont utilisées.

    La compilation « Just-in-time » (JIT) à l'aide de LLVM, introduite dans PostgreSQL 11, est désormais activée par défaut. La compilation JIT peut apporter des gains de performance sur l'exécution de requêtes dans les clauses WHERE, les listes cibles, les agrégats, et d'autres opérations internes. Cette option est disponible sur les versions compilées ou packagées avec le support de LLVM.

    Améliorations de la conformité et des fonctionnalités du SQL

    PostgreSQL est connu pour sa conformité au standard SQL - une des raisons pour lesquelles il a été renommé de « POSTGRES » en « PostgreSQL » - et PostgreSQL 12 ajoute plusieurs fonctionnalités dans la continuité de l'implantation du standard avec des fonctionnalités avancées.

    PostgreSQL 12 introduit la possibilité d'effectuer des requêtes sur des documents JSON à l'aide d'expressions JSON path definies dans le standard SQL/JSON. Ces requêtes peuvent utiliser les mécanismes d'indexation de documents stockés au format JSONB pour accéder efficacement aux données.

    Les « Common table expressions », connues aussi sous le nom de requêtes « WITH », peuvent désormais être écrites en ligne avec PostgreSQL 12, ce qui peut augmenter les performances de nombreuses requêtes. Dans cette version, une requête « WITH » peut être écrite en ligne si elle n'est pas récursive, n'a pas d'effet de bord, et n'est référencée qu'une fois dans une partie suivante de la requête.

    PostgreSQL 12 introduit les « colonnes calculées ». Définies dans le standard SQL, ce type de colonne calcule sa valeur à partir du contenu d'autres colonnes de la même table. Dans cette version, PostgreSQL supporte les colonnes générées stockées, pour lesquelles la valeur calculée est stockée sur disque.

    Nom : post.png
Affichages : 7587
Taille : 49,3 Ko

    En savoir plus sur les colonnes générées

    PostgreSQL 12 est fourni avec une nouvelle fonctionnalité appelée colonnes générées. D'autres SGBDR populaires supportent déjà les colonnes générées en tant que « colonnes calculées » ou « colonnes virtuelles ». Avec Postgres 12, vous pouvez désormais l'utiliser également dans PostgreSQL.

    Qu'est-ce qu'une colonne générée ?

    Une colonne générée est un peu comme une vue, mais pour des colonnes. Voici un exemple de base:

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    db=# CREATE TABLE t (w real, h real, area real GENERATED ALWAYS AS (w*h) STORED);
    CREATE TABLE
    db=# INSERT INTO t (w, h) VALUES (10, 20);
    INSERT 0 1
    db=# SELECT * FROM t;
     w  | h  | area
    ----+----+------
     10 | 20 |  200
    (1 row)
     
    db=#

    Nous avons créé une table t avec deux colonnes régulières appelées w et h, et une colonne générée appelée area. La valeur de surface est calculée au moment de la création de la ligne et est conservée sur le disque.

    La valeur des colonnes générées est recalculée lorsque la ligne est mise à jour:

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    db=# UPDATE t SET w=40;
    UPDATE 1
    db=# SELECT * FROM t;
     w  | h  | area
    ----+----+------
     40 | 20 |  800
    (1 row)
     
    db=#

    Auparavant, cette fonctionnalité était généralement obtenue avec des déclencheurs, mais avec les colonnes générées, cela devient beaucoup plus élégant et plus propre.

    Quelques points à connaître sur les colonnes générées:
    • persistance : actuellement, la valeur des colonnes générées doit être conservée et ne peut pas être calculée à la volée au moment de la requête. Le mot clé «STORED» doit être présent dans la définition de la colonne.
    • l'expression : l'expression utilisée pour calculer la valeur doit être immuable, c'est-à-dire qu'elle doit être déterministe. Cela peut dépendre d'autres colonnes tant qu'il ne s'agit pas d'autres colonnes générées de la table.
    • index : les colonnes générées peuvent être utilisées dans les index, mais ne peuvent pas être utilisées comme clé de partition pour les tables partitionnées.
    • Copy et pg_dump : les valeurs des colonnes générées sont omises dans les sorties des commandes “pg_dump” et “COPY table”, car elles sont inutiles. Vous pouvez les inclure explicitement dans COPY en utilisant COPY (SELECT * FROM t) TO STDOUT plutôt que COPY t TO STDOUT.

    Exemple pratique

    Ajoutons la prise en charge de la recherche en texte intégral à une table en utilisant les colonnes générées. Voici un tableau qui stocke le texte intégral de toutes les pièces de Shakespeare:

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE scenes (
        workid text,       -- denotes the name of the play (like "macbeth")
        act integer,       -- the act (like 1)
        scene integer,     -- the scene within the act (like 7)
        description text,  -- short desc of the scene (like "Macbeth's castle.")
        body text          -- full text of the scene
    );

    Voici à quoi ressemblent les données:

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    shakespeare=# SELECT workid, act, scene, description, left(body, 200) AS body_start
    shakespeare-# FROM scenes WHERE workid='macbeth' AND act=1 AND scene=1;
     workid  | act | scene |   description   |                  body_start
    ---------+-----+-------+-----------------+----------------------------------------------
     macbeth |   1 |     1 | A desert place. | [Thunder and lightning. Enter three Witches]+
             |     |       |                 |                                             +
             |     |       |                 | First Witch: When shall we three meet again +
             |     |       |                 | In thunder, lightning, or in rain?          +
             |     |       |                 |                                             +
             |     |       |                 | Second Witch: When the hurlyburly's done,   +
             |     |       |                 | When the battle's lost and won.             +
             |     |       |                 |
    (1 row)

    Nous allons ajouter une colonne qui contiendra les lexèmes dans la valeur de "body". La fonction to_tsvector renvoie les lexèmes dont nous avons besoin:

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    shakespeare=# SELECT to_tsvector('english', 'move moving moved movable mover movability');
                 to_tsvector
    -------------------------------------
     'movabl':4,6 'move':1,2,3 'mover':5
    (1 row)

    Le type de la valeur renvoyée par to_tsvector est tsvector.

    Modifions le tableau pour ajouter une colonne générée:

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    ALTER TABLE scenes
      ADD tsv tsvector
        GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;

    Vous pouvez voir le changement avec \d:

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    shakespeare=# \d scenes
                                                    Table "public.scenes"
       Column    |   Type   | Collation | Nullable |                               Default
    -------------+----------+-----------+----------+----------------------------------------------------------------------
     workid      | text     |           | not null |
     act         | integer  |           | not null |
     scene       | integer  |           | not null |
     description | text     |           |          |
     body        | text     |           |          |
     tsv         | tsvector |           |          | generated always as (to_tsvector('english'::regconfig, body)) stored
    Indexes:
        "scenes_pkey" PRIMARY KEY, btree (workid, act, scene)

    Vous pouvez maintenant faire des recherches en texte intégral:

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    shakespeare=# SELECT
      workid, act, scene, ts_headline(body, q)
    FROM (
      SELECT
        workid, act, scene, body, ts_rank(tsv, q) as rank, q
      FROM
        scenes, plainto_tsquery('uneasy head') q
      WHERE
        tsv @@ q
      ORDER BY
        rank DESC
      LIMIT
        5
    ) p
    ORDER BY
      rank DESC;
      workid  | act | scene |                        ts_headline
    ----------+-----+-------+-----------------------------------------------------------
     henry4p2 |   3 |     1 | <b>Uneasy</b> lies the <b>head</b> that wears a crown.   +
              |     |       |                                                          +
              |     |       |    Enter WARWICK and Surrey                              +
              |     |       |                                                          +
              |     |       | Earl of Warwick
     henry5   |   2 |     2 | <b>head</b> assembled them?                              +
              |     |       |                                                          +
              |     |       | Lord Scroop: No doubt, my liege, if each man do his best.+
              |     |       |                                                          +
              |     |       | Henry V: I doubt not that; since we are well persuaded   +
              |     |       | We carry not a heart with us from hence
    (2 rows)
     
    shakespeare=#

    Nom : gre.png
Affichages : 7025
Taille : 8,3 Ko

    Authentification

    PostgreSQL étend son support des méthodes d'authentification robustes par plusieurs améliorations qui fournissent des fonctionnalités et sécurités additionnelles. Cette version introduit un chiffrement client et serveur pour l'authentification au travers des interfaces GSSAPI, ainsi que la possibilité de découverte de serveurs LDAP, si PostgreSQL est compilé avec le support d'OpenLDAP.

    De plus, PostgreSQL 12 supporte désormais une forme d'authentification multifacteur. Un serveur PostgreSQL peut ainsi obliger un client s'authentifiant à fournir un certificat SSL valide avec le nom de l'utilisateur avec l'option clientcert=verify-full et combiner cela avec la demande d'une méthode d'authentification séparée (scram-sha-256 par exemple).

    Administration

    PostgreSQL 12 introduit la possibilité de reconstruire les index sans bloquer les écritures sur l'index à l'aide de la commande REINDEX CONCURRENTLY. Ce qui permet aux utilisateurs d'éviter les interruptions de service lors de reconstruction longue d'index.

    De plus, PostgreSQL 12 permet d'activer ou désactiver les sommes de vérification (checksums) de page sur un cluster hors ligne à l'aide de la commande pg_checksums. Avant cela, les checksums sur les pages, fonctionnalité permettant de vérifier l'intégrité des données stockées sur disque, ne pouvaient être activées qu'à la création d'un cluster par la commande initdb.

    Télécharger PostgreSQL (Windows, Linux, macOS, BSD, Solaris)
    Code source

    Source : note de version, colonnes générées

    Et vous ?

    Utilisez-vous un système de gestion de base de données ?
    Si oui, lequel ?
    Que pensez-vous de PostgreSQL ?
    Quels sont les améliorations qui vous intéressent le plus ?

    Voir aussi :

    PostgreSQL 11.3 et 10.8 sont disponibles en téléchargement et s'accompagnent de la correction de plus de 60 bogues signalés en un trimestre
    Azure Data Studio, l'éditeur open source d'interface graphique développé par Microsoft, prend en charge en préversion PostgreSQL
    Red Hat Satellite va se séparer de MongoDB Community Edition et conservera PostgreSQL comme base de données unique
    Depuis 20 ans, PostgreSQL aurait mal utilisé fsync(), compromettant la cohérence des données, des solutions ont été proposées au FOSDEM 2019
    Microsoft fait l'acquisition de Citus Data l'extension qui transforme PostgreSQL en une base de données distribuée
    Contribuez au club : Corrections, suggestions, critiques, ... : Contactez le service news et Rédigez des actualités

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Ces avancées notamment en matière de sécurité rendent PostgreSQL enfin conforme à la RGPD au niveau de l'authentification…. Reste le problème du chiffrement des données qui n'est pas salé de manière interne et pour lequel il faut toujours laisser les clefs sur le serveur, ce qui constitue un trou de sécurité un peu comme sis on laissait les clefs de sa voiture dans la boite à gant….

    Un petit commentaire sur les colonnes calculées que je n'ai pas encore testé… Est-il possible le créer une colonne calculée non déterministe (exemple, calcul d'âge) et comment le SGBD se comporte t-il si je tente de l'indexer...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre averti
    Profil pro
    Inscrit en
    Mars 2010
    Messages
    349
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 349
    Points : 439
    Points
    439
    Par défaut
    J’étais à la pgconf 2019 et ce sujet concernant les clés a été évoqué avec le TDE (transparent data encryption). D'après mes souvenirs il serait possible de stocker les clés en dehors du serveur comme vous le préconisez.

  4. #4
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Ce serait déjà un pas d'avance… Mais l'idée suprême est d'encapsuler les clefs à l'intérieur de la BD de manière masquée afin que tout figure dans la base ne serait-ce que pour une éventuelle migration….

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

Discussions similaires

  1. Réponses: 12
    Dernier message: 23/10/2018, 16h45
  2. ToutEnClic 5.02 est disponible pour gommer les différences
    Par ideefixe dans le forum Logiciels Libres & Open Source
    Réponses: 0
    Dernier message: 07/05/2018, 22h05
  3. VLC 3.0 est disponible sur toutes les plateformes de bureau et mobiles
    Par Michael Guilloux dans le forum Multimédia
    Réponses: 16
    Dernier message: 09/03/2018, 21h12
  4. Une version revisitée de Paint est disponible pour tous les navigateurs Web
    Par Stéphane le calme dans le forum Actualités
    Réponses: 3
    Dernier message: 22/01/2018, 19h02
  5. Réponses: 5
    Dernier message: 12/03/2014, 23h04

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo