Nullable columns or jsonb for storing user profile?

Posted on

Question :

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.

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:

Leave a Reply

Your email address will not be published.