TL ; DR : JSONB peut grandement simplifier le développement de schémas de base de données sans sacrifier les performances des requêtes.
introduction
Donnons un exemple classique de probablement l'un des cas d'utilisation les plus anciens au monde d'une base de données relationnelle (base de données) : nous avons une entité, et nous devons sauvegarder certaines propriétés (attributs) de cette entité. Mais toutes les instances peuvent ne pas avoir le même ensemble de propriétés, et d'autres propriétés pourraient être ajoutées à l'avenir.
Le moyen le plus simple de résoudre ce problème consiste à créer une colonne dans la table de base de données pour chaque valeur de propriété et à remplir simplement celles nécessaires pour une instance d'entité spécifique. Super! Problème résolu... jusqu'à ce que votre table contienne des millions d'enregistrements et que vous deviez ajouter un nouvel enregistrement.
Considérez le modèle EAV (), cela arrive assez souvent. Une table contient des entités (enregistrements), une autre table contient des noms de propriétés (attributs) et une troisième table associe les entités à leurs attributs et contient la valeur de ces attributs pour l'entité actuelle. Cela vous donne la possibilité d'avoir différents ensembles de propriétés pour différents objets, et également d'ajouter des propriétés à la volée sans modifier la structure de la base de données.
Cependant, je n’écrirais pas cet article s’il n’y avait pas quelques inconvénients à l’approche EVA. Ainsi, par exemple, pour obtenir une ou plusieurs entités ayant chacune 1 attribut, 2 jointures sont nécessaires dans la requête : la première est une jointure avec la table attributaire, la seconde est une jointure avec la table des valeurs. Si une entité possède 2 attributs, alors 4 jointures sont nécessaires ! De plus, tous les attributs sont généralement stockés sous forme de chaînes, ce qui entraîne un transtypage du résultat et de la clause WHERE. Si vous écrivez beaucoup de requêtes, cela représente un gaspillage considérable en termes d'utilisation des ressources.
Malgré ces défauts évidents, l’EAV est utilisé depuis longtemps pour résoudre ce type de problèmes. Il s’agissait là de défauts 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, le type de données JSONB a été ajouté pour stocker les données binaires JSON. 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, l'exécution d'opérations dessus est beaucoup plus rapide. JSONB prend également 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 une seule colonne JSONB à notre table d'entités, simplifiant ainsi considérablement la conception de la base de données. Mais beaucoup soutiennent que cela devrait s'accompagner d'une diminution de la productivité... C'est pourquoi j'ai écrit cet article.
Mise en place d'une base de données de tests
Pour cette comparaison, j'ai créé la base de données sur une nouvelle installation de PostgreSQL 9.5 sur la version à 80 $ Ubuntu 14.04. Après avoir défini certains paramètres dans postgresql.conf, j'ai exécuté script utilisant psql. Les tableaux suivants ont été créés pour présenter les données sous forme EAV :
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 un tableau où les mêmes données seront stockées, mais avec des attributs dans une colonne de type JSONB – propriétés.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Cela semble beaucoup plus simple, n'est-ce pas ? Ensuite, il a été ajouté aux tables d'entités (entité & entité_jsonb) 10 millions d'enregistrements, et par conséquent, le tableau a été rempli avec les mêmes données en utilisant le modèle EAV et l'approche avec une colonne JSONB - entité_jsonb.properties. Ainsi, nous avons reçu plusieurs types de données différents parmi l’ensemble des propriétés. Exemples de données :
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Nous avons donc maintenant les mêmes données pour les deux options. Commençons par comparer les implémentations au travail !
Simplifiez votre conception
Il a été indiqué précédemment que la conception de la base de données était grandement simplifiée : une table, en utilisant une colonne JSONB pour les propriétés, au lieu d'utiliser trois tables pour l'EAV. Mais comment cela se reflète-t-il dans les demandes ? La mise à jour d'une propriété d'entité ressemble à ceci :
-- 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 vous pouvez le constater, 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 devrait transmettre notre nouvelle valeur en tant qu'objet JSONB. Cependant, nous n’avons pas besoin de connaître un identifiant à l’avance. En regardant l'exemple EAV, nous devons connaître à la fois l'entity_id et l'entity_attribute_id afin d'effectuer la mise à jour. Si vous souhaitez mettre à jour une propriété dans une colonne JSONB en fonction du nom de l'objet, tout se fait sur une seule ligne.
Sélectionnons maintenant 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 l'on peut convenir que le second est plus court (pas de jointure !), et donc plus lisible. JSONB gagne ici ! Nous utilisons l'opérateur JSON ->> pour obtenir la couleur sous forme de valeur texte à partir d'un objet JSONB. Il existe également une deuxième façon d'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é : on vérifie si l'objet JSON dans sa colonne de propriétés contient un objet qui se trouve à droite de l'opérateur @>. Moins lisible, plus productif (voir ci-dessous).
Rendons l'utilisation de JSONB encore plus facile lorsque vous devez sélectionner plusieurs propriétés à la fois. C'est là que l'approche JSONB entre vraiment en jeu : nous sélectionnons simplement des propriétés comme colonnes supplémentaires dans notre jeu de résultats sans avoir besoin 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 une grande simplification dans la conception des bases de données. Voir plus d'exemples sur la façon d'écrire des requêtes JSONB, également dans poste.
Il est maintenant temps de parler de performances.
Performance
Pour comparer les performances, j'ai utilisé dans les requêtes, pour calculer le temps d'exécution. Chaque requête a été exécutée au moins trois fois car le planificateur de requêtes prend plus de temps la première fois. J’ai d’abord exécuté les requêtes sans aucun index. Évidemment, c'était un avantage de JSONB, puisque les jointures requises 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 sur les 2 colonnes de clés étrangères de la table de valeurs EAV, ainsi qu'un index pour une colonne JSONB.
La mise à jour des données a montré les résultats suivants en termes de temps (en ms). Notez que l'échelle est logarithmique :
On voit que JSONB est beaucoup (> 50000-x) plus rapide que EAV si vous n'utilisez pas d'index, pour la raison évoquée ci-dessus. Lorsque nous indexons des colonnes avec des clés primaires, la différence disparaît presque, mais JSONB est toujours 1,3 fois plus rapide qu'EAV. Notez que l'index sur la colonne JSONB n'a aucun effet ici puisque nous n'utilisons pas la colonne property dans les critères d'évaluation.
Pour sélectionner les données en fonction de la valeur de la propriété, nous obtenons les résultats suivants (échelle normale) :
Vous pouvez remarquer que JSONB fonctionne à nouveau plus rapidement que EAV sans index, mais lorsque EAV avec index, il fonctionne toujours plus rapidement que JSONB. Mais ensuite j'ai vu que les temps pour les requêtes JSONB étaient les mêmes, cela m'a fait comprendre que les index GIN ne fonctionnent pas. Apparemment, lorsque vous utilisez un index GIN sur une colonne avec des propriétés renseignées, cela ne prend effet que lors de l'utilisation de l'opérateur d'inclusion @>. Je l'ai utilisé dans un nouveau test et cela a eu un impact énorme sur le temps : seulement 0,153 ms ! C'est 15000 25000 fois plus rapide que l'EAV et XNUMX XNUMX fois plus rapide que l'opérateur ->>.
Je pense que c'était assez rapide !
Taille de la table de base de données
Comparons les tailles de table pour les deux approches. Dans psql, nous pouvons afficher la taille de toutes les tables et index en utilisant la commande dti+
Pour l'approche EAV, les tailles de table sont d'environ 3068 3427 Mo et les index jusqu'à 6,43 1817 Mo pour un total de 318 Go. L'approche JSONB utilise 2,08 3 Mo pour la table et XNUMX Mo pour les index, soit XNUMX Go. Il s'avère que c'est XNUMX fois moins ! Ce fait m'a un peu surpris car nous stockons les noms de propriétés dans chaque objet JSONB.
Mais quand même, les chiffres parlent d'eux-mêmes : dans EAV, nous stockons 2 clés étrangères entières par valeur d'attribut, ce qui donne 8 octets de données supplémentaires. De plus, EAV stocke toutes les valeurs de propriété sous forme de texte, tandis que JSONB utilisera des valeurs numériques et booléennes en interne lorsque cela est possible, ce qui réduira l'encombrement.
Les résultats de
Dans l'ensemble, je pense que l'enregistrement des propriétés d'entité au format JSONB peut faciliter la conception et la maintenance de votre base de données. Si vous exécutez beaucoup de requêtes, conserver tout dans la même table que l'entité fonctionnera plus efficacement. Et le fait que cela simplifie l'interaction entre les données est déjà un plus, mais la base de données résultante est 3 fois plus petite en volume.
De plus, sur la base des tests effectués, nous pouvons conclure que les pertes de performances sont très insignifiantes. Dans certains cas, JSONB est encore plus rapide que EAV, ce qui le rend encore meilleur. Cependant, ce benchmark ne couvre bien sûr pas tous les aspects (ex : entités avec un très grand nombre de propriétés, augmentation significative du nombre de propriétés des données existantes,...), alors si vous avez des suggestions pour les améliorer , n'hésitez pas à laisser vos commentaires dans les commentaires !
Source: habr.com