Prompt
Do I need JSONB in PostgreSQL for my app, or should I normalize everything?
Latest observation
Not necessarily. In PostgreSQL, you usually shouldn’t choose between “JSONB everywhere” and “fully normalize everything”. The best design is often a hybrid:
- Normalize core, relational data
- Use JSONB for flexible, semi-structured, or optional data
Good rule of thumb
Use normalized tables when:
- The data has a clear structure
- You need to query/filter/join on fields often
- You need constraints like foreign keys, unique checks, not-null, etc.
- The data is part of your app’s core domain model
Examples:
- users
- orders
- order_items
- products
- payments
These are usually better as regular columns/tables.
Use JSONB when:
- The shape of the data can vary a lot
- Fields are optional or user-defined
- You need to store metadata, settings, preferences, or event payloads
- You want to evolve schema quickly without migrations for every small change
Examples:
- user preferences
- integration configs
- webhook payloads
- audit/event blobs
- custom attributes
Why not JSONB for everything?
JSONB is flexible, but it has tradeoffs:
- Harder to enforce schema and data integrity
- More awkward joins and reporting
- Can become messy over time
- Indexing/querying is possible, but often less straightforward than normal columns
- Developers may “dump stuff in JSON” and lose model clarity
Why not normalize everything?
Over-normalizing can also hurt:
- Too many tables and joins
- Slower development
- Complex queries for data that naturally belongs together
- Schema churn for fields that are rarely queried or are highly variable
A practical approach
A common pattern is:
- Put the important, frequently queried fields in columns
- Put rarely used, variable, or nested fields in a JSONB column
Example:
users (
id,
email,
name,
created_at,
preferences jsonb
)
Here, email, name, and created_at are relational; preferences is flexible.
Decision checklist
Ask yourself:
-
Will I filter/sort/join on this field often?
If yes, use normal columns. -
Do I need constraints on it?
If yes, normalize it. -
Does its structure vary a lot?
If yes, JSONB may fit. -
Is this mostly for storage/display rather than querying?
JSONB is a good fit. -
Could this later become important for reporting/search?
If likely, consider making it a real column now or use generated/indexed columns later.
Rule of thumb summary
- Default to normalization
- Add JSONB selectively for flexible data
- Don’t use JSONB as a replacement for a relational model
If you want, I can help you decide for your specific schema and show which fields should be columns vs JSONB.
Brands