2 Types avancés
Types avancés
PostgreSQL offre des types avancés :
- UUID
- Tableaux
- Composés :
hstore- JSON :
json,jsonb - XML
- Pour les objets binaires :
bytea- Large Objects
UUID
UUID : principe
- Ex:
d67572bf-5d8c-47a7-9457-a9ddce259f05 - Un identifiant universellement unique sur 128 bits
- Type :
uuid - Différentes versions
- Avec des inconvénients…
Les UUID (pour Universally Unique IDentifier) sont nés d’un besoin d’avoir des identifiants uniques au niveau mondial pour divers objets, avec un risque de collision théoriquement négligeable. Ce sont des identifiants sur 128 bits.
Le standard propose (en 2026) huit versions à cause d’un historique déjà long depuis les années 1980, et de différents algorithmes de création ou d’utilisation dans des bases de données. Il existe aussi des versions dérivées liées à certains éditeurs.
Dans une base, les clés primaires « techniques » (surrogate), servent à identifier de manière unique une ligne, sans posséder de sens propre : les UUID peuvent donc parfaitement remplacer les numéros de séquence traditionnels. Ce n’est pas toujours une bonne idée.
Références :
- Pages Wikipédia francophone et anglophone ;
- RFC 4122, ISO/IEC 9834-8:2005 (juillet 2005), définissant les UUID versions 1 à 5 et leurs utilisations ;
- RFC 9562 (mai 2024), définissant les UUID versions 1 à 8 ;
- Un excellent article de Victor Adossi sur le choix de différents types de clés primaires, les différents types d’UUID, et des benchmarks de génération d’UUID en masse (septembre 2022).
UUID : avantages
- Faciles à générer
- Pouvoir désigner des entités arbitrairement
- Pouvoir exporter des données de sources différentes
- au contraire des séquences traditionnelles
- Pouvoir fusionner des bases
- Pour toutes les clés primaires ?
Sous PostgreSQL, de simples fonctions permettent de générer des UUID aussi facilement que des numéros de séquences. Il est bien sûr possible que ces UUID soient fournis par des applications extérieures.
Très souvent, les clés primaires des tables proviennent d’entiers générés successivement avec des séquences (via nextval(), serial, GENERATED ALWAYS AS IDENTITY) et généralement en partant de 1. L’unicité des identifiants dans une colonne est ainsi facilement garantie. Cela ne pose aucun souci jusqu’au jour où les données sont à rapprocher de données d’une autre base. Il y a de bonnes chances que les deux bases utilisent les mêmes identifiants pour des choses différentes. Souvent, une clé fonctionnelle (unique aussi) permet de faire le lien (commande DALIBO-CRA-1234, personne de numéro 25502123123…) mais ce n’est pas toujours le cas, et des erreurs de génération sont possibles.
Un UUID est arbitraire et unique. C’est une solution facile pour nommer n’importe quelle entité logique ou physique sans risque de collision avec les identifiants d’un autre système. Les UUID sont dont parfaits pour fusionner des bases de données issues de plusieurs bases de même structure (contextes distribués ou multitenants).
Hormis ce cas particulier, les UUID sont surtout utiles pour identifier un ensemble de données échangés entre deux systèmes (que ce soit en JSON, CSV ou un autre moyen) : l’UUID devient une sorte de clé primaire publique. En interne, les deux bases peuvent continuer à utiliser des séquences classiques pour leurs jointures.
Il est techniquement possible de pousser la logique jusqu’au bout et de décider que chaque clé primaire d’une ligne sera un UUID et non un entier, et de faire les jointures entre tables sur ces UUID.
Des numéros de séquence consécutifs peuvent se deviner (dans l’URL d’un site web par exemple), ce qui peut être une faille de sécurité. Des UUID (apparemment) aléatoires ne présentent pas ce problème… si l’on a bien choisi la version d’UUID (voir plus loin).
UUID : inconvénients
- Lisibilité
- Temps de génération (mineur)
- Taille
- 16 octets…
- Surtout
- fragmentation des index
- mauvais pour le cache
- amplification des écritures
- sauf UUID v7 (conseillé)
Lisibilité :
Le premier inconvénient n’est pas technique mais humain : il est plus aisé de lire et retenir des valeurs courtes comme un ticket 10023, une commande 2024-67 ou une immatriculation AT-389-RC que « d67572bf-5d8c-47a7-9457-a9ddce259f05 ». Les UUID sont donc à réserver aux clés techniques. De même, un développeur qui consulte une base retiendra et discernera plus facilement des valeurs entre 1 000 et 100 000 que des UUID à première vue aléatoires, et surtout à rallonge.
Pour la base de données, il y a d’autres inconvénients :
Taille :
Le type uuid de PostgreSQL prend 128 bits, donc 16 octets. Les types numériques entiers de PostgreSQL utilisent 2 octets pour un smallint (int2, de -32768 à +32767), 4 pour un integer (de -2 à +2 milliards environ), 8 pour un bigint (int8, de -9.10¹⁸ à +9.10¹⁸ environ). Ces types entiers suffisent généralement à combler les besoins, tout en permettant de choisir le type le plus petit possible. On a donc une différence de 8 octets par ligne entre uuid et bigint, à multiplier par autant de lignes, parfois des milliards.
Cette différence s’amplifie tout le long de l’utilisation de la clé :
- index plus gros (puisque ces champs sont toujours indexés) ;
- clés étrangères plus grosses, avec leurs index ;
- jointures plus gourmandes en CPU, mémoire, voire disque ;
- avec un impact sur le cache.
Ce n’est pas forcément bloquant si votre utilisation le nécessite.
Le pire est le stockage d’UUID dans un champ varchar : la taille passe à 36, les jointures sont bien plus lourdes, et la garantie d’avoir un véritable UUID disparaît !
Temps de génération :
Selon l’algorithme de génération utilisé, la création d’un UUID peut être plusieurs fois plus lente que celle d’un numéro de séquence. Mais ce n’est pas vraiment un souci avec les processeurs modernes, qui sont capables de générer des dizaines, voire des centaines de milliers d’UUID, aléatoires ou pas, par seconde.
Fragmentation des index et amplification des écritures :
Le plus gros problème des UUID vient de leur apparence aléatoire. Cela a un impact sur la fragmentation des index et leur utilisation du cache.
Parlons d’abord de l’insertion de nouvelles lignes. Les UUID sont très souvent générés en utilisant la version 4. L’algorithme se base sur des nombres aléatoires. Par conséquent, les UUID produits sont imprévisibles.
À l’insertion, le nouvel UUID est donc susceptible d’être ajouté dans n’importe quel bloc d’index. Or un index est par définition trié, et il n’y pas forcément la place pour ajouter une nouvelle valeur en plein milieu. Cela peut entraîner de fréquents splits des pages d’index (division d’une page pleine qui doit accueillir de nouvelles entrées).
La première conséquence est la fragmentation de l’index, une augmentation de sa taille (avec un effet négatif sur le cache), et l’augmentation du nombre d’accès disques (en lecture et écriture) à chaque insertion. De plus, comme les mises à jour sont réparties sur toute la longueur des index, ceux-ci tendent à rester entièrement dans le cache de PostgreSQL. Si celui-ci est insuffisant, la conséquence est l’augmentation des accès disques aléatoires.
Ces soucis avec les UUID v4 se posent pour toutes les bases de donnés. Ajoutons un autre, propre à PostgreSQL : un bloc modifié, même par une seule ligne, depuis le dernier checkpoint est réécrit intégralement dans les journaux (full page images), sauf dans certains cas. Répartir les modifications dans de nombreux blocs dispersés dans les index fait donc exploser la volumétrie à y écrire, et ce d’autant plus que les index sont gros.
Avec un petit index ou une table statique, ce n’est pas vraiment un problème. Mais dans un modèle où chaque clé de table et chaque clé étrangère est un UUID, il y a intérêt à pouvoir garder tous ces index en mémoire.
À l’inverse, une séquence numérique génère des valeurs strictement croissantes, donc toujours insérées à la fin de l’index dans les mêmes blocs. La fragmentation reste basse, et seule la partie utile de l’index reste en cache. (Évidemment, cela devient plus complexe quand on modifie ensuite les lignes. Mais beaucoup d’applications modifient surtout les lignes récentes, et délaissent les blocs d’index des lignes anciennes.)
Une solution standardisée n’est apparue qu’avec les UUID version 7, standardisés dans la RFC 9562 en 2024. Ces UUID utilisent l’heure de génération et sont donc triés. Les soucis de pollution du cache et de fragmentation d’index disparaissent donc.
Nous recommandons fortement de généraliser l’utilisation des UUID v7.
UUID : utilisation sous PostgreSQL
uuid:- type simple (16 octets)
- garantit le format
- Génération :
uuidv4(aléatoire) (gen_random_uuidavant la v18)uuidv7(v18+)- ou extension (
pg_idkit…) ou fonction SQL (v7)
- ou extension (
- extension
uuid-ossp(v1,3,4,5)
Le type uuid est connu de PostgreSQL, c’est un champ simple de taille fixe. Si l’UUID provient de l’extérieur, le type garantit qu’il s’agit d’un UUID valide.
uuidv4() et gen_random_uuid() :
Générer un UUID depuis le SQL est très simple avec la fonction uuidv4() :
Les UUID générés en version 4 sont totalement aléatoires, avec tous les inconvénients vus ci-dessus.
Avant PostgreSQL 18, la fonction s’appelle gen_random_uuid(). Elle existe toujours par la suite, mais uuidv4() a le mérite d’indiquer clairement la version utilisée.
uuidv7() :
PostgreSQL sait générer des UUID en version 7 à partir de la version 18, avec la fonction uuidv7().
L’exemple ci-dessus montre que les UUID générés consécutivement sont naturellement classés par ordre croissant. Cela signifie que des lignes qui se suivent chronologiquement se suivent aussi physiquement dans l’index, c’est tout l’intérêt de la version 7.
Si vous ne voulez pas trahir la date de génération, contenue dans l’UUID, ajouter un intervalle d’offset est possible. Ce doit être une constante pour un même champ pour conserver l’ordre lexicographique.
Les UUID v7 avant PostgreSQL 18 :
Pour les versions antérieures, il existe plusieurs extensions dédiées, avec les soucis habituels de disponibilité de paquets, maintenance des versions, confiance dans le mainteneur et disponibilité dans un PostgreSQL en SaaS. Par exemple, Supabase propose pg_idkit (versions Rust, et PL/pgSQL).
Le plus simple est sans doute d’utiliser la fonction SQL suivante, de Kyle Hubert, modifiée par Daniel Vérité. Elle est sans doute suffisamment rapide pour la plupart des besoins. Elle fait partie de son extension postgres-uuidv7-sql.
Il existe une version plus lente avec une meilleure précision, inférieure à la milliseconde.
Fonctions utilitaires :
PostgreSQL 17 a introduit une fonction de récupération de la version de l’UUID, uuid_extract_version() :
Depuis PostgreSQL 17 existe aussi uuid_extract_timestamp() pour renvoyer l’heure utilisée pour la génération d’un UUID v7 ou v1 :
Si vous utilisez un UUID v7, vous pouvez ainsi économiser les 8 octets par ligne d’un champ creation_date, que beaucoup de développeurs ajoutent.
Pour PostgreSQL 16 et précédents, le billet de blog de Daniel Vérité ci-dessus offre aussi une version en SQL de cette même fonction :
L’extension uuid-ossp :
L’extension uuid-ossp, fournie avec PostgreSQL, possède plusieurs algorithmes de génération d’UUID avec les fonctions suivantes… sauf en v7 !
Avec uuid_generate_v1(), l’UUID généré est lié à l’adresse MAC de la machine et à l’heure.
Sur une autre machine :
Noter que le problème de fragmentation des index se pose déjà. Utiliser l’extension pg_idkit pour générer un UUID version 6 si la compatibilité avec la version 1 est primordiale, le tri est meilleur.
Cette propriété peut faciliter la prédiction de futures valeurs d’UUID, et l’adresse MAC est identifiante. Les UUID v1 (ou v6) peuvent donc être considérés comme une faille de sécurité dans certains contextes.
Il existe une version uuid_generate_v1mc() un peu plus sécurisée (avec une adresse MAC multicast aléatoire).
uuid_generate_v3() et uuid_generate_v5() génèrent des valeurs reproductibles en fonction des paramètres. La version 5 utilise un algorithme plus sûr.
UUID : une indexation facile
L’index B-tree classique convient parfaitement pour trier des UUID. En général on le veut UNIQUE (plus pour parer à des erreurs humaines qu’à de très improbables collisions dans l’algorithme de génération).
Les UUID font de très bonnes clés primaires, c’est leur but.
Rappelons une fois de plus que la version 7 est conseillée hors des plus petites tables.
UUID : résumé
- Si vous avez besoin des UUID, préférez la version 7.
- Les séquences habituelles restent recommandables en interne.
Si des données doivent être échangées avec d’autres systèmes, les UUID sont un excellent moyen de garantir l’unicité d’identifiants universels.
Si vous les générez vous-mêmes, préférez les UUID version 7. Des UUID v4 (totalement aléatoires) restent sinon recommandables, avec les soucis potentiels de cache et de fragmentation évoqués ci-dessus.
Pour les jointures internes à l’applicatif, conservez les séquences habituelles, (notamment avec GENERATED ALWAYS AS IDENTITY), ne serait-ce que pour leur simplicité.
Types tableaux
Tableaux : principe
- Collection ordonnée d’un même type
- Types
integer[],text[], etc.
Une tableau est un ensemble d’objets d’un même type. Ce type de base est généralement un numérique ou une chaîne, mais ce peut être un type structuré (géométrique, JSON, type personnalisé…), voire un type tableau. Les tableaux peuvent être multidimensionnels.
Un tableau se crée par exemple avec le constructeur ARRAY, avec la syntaxe {…}::type[], ou en agrégeant des lignes existantes avec array_agg. À l’inverse, on peut transformer un tableau en lignes grâce à la fonction unnest. Les syntaxes [numéro] et [début:fin] permettent d’extraire un élément ou une partie d’un tableau. Deux tableaux se concatènent avec ||.
Les tableaux sont ordonnés, ce ne sont pas des ensembles. Deux tableaux avec les mêmes données dans un ordre différent ne sont pas identiques.
Références :
- Documentation officielle :
- syntaxe et exemples ;
- constructeur de tableau ARRAY ;
- fonctions et opérateurs tableau :
array_to_string,string_to_array,unnest,array_agg,array_length,array_cat,array_append,array_prepend,cardinality,array_position/array_positions,array_fill,array_remove,array_shuffle,trim_array…
Tableaux : recherche de valeurs
Recherchons des lignes contenant certaines valeurs :
Tableaux : performances
Quel intérêt par rapport à 1 valeur par ligne ?
- Allège certains modèles en réduisant les jointures
- ex : champ avec des n°s de téléphone
- si pas de contraintes
- Grosse économie en place (time series)
- 24 octets par ligne
- et parfois mécanisme TOAST
- Mais…
- mise à jour compliquée/lente
- indexation moins simple
Une bonne modélisation aboutit en général à des valeurs uniques, chacune dans son champ sur sa ligne. Les tableaux stockent plusieurs valeurs dans un même champ d’une ligne, et vont donc à l’encontre de cette bonne pratique.
Cependant les tableaux peuvent être très pratiques pour alléger la modélisation sans tomber dans de mauvaises pratiques. Typiquement, on remplacera :
par :
Une table des numéros de téléphone serait stricto censu plus propre et flexible, mais induirait des jointures supplémentaires. De plus, il est impossible de poser des contraintes de validation (CHECK) sur des éléments de tableau sans créer un type intermédiaire. (Dans des cas plus complexes où il faut typer le numéro, on peut utiliser un tableau d’un type structuré, ou basculer vers un type JSON, qui peut lui-même contenir des tableaux, mais a un maniement un peu moins évident. L’intérêt du type structuré sur un champ JSON ou hstore est qu’il est plus compact, mais évidemment sans aucune flexibilité.)
Quand il y a beaucoup de lignes et peu de valeurs sur celles-ci, il faut se rappeler que chaque ligne d’une base de données PostgreSQL a un coût d’au moins 24 octets de données « administratives », même si l’on ne stocke qu’un entier par ligne, par exemple. Agréger des valeurs dans un tableau permet de réduire mécaniquement le nombre de lignes et la volumétrie sur le disque et celles des écritures. Par contre, le développeur devra savoir comment utiliser ces tableaux, comment retrouver une valeur donnée à l’intérieur d’un champ multicolonnes, et comment le faire efficacement.
De plus, si le champ concaténé est assez gros (typiquement 2 ko), le mécanisme TOAST peut s’activer et procéder à la compression du champ tableau, ou à son déport dans une table système de manière transparente.
Les tableaux peuvent donc permettre un gros gain de volumétrie. De plus, les données d’un même tableau, souvent utilisées ensemble, restent forcément dans le même bloc. L’effet sur le cache est donc extrêmement intéressant.
Par exemple, ces deux tables contiennent 6,3 millions de valeurs réparties sur 366 jours :
La différence de taille est d’un facteur 1000 :
Ce cas est certes extrême (beaucoup de valeurs par ligne et peu de valeurs distinctes).
Les cas réels sont plus complexes, avec un horodatage moins régulier. Par exemple, l’outil OPM stocke plutôt des tableaux d’un type composé d’une date et de la valeur relevée, et non la valeur seule.
Dans beaucoup de cas, cette technique assez simple évite de recourir à des extensions spécialisées payantes comme TimescaleDB, voire à des bases de données spécialisées.
Évidemment, le code devient moins simple. Selon les besoins, il peut y avoir besoin de stockage temporaire, de fonctions de compactage périodique…
Il devient plus compliqué de retrouver une valeur précise. Ce n’est pas trop un souci dans les cas pour une recherche ou pré-sélection à partir d’un autre critère (ici la date, indexée). Pour la recherche dans les tableaux, voir plus bas.
Les mises à jour des données à l’intérieur d’un tableau deviennent moins faciles et peuvent être lourdes en CPU avec de trop gros tableaux.
Tableaux : indexation
- B-tree inutilisable
- GIN plus adapté pour recherche d’une valeur
- opérateurs
&&,@> - mais plus lourd
- opérateurs
Indexer un champ tableau est techniquement possible avec un index B-tree, le type d’index par défaut. Mais cet index est en pratique peu performant. De toute façon il ne permet que de chercher un tableau entier (ordonné) comme critère.
Dans les exemples précédents, les index B-tree sont plutôt à placer sur un autre champ (la date, l’ID), qui ramène une ligne entière.
D’autres cas nécessitent de chercher une valeur parmi celles des tableaux (par exemple dans des listes de propriétés). Dans ce cas, un index GIN sera plus adapté, même si cet index est un peu lourd. Les opérateurs || et @> sont utilisables. La valeur recherchée peut être n’importe où dans les tableaux.
Là encore, on récupère les tableaux entiers qui contiennent la valeur demandée. Selon le besoin, il faudra peut-être reparcourir les éléments récupérés, ce qui coûtera un peu de CPU :
Quant aux recherches sur une plage de valeurs dans les tableaux, elles ne sont pas directement indexables par un index GIN.
Pour les détails sur les index GIN, voir le module J5.
Types composés
Types composés : généralités
- Un champ = plusieurs attributs
- De loin préférable à une table Entité/Attribut/Valeur
- Uniquement si le modèle relationnel n’est pas assez souple
- 3 types dans PostgreSQL :
hstore: clé/valeurjson: JSON, stockage texte, validation syntaxique, fonctions d’extractionjsonb: JSON, stockage binaire, accès rapide, fonctions d’extraction, de requêtage, indexation avancée
Ces types sont utilisés quand le modèle relationnel n’est pas assez souple, donc s’il est nécessaire d’ajouter dynamiquement des colonnes à la table suivant les besoins du client, ou si le détail des attributs d’une entité n’est pas connu (modélisation géographique par exemple), etc.
La solution traditionnelle est de créer des tables entité/attribut de ce format :
On y stocke dans entite la clé de l’enregistrement de la table principale, dans attribut la colonne supplémentaire, et dans valeur la valeur de cet attribut. Ce modèle présente l’avantage évident de résoudre le problème. Les défauts sont par contre nombreux :
- Les attributs d’une ligne peuvent être totalement éparpillés dans la table
attributs_sup: récupérer n’importe quelle information demandera donc des accès à de nombreux blocs différents. - Il faudra plusieurs requêtes (au moins deux) pour récupérer le détail d’un enregistrement, avec du code plus lourd côté client pour fusionner le résultat des deux requêtes, ou bien une requête effectuant des jointures (autant que d’attributs, sachant que le nombre de jointures complexifie énormément le travail de l’optimiseur SQL) pour retourner directement l’enregistrement complet.
Toute recherche complexe est très inefficace : une recherche multicritère sur ce schéma va être extrêmement peu performante. Les statistiques sur les valeurs d’un attribut deviennent nettement moins faciles à estimer pour PostgreSQL. Quant aux contraintes d’intégrité entre valeurs, elles deviennent pour le moins complexes à gérer.
Les types hstore, json et jsonb permettent de résoudre le problème autrement. Ils stockent les différentes entités dans un seul champ pour chaque ligne de l’entité. L’accès aux attributs se fait par une syntaxe ou des fonctions spécifiques.
Il n’y a même pas besoin de créer une table des attributs séparée : le mécanisme du TOAST permet de déporter les champs volumineux (texte, JSON, hstore…) dans une table séparée gérée par PostgreSQL, éventuellement en les compressant, et cela de manière totalement transparente. On y gagne donc en simplicité de développement.
hstore
hstore : principe
Stocker des données non structurées
- Extension
- Stockage Clé/Valeur, uniquement texte
- Binaire
- Indexable
- Plusieurs opérateurs disponibles
hstore est une extension, fournie en « contrib ». Elle est donc systématiquement disponible. L’installer permet d’utiliser le type de même nom. On peut ainsi stocker un ensemble de clés/valeurs, exclusivement textes, dans un unique champ.
Ces champs sont indexables et peuvent recevoir des contraintes d’intégrité (unicité, non recouvrement…).
Les hstore ne permettent par contre qu’un modèle « plat ». Il s’agit d’un pur stockage clé-valeur. Si vous avez besoin de stocker des informations davantage orientées document, vous devrez vous tourner vers un type JSON.
Ce type perd donc de son intérêt depuis que PostgreSQL 9.4 a apporté le type jsonb. Il lui reste sa simplicité d’utilisation.
hstore : exemple
Les ordres précédents installent l’extension, créent une table avec un champ de type hstore, insèrent trois lignes, avec des attributs variant sur chacune, indexent l’ensemble avec un index GiST, et enfin recherchent les lignes où l’attribut carnivore possède la valeur t.
Les différentes fonctions disponibles sont bien sûr dans la documentation.
Par exemple :
Il est possible de convertir un hstore en tableau :
ou en JSON :
L’indexation de ces champs peut se faire avec divers types d’index. Un index unique n’est possible qu’avec un index B-tree classique. Les index GIN ou GiST sont utiles pour rechercher des valeurs d’un attribut. Les index hash ne sont utiles que pour des recherches d’égalité d’un champ entier ; par contre ils sont très compacts.
JSON
JSON & PostgreSQL
json: format textejsonb: format binaire, à préférerjsonpath: SQL/JSON paths (requêtage)
Le format JSON est devenu extrêmement populaire. Au-delà d’un simple stockage clé/valeur, il permet de stocker des tableaux, ou des hiérarchies, de manière plus simple et lisible qu’en XML. Par exemple, pour décrire une personne, on peut utiliser cette structure :
Historiquement, le JSON est apparu dans PostgreSQL 9.2, mais n’est vraiment utilisable qu’avec l’arrivée du type jsonb (binaire) dans PostgreSQL 9.4. Ce dernier est le type à utiliser. Les opérateurs SQL/JSON path ont été ajoutés dans PostgreSQL 12, suite à l’introduction du JSON dans le standard SQL:2016.
Type json
- Type texte
- avec validation du format
- Réservé au stockage à l’identique
- Préférer
jsonb
Le type natif json, dans PostgreSQL, n’est rien d’autre qu’un habillage autour du type texte. Il valide à chaque insertion/modification que la donnée fournie est une syntaxe JSON valide. Le stockage est exactement le même qu’une chaîne de texte, et utilise le mécanisme du TOAST, qui compresse les grands champs au besoin, de manière transparente pour l’utilisateur. Le fait que la donnée soit validée comme du JSON permet d’utiliser des fonctions de manipulation, comme l’extraction d’un attribut, la conversion d’un JSON en enregistrement, de façon systématique sur un champ sans craindre d’erreur.
Mais on préférera généralement le type binaire jsonb pour les performances, et ses fonctionnalités supplémentaires. Le seul intérêt du type json texte est de conserver un objet JSON sous sa forme originale, y compris l’ordre des clés, les espaces inutiles compris, et les clés dupliquées (la dernière étant celle prise en compte) :
Une partie des exemples suivants avec le type jsonb est aussi applicable au json. Beaucoup de fonctions existent sous les deux formes (par exemple json_build_object et jsonb_build_object), mais beaucoup d’autres sont propres au type jsonb.
Type jsonb
- JSON au format Binaire
- Indexation GIN
- Langage JSONPath
Le type jsonb permet de stocker les données dans un format binaire optimisé. Ainsi, il n’est plus nécessaire de désérialiser l’intégralité du document pour accéder à une propriété.
Les gains en performance sont importants. Par exemple une requête simple comme celle-ci :
passe de 5 à 1,5 s sur une machine en convertissant le champ de json à jsonb (pour ½ million de champs JSON totalisant 900 Mo environ pour chaque version, ici sans TOAST notable et avec une table intégralement en cache).
Encore plus intéressant : jsonb supporte les index GIN, et la syntaxe JSONPath pour le requêtage et l’extraction d’attributs. jsonb est donc le type le plus intéressant pour stocker du JSON dans PostgreSQL.
Validation du format JSON
- Aussi :
IS JSON WITH/WITHOUT UNIQUE KEYS,IS JSON ARRAY,IS JSON OBJECT,IS JSON SCALAR - PostgreSQL 16+
À partir de PostgreSQL 16 existe le prédicat IS JSON. Il peut être appliqué sur des champs text ou bytea et évidemment sur des champs json et jsonb. Il permet de repérer notamment une faute de syntaxe comme dans le deuxième exemple ci-dessus.
Existent aussi :
- l’opérateur
IS JSON WITH UNIQUE KEYSpour garantir l’absence de clé en doublon :
l’opérateur
IS JSON WITHOUT UNIQUE KEYSpour garantir l’absence de clé unique ;l’opérateur
IS JSON ARRAYpour le bon formatage des tableaux :
- des opérateurs
IS JSON SCALARetIS JSON OBJECTpour valider par exemple le contenu de fragments d’un objet JSON.
Noter que la RFC impose qu’un JSON soit en UTF-8, qui est l’encodage recommandé, mais pas obligatoire, d’une base PostgreSQL.
JSON : Exemple d’utilisation
Un champ de type jsonb (ou json) accepte tout champ JSON directement.
JSON : construction
Pour construire un petit JSON, le transtypage d’une chaîne peut suffire dans les cas simples. jsonb_build_object permet de limiter les erreurs de syntaxe.
Dans un JSON, l’ordre n’a pas d’importance.
JSON : Affichage des attributs
- Attention : pas de contrôle de l’existence de la clé !
- et attention à la casse
Le type json dispose de nombreuses fonctions et opérateurs de manipulation et d’extraction.
Attention au type en retour, qui peut être du texte ou du JSON. Les opérateurs ->> et -> renvoient respectivement une valeur au format texte, et au format JSON :
Pour l’affichage, la fonction jsonb_pretty améliore la lisibilité :
L’équivalent existe avec des chemins, avec #> et #>> :
Une autre syntaxe plus claire est disponible, et qui renvoie du JSON :
Avec cette syntaxe, une petite astuce permet de convertir en texte sans utiliser ->>['city'] (en toute rigueur, ->>0 renverra le premier élément d’un tableau):
PostgreSQL ne contrôle absolument pas que les clés JSON (comme ici
firstNameoucity) sont valides ou pas. La moindre faute de frappe (ou de casse !) entraînera une valeurNULLen retour. C’est la conséquence de l’absence de schéma dans un JSON, contrepartie de sa souplesse.
Modifier un JSON
L’opérateur || concatène deux jsonb pour donner un autre jsonb :
Comme d’habitude, le résultat est NULL si l’un des JSON est NULL. Dans le doute, on peut utiliser {} comme élément neutre :
Pour supprimer un attribut d’un jsonb, il suffit de l’opérateur - et d’un champ texte indiquant l’attribut à supprimer. Il existe une variante avec text[] pour supprimer plusieurs attributs :
ainsi que l’opérateur pour supprimer un sous-attribut :
La fonction jsonb_set modifie l’attribut indiqué dans un jsonb :
Attention, le sous-attribut est intégralement remplacé, et non fusionné. Dans cet exemple, le code postal disparaît :
Il vaut mieux indiquer le chemin complet en second paramètre :
JSON, tableaux et agrégation
- Manipuler des tableaux :
jsonb_array_elements(),jsonb_array_elements_text()jsonb_agg()(de JSON ou de scalaires)jsonb_agg()_strict(sans les NULL)
Un JSON peut contenir un tableau de texte, nombre, ou autres JSON. Il est possible de déstructurer ces tableaux, mais il est compliqué de requêter sur leur contenu.
jsonb_array_elements permet de parcourir ces tableaux :
Avec la syntaxe JSONPath, le résultat est le même :
Si l’on veut retrouver un type tableau, il faut réagréger, car jsonb_path_query et jsonb_array_elements renvoient un ensemble de lignes. On peut utiliser une clause LATERAL, qui sera appelée pour chaque ligne (ce qui est lent) puis réagréger :
On voit que la fonction jsonb_agg envoie un tableau de JSON.
Si l’on veut un tableau de textes :
Noter que la clause LATERAL supprime les personnes sans téléphone. On peut utiliser LEFT OUTER JOIN LATERAL, ou l’exemple suivant. On suppose aussi que le prénom est une clé de regroupement suffisante ; un identifiant quelconque serait plus pertinent.
Cette autre variante avec un sous-SELECT sera plus performante avec de nombreuses lignes, car elle évite le regroupement :
Il existe une autre fonction d’agrégation des JSON plus pratique, nommée jsonb_agg_strict(), qui supprime les valeurs à null de l’agrégat (mais pas un attribut à null). Pour d’autres cas, il existe aussi jsonb_strip_nulls() pour nettoyer un JSON de toutes les valeurs null si une clé est associée (pas dans un tableau) :
Conversions jsonb / relationnel (1)
- Construire un ensemble de tuples depuis un objet JSON :
jsonb_each()
- Avec des types préexistants :
jsonb_populate_record(),jsonb_populate_recordset
- Types définis à la volée :
jsonb_to_record(),jsonb_to_recordset()
- Construire un JSON depuis une requête :
to_jsonb (<requête>)
- Attention aux types
jsonb_typeof()
Plusieurs fonctions permettant de construire du jsonb, ou de le manipuler de manière ensembliste. (En version 17 ou supérieure, le plus simple et pratique sera souvent la fonction JSON_TABLE présentée plus bas.)
jsonb_each :
jsonb_each décompose les clés et retourne une ligne par clé. Là encore, on multiplie le nombre de lignes.
jsonb_populate_record/jsonb_populate_recordset :
Si les noms des attributs et champs sont bien identiques (casse comprise !) entre JSON et table cible, jsonb_populate_record peut être pratique :
Les attributs du JSON non récupérés sont ignorés, les valeurs absentes du JSON sont à NULL. Il existe une possibilité de créer des valeurs par défaut :
jsonb_populate_recordset sert dans le cas des tableaux de JSON.
Définir un type au lieu d’une table fonctionne aussi.
jsonb_to_record/jsonb_to_recordset :
jsonb_to_record renvoie un enregistrement avec des champs correspondant à chaque attribut JSON, donc bien typés. Pour fonctionner, la fonction exige une clause AS avec les attributs voulus et leur bon type. (Là encore, attention à la casse exacte des noms d’attributs sous peine de se retrouver avec des valeurs à NULL.)
Les autres attributs de notre exemple peuvent être extraits également, ou re-convertis en enregistrements avec une autre clause LATERAL. Si ces attributs sont des tableaux, on peut générer une ligne par élément de tableau avec json_to_recordset :
À l’inverse, transformer le résultat d’une requête en JSON est très facile avec to_jsonb :
jsonb_typeof :
Pour connaître le type d’un attribut JSON :
Conversions jsonb / relationnel (2) : JSON_TABLE
- PostgreSQL 17+
- conversion d’un JSON en vue, avec types
- clause
NESTED PATHpour les tableaux dans le JSON - paramétres : clause
PASSING
À partir de PostgreSQL 17, la fonction JSON_TABLE() permet de présenter des données JSON sous forme de vue. C’est sans doute la manière la plus pratique.
Le premier paramètre que prend la fonction, appelé context_item, est une source JSON sur laquelle sera évaluée l’expression JSON. Il est possible de récupérer des données présentes dans une structure imbriquée, avec la clause NESTED PATH.
Exemple :
Prenons l’exemple suivant d’une table créée à partir d’un fichier JSON. Le fichier d’exemple généré à l’aide de mockaroo.com peut être récupéré sur https://dali.bo/999people.
Voici un exemple d’une ligne contenue dans la table t1 :
Si l’on souhaite récupérer une vue avec uniquement les informations de l’âge et du nom présents dans les lignes de la table t1, la commande suivante peut être utilisée :
JSON_TABLE(utilisateur, '$'indique que toute la source JSON (le champ de nomutilisateur) doit être utilisée lors de l’évaluation. Cette fonction sera appelée une fois pour chaque ligne det1.COLUMNS (age int PATH '$.age', name text PATH '$.name'))est la clause qui permet d’indiquer quelles colonnes seront présentes dans la vue résultante et où retrouver les données associées.- La clause
PATHindique l’emplacement de la donnée dans la donnée JSON.
Le résultat peut être manipulé comme tout résultat de requête : consultation, export, vue…
Les champs sont correctement typés (suivant ceux indiqués dans
COLUMNS). En cas d’incohérence, il n’y aura pas d’erreur, mais une valeurNULL.
Filtrage des valeurs :
La clause '$' peut être plus complexe pour ne renvoyer que certaines lignes :
La syntaxe utilisée pour les filtrages et sélections de champs est le JSON path, qui sera détaillée plus loin.
Nested path :
La clause NESTED PATH permet d’exploiter des tableaux à l’intérieur d’un champ JSON tel que celui-ci, ce qui multiplie encore les lignes. Ils peuvent être numérotés avec la clause FOR ORDINALITY.
L’expression d’évaluation est légèrement plus complexe :
Il existe une clause PASSING pour transmettre des critères de recherche proprement. Par exemple, pour connaître les amis de Roach Crosby (paramètre filter1), qui ne commencent pas par « Pitts » (paramètre filter2), et pour calculer un critère booléen sur un critère (filter2) :
Le plan d’exécution d’une telle requête (ici avec VERBOSE) indique un appel de fonction pour chaque ligne (loops=1001), sans détail sur l’exécution de cette fonction :
Documentation :
- Documentation officielle : JSON_TABLE
JSON : performances
Inconvénients par rapport à un modèle normalisé :
- Perte d’intégrité (types, contraintes, FK…)
- Complexité du code
- Pas de statistiques sur les clés JSON !
- Pas forcément plus léger en disque
- clés répétées
- Lire 1 attribut = lire tout le JSON
- voire accès table TOAST
- Mise à jour : tout ou rien
- Indexation délicate
Les attributs JSON sont très pratiques quand le schéma est peu structuré. Mais la complexité supplémentaire de code nuit à la lisibilité des requêtes. En termes de performances, ils sont coûteux, pour les raisons que nous allons voir.
Les contraintes d’intégrité sur les types, les tailles, les clés étrangères… ne sont pas disponibles. Rien ne vous interdit d’utiliser un attribut country au lieu de pays, avec une valeur FR au lieu de France. Les contraintes protègent de nombreux bugs, mais elles sont aussi une aide précieuse pour l’optimiseur.
Chaque JSON récupéré l’est en bloc. Si un seul attribut est récupéré, PostgreSQL devra charger tout le JSON et le décomposer. Cela peut même coûter un accès supplémentaire à une table TOAST pour les gros JSON. Rappelons que le mécanisme du TOAST permet à PostgreSQL de compresser à la volée un grand champ texte, binaire, JSON… et/ou de le déporter dans une table annexe interne, le tout étant totalement transparent pour l’utilisateur. Pour les détails, voir cet extrait de la formation DBA2.
Il n’y a pas de mise à jour partielle : modifier un attribut implique de décomposer tout le JSON pour le réécrire entièrement (et parfois en le détoastant/retoastant). Si le JSON est trop gros, modifier ses sous-attributs par plusieurs processus différents peut poser des problèmes de verrouillage. Pour citer la documentation :
« Les données JSON sont sujettes aux mêmes considérations de contrôle de concurrence que pour n’importe quel autre type de données quand elles sont stockées en table. Même si stocker de gros documents est prévisible, il faut garder à l’esprit que chaque mise à jour acquiert un verrou de niveau ligne sur toute la ligne. Il faut envisager de limiter les documents JSON à une taille gérable pour réduire les contentions sur verrou lors des transactions en mise à jour. Idéalement, les documents JSON devraient chacun représenter une donnée atomique, que les règles métiers imposent de ne pas pouvoir subdiviser en données plus petites qui pourraient être modifiées séparément. »
Un gros point noir est l’absence de statistiques propres aux clés du JSON. Le planificateur va avoir beaucoup de mal à estimer les cardinalités des critères. Nous allons voir des contournements possibles.
Suivant le modèle, il peut y avoir une perte de place, puisque les clés sont répétées entre chaque attribut JSON, et non normalisées dans des tables séparées.
Enfin, nous allons voir que l’indexation est possible, mais moins triviale qu’à l’habitude.
Ces inconvénients sont à mettre en balance avec les intérêts du JSON (surtout : éviter des lignes avec trop d’attributs toujours à NULL, si même on les connaît), les fréquences de lecture et mises à jour des JSON, et les modalités d’utilisation des attributs.
Certaines de ces limites peuvent être réduites par les techniques ci-dessous.
Recherche dans un champ JSON (1)
Sans JSONPath :
Mais comment indexer ?
Pour chercher les lignes avec un champ JSON possédant un attribut d’une valeur donnée, il existe plusieurs opérateurs (au sens syntaxique). Les comparaisons directes de textes ou de JSON sont possibles, mais nous verrons qu’elles ne sont pas simplement indexables.
L’opérateur @> (« contient ») est généralement plus adapté, mais il faut fournir un JSON avec le critère de recherche.
L’opérateur ? permet de tester l’existence d’un attribut dans le JSON (même vide). Plusieurs attributs peuvent être testés avec ?| (« ou » logique) ou ?& (« et » logique).
Recherche dans un champ JSON (2) : SQL/JSON et JSONPath
- SQL:2016 introduit SQL/JSON et le langage JSONPath
- JSONPath :
- langage de recherche pour JSON
- concis, flexible, plus rapide
- étendu à chaque version
JSONPath est un langage de requêtage permettant de spécifier des parties d’un champ JSON, même complexe. Il a été implémenté dans de nombreux langages, et a donc une syntaxe différente de celle du SQL, mais souvent déjà familière aux développeurs. Il évite de parcourir manuellement les nœuds et tableaux du JSON, ce qui est vite fastidieux en SQL.
Le standard SQL:2016 intègre le SQL/JSON. PostgreSQL 12 contenait déjà l’essentiel des fonctionnalités SQL/JSON, y compris JSONPath, mais elles sont complétées dans les versions suivantes.
Recherche dans un champ JSON (3) : Exemples SQL/JSON & JSONPath
Par exemple, une recherche peut se faire ainsi, et elle profitera d’un index GIN :
Les opérateurs @@ et @? sont liés à la recherche et au filtrage. La différence entre les deux est liée à la syntaxe à utiliser. Ces deux exemples renvoient la même ligne :
Il existe des fonctions équivalentes, jsonb_path_exists et jsonb_path_match :
(Pour les détails sur ces opérateurs et fonctions, et des exemples sur des filtres plus complexes (inégalités par exemple), voir par exemple : https://justatheory.com/2023/10/sql-jsonpath-operators/.)
Un autre intérêt est la fonction jsonb_path_query, qui permet d’extraire facilement des parties d’un tableau :
Ici, jsonb_path_query génère une ligne par élément du tableau phoneNumbers inclus dans le JSON.
L’appel suivant effectue un filtrage sur la ville :
Cependant, pour que l’indexation GIN fonctionne, il faudra l’opérateur @? :
Au final, le code JSONPath est souvent plus lisible que celui utilisant de nombreuses fonctions jsonb spécifiques à PostgreSQL. Un développeur le manipule déjà souvent dans un autre langage.
On trouvera d’autres exemples dans la présentation de Postgres Pro dédié à la fonctionnalité lors la parution de PostgreSQL 12, ou dans un billet de Michael Paquier.
jsonb : indexation (1/2)
- Index fonctionnel sur un attribut précis
- bonus : statistiques
- Colonne générée stockée (dénormalisée) :
- champ normal, indexable, facile à utiliser, rapide à lire
- statistiques
- Préférer
@>et@?que des fonctions
Index fonctionnel :
L’extraction d’une partie d’un JSON est en fait une fonction immutable, donc indexable. Un index fonctionnel permet d’accéder directement à certaines propriétés, par exemple :
Mais il ne fonctionnera que s’il y a une clause WHERE avec cette expression exacte. Pour un attribut fréquemment utilisé pour des recherches, c’est le plus efficace.
On n’oubliera pas de lancer un
ANALYZEpour calculer les statistiques après création de l’index fonctionnel. Même si l’index est peu discriminant, on obtient ainsi de bonnes statistiques sur son critère.
Colonne générée :
Une autre possibilité est de dénormaliser l’attribut JSON intéressant dans un champ séparé de la table, et indexable. Les colonnes générées stockées (pas virtuelles) sont idéales pour cela :
Cette colonne générée est mise à jour quand le JSON est modifié, et n’est pas modifiable autrement. C’est à part cela un champ à part entière, indexable avec un B-tree, et avec ses statistiques propres.
Ce champ coûte certes un peu d’espace disque supplémentaire, mais il améliore la lisibilité du code, et facilite l’usage avec certains outils ou pour certains utilisateurs. Dans le cas des gros JSON, il peut aussi éviter quelques allers-retours vers la table TOAST. Même sans utilisation d’un index, un champ normal est beaucoup plus rapide à lire dans la ligne qu’un attribut extrait d’un JSON.
Ajoutons qu’un Index Only Scan ne fonctionne pas avec les index fonctionnel, mais est utilisable sur une colonne générée stockée.
jsonb : indexation (2/2)
- Indexation « schemaless » grâce au GIN :
- attention au choix de l’opérateur
Index GIN :
Les champs jsonb peuvent tirer parti de fonctionnalités avancées de PostgreSQL, notamment les index GIN, et ce via deux classes d’opérateurs.
L’opérateur par défaut de GIN pour jsonb est jsonb_ops. Mais il est souvent plus efficace de choisir l’opérateur jsonb_path_ops. Ce dernier donne des index plus petits et performants sur des clés fréquentes, bien qu’il ne supporte que certains opérateurs de recherche (@>, @? et @@) (voir les détails), ce qui suffit généralement.
jsonb_path_ops supporte notamment l’opérateur « contient » (@>) :
Un index GIN est moins efficace qu’un index fonctionnel B-tree classique, mais il est idéal quand la clé de recherche n’est pas connue, et que n’importe quel attribut du JSON peut être un critère.
Un index GIN ne permet cependant pas d’Index Only Scan.
Surtout, un index GIN ne permet pas de recherches sur des opérateurs B-tree classiques (<, <=, >, >=), ou sur le contenu de tableaux. On est obligé pour cela de revenir au monde relationnel, ou se rabattre sur les index fonctionnels ou colonnes générées vus plus haut. Attention, des fonctions comme jsonb_path_query ne savent pas utiliser les index. Il est donc préférable d’utiliser les opérateurs spécifiques, comme « contient » (@>) ou « existe » en JSONPath (@?).
Pour aller plus loin…
Lire la documentation
- 8.14 Types JSON :
- 9.16 Fonctions & opérateurs JSON :
Les fonctions et opérateurs indiqués ici ne représentent qu’une partie de ce qui existe. Certaines fonctions sont très spécialisées, ou existent en plusieurs variantes voisines. Il est conseillé de lire ces deux chapitres de documentation lors de tout travail avec les JSON. Attention à la version de la page : des fonctionnalités sont ajoutées à chaque version de PostgreSQL.
XML
XML : présentation
- Type
xml- stocke un document XML
- valide sa structure
- Quelques fonctions et opérateurs disponibles :
XMLPARSE,XMLSERIALIZE,query_to_xml,xmlaggxpath(XPath 1.0 uniquement)
Le type xml, inclus de base, vérifie que le XML inséré est un document « bien formé », ou constitue des fragments de contenu (« content »). L’encodage UTF-8 est impératif. Il y a quelques limitations par rapport aux dernières versions du standard, XPath et XQuery. Le stockage se fait en texte, donc bénéficie du mécanisme de compression TOAST.
Il existe quelques opérateurs et fonctions de validation et de manipulations, décrites dans la documentation du type xml ou celle des fonctions. Par contre, une simple comparaison est impossible et l’indexation est donc impossible directement. Il faudra passer par une expression XPath.
À titre d’exemple : XMLPARSE convertit une chaîne en document XML, XMLSERIALIZE procède à l’opération inverse.
Il existe aussi query_to_xml pour convertir un résultat de requête en XML, xmlagg pour agréger des champs XML, ou xpath pour extraire des nœuds suivant une expression XPath 1.0.
NB : l’extension xml2 est dépréciée et ne doit pas être utilisée dans les nouveaux projets.
Objets binaires
Objets binaires : les types
Souvent une mauvaise idée…
2 méthodes
bytea: type binaire- Large Objects : manipulation comme un fichier
PostgreSQL permet de stocker des données au format binaire, potentiellement de n’importe quel type, par exemple des images ou des PDF.
Il faut vraiment se demander si des binaires ont leur place dans une base de données relationnelle. Ils sont généralement beaucoup plus gros que les données classiques. La volumétrie peut donc devenir énorme, et encore plus si les binaires sont modifiés, car le mode de fonctionnement de PostgreSQL aura tendance à les dupliquer. Cela aura un impact sur la fragmentation, la quantité de journaux, la taille des sauvegardes, et toutes les opérations de maintenance. Ce qui est intéressant à conserver dans une base sont des données qu’il faudra rechercher, et l’on recherche rarement au sein d’un gros binaire. En général, l’essentiel des données binaires que l’on voudrait confier à une base peut se contenter d’un stockage classique, PostgreSQL ne contenant qu’un chemin ou une URL vers le fichier réel.
PostgreSQL donne le choix entre deux méthodes pour gérer les données binaires :
bytea: un type comme un autre ;- Large Object : des objets séparés, à gérer indépendamment des tables.
bytea
- Un type comme les autres
bytea: tableau d’octets- en texte :
bytea_output=hexouescape
- Récupération intégralement en mémoire !
- Toute modification entraîne la réécriture complète du
bytea - Maxi 1 Go (à éviter)
- en pratique intéressant pour quelques Mo
- compressé/déporté (TOAST)
- Import :
Voici un exemple :
Nous avons inséré la chaîne de caractère « bonjour » dans le champ bytea, en fait sa représentation binaire dans l’encodage courant (UTF-8). Si nous interrogeons la table, nous voyons la représentation textuelle du champ bytea. Elle commence par \x pour indiquer un encodage de type hex. Ensuite, chaque paire de valeurs hexadécimales représente un octet.
Un second format d’affichage est disponible : escape :
Le format de sortie escape ne protège donc que les valeurs qui ne sont pas représentables en ASCII 7 bits. Ce format peut être plus compact pour des données textuelles essentiellement en alphabet latin sans accent, où le plus gros des caractères n’aura pas besoin d’être protégé.
Cependant, le format hex est bien plus efficace à convertir, et est le défaut depuis PostgreSQL 9.0.
Avec les vieilles applications, ou celles restées avec cette configuration, il faudra peut-être forcer
bytea_outputàescape, sous peine de corruption.)
Pour charger directement un fichier, on peut notamment utiliser la fonction pg_read_binary_file, exécutée par le serveur PostreSQL :
En théorie, un bytea peut contenir 1 Go. En pratique, on se limitera à nettement moins, ne serait-ce que parce pg_dump tombe en erreur quand il doit exporter des bytea de plus de 500 Mo environ (le décodage double le nombre d’octets et dépasse cette limite de 1 Go).
Les bytea profitent du mécanisme TOAST et sont donc généralement compressés et/ou déplacés dans une table associé à la table principale. Encore plus que d’habitude, il faut proscrire les SELECT *dans votre code pour éviter de lire cette table quand ce n’est pas nécessaire.
La documentation officielle liste les fonctions pour encoder, décoder, extraire, hacher… les bytea.
Large Object
- À éviter…
- préférer
bytea
- préférer
- Maxi 4 To (éviter…)
- Objet indépendant des tables
- OID à stocker dans les tables
- se compresse mal
- Suppression manuelle !
- trigger
lo_unlink&vacuumlo
- Fonction de manipulation, modification
lo_create,lo_importlo_seek,lo_open,lo_read,lo_write…
Un large object est un objet totalement décorrélé des tables. Le code doit donc gérer cet objet séparément :
- créer le large object et stocker ce qu’on souhaite dedans ;
- stocker la référence à ce large object dans une table (avec le type
lob) ; - interroger l’objet séparément de la table ;
- le supprimer explicitement quand il n’est plus référencé : il ne disparaîtra pas automatiquement !
Le large object nécessite donc un plus gros investissement au niveau du code.
En contrepartie, il a les avantages suivant :
- une taille jusqu’à 4 To, ce qui n’est tout de même pas conseillé ;
- la possibilité d’accéder à une partie directement (par exemple les octets de 152 000 à 153 020), ce qui permet de le transférer par parties sans le charger en mémoire (notamment, le driver JDBC de PostgreSQL fournit une classe LargeObject) ;
- de ne modifier que cette partie sans tout réécrire.
Cependant, nous déconseillons son utilisation autant que possible :
- le stockage des large objects ne prévoit pas vraiment de compression : des
byteaprendront moins de place ; - il est facile et fréquent d’oublier de purger des large objects supprimés ;
- une sauvegarde logique partielle les oublie facilement (voir l’option
--large-objectsdepg_dump) ; pg_dumpn’est pas optimisé pour sauver de nombreux large objects : la sauvegarde de la tablepg_largeobjectne peut être parallélisée et peut consommer transitoirement énormément de mémoire s’il y a trop d’objets.
Il y a plusieurs méthodes pour nettoyer les large objects devenu inutiles :
- appeler la fonction
lo_unlinkdans le code client — au risque d’oublier ; - utiliser la fonction trigger
lo_managefournie par le module contriblo: (voir documentation, si les large objects ne sont jamais référencés plus d’une fois ; - appeler régulièrement le programme
vacuumlo(là encore un contrib) : il liste tous les large objects référencés dans la base, puis supprime les autres. Ce traitement est bien sûr un peu lourd.
Techniquement, un large object est stocké dans la table système pg_largeobject sous forme de pages de 2 ko. Voir la documentation pour les détails.
Quiz
Travaux pratiques
La version en ligne des solutions de ces TP est disponible sur https://dali.bo/s22_solutions.
UUID
But : Constater l’impact en cache des types d’UUID avec pg_buffercache.
Ce TP étant purement descriptif, allez voir directement la solution.
jsonb
But : Découvrir JSON
Si vous ne l’avez pas déjà, la base personnes_et_dossiers peut être installé ainsi. Elle pèse en version complète 613 Mo, pour 2 Go sur disque au final.
La base personnes contient alors deux schémas json et eav avec les mêmes données sous deux formes différentes.
Chercher la ville et le numéro de téléphones (sous-attribut
villede l’attributadressedu champ JSONpersonne) de Gaston Lagaffe, grâce aux attributsprenometnom. Effectuer de préférence la recherche en cherchant un JSON avec@>(« contient ») (Ne pas chercher encore à utiliser JSONPath).
Créer une requête qui renvoie les attributs
nom,prenom,date_naissance(comme type date) de toutes les personnes avec le nom « Lagaffe ». Utiliser la fonctionjsonb_to_record()etLATERAL. Rajoutervilleetpaysensuite de la même manière.
En supprimant le filtre, comparer le temps d’exécution de la requête précédente avec cette requête plus simple qui récupère les champs plus manuellement :
Créer un index GIN ainsi :
Quelle taille fait-il ?
Retenter les requêtes précédentes. Lesquelles utilisent l’index ?
Récupérer les numéros de téléphone de Léon Prunelle.
Afficher les noms et prénoms de Prunelles, et un tableau de champs texte contenant ses téléphones (utiliser
jsonb_array_elements_text).
Comparer le résultat et les performances de ces deux requêtes, qui récupèrent aussi les numéros de téléphone de Prunelle :
Chercher qui possède le numéro de téléphone
0650041821avec la syntaxe JSONPath.
Compter le nombre de personnes habitant à Paris ou Bruxelles avec :
- la syntaxe
@>et unOR;- une syntaxe JSONPath
@?et un « ou logique (||) ;- une syntaxe JSONPath
@?et une regex@.ville like_regex "^(Paris|Bruxelles)$".
Le compte du nombre de personne par pays doit être optimisé au maximum. Ajouter un index fonctionnel sur l’attribut
pays. Tester l’efficacité sur une recherche, et un décompte de toute les personnes par pays.
Ajouter un champ généré dans
json.personne, correspondant à l’attributpays.
Comparer les temps d’exécution du décompte des pays par l’attribut, et par cette colonne générée.
Créer un index B-tree sur la colonne générée
pays. Consulter les statistiques danspg_stats. Cet index est-il utilisable pour des filtres et le décompte parpays?
(Optionnel) Créer des colonnes générées sur
nom,prenom,date_naissance, etville(en un seul ordre). Reprendre la requête plus haut qui les affiche tous et comparer les performances.
Ajouter l’attribut
animauxà Gaston Lagaffe, avec la valeur 18. Vérifier en relisant la ligne.
Ajouter l’attribut
animauxà 2% des individus au hasard, avec une valeur 1 ou 2.
Compter le nombre de personnes avec des animaux (avec ou sans JSONPath). Proposer un index qui pourait convenir à d’autres futurs nouveaux attributs peu fréquents.
Large Objects
But : Utilisation de Large Objects
- Créer une table
fichiersavec un texte et une colonne permettant de référencer des Large Objects.
- Importer un fichier local à l’aide de psql dans un large object.
- Noter l’
oidretourné.
- Importer un fichier du serveur à l’aide de psql dans un large object.
- Afficher le contenu de ces différents fichiers à l’aide de psql.
- Les sauvegarder dans des fichiers locaux.
Travaux pratiques (solutions)
UUID
Tout ce qui suit doit se dérouler dans la même base, par exemple :
Ce TP est prévu pour un shared_buffers de 128 Mo (celui par défaut). Si le vôtre est plus gros, le TP devra peut-être durer plus longtemps :
Utilisez au moins une fenêtre pour les ordres shell et une pour les ordres SQL.
Créer avec le script suivants les deux versions d’un petit modèle avec des capteurs, et les données horodatées qu’ils renvoient ; ainsi que les deux procédures pour remplir ces tables ligne à ligne :
Vous devez obtenir ces tables et une séquence :
et ces index :
Créer deux fichiers SQL contenants juste les appels de fonctions, qui serviront pour pgbench :
Dans la même base que les table ci-dessus, installer l’extension pg_buffercache qui va nous permettre de voir ce qu’il y a dans le cache de PostgreSQL :
La vue du même nom contient une ligne par bloc. La requête suivante permet de voir lesquelles de nos tables utilisent le cache :
Cette version semi-graphique est peut-être plus parlante :
Dans une fenêtre, lancer l’une de ces requêtes (dans la bonne base !), puis la répéter toutes les secondes ainsi :
Dans une autre fenêtre, lancer
pgbenchavec deux clients, et le script pour remplir la tabledonnees1:
Le nombre de transactions dépend fortement de la machine, mais peut atteindre plusieurs milliers à la seconde.
Les tables peuvent rapidement atteindre plusieurs gigaoctets. N’hésitez pas à les vider ensemble de temps à autre.
Quelle est la répartition des données dans le cache ?
Après peu de temps, la répartition doit ressembler à peu près à ceci :
Et ce, même si la table et ses index ne tiennent plus intégralement dans le cache.
La table donnees1 représente la majorité du cache.
Interrompre
pgbenchet le relancer pour remplirdonnees2:
Noter que le débit en transaction est du même ordre de grandeur : les UUID ne sont pas spécialement lourds à générer.
Que devient la répartition des données dans le cache ?
donnees1 et ses index est chassé du cache par les nouvelles données, ce qui est logique.
Surtout, on constate que la clé primaire de donnnes2 finit par remplir presque tout le cache. Dans ce petit cache, il n’y a plus de place même pour les données de donnees2 !
Interrompre
pgbench, purger les tables et lancer les deux scripts d’alimentation en même temps.
On constate le même phénomène de monopolisation du cache par donnees2, bien que les deux tables de données aient le même nombre de lignes :
Avez-vous remarqué une différence de vitesse entre les deux traitements ?
Ce ne peut être rigoureusement établi ici. Les volumétries sont trop faibles par rapport à la taille des mémoires et il faut tester sur la durée. Le nombre de clients doit être étudié pour utiliser au mieux les capacités de la machine sans monter jusqu’à ce que la contention devienne un problème. Les checkpoints font également varier les débits.
Cependant, si vous laissez le test tourner très longtemps avec des tailles de tables de plusieurs Go, les effets de cache seront très différents :
- dans
donnees1, le débit en insertion devrait rester correct, car seuls les derniers blocs en cache sont utiles ; - dans
donnees2, le débit en insertion doit progressivement baisser : chaque insertion a besoin d’un bloc de l’index de clé primaire différent, qui a de moins en moins de chance de se trouver dans le cache de PostgreSQL, puis dans le cache de Linux.
L’impact sur les I/O augmente donc, et pas seulement à cause de la volumétrie supérieure des tables avec UUID. À titre d’exemple, sur une petite base de formation avec 3 Go de RAM :
Comparer les tailles des tables et index avant et après un
VACUUM FULL. Où était la fragmentation ?
VACUUM FULL reconstruit complètement les tables et aussi les index.
Tables avant le VACUUM FULL :
Après :
Les tailles des tables donnees1 et donnees2 ne bougent pas. C’est normal, il n’y a eu que des insertions à chaque fois en fin de table, et ni modification ni suppression de données.
Index avant le VACUUM FULL :
Index après :
Les index d’horodatage gardent la même taille qu’avant (la différence entre eux est dû à des nombres de lignes différents dans cet exemple). L’index sur la clé primaire de donnees1 (bigint) n’était pas fragmenté. Par contre, donnees2_pkey se réduit de 29% ! Les index UUID (v4) ont effectivement tendance à se fragmenter.
Les UUID générés avec
gen_random_uuidsont de version 4. Créer la fonction suivante pour générer des UUID version 7, l’utiliser dans la fonction d’alimentation dedonnees2, et relancer les deux alimentations :
Après quelques dizaines de secondes :
- les deux tables doivent être présentes dans le cache de manière similaire ;
- les index primaires doivent être présents de manière anecdotique ;
donnees2occupe une taille un peu supérieure à cause de la taille double des UUID par rapport auxbigintdedonnees1:
Relancez
pgbenchpour chargerdonnees2, alternez entre les deux versions de la fonctioninsere_donnees_2.
Le débit en transactions varie ici d’un facteur 2. Noter que la durée des transactions est aussi beaucoup plus stable (stddev).
jsonb : lecture de champs
Si vous ne l’avez pas déjà, la base personnes_et_dossiers peut être installé ainsi. Elle pèse en version complète 613 Mo, pour 2 Go sur disque au final.
La base personnes contient alors deux schémas json et eav avec les mêmes données sous deux formes différentes.
La table json.personnes contient une ligne par personne, un identifiant et un champ JSON avec de nombreux attributs. Elle n’est pas encore indexée :
Chercher la ville et le numéro de téléphone (sous-attribut
villede l’attributadressedu champ JSONpersonne) de Gaston Lagaffe, grâce aux attributsprenometnom. Effectuer de préférence la recherche en cherchant un JSON avec@>(« contient ») (Ne pas chercher encore à utiliser JSONPath).
La recherche peut s’effectuer en convertissant tous les attributs en texte :
On obtient « Bruxelles ».
Autre variante de syntaxe :
Il est plus propre de rechercher grâce à une de ces syntaxes, notamment parce qu’elles seront indexables plus tard :
ou :
Créer une requête qui renvoie les attributs
nom,prenom,date_naissance(comme type date) de toutes les personnes avec le nom « Lagaffe ». Utiliser la fonctionjsonb_to_record()etLATERAL. Rajoutervilleetpaysensuite de la même manière.
jsonb_to_record exige que l’on fournisse le nom de l’attribut et son type :
Avec la ville, qui est dans un sous-attribut, il faut rajouter une clause LATERAL :
En supprimant le filtre, comparer le temps d’exécution de la requête précédente avec cette requête plus simple qui récupère les champs plus manuellement :
Cette dernière requête est nettement plus lente que l’utilisation de jsonb_to_record, même si les I/O sont plus réduites :
La maintenabilité plaide pour la seconde version. Quant à la lisibilité entre les deux versions de la requête, c’est un choix personnel.
jsonb : index GIN jsonb_path_ops
Créer un index GIN ainsi :
Quelle taille fait-il ?
L’index peut être un peu long à construire (plusieurs dizaines de secondes) et est assez gros :
Retenter les requêtes précédentes. Lesquelles utilisent l’index ?
Les requêtes utilisant les égalités (que ce soit sur du texte ou en JSON) n’utilisent pas l’index :
Par contre, la syntaxe @> (« contient ») utilise l’index, quelle que soit la manière dont on construit le JSON critère. Le gain en temps et en I/O (et en CPU) grâce à l’index est assez foudroyant. Et ceci, quelle que soit la manière dont on récupère les champs, puisqu’il n’y a plus qu’une poignée de lignes à analyser :
Les requêtes sans filtre n’utilisent pas l’index, bien sûr.
jsonb et tableaux
Récupérer les numéros de téléphone de Léon Prunelle.
Le sous-attribut telephones est un tableau. La syntaxe ->0 ne renvoie que le premier élément :
Les 4 premières lignes renvoient un jsonb, les trois dernières sa conversion en texte :
Afficher les noms et prénoms de Prunelles, et un tableau de champs texte contenant ses numéros de téléphone (utiliser
jsonb_array_elements_text).
Il vaut mieux ne pas « bricoler » avec des conversions manuelles du JSON en texte puis en tableau. La fonction dédiée est jsonb_array_elements_text.
Cependant on multiplie les lignes par le nombre de numéros de téléphone, et il faut réagréger :
La version suivante fonctionnerait aussi dans ce cas précis (cependant elle sera moins performante s’il y a beaucoup de lignes, car PostgreSQL voudra faire un agrégat global au lieu d’un simple parcours ; il faudra aussi vérifier que la clé d’agrégation tient compte d’homonymes).
(Noter que la fonction sœur jsonb_array_elements() renverrait, elle, des JSON.)
Accès JSONPath
Comparer le résultat et les performances de ces deux requêtes, qui récupèrent aussi les numéros de téléphone de Prunelle :
Le résultat est le même dans les deux cas :
Par contre, le plan et les temps d’exécutions sont totalement différents. La clause jsonb_path_query unique parcourt complètement la table :
Tandis que la séparation du filtrage et de l’affichage permet à PostgreSQL de sélectionner les lignes, et donc de passer par un index avant de procéder à l’affichage.
(À la place de @@, la syntaxe classique @> avec un JSON comme critère, est aussi performante dans ce cas simple.)
Chercher qui possède le numéro de téléphone
0650041821avec la syntaxe JSONPath.
Ces deux syntaxes sont équivalentes :
Dans les deux cas, EXPLAIN montre que l’index GIN est bien utilisé.
Compter le nombre de personnes habitant à Paris ou Bruxelles avec :
- la syntaxe
@>et unOR;- une syntaxe JSONPath
@?et un « ou » logique (||) ;- une syntaxe JSONPath
@?et une regex@.ville like_regex "^(Paris|Bruxelles)$".
Vous devez trouver 63 personnes avec la version complète de la base.
Cet appel va utiliser l’index GIN :
Cet appel aussi :
Par contre, l’index GIN est inutilisable si l’on demande une expression régulière (aussi simple soit-elle) :
Index fonctionnel, colonne générée et JSON
Le compte du nombre de personne par pays doit être optimisé au maximum. Ajouter un index fonctionnel sur l’attribut
pays. Tester l’efficacité sur une recherche, et un décompte de toutes les personnes par pays.
Suivant la syntaxe préférée, l’index peut être par exemple ceci :
L’index contient peu de valeurs et profite de la déduplication, il fait donc au plus 3 Mo.
Cet index est utilisable pour une recherche à condition que la syntaxe de l’expression soit rigoureusement identique, ce qui limite les cas d’usage.
Par contre, pour le décompte complet, il n’a aucun intérêt :
En effet, un index fonctionnel ne permet pas un Index Only Scan. Pourtant, il pourrait être très intéressant ici.
Ajouter un champ généré dans
json.personne, correspondant à l’attributpays.
Attention, l’ordre va réécrire la table, ce qui peut être long (de l’ordre de la minute, suivant le matériel) :
Comparer les temps d’exécution du décompte des pays par l’attribut, et par cette colonne générée.
Par contre, la lecture directe du champ est nettement plus rapide :
Le plan est pourtant le même : un Seq Scan, faute de clause de filtrage et d’index, suivi d’un agrégat parallélisé n’utilisant que quelques kilooctets de mémoire.
Le champ généré a donc un premier intérêt en terme de rapidité de lecture des champs, surtout avec des JSON importants comme ici.
Créer un index B-tree sur la colonne générée
pays. Consulter les statistiques danspg_stats. Cet index est-il utilisable pour des filtres et le décompte parpays?
Ces deux ordres ne durent qu’1 ou 2 secondes.
Le gain en temps est appréciable. Mais l’intérêt principal réside ici dans le nombre de blocs lus divisé par 100 ! Le nouvel index ne fait que 3 Mo.
(Optionnel) Créer des colonnes générées sur
nom,prenom,date_naissance, etville(en un seul ordre). Reprendre la requête plus haut qui les affiche tous et comparer les performances.
Un champ va poser problème : la date de naissance. En effet, la date est stockée au format texte, il faudra soi-même faire la conversion. De plus, un simple opérateur ::date ne peut être utilisé dans une expression de GENERATED car il n’est pas « immutable » (pour des raisons techniques).
Un contournement pas très performant est celui-ci :
Une autre possibilité plus performante est d’enrober to_date() dans une fonction immutable, puisqu’il n’y a, dans ce cas précis, pas d’ambiguïté sur le format ISO :
et l’ordre devient :
Les conversions de texte vers des dates sont des sources fréquentes de problèmes. Le conseil habituel est de toujours stocker une date dans un champ de type date ou timestamp/timestamptz. Mais si elle provient d’un JSON, il faudra gérer soi-même la conversion.
Quelle que soit la méthode, la requête suivante :
est beaucoup plus rapide que :
elle-même plus rapide que les extractions manuelles des attributs un à un, comme vu plus haut.
Certes, la table est un peu plus grosse, mais le coût d’insertion des colonnes générées est donc souvent rentable pour les champs fréquemment utilisés.
jsonb et mise à jour
Ajouter l’attribut
animauxà Gaston Lagaffe, avec la valeur 18. Vérifier en relisant la ligne.
Ajouter l’attribut
animauxà 2% des individus au hasard, avec une valeur 1 ou 2.
On utilise ici la fonction jsonb_build_object(), plus adaptée à la construction d’un JSON qui n’est pas une constante. Le choix des individus peut se faire de plusieurs manières, par exemple avec random(), mod()…
Compter le nombre de personnes avec des animaux (avec ou sans JSONPath). Proposer un index qui pourrait convenir à d’autres futurs nouveaux attributs peu fréquents.
Ces requêtes renvoient 10654, mais effectuent toutes un Seq Scan avec une durée d’exécution aux alentours de la seconde :
(Remarquer que les deux dernières requêtes utiliseraient l’index GIN pour des égalités comme (@ == 0) ou (@ == 18), et seraient presque instantanées. Là encore, c’est une limite des index GIN.)
On pourrait indexer (personne->>'animaux')::int, ce qui serait excellent pour la première requête, mais ne conviendrait pas à d’autres critères.
L’opérateur ? ne sait pas utiliser l’index GIN jsonb_path_ops existant. Par contre, il peut profiter de l’opérateur GIN par défaut :
Il est directement utilisable par tout autre attribut :
Cet index avec l’opérateur jsonb_ops a par contre le gros inconvénient d’être encore plus gros que l’index GIN avec jsonb_path_ops (303 Mo contre 235 Mo), et d’alourdir encore les mises à jour. Il peut cependant remplacer ce dernier, de manière un peu moins performante. Il faut aviser selon les requêtes, la place, les écritures…
Large Objects
- Créer une table
fichiersavec un texte et une colonne permettant de référencer des Large Objects.
- Importer un fichier local à l’aide de psql dans un large object.
- Noter l’
oidretourné.
- Importer un fichier du serveur à l’aide de psql dans un large object.
- Afficher le contenu de ces différents fichiers à l’aide de psql.
- Les sauvegarder dans des fichiers locaux.
Navigation
Pour revenir au chapitre précédent, voir le chapitre précédent. Pour poursuivre la lecture, voir le chapitre suivant.