I'm deciding between using nullable fields or jsonb to store user profile. Initially, this would be used for contacts: email
and phone
. I anticipate that there may be other columns added later, such as mobile
and website
. Additionally, there may be other non-related fields, such as settings/preferences, saved searches, etc.
I've already decided that I don't want to use any form of key-value store (or any schema involving many-to-many relations) for this, unless there's an extremely good reason for it.
Pros of jsonb:
- Can store multiple values for each "column" if necessary
- Adding new field just needs JS coding and documentation
Cons of jsonb:
- Overhead of storing the "column" name as a string for each "row"
- Wonky to perform comparison queries (I think N/A to my usage scenario)
- Have to expect the unexpected
Anything else to add to this list of pros/cons? As much as I'd like to simply use nullable columns, I think it would be negligent to ignore jsonb – it seems like a compelling choice.
Best Answer
You can use "best of both worlds". Implement every regular column you know of as dedicated column (with exact proper data type, possibly with NOT NULL / CHECK / PK / FK etc. constraints to enforce detailed rules, default values and comment for documentation) and add a "catch-all"
jsonb
column for very rare cases / later additions / highly volatile attributes that do not need any of the features mentioned above.To be clear: "highly volatile" does not mean the value is updated a lot, which would clearly speak against any document type like
jsonb
, making updates more complex and expensive. It means the attribute per se (on the meta level) changes a lot (different names, rare and and pops in and out of existence over time or similar).Most people underestimate how cheap NULL storage really is:
Related: