Remplacement d'EAV par JSONB dans PostgreSQL

TL DR: JSONB peut grandement simplifier le développement de schémas de base de données sans sacrifier les performances des requêtes.

Présentation

Donnons un exemple classique, probablement, de l'un des cas d'utilisation les plus anciens des bases de données relationnelles (base de données): nous avons une entité, et il faut conserver certaines propriétés (attributs) de cette entité. Mais toutes les instances ne peuvent pas avoir le même ensemble de propriétés, en plus, à l'avenir, l'ajout possible de plus de propriétés.

La façon la 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 qui sont nécessaires pour une instance d'entité spécifique. Super! Le problème est résolu ... jusqu'à ce que votre table contienne des millions d'enregistrements et que vous n'ayez pas besoin d'en ajouter un nouveau.

Considérez le modèle EAV ( Entity-Attribute-Value ), il est assez courant. Une table contient des entités (enregistrements), une autre table contient les noms des propriétés (attributs) et la troisième table associe des 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, ainsi que d'ajouter des propriétés à la volée, sans changer la structure de la base de données.

Néanmoins, je n'écrirais pas cette note s'il n'y avait pas de lacunes dans l'approche utilisant l'EVA. Ainsi, par exemple, pour obtenir une ou plusieurs entités qui ont 1 attribut chacune, 2 join'a (jointures) dans la requête sont requises: la première est une union avec la table attributaire, la seconde est l'union avec la table de valeurs. Si une entité a 2 attributs, alors 4 jointures sont déjà nécessaires! De plus, tous les attributs sont généralement stockés sous forme de chaînes, ce qui conduit à une conversion de type pour le résultat et la clause WHERE. Si vous écrivez beaucoup de demandes, c'est plutôt un gaspillage en termes d'utilisation des ressources.

Malgré ces défauts évidents, l'EAV a longtemps été utilisé pour résoudre ce genre de problèmes. Ces défauts étaient inévitables et il n'y avait tout simplement pas de meilleure alternative.
Mais une nouvelle «technologie» est apparue dans PostgreSQL ...

À partir de PostgreSQL 9.4, un type de données JSONB a été ajouté pour stocker les 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 avec lui sont beaucoup plus rapides. 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 volumineux en ajoutant une seule colonne JSONB à notre table d'entités, ce qui simplifie considérablement la conception de la base de données. Mais beaucoup soutiennent que cela devrait s'accompagner d'une baisse de productivité ... C'est pourquoi je suis apparu dans cet article.

Tester la configuration de la base de données

Pour cette comparaison, j'ai créé une base de données sur une nouvelle installation de PostgreSQL 9.5 sur la construction à 80 $ de DigitalOcean Ubuntu 14.04. Après avoir défini certains paramètres dans postgresql.conf, j'ai exécuté ce script à l'aide de psql. Les tableaux suivants ont été créés pour représenter les données au format 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 est un tableau où les mêmes données seront stockées, mais avec des attributs dans la colonne de type JSONB - propriétés .

 CREATE TABLE entity_jsonb ( id SERIAL PRIMARY KEY, name TEXT, description TEXT, properties JSONB ); 

Semble beaucoup plus facile, non? Ensuite, 10 millions d'enregistrements ont été ajoutés aux tables d'entité ( entity & entity_jsonb ), et en conséquence, les mêmes données de table ont été remplies à l'aide du modèle EAV et de l'approche avec la colonne JSONB - entity_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" } } 

Donc, maintenant, nous avons les mêmes données, pour deux options. Commençons par comparer les implémentations au travail!

Simplification de la conception

Il a déjà été dit que la conception de la base de données était grandement simplifiée: une table, en utilisant la colonne JSONB pour les propriétés, au lieu d'utiliser trois tables pour EAV. Mais comment cela se reflète-t-il dans les demandes? La mise à jour d'une propriété d'une entité est la suivante:

 -- 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 voir, la dernière demande ne semble pas plus facile. Pour mettre à jour la valeur d'une propriété dans un objet JSONB, nous devons utiliser la fonction jsonb_set () et passer notre nouvelle valeur en tant qu'objet JSONB. Cependant, nous n'avons besoin de connaître aucun identifiant à l'avance. En regardant l'exemple EAV, nous devons connaître à la fois entity_id et entity_attribute_id afin de mettre à jour. Si vous souhaitez mettre à jour une propriété dans une colonne JSONB en fonction du nom de l'objet, tout cela se fait sur une seule ligne.

Choisissons maintenant l'entité que nous venons de mettre à jour, en fonction de l'état 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 le second est plus court (sans jointure!), Et donc plus lisible. Voici la victoire de JSONB! Nous utilisons l'opérateur JSON - >> pour obtenir la couleur comme valeur de 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 à l'aide de 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 la colonne des propriétés contient l'objet à droite de l'opérateur @>. Moins lisible, plus productif (voir ci-dessous).

Simplifiez encore plus l'utilisation de JSONB 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 les propriétés en tant que 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 demander. À mon avis, les requêtes ci-dessus montrent une grande simplification dans la conception de la base de données. Voir plus d'exemples sur la façon d'écrire des requêtes JSONB, également dans ce post.
Il est maintenant temps de parler de performance.

Performances

Pour comparer les performances, j'ai utilisé EXPLAIN ANALYZE dans les requêtes, pour calculer le temps d'exécution. Chaque demande a été exécutée au moins trois fois car la première fois que le planificateur de requêtes prend plus de temps. Au début, j'ai exécuté des requêtes sans index. De toute évidence, cela servait d'avantage à JSONB, car la jointure requise pour EAV ne pouvait 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 de valeurs EAV, ainsi qu'un index GIN pour la colonne JSONB.

Les mises à jour des données ont montré les résultats suivants en temps (en ms). Notez que l'échelle est logarithmique:



Nous voyons que JSONB est beaucoup (> 50 000-x) plus rapide que EAV si vous n'utilisez pas d'index, 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 est toujours 1,3 fois plus rapide que EAV. Veuillez noter que l'index dans la colonne JSONB n'a aucun effet ici, car nous n'utilisons pas la colonne de propriété dans les critères d'évaluation.

Pour sélectionner des données en fonction d'une valeur de propriété, nous obtenons les résultats suivants (échelle normale):



Vous pouvez remarquer que JSONB est à nouveau plus rapide que EAV sans index, mais lorsque EAV est avec index, il fonctionne toujours plus rapidement que JSONB. Mais ensuite, j'ai vu que le temps pour les demandes JSONB était le même, cela m'a incité au fait que l'index GIN ne fonctionnait pas. Apparemment, lorsque vous utilisez l'index GIN pour une colonne avec des propriétés remplies, il n'agit que lorsque vous utilisez l'opérateur d'inclusion @>. Je l'ai utilisé dans un nouveau test, qui a eu un énorme impact sur le temps: seulement 0,153 ms! C'est 15 000 fois plus rapide que l'EAV, et 25 000 fois plus rapide que l'opérateur - >>.

Je pense que c'était assez rapide!

Taille de la table DB

Comparons les tailles de table pour les deux approches. En psql, nous pouvons afficher la taille de toutes les tables et index en utilisant la commande \ dti +



Pour l'approche EAV, la taille des tables est d'environ 3068 Mo et les index peuvent atteindre 3427 Mo, ce qui donne au total 6,43 Go. En utilisant l'approche JSONB, 1817 Mo pour la table et 318 Mo pour les index sont utilisés, soit 2,08 Go. Il s'avère 3 fois moins! Ce fait m'a un peu surpris car nous stockons les noms de propriété dans chaque objet JSONB.

Mais tout de même, les chiffres parlent d'eux-mêmes: dans EAV, nous stockons 2 clés étrangères entières pour la valeur d'attribut, ce qui nous donne 8 octets de données supplémentaires. De plus, dans EAV, toutes les valeurs de propriété sont stockées sous forme de texte, tandis que JSONB utilisera des valeurs numériques et logiques à l'intérieur, si possible, ce qui réduira le volume.

Résumé

En général, je pense que le stockage des propriétés d'entité au format JSONB peut grandement simplifier la conception et la maintenance de votre base de données. Si vous exécutez un grand nombre de requêtes, tout ce qui est stocké dans la même table avec l'entité fonctionnera en fait 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.

En outre, selon le test, nous pouvons conclure que la perte de performances est très faible. Dans certains cas, JSONB fonctionne même plus rapidement que EAV, ce qui le rend encore meilleur. Cependant, cette référence ne couvre bien sûr pas tous les aspects (par exemple, les entités avec un très grand nombre de propriétés, une augmentation significative du nombre de propriétés des données existantes, ...), par conséquent, si vous avez des suggestions sur la façon de les améliorer, veuillez N'hésitez pas à laisser un commentaire!