Re:When to Avoid JSONB in a PostgreSQL Schema
by ngrilly on Friday September 02, 2016 @03:02AM
(#12411407)
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 ?
Re:When to Avoid JSONB in a PostgreSQL Schema
by inopinatus on Friday September 02, 2016 @10:31PM
(#12417443)
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.
Re:When to Avoid JSONB in a PostgreSQL Schema
by ngrilly on Saturday September 03, 2016 @06:11AM
(#12418597)
> 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
by inopinatus on Sunday September 04, 2016 @01:28AM
(#12422873)
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)
Having learned that postgresql NULLs are cheap in storage (since they just use a bitmap) I stopped caring much about empty column proliferation.
I've also found that arbitrary user-defined keys are still better treated via a "known keys" table, the long-term visibility into data is invaluable, there's more join flexibility, and there's opportunity to assign metadata (e.g. type hints) for each user-defined key. At scale, these things make json columns look like technical debt.
The only use case I currently have for jsonb is when the absence of an element is meaningfully different to a null value e.g. options structures that wouldn't otherwise benefit from being in normalized tables.