2

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.

5

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:

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.