Nullable columns or jsonb for storing user profile?

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 1

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:

• Do nullable columns occupy additional space in PostgreSQL?
• Measure the size of a PostgreSQL table row

Related:

• Does it make sense to store a couple of Boolean values as array?
• Is there a name for this database schema of key values?
• jsonb and primary/foreign keys: which performs better in PostgreSQL?
• Does JSONB make PostgreSQL arrays useless?