{"id":52584,"date":"2019-11-12T00:00:00","date_gmt":"2019-11-11T21:00:00","guid":{"rendered":"https:\/\/prohoster.info\/blog\/blog_prohoster\/zamena-eav-na-jsonb-v-postgresql"},"modified":"2020-02-18T14:00:21","modified_gmt":"2020-02-18T11:00:21","slug":"zamena-eav-na-jsonb-v-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","title":{"rendered":"Replacing EAV with JSONB in PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

TL; DR: JSONB can significantly simplify database schema development without sacrificing query performance.<\/p><\/blockquote>\n

<\/p>\n

Introduction<\/h3>\n

\nLet's consider a classic example, probably one of the oldest use cases in the world of relational databases: we have an entity and need to store certain properties (attributes) of this entity. However, not all instances may have the same set of properties, and in the future, it may be necessary to add more properties.<\/p>\n

The simplest way to solve this problem is to create a column in the database table for each property value and simply fill in those that are needed for a particular instance of the entity. Great! Problem solved\u2026 until your table contains millions of records and you need to add a new record.<\/p>\n

Let's consider the EAV pattern (Entity-Attribute-Value<\/a><\/noindex>), which is quite common. One table contains entities (records), another table contains names of properties (attributes), and a third table links entities to their attributes and contains the values of these attributes for the current entity. This allows you to have different sets of properties for different objects and to add properties \"on the fly\" without changing the database structure.
\n<\/a><\/noindex>
\nHowever, I wouldn't have written this note if there weren't shortcomings in the EVA approach. For instance, to retrieve one or several entities that have one attribute, two joins are required in the query: the first one is a join with the attributes table, and the second is a join with the values table. If an entity has two attributes, then four joins are needed! Furthermore, all attributes are usually stored as strings, which leads to type casting for both the result and the WHERE condition. If you write many queries, this is quite wasteful in terms of resource usage.<\/p>\n

Despite these obvious drawbacks, EAV has long been used to solve such problems. These were inevitable drawbacks, and there simply wasn't a better alternative.
\nBut then a new \"technology\" appeared in PostgreSQL...<\/p>\n

Starting with PostgreSQL 9.4, a JSONB data type was introduced for storing binary JSON data. While storing JSON in this format typically takes up slightly more space and time than plain text JSON, performing operations on it is significantly faster. JSONB also supports indexing, making queries even faster.<\/p>\n

The JSONB data type allows us to replace the cumbersome EAV pattern by adding just one JSONB column to our entities table, greatly simplifying database design. However, many argue that this should come with a performance trade-off... This is the reason this article was created.<\/p>\n

Setting up a test database<\/h3>\n

\nFor this comparison, I created a database on a new PostgreSQL 9.5 installation on an $80 setup DigitalOcean<\/a><\/noindex> Ubuntu 14.04. After configuring some parameters in postgresql.conf, I ran this<\/a><\/noindex> the script using psql. The following tables were created to represent the data in the EAV format:<\/p>\n

CREATE TABLE entity ( \n  id           SERIAL PRIMARY KEY, \n  name         TEXT, \n  description  TEXT\n);\nCREATE TABLE entity_attribute (\n  id          SERIAL PRIMARY KEY, \n  name        TEXT\n);\nCREATE TABLE entity_attribute_value (\n  id                  SERIAL PRIMARY KEY, \n  entity_id           INT    REFERENCES entity(id), \n  entity_attribute_id INT    REFERENCES entity_attribute(id), \n  value               TEXT\n);\n<\/code><\/pre>\n
\nBelow is a table where the same data will be stored, but with attributes in a JSONB column \u2013 properties<\/i>.<\/p>\n
CREATE TABLE entity_jsonb (\n  id          SERIAL PRIMARY KEY, \n  name        TEXT, \n  description TEXT,\n  properties  JSONB\n);\n<\/code><\/pre>\n
\nLooks much simpler, doesn\u2019t it? Then, 10 million records were added to the entities tables (entity<\/i> & entity_jsonb<\/i>) and, accordingly, the same data was filled into the tables using the EAV pattern and the approach with the JSONB column \u2013 entity_jsonb.properties<\/i>. Thus, we obtained several different data types among the entire set of properties. Example data:<\/p>\n
{\n  id:          1\n  name:        \"Entity1\"\n  description: \"Test entity no. 1\"\n  properties:  {\n    color:        \"red\"\n    length:       120\n    width:        3.1882420\n    hasSomething: true\n    country:      \"Belgium\"\n  } \n}<\/code><\/pre>\n
\nSo now we have the same data for both versions. Let\u2019s start comparing their implementations in action!<\/p>\n
Design Simplification<\/h3>\n
\nIt has already been noted that the database design has been significantly simplified: one table, thanks to using a JSONB column for properties, instead of using three tables for EAV. But how does this reflect in queries? Updating a property of an entity looks as follows:<\/p>\n
-- EAV\nUPDATE entity_attribute_value \nSET value = 'blue' \nWHERE entity_attribute_id = 1 \n  AND entity_id = 120;\n\n-- JSONB\nUPDATE entity_jsonb \nSET properties = jsonb_set(properties, '{\"color\"}', '\"blue\"') \nWHERE id = 120;\n<\/code><\/pre>\n
\nAs we can see, the last query does not look simpler. To update a property value in a JSONB object, we must use the function jsonb_set()<\/a><\/noindex>, and we need to pass our new value as a JSONB object. However, we do not need to know any identifier in advance. Looking at the example with EAV, we need to know both entity_id and entity_attribute_id to perform the update. If you want to update a property in the JSONB column based on the object name, it can all be done with a single simple line.<\/p>\n
Now let's select the entity that we just updated based on its new color condition:<\/p>\n
-- EAV\nSELECT e.name \nFROM entity e \n  INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id\n  INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id\nWHERE ea.name = 'color' AND eav.value = 'blue';\n\n-- JSONB\nSELECT name \nFROM entity_jsonb \nWHERE properties ->> 'color' = 'blue';\n<\/code><\/pre>\n
\nI think we can agree that the second is shorter (without joins!) and consequently more readable. Here JSONB wins! We use the JSON operator ->> to get the color as a text value from the JSONB object. There is also a second way to achieve the same result in the JSONB model using the @> operator:<\/p>\n
-- JSONB \nSELECT name \nFROM entity_jsonb \nWHERE properties @> '{\"color\": \"blue\"}';\n<\/code><\/pre>\n
\nThis is a bit more complex: we check if the JSON object in the properties column contains the object that is to the right of the @> operator. Less readable, but more efficient (see below). <\/p>\n
We can simplify the use of JSONB even further when you need to select several properties at once. This is where the JSONB approach really shines: we simply select properties as additional columns in our result set without needing any joins:<\/p>\n
-- JSONB \nSELECT name\n  , properties ->> 'color'\n  , properties ->> 'country'\nFROM entity_jsonb \nWHERE id = 120;\n<\/code><\/pre>\n
\nWith EAV, you will need 2 joins for each property you want to query. In my opinion, the queries above demonstrate a significant simplification in database design. You can also see more examples of how to write queries for JSONB in this<\/a><\/noindex> the post.
\nNow it's time to talk about performance.<\/p>\n
Performance<\/h3>\n
\nTo compare performance, I used EXPLAIN ANALYZE<\/a><\/noindex> in queries for measuring execution time. Each query was executed at least three times because initially the query planner needs more time. First, I executed the queries without any indexes. This clearly highlighted the advantages of JSONB, since the joins required for EAV could not utilize indexes (the foreign key fields were not indexed). After that, I created an index for the two foreign key columns in the EAV values table, as well as an index GIN<\/a><\/noindex> for the JSONB column.<\/p>\n
Data updates showed the following results in terms of time (in ms). Note that the scale is logarithmic:<\/p>\n
\"Replacing
\n
\nWe can see that JSONB is significantly (> 50000x) faster than EAV when no indexes are used, for the reason mentioned above. When we index the columns with primary keys, the difference nearly disappears, but JSONB is still 1.3 times faster than EAV. Note that the index on the JSONB column here has no impact, as we are not using the properties column in the evaluation criteria. <\/p>\n
To select data based on property value, we get the following results (normal scale):<\/p>\n
\"Replacing
\n
\nIt can be observed that JSONB again performs faster than EAV without indexes, but when EAV has indexes, it still performs faster than JSONB. However, I then noticed that the time for JSONB queries remained the same, which led me to the realization that the GIN index was not being activated. Apparently, when you use a GIN index for a column with populated properties, it only activates with the inclusion operator @> . I applied this in a new test, which significantly impacted the time: just 0.153 ms! This is 15000 times faster than EAV and 25000 times faster than the operator ->>. <\/p>\n
I think that's fast enough!<\/p>\n
Database Table Sizes<\/h3>\n
\nLet's compare the sizes of the tables in both approaches. In psql, we can show the size of all tables and indexes with the command dti+<\/b><\/p>\n
\"Replacing
\n
\nFor the EAV approach, the table sizes are around 3068 MB, while the indexes are up to 3427 MB, totaling 6.43 GB. In the JSONB approach, it uses 1817 MB for the table and 318 MB for the indexes, totaling 2.08 GB. That's three times less! This fact surprised me a bit because we store property names in each JSONB object. <\/p>\n
However, the numbers speak for themselves: in EAV, we store 2 integer foreign keys for the attribute value, resulting in 8 bytes of additional data. Moreover, in EAV, all property values are stored as text, while JSONB utilizes numeric and logical values where possible, resulting in a smaller size.<\/p>\n
Summary<\/h3>\n
\nOverall, I believe that storing entity properties in JSONB format can significantly simplify the design and maintenance of your database. If you are executing many queries, everything stored in a single table with the entity will indeed perform more efficiently. The fact that it simplifies interaction between data is already a plus, but the resulting database is also three times smaller in size.<\/p>\n
Additionally, based on the tests conducted, it can be concluded that performance loss is quite minimal. In some cases, JSONB even performs faster than EAV, which makes it even better. However, this benchmark test does not cover all aspects (for example, entities with a very large number of properties, significant increases in the number of properties of existing data, etc.), so if you have any suggestions on how to improve them, please feel free to leave comments!
\n
Source: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
TL; DR: JSONB \u043c\u043e\u0436\u0435\u0442 \u0437\u043d\u0430\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0443\u043f\u0440\u043e\u0441\u0442\u0438\u0442\u044c \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0443 \u0441\u0445\u0435\u043c\u044b \u0411\u0414 \u0431\u0435\u0437 \u0443\u0449\u0435\u0440\u0431\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u0430\u0445. \u0412\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u041f\u0440\u0438\u0432\u0435\u0434\u0435\u043c \u043a\u043b\u0430\u0441\u0441\u0438\u0447\u0435\u0441\u043a\u0438\u0439 \u043f\u0440\u0438\u043c\u0435\u0440, \u043d\u0430\u0432\u0435\u0440\u043d\u043e\u0435, \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0441\u0442\u0430\u0440\u0435\u0439\u0448\u0438\u0445 \u0432\u0430\u0440\u0438\u0430\u043d\u0442\u043e\u0432 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0432 \u043c\u0438\u0440\u0435 \u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u0445 \u0411\u0414 (\u0431\u0430\u0437\u0430 \u0434\u0430\u043d\u043d\u044b\u0445): \u0443 \u043d\u0430\u0441 \u0435\u0441\u0442\u044c \u0441\u0443\u0449\u043d\u043e\u0441\u0442\u044c, \u0438 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u044b\u0435 \u0441\u0432\u043e\u0439\u0441\u0442\u0432\u0430 (\u0430\u0442\u0440\u0438\u0431\u0443\u0442\u044b) \u044d\u0442\u043e\u0439 \u0441\u0443\u0449\u043d\u043e\u0441\u0442\u0438. \u041d\u043e \u043d\u0435 \u0432\u0441\u0435 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u044b \u043c\u043e\u0433\u0443\u0442 \u0438\u043c\u0435\u044e\u0442 \u043e\u0434\u0438\u043d\u0430\u043a\u043e\u0432\u044b\u0439 \u043d\u0430\u0431\u043e\u0440 \u0441\u0432\u043e\u0439\u0441\u0442\u0432, \u043a \u0442\u043e\u043c\u0443 \u0436\u0435 \u0432 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-52584","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Replacing EAV with JSONB in PostgreSQL | ProHoster","description":"TL; DR: JSONB can significantly simplify the development of database schemas without sacrificing query performance. Introduction Let\u2019s provide a classic example, probably one of the oldest.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0417\u0430\u043c\u0435\u043d\u0430 EAV \u043d\u0430 JSONB \u0432 PostgreSQL | ProHoster","og:description":"TL; DR: JSONB \u043c\u043e\u0436\u0435\u0442 \u0437\u043d\u0430\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0443\u043f\u0440\u043e\u0441\u0442\u0438\u0442\u044c \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0443 \u0441\u0445\u0435\u043c\u044b \u0411\u0414 \u0431\u0435\u0437 \u0443\u0449\u0435\u0440\u0431\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u0430\u0445. \u0412\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u041f\u0440\u0438\u0432\u0435\u0434\u0435\u043c \u043a\u043b\u0430\u0441\u0441\u0438\u0447\u0435\u0441\u043a\u0438\u0439 \u043f\u0440\u0438\u043c\u0435\u0440, \u043d\u0430\u0432\u0435\u0440\u043d\u043e\u0435, \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0441\u0442\u0430\u0440\u0435\u0439\u0448\u0438\u0445.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/zamena-eav-na-jsonb-v-postgresql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-11-11T21:00:00+00:00","article:modified_time":"2020-02-18T11:00:21+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"52584","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-24 04:07:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 20:40:27","updated":"2026-01-24 04:07:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/52584","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=52584"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/52584\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=52584"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=52584"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=52584"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}