When JSONB Helps and When It's Laziness

~12 min · extensions, jsonb

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Three good reasons to reach for JSONB

Genuinely variable shape: event payloads from many sources, audit logs, third-party API snapshots.
Sparse, optional fields: 50 possible attributes, any given row uses 5-10 of them.
Bag of settings or flags: user preferences where the set of keys evolves over time.

Three bad reasons (don't)

"To avoid schema migrations." The migration cost is real, but the schema-less cost is bigger — you push the validation problem onto every consumer of the data.
"Because it's flexible." Flexibility is fine; uncontrolled flexibility is technical debt.
"Because the ORM made it easy." The ORM doesn't run queries against your data — your future self does.

The hybrid approach

The right pattern is usually: stable fields in real columns (with NOT NULL, CHECK, FK), variable bits in a JSONB sidecar. You get the integrity of relational + the flexibility of JSON, with no compromise on either.

Code

Anti-pattern: everything in JSONB·sql

-- DO NOT do this
CREATE TABLE everything (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data JSONB NOT NULL  -- shrug emoji
);
-- No NOT NULL on required fields, no FK, no type safety, no constraints.

Hybrid: stable + flexible·sql

CREATE TABLE events (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id),
    event_type  TEXT NOT NULL CHECK (event_type IN ('click','view','signup','purchase')),
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    payload     JSONB NOT NULL DEFAULT '{}'::jsonb  -- type-specific extras
);

CREATE INDEX events_user_id_idx ON events (user_id);
CREATE INDEX events_payload_gin ON events USING gin (payload);

External links

Postgres JSONB Best Practices

Exercise

Take a JSONB column you control. List which of its keys are present in >90% of rows. Promote those to real columns; keep only the genuinely sparse ones in JSONB.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousJSONB: Semi-Structured Data, First-Class Next →Arrays in PostgreSQL

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.