TL; DR: JSONB can significantly simplify database schema development without sacrificing query performance.
Introduction
Let'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.
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… until your table contains millions of records and you need to add a new record.
Let's consider the EAV pattern (), 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.
However, I wouldn't be writing this note if there weren't drawbacks to using the EAV approach. For example, to retrieve one or more entities that have 1 attribute requires 2 joins in the query: the first is a join with the attributes table, and the second is a join with the values table. If the entity has 2 attributes, you need 4 joins! Additionally, all attributes are usually stored as strings, leading to type casting, both for the result and for the WHERE condition. If you write many queries, this can be quite wasteful in terms of resource usage.
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.
But then a new "technology" appeared in PostgreSQL...
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.
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.
Setting up a test database
For this comparison, I created a database on a new PostgreSQL 9.5 installation on an $80 setup Ubuntu 14.04. After configuring some parameters in postgresql.conf, I ran the script using psql. The following tables were created to represent the data in the EAV format:
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
);
Below is a table where the same data will be stored, but with attributes in a JSONB column – properties.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Looks much simpler, doesn’t it? Then, 10 million records were added to the entities tables (entity & entity_jsonb) and, accordingly, the same data was filled into the tables using the EAV pattern and the approach with the JSONB column – entity_jsonb.properties. Thus, we obtained several different data types among the entire set of properties. Example data:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
length: 120
width: 3.1882420
hasSomething: true
country: "Belgium"
}
}So now we have the same data for both versions. Let’s start comparing their implementations in action!
Design Simplification
It 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:
-- 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;
As we can see, the last query does not look simpler. To update a property value in a JSONB object, we must use the function , 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.
Now let's select the entity that we just updated based on its new color condition:
-- 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';
I 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:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
This 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).
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:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
With 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 the post.
Now it's time to talk about performance.
Performance
To compare performance, I used 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 for the JSONB column.
Data updates showed the following results in terms of time (in ms). Note that the scale is logarithmic:
We 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.
To select data based on property value, we get the following results (normal scale):
It 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 ->>.
I think that's fast enough!
Database Table Sizes
Let'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+
For 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.
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.
Summary
Overall, 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.
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!
Source: habr.com
