TL; DR : JSONB peut simplifier considérablement le développement du schéma de base de données sans compromettre la performance des requêtes.
Introduction
Prenons un exemple classique, probablement l'une des plus anciennes utilisations dans le monde des bases de données relationnelles : nous avons une entité et devons conserver certaines propriétés (attributs) de cette entité. Mais tous les exemples peuvent ne pas avoir le même ensemble de propriétés, et il est également possible d'ajouter d'autres propriétés à l'avenir.
La manière la plus simple de résoudre ce problème est de créer une colonne dans la table de la base de données pour chaque valeur de propriété et de simplement remplir celles qui sont nécessaires pour un exemple spécifique de l'entité. Excellent ! Problème résolu … jusqu'à ce que votre table contienne des millions d'enregistrements et que vous deviez ajouter un nouvel enregistrement.
Considérons le modèle EAV (), qui est assez courant. Une table contient des entités (enregistrements), une autre table contient les noms des propriétés (attributs) et une troisième table lie les entités à leurs attributs et contient la valeur de ces attributs pour l'entité actuelle. Cela vous permet d'avoir différents ensembles de propriétés pour différents objets, ainsi que d'ajouter des propriétés "à la volée", sans modifier la structure de la base de données.
Cependant, je ne rédigerais pas cette note s'il n'y avait pas de inconvénients à l'approche utilisant EAV. Par exemple, pour obtenir une ou plusieurs entités ayant 1 attribut, il faut 2 jointures dans la requête : la première est une jointure avec la table des attributs, la deuxième une jointure avec la table des valeurs. Si une entité a 2 attributs, il en faut déjà 4 ! De plus, tous les attributs sont généralement stockés sous forme de chaînes, ce qui entraîne des conversions de types tant pour le résultat que pour la condition WHERE. Si vous écrivez beaucoup de requêtes, cela devient assez gaspilleux en termes d'utilisation des ressources.
Malgré ces inconvénients évidents, EAV est depuis longtemps utilisé pour résoudre ce type de problèmes. Ce sont des inconvénients inévitables, et il n'y avait tout simplement pas de meilleure alternative.
Mais ensuite, une nouvelle "technologie" est apparue dans PostgreSQL…
À partir de PostgreSQL 9.4, un type de données JSONB a été ajouté pour stocker des données JSON binaires. Bien que le stockage de JSON dans ce format prenne généralement un peu plus d'espace et de temps que le JSON en texte brut, les opérations effectuées sur celles-ci se font beaucoup plus rapidement. De plus, JSONB prend en charge l'indexation, ce qui rend les requêtes encore plus rapides.
Le type de données JSONB nous permet de remplacer le modèle EAV encombrant en ajoutant simplement une colonne JSONB dans notre table d'entités, ce qui simplifie considérablement la conception de la base de données. Mais beaucoup soutiennent que cela doit s'accompagner d'une baisse des performances… C'est pour cette raison que cet article a été écrit.
Configuration de la base de données de test
Pour cette comparaison, j'ai créé une base de données sur une nouvelle installation de PostgreSQL 9.5 sur une machine à 80 dollars. Ubuntu 14.04. Après avoir configuré certains paramètres dans postgresql.conf, j'ai exécuté le script à l'aide de psql. Pour représenter les données sous forme EAV, les tables suivantes ont été créées :
CREATE TABLE entity (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT
);
CREATE TABLE entity_attribute (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE entity_attribute_value (
id SERIAL PRIMARY KEY,
entity_id INT REFERENCES entity(id),
entity_attribute_id INT REFERENCES entity_attribute(id),
value TEXT
);
Ci-dessous se trouve une table où seront stockées les mêmes données, mais avec des attributs dans une colonne de type JSONB – properties.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Cela semble beaucoup plus simple, n'est-ce pas ? Ensuite, 10 millions d'enregistrements ont été ajoutés aux tables d'entités (entity & entity_jsonb) et, en conséquence, les données identiques ont été peuplées dans les tables où le modèle EAV est utilisé et celles avec la colonne JSONB – entity_jsonb.properties. Ainsi, nous avons obtenu plusieurs types de données différents parmi l'ensemble des propriétés. Exemple de données :
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "rouge"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgique"
}
}Ainsi, nous avons maintenant des données identiques pour les deux variantes. Commençons à comparer les implémentations en action !
Simplification de la conception
Il a déjà été mentionné que la conception de la base de données a été considérablement simplifiée : une seule table, grâce à l'utilisation de la colonne JSONB pour les propriétés, au lieu de trois tables pour EAV. Mais comment cela se reflète-t-il dans les requêtes ? La mise à jour d'une propriété d'une entité se présente comme suit :
-- EAV
UPDATE entity_attribute_value
SET value = 'blue'
WHERE entity_attribute_id = 1
AND entity_id = 120;
-- JSONB
UPDATE entity_jsonb
SET properties = jsonb_set(properties, '{"color"}', '"blue"')
WHERE id = 120;
Comme nous le voyons, la dernière requête ne semble pas plus simple. Pour mettre à jour la valeur d'une propriété dans un objet JSONB, nous devons utiliser la fonction , et nous devons passer notre nouvelle valeur sous forme d'objet JSONB. Cependant, nous n'avons pas besoin de connaître d'identifiant à l'avance. En regardant l'exemple avec EAV, nous devons connaître à la fois entity_id et entity_attribute_id pour effectuer la mise à jour. Si vous souhaitez mettre à jour une propriété dans la colonne JSONB en fonction du nom de l'objet, cela se fait en une seule ligne simple.
Maintenant, sélectionnons l'entité que nous venons de mettre à jour, en fonction de sa nouvelle couleur :
-- EAV
SELECT e.name
FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties ->> 'color' = 'blue';
Je pense que nous pouvons convenir que la seconde est plus courte (sans jointure !), et donc plus lisible. Ici, la victoire est à JSONB ! Nous utilisons l'opérateur JSON ->> pour obtenir la couleur comme valeur texte de l'objet JSONB. Il existe également une seconde méthode pour obtenir le même résultat dans le modèle JSONB en utilisant l'opérateur @> :
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
C'est un peu plus compliqué : nous vérifions si l'objet JSON dans la colonne des propriétés contient l'objet qui se trouve à droite de l'opérateur @>. Moins lisible, plus performant (voir ci-dessous).
Simplifions l'utilisation de JSONB encore plus quand vous devez sélectionner plusieurs propriétés en même temps. C'est là que l'approche JSONB est vraiment adaptée : nous choisissons simplement les propriétés comme colonnes supplémentaires dans notre ensemble de résultats sans nécessiter de jointures :
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Avec EAV, vous aurez besoin de 2 jointures pour chaque propriété que vous souhaitez interroger. À mon avis, les requêtes ci-dessus montrent un grand simplification dans la conception de la base de données. Vous pouvez également voir plus d'exemples de requêtes JSONB dans ce post.
Maintenant, il est temps de parler des performances.
Performance
Pour comparer les performances, j'ai utilisé dans les requêtes, pour le calcul du temps d'exécution. Chaque requête a été exécutée au moins trois fois, car le planificateur de requêtes nécessite plus de temps lors de la première exécution. Tout d'abord, j'ai exécuté les requêtes sans aucun index. Évidemment, cela a été un avantage pour JSONB, puisque les jointures nécessaires pour EAV ne pouvaient pas utiliser d'index (les champs de clé étrangère n'étaient pas indexés). Après cela, j'ai créé un index pour 2 colonnes de clés étrangères dans la table des valeurs EAV, ainsi qu'un index pour la colonne JSONB.
Les mises à jour de données ont montré les résultats suivants en termes de temps (en ms). Notez que l'échelle est logarithmique :
Nous constatons que JSONB est de loin (> 50000 fois) plus rapide que EAV lorsqu'aucun index n'est utilisé, pour la raison indiquée ci-dessus. Lorsque nous indexons les colonnes avec des clés primaires, la différence disparaît presque, mais JSONB reste 1,3 fois plus rapide que EAV. Notez que l'index de la colonne JSONB n'a ici aucun impact, car nous n'utilisons pas la colonne des propriétés dans les critères d'évaluation.
Pour la sélection de données basée sur la valeur d'une propriété, nous obtenons les résultats suivants (échelle standard) :
On peut constater que JSONB est à nouveau plus rapide que EAV sans index, mais lorsque EAV est indexé, il fonctionne tout de même plus rapidement que JSONB. Cependant, j'ai remarqué que le temps pour les requêtes JSONB était constant, ce qui m'a amené à penser que l'index GIN ne fonctionnait pas. En effet, lorsque vous utilisez un index GIN pour une colonne avec des propriétés remplies, il n'agit que lors de l'utilisation de l'opérateur d'inclusion @> . J'ai utilisé cela dans un nouveau test, ce qui a eu un énorme impact sur le temps : seulement 0,153 ms ! C'est 15000 fois plus rapide que EAV et 25000 fois plus rapide que l'opérateur ->.
Je pense que c'était suffisamment rapide !
Taille des tables BD
Comparons les tailles des tables selon les deux approches. Dans psql, nous pouvons afficher la taille de toutes les tables et indexes avec la commande dti+
Pour l'approche EAV, la taille des tables est d'environ 3068 Mo, et celle des index jusqu'à 3427 Mo, ce qui donne un total de 6,43 Go. Avec l'approche JSONB, il utilise 1817 Mo pour la table et 318 Mo pour les index, soit 2,08 Go. Cela représente trois fois moins ! Ce fait m'a un peu surpris, car nous stockons les noms des propriétés dans chaque objet JSONB.
Mais les chiffres parlent d'eux-mêmes : dans EAV, nous stockons 2 clés étrangères entières pour la valeur de l'attribut, ce qui nous donne 8 octets de données supplémentaires. De plus, dans EAV, toutes les valeurs des propriétés sont stockées sous forme de texte, tandis que JSONB utilisera des valeurs numériques et logiques à l'intérieur, lorsque cela est possible, ce qui entraîne un volume d'informations plus faible.
Résultats
Dans l'ensemble, je pense que le stockage des propriétés des entités au format JSONB peut considérablement simplifier la conception et la maintenance de votre base de données. Si vous effectuez de nombreuses requêtes, tout ce qui est stocké dans une table avec l'entité fonctionnera réellement de manière plus efficace. Et le fait que cela facilite l'interaction entre les données est déjà un avantage, mais la base de données résultante est également trois fois plus petite en volume.
De plus, d'après les tests réalisés, il est possible de conclure que les pertes de performance sont très peu significatives. Dans certains cas, JSONB fonctionne même plus rapidement que EAV, ce qui le rend encore meilleur. Cependant, ce test de référence ne couvre bien sûr pas tous les aspects (par exemple, des entités avec un très grand nombre de propriétés, un nombre considérable d'augmentations des propriétés des données existantes,...). Donc, si vous avez des suggestions pour l'améliorer, n'hésitez pas à les laisser dans les commentaires !
Source : habr.com
