Use PostgreSQL JSONB for Flexible Fields, Not as a Schema Escape

A practical approach

Step 1

List the fields you plan to store in JSONB and mark which are always present, filtered on or validated.

Step 2

Move always-present, frequently queried and constrained fields into typed columns with NOT NULL, foreign keys or checks as needed.

Step 3

Keep genuinely variable data in JSONB, and document the expected shape near the code that writes it.

Step 4

Index JSONB only for the queries you run, for example a GIN index for containment queries or an expression index on one extracted key.

Step 5

Validate JSON in the application before writing it, and consider a CHECK constraint for required keys.

Worked example

A course table stores everything except the ID in a single JSONB column called data. Filtering by level and category requires casting extracted text on every query, a course without a title slipped in unnoticed and reports are slow. Moving title, level, category and price into typed columns with constraints, while keeping provider-specific import metadata in JSONB, makes queries simpler and faster and prevents incomplete courses.

A mistake to avoid

Do not store values that reference other tables, such as user IDs, only inside JSONB. You lose foreign-key protection, so deleted records leave dangling references that the database cannot detect.

Try it yourself

Take one table that uses a JSON or JSONB column. List every key it contains, how often each is present and where each is queried. Propose which keys should become columns and write the migration.

Your implementation checklist

• JSONB holds only genuinely variable data
• Common, filtered and constrained fields are typed columns
• References to other tables use real foreign keys
• JSONB indexes match actual queries
• JSON shapes are validated and documented