PostgreSQL JSON Columns: Flexible Schema Design for Modern Applications

I've built three production SaaS products, and I can tell you with confidence: premature normalization is the silent killer of developer velocity. Your schema shouldn't force you to create seventeen junction tables for features you're still figuring out.

PostgreSQL's JSONB is the antidote—but only if you understand when to use it and how to query it properly. I've watched teams swing between two extremes: pure relational schemas that require migrations every sprint, and schemaless blobs that become impossible to query. The sweet spot is JSONB for semi-structured data with strategic indexing.

Why JSONB Over Regular JSON

PostgreSQL offers two JSON types: json (stores text, re-parses on every query) and jsonb (binary format, indexable, queryable). Use JSONB. Always. The performance difference is night and day, and JSONB supports all the operators you actually need.

I prefer JSONB because it lets me ship features faster without sacrificing queryability. In CitizenApp, user preferences evolved constantly—we added dark mode, notification settings, feature flags, timezone overrides. Normalizing each of these would've meant schema changes every two weeks. Instead, we stored preferences as JSONB with strategic GIN indexes.

The Real Question: When, Not If

JSONB isn't a replacement for normalization—it's a tool for data that's genuinely flexible. Ask yourself:

• Is the structure likely to change? Use JSONB.
• Do you need to filter or aggregate on every field? Normalize it.
• Is this metadata or configuration? JSONB.
• Is this your primary data model? Normalize.

In CitizenApp, user organization_settings is JSONB because orgs customize features individually. But users themselves? That's relational. The email field never changes structure, so it doesn't belong in JSON.

Practical Schema Design

Here's how I structure tables with JSONB columns:

CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    settings JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}'
);

-- Critical: Add GIN indexes for queryability
CREATE INDEX idx_org_settings_gin ON organizations USING GIN (settings);
CREATE INDEX idx_org_settings_plan ON organizations USING GIN (settings) 
    WHERE settings->>'plan' IS NOT NULL;

The second index is intentional—I prefer targeted indexes on paths you'll actually query. Full GIN indexes on huge JSONB columns burn memory without helping specific queries.

Querying JSONB: The Operators You Need

PostgreSQL gives you powerful operators. Learn them:

-- Arrow operators: -> (returns JSONB), ->> (returns TEXT)
SELECT * FROM organizations 
WHERE settings->>'plan' = 'enterprise';

-- Deep path access
SELECT * FROM organizations 
WHERE (settings->'features'->>'ai_enabled')::boolean = true;

-- Existence checking
SELECT * FROM organizations 
WHERE settings ? 'legacy_mode';

-- Array containment
SELECT * FROM organizations 
WHERE settings->'allowed_domains' @> '["example.com"]'::jsonb;

-- JSONB contains (subset check)
SELECT * FROM organizations 
WHERE settings @> '{"plan": "enterprise"}'::jsonb;

That last one is powerful. @> checks if the left JSONB contains all key-value pairs from the right. Combined with a GIN index, it's incredibly fast.

FastAPI Integration: Type Safety Matters

Here's how I handle JSONB in FastAPI with SQLAlchemy. Type safety prevents silent bugs:

from pydantic import BaseModel, Field
from sqlalchemy import Column, UUID, JSONB, String
from sqlalchemy.orm import declarative_base
from typing import Optional, Dict, Any
import uuid

Base = declarative_base()

class OrganizationSettingsSchema(BaseModel):
    plan: str = Field(default="free", pattern="^(free|pro|enterprise)$")
    ai_features_enabled: bool = True
    max_users: int = 10
    custom_branding: Optional[Dict[str, str]] = None

    model_config = {"extra": "allow"}  # Allow other fields

class Organization(Base):
    __tablename__ = "organizations"

    id = Column(UUID, primary_key=True, default=uuid.uuid4)
    name = Column(String, nullable=False)
    settings = Column(JSONB, default={}, nullable=False)

# FastAPI endpoint
from fastapi import FastAPI, HTTPException
from sqlalchemy.orm import Session

app = FastAPI()

@app.post("/organizations/{org_id}/settings")
async def update_settings(
    org_id: str,
    settings: OrganizationSettingsSchema,
    db: Session
):
    org = db.query(Organization).filter(Organization.id == org_id).first()
    if not org:
        raise HTTPException(status_code=404)

    # Validate before storing
    org.settings = settings.model_dump(exclude_none=True)
    db.commit()
    return org.settings

@app.get("/organizations")
async def list_by_plan(plan: str, db: Session):
    # Query JSONB directly—PostgreSQL does the filtering
    return db.query(Organization).filter(
        Organization.settings.astext.op('->>')('plan') == plan
    ).all()

Notice: I use Pydantic schemas to validate before storing JSONB. This prevents garbage data that looks valid in the database but breaks your application logic. This burned me when developers pushed random nested objects into settings without validation.

Schema Evolution Without Migrations

This is where JSONB shines. Adding a new setting field requires zero migrations:

# Old code
settings = {"plan": "pro", "ai_enabled": true}

# New code adds a field—no schema changes needed
settings = {"plan": "pro", "ai_enabled": true, "webhook_url": "https://..."}

# Backfill safely with a migration script, not a schema migration
db.query(Organization).filter(
    Organization.settings['webhook_url'].astext == None
).update({"settings": func.jsonb_set(
    Organization.settings, 
    '{webhook_url}', 
    '"https://default.webhook"'
)})

That jsonb_set function is your friend for bulk updates without downtime.

Gotcha: N+1 Queries on Related Data

This burned me hard. JSONB looks like it solves everything, so I stored nested relationships as JSON arrays:

# Bad: Storing user IDs in JSONB
settings = {"allowed_user_ids": [1, 2, 3, 4, 5]}

# Then querying:
SELECT * FROM organizations WHERE settings->'allowed_user_ids' @> '[2]'::jsonb

This works, but it's inefficient at scale and loses referential integrity. If you need to query on that data frequently, normalize it to a junction table. JSONB is for metadata, not foreign keys.

Indexing Strategy

I learned this the hard way:

-- Over-indexing (wastes space)
CREATE INDEX ON organizations USING GIN (settings);
CREATE INDEX ON organizations USING GIN (settings) WHERE settings ? 'plan';

-- Smart indexing (use targeted indexes)
CREATE INDEX idx_org_plan ON organizations ((settings->>'plan'));
CREATE INDEX idx_org_ai_features ON organizations USING GIN (settings) 
    WHERE (settings->>'ai_enabled')::boolean = true;

Targeted B-tree indexes on scalar values are faster than GIN when you're filtering on a single field. GIN wins when you need @> or ? operators.

The Verdict

JSONB isn't a shortcut to schema design—it's a tool for controlled flexibility. Use it for settings, preferences, metadata, and evolving features. Keep your core data relational. Index strategically. Validate aggressively in your application layer.

Done right, you get rapid iteration without the chaos of schemaless databases. Done wrong, you end up with a blob column that's impossible to query. The difference is discipline.

Comments

[Mads Hansen]

Nice balance between a relational core and flexible edges. One index detail worth tightening: a partial GIN index on settings with WHERE settings->>'plan' IS NOT NULL does not make settings->>'plan' = 'enterprise' a B-tree-style lookup. For scalar equality, an expression B-tree index on ((settings->>'plan')) aligns with that predicate; for containment, keep GIN and write the query with settings @> .... I’d confirm both with EXPLAIN (ANALYZE, BUFFERS) on representative distributions.

Application-only validation can also be bypassed by imports, backfills or another service. For business-critical keys, I’d add database CHECK constraints (jsonb_typeof, allowed values), a schema_version key, and concurrency-safe patch semantics with an expected version. That avoids a whole-document update silently overwriting another writer’s JSONB changes.