PostgreSQL JSONB vs Relational Tables in SaaS: When Flexibility Becomes a Liability

JSONB is genuinely useful in PostgreSQL, but SaaS teams reach for it too early and too broadly. The result is unindexed blobs, unmaintainable queries, and schema debt that compounds every quarter. This post walks through when JSONB is the right call and when it is the wrong one, using real examples from production SaaS systems built for the MENA market.

What is the actual problem JSONB solves?

PostgreSQL JSONB stores arbitrary JSON documents in a binary format. Unlike the JSON type, JSONB parses and stores the data in a decomposed form, which makes operators like @> (contains) and indexing via GIN work efficiently.

The feature exists because real systems have genuinely variable data. A form builder SaaS needs to store form responses where each form has different fields. A product catalog with thousands of attribute combinations cannot have a column for every possible attribute. An audit trail needs to capture the old and new state of a record regardless of which columns changed.

These are real use cases. The problem is that JSONB ends up being used for cases that have clear relational structure, simply because it feels faster to add a JSONB column than to think through a migration.

Where JSONB causes production pain

Filtering across JSON fields at scale

The most common mistake is storing data you need to filter on inside JSONB without thinking through indexes.

Consider a multi-tenant SaaS where each workspace configures custom metadata on orders. Early on you store it as JSONB:

CREATE TABLE orders (
  id         UUID PRIMARY KEY,
  workspace_id UUID NOT NULL,
  metadata   JSONB
);

Then a customer wants to filter orders by a custom field inside metadata:

SELECT * FROM orders
WHERE workspace_id = $1
  AND metadata @> '{"status": "exported"}';

Without a GIN index, this is a sequential scan over every order in that workspace. For a restaurant chain with 500,000 orders, this query takes seconds.

You can fix it with a partial GIN index:

CREATE INDEX idx_orders_metadata_gin
  ON orders USING GIN (metadata)
  WHERE metadata IS NOT NULL;

But the index grows with every unique key path in the document. If customers store highly variable metadata, the index becomes expensive to maintain and still does not help with queries that touch a specific deep path efficiently.

Schema migrations become invisible

With relational columns, a schema change is a migration file with a clear intent. With JSONB, schema evolves silently in application code. A field gets renamed from export_status to sync_status across six months of code changes, and half your records use one key and half use the other.

Debugging this in production at 2am is not fun. The \d orders output tells you nothing because the schema looks fine. The problem lives inside the blobs.

Go query code becomes fragile

With sqlc and typed queries, relational data maps directly to Go structs:

type Order struct {
  ID          uuid.UUID
  WorkspaceID uuid.UUID
  ExportStatus string
  SyncedAt    sql.NullTime
}

With JSONB, you end up with json.RawMessage fields and manual unmarshaling:

type Order struct {
  ID       uuid.UUID
  Metadata json.RawMessage
}

type OrderMetadata struct {
  ExportStatus string    `json:"export_status"`
  SyncedAt     time.Time `json:"synced_at"`
}

func parseMetadata(raw json.RawMessage) (OrderMetadata, error) {
  var m OrderMetadata
  return m, json.Unmarshal(raw, &m)
}

Every caller must remember to parse the metadata. Field additions require no migration but also no type safety. The compiler cannot catch a typo in json:"synced_at". Over time, the metadata object grows into a catch-all for anything the team did not want to write a migration for.

Where JSONB is the correct choice

Truly variable schema: form builders and custom fields

If your SaaS lets each workspace define its own field structure, relational tables cannot model this without dynamic DDL or an entity-attribute-value table (which is worse). JSONB is the right answer here.

CREATE TABLE form_submissions (
  id          UUID PRIMARY KEY,
  form_id     UUID NOT NULL REFERENCES forms(id),
  workspace_id UUID NOT NULL,
  submitted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  data        JSONB NOT NULL
);

The key constraint is that you almost never need to filter or sort by fields inside data. You retrieve a submission by ID or form ID, then read the whole document in application code. The JSONB serves as a serialized payload, not a queryable structure.

Audit trail and event history

Storing the before/after state of a row change is a natural fit for JSONB:

CREATE TABLE audit_log (
  id          BIGSERIAL PRIMARY KEY,
  workspace_id UUID NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id   UUID NOT NULL,
  action      TEXT NOT NULL,
  actor_id    UUID NOT NULL,
  before_data JSONB,
  after_data  JSONB,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

You query audit logs by entity and time range, never by fields inside before_data. The JSONB documents are read-only after insert. This is exactly the use case JSONB was built for.

Configuration and settings objects

Workspace settings that have optional, deeply nested structure are a good fit:

CREATE TABLE workspace_settings (
  workspace_id UUID PRIMARY KEY REFERENCES workspaces(id),
  settings     JSONB NOT NULL DEFAULT '{}'
);

You read settings by workspace ID and apply them in application code. You never need to query across workspaces by a setting value. The schema is stable from PostgreSQL's perspective even as the settings structure evolves.

The decision rule in practice

Before reaching for JSONB, ask three questions:

1. Will you ever filter rows by a field inside this document? If yes, use a relational column. If the field set is variable, consider a separate attribute table.

2. Will this field appear in ORDER BY or aggregate queries? If yes, use a relational column. You cannot efficiently sort or aggregate JSONB fields at scale without functional indexes that you will maintain forever.

3. Is the schema genuinely unknown at design time? If yes, JSONB is appropriate. If you know what the fields are but want to avoid writing a migration, that is the wrong reason to use JSONB.

Migrating out of JSONB when you got it wrong

If you have JSONB columns that should be relational, the migration path is:

-- Step 1: Add the real column
ALTER TABLE orders ADD COLUMN export_status TEXT;

-- Step 2: Backfill from JSONB
UPDATE orders
SET export_status = metadata->>'export_status'
WHERE metadata ? 'export_status';

-- Step 3: Add index
CREATE INDEX CONCURRENTLY idx_orders_export_status
  ON orders (workspace_id, export_status)
  WHERE export_status IS NOT NULL;

Deploy application code that writes to both the JSONB field and the new column during a transition period. Then remove the JSONB field access from reads. Then clean up.

In Go with sqlc, you add the column to your SQL queries and regenerate types. The compiler shows you every call site that needs updating.

Lessons from production

The teams that get this right use JSONB for genuinely variable data that they will not query against, and relational columns for everything they need to filter, sort, or aggregate. The temptation to avoid migrations by putting data in JSONB is real, but the debt accumulates faster than you expect, especially in multi-tenant systems in Lebanon and the MENA region where client customization requests arrive constantly.

A good way to audit your schema: for every JSONB column, write down the actual queries that touch it. If any of those queries filter or sort by a field inside the document, you have a relational column hiding inside a blob.