When to Avoid JSONB in a PostgreSQL Schema

Re:When to Avoid JSONB in a PostgreSQL Schema by Matt3o12_ on Friday September 02, 2016 @02:09AM (#12411188)

There is no need to do that. Should not do that if you have arbitrary values in your database. Just create another table with all the keys and values aligned like that:

    id | other_pk | key | value

This of cause would require another query to the database and you might have to iterate through all retrieved rows in your code to build the hash table manually (although your JSON library will do the same).

Im not saying that you should not use JSONB for that (I probably would too for that example), Im just saying there are more ways to design your data storages.

[ Parent | on HN ]

Re:When to Avoid JSONB in a PostgreSQL Schema by zrail on Friday September 02, 2016 @04:47AM (#12411776)

This well-trodden pattern is called EAV (entity, attribute, value). It's attractive up to the point where you want to do interesting things with multiple attributes, which is when you start doing multiple nested self joins on the EAV table and performance gets hairy.

For smaller scale auxiliary data it's fine, but so is JSONB.

[ Parent | on HN ]

Re:When to Avoid JSONB in a PostgreSQL Schema by palmdeezy on Friday September 02, 2016 @09:14AM (#12412755)

Where can I read more about this?

[ Parent | on HN ]

Re:When to Avoid JSONB in a PostgreSQL Schema by zrail on Friday September 02, 2016 @10:24PM (#12417417)

Wikipedia has an exhaustive article[1].

https://en.wikipedia.org/wiki/Entity–attribute–value_model#E...

[ Parent | on HN ]