When to Avoid JSONB in a PostgreSQL Schema • Hacker News Board

Re: When to Avoid JSONB in a PostgreSQL Schema

#111

Post by ngrilly » Sat, Sep 03, 2016, 10:11 AM UTC

inopinatus wrote: post #110

ngrilly wrote: post #79

Earlier quoted context omitted.

What is a "known keys" table? Is it a table with columns like user_defined_text_1, user_defined_text_2, ..., user_defined_text_10, user_defined_integer_1, user_defined_integer_2, ... user_defined_integer_n ?

No, that kind of "extension table" is a well-known antipattern being fragile, unscalable, and denormalized. Just a table with columns for id, keyname & metadata about the key will do.

> No, that kind of "extension table" is a well-known antipattern being fragile, unscalable, and denormalized.

Ok, I agree. This is the reason why I was asking :-)

> Just a table with columns for id, keyname & metadata about the key will do.

Isn't this the Entity-Attribute-Value model (which has the well known drawback of requiring a join for each attribute)?

Re: When to Avoid JSONB in a PostgreSQL Schema

#112

Post by inopinatus » Sun, Sep 04, 2016, 5:28 AM UTC

ngrilly wrote: post #111

inopinatus wrote: post #110

Earlier quoted context omitted.

No, that kind of "extension table" is a well-known antipattern being fragile, unscalable, and denormalized. Just a table with columns for id, keyname & metadata about the key will do.

> No, that kind of "extension table" is a well-known antipattern being fragile, unscalable, and denormalized. Ok, I agree. This is the reason why I was asking :-) > Just a table with columns for id, keyname & metadata about the key will do. Isn't this the Entity-Attribute-Value model (which has the well known drawback of requiring a join for each attribute)?

Not exactly, I wouldn't be afraid of the data table having both attribute name & value columns. EAV is practically 6NF which would prohibit that. (to be honest that id field was out of habit, the known-keys table should probably be unique and indexed on the key name)

Re: When to Avoid JSONB in a PostgreSQL Schema

#113

Post by ngrilly » Mon, Sep 05, 2016, 12:46 PM UTC

inopinatus wrote: post #112

ngrilly wrote: post #111

Earlier quoted context omitted.

> No, that kind of "extension table" is a well-known antipattern being fragile, unscalable, and denormalized. Ok, I agree. This is the reason why I was asking :-) > Just a table with columns for id, keyname & metadata about the key will do. Isn't this the Entity-Attribute-Value model (which has the well known drawback of requiring a join for each attribute)?

Not exactly, I wouldn't be afraid of the data table having both attribute name & value columns. EAV is practically 6NF which would prohibit that. (to be honest that id field was out of habit, the known-keys table should probably be unique and indexed on the key name)

I'm sorry, but I'm still unsure what you mean. A "known-keys table" is not a common term in the literature. Can you give an example by describing the tables and their columns?

Re: When to Avoid JSONB in a PostgreSQL Schema

#114

Post by ngrilly » Mon, Sep 05, 2016, 12:51 PM UTC

malisper wrote: post #105

ngrilly wrote: post #104

Earlier quoted context omitted.

Thanks Michael for clearing my doubts :-) One last question: I guess most queries target a time range. Do you use BRIN indexes to avoid scanning the whole 800 MB of data in each shard, and just read the necessary pages?

That get's into our indexing strategy which Dan talks about in this talk[0]. Currently our tables aren't completely insert only, so a BRIN index wouldn't work for us, as one row in the wrong place can cause a huge amount of extra reads. [0] https://www.youtube.com/watch?v=NVl9_6J1G60

No more question :-) Thanks Michael for you time, and for the link to Dan's talk. It's great to see how, at Heap, you scaled a multitenant SaaS based on PostgreSQL!

Re: When to Avoid JSONB in a PostgreSQL Schema

#115

Post by throwa » Fri, Sep 09, 2016, 11:55 AM UTC

malisper wrote: post #33

silverlight wrote: post #26

Does adding a Gin index to the JSONB help this?

A Gin index only helps with querying the data from the table. It won't help with making the proper join choice or with getting a bitmap scan between multiple indexes. Additionally, you are unable to query numeric values by an inequality with a Gin index.

Does using a UNION OR UNION ALL query instead of a join query reduce the implication of jsonb column not having statistics, especially since the postgresql query planner might not use the nested loop join.