Postgresql – Postgres 9.4+: JSONB instead of EAV

database-designeavjsonpostgresql

I'm figuring out a way to structure a new PostgreSQL database, and I'm curious if I could use a JSONB column instead of the EAV model for storing properties of an entity. This seems like a flexible way to store these kinds of things.

In the EAV case, I would have 3 tables:

materials (mat_id, mat_name, mat_descr)
material_properties (prop_id, prop_name, prop_desc)
material_property_values (mat_id, prop_id, val)

Each material can have a different set of properties.

Are there any reasons why I shouldn't just add a column mat_props JSONB to my materials table, and store all properties there as {"name":"value"}? This way, querying properties as columns would be as easy as:

SELECT mat_name, mat_props->'name' as propval FROM materials

instead of joins or subselects.

Also, could I add this column in the materials table or would it be much more performant to keep it in a separate table as:

material_properties (mat_id, properties JSONB)

Best Answer

JSONB may be easy to read, but it's complicated and inefficient to write into.

See for example this question: PostgreSQL update and delete property from JSONB column, on how it looks like. It's an order of magnitude harder than an update/delete with classic EAV tables.

Possibly when you'll have written the parts to append/merge/delete key/value pairs, the elegance or simplicity of that JSON-based design are going to seem much less obvious. As for the performance, I'm betting on it being much worse.

At the storage level, any write into one property in a JSON structure will require a rewrite of the entire column (and in fact of the containing row), with the same cost as if all properties changed. This is not optimal in terms of I/O size and pressure on vacuum.

This problem is mentioned on S.O in another question with some more links and references:

How to perform update operations on columns of type JSONB in Postgres 9.4

There's ongoing work to ease JSONB updates for the programmer. jsonbx provides functions and operators that can help with 9.4; presumably these will be integrated into PostgreSQL core in future versions. But the large I/O cost of a small update inside a large JSON object will remain.

jsonbx was demonstrated in a recent PG conference (youtube links):

Update and Delete operations for jsonb (part 1 of 2)
Update and Delete operations for jsonb (part 2 of 2)

Related Question