6

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)
Improve this question
6
  • 3
    I'd suggest just putting together both versions, insert a decent amount of data into both and do some tests. – dezso Jun 30 '15 at 12:43
  • 2
    What's wrong with using proper relational tables? CREATE TABLE material (mat_id SERIAL (PRIMARY KEY).... melting_point INTEGER, colour VARCHAR(20).... &c...)? – Vérace Jun 30 '15 at 12:47
  • 1
    @Vérace Different materials have different properties that you don't always now upfront. Design should be generic and easily allow to create new properties on the fly. Adding a column each time a new property is added is not really best practice I guess, and you'd end up with a table having 100+ columns. – coussej Jul 1 '15 at 6:31
  • Please do as @dezso suggests and answer your own question here. I'm guessing jsonb will win as it has been a much touted feature for this kind of use case. I've seen some benchmarks and it's very fast. – Colin 't Hart Jul 1 '15 at 7:31
  • @Colin'tHart: I'll do some tests, but I was mostly interested to hear if anyone has any reasons not to do it from a design point of view. – coussej Jul 1 '15 at 9:14
5

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)

Improve this answer
2
  • Reading between the lines, if the data is mostly just inserted and then queried with very few updates, it could still be a good solution? – Colin 't Hart Jul 4 '15 at 12:52
  • 1
    @Colin'tHart: yes if we're refering to INSERT at the SQL level, as creating new rows. But inserting a new key into a JSON column means an UPDATE of that column at the SQL level. – Daniel Vérité Jul 4 '15 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.