Why should you care?

PostgresVerse SaaS tenants share orders table but store custom fields per tenant in metadata jsonb without 500 ALTER TABLEs.

See it live — copy this example

Run in pgAdmin or psql.

CREATE TABLE tenant_orders (
  order_id bigserial PRIMARY KEY,
  tenant_id int NOT NULL REFERENCES tenants(tenant_id),
  total numeric(12,2) NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}'
);

SELECT order_id, metadata->>'po_number'
FROM tenant_orders
WHERE tenant_id = 3
  AND metadata @> '{"priority":"high"}';

What happened?

• Relational tenant_id and total stay typed and indexed.
• metadata holds tenant-specific keys like po_number.
• Queries combine FK filter and jsonb containment.

Practice next

1. Create tenants and tenant_orders.
2. Insert rows with different metadata shapes per tenant.
3. Index tenant_id B-Tree and metadata GIN.
4. Generated column: po_number text GENERATED ALWAYS AS (metadata->>'po_number') STORED.
5. CHECK (jsonb_typeof(metadata->'priority') = 'string').

Remember

Relational for shared core; jsonb for extensions. Index both tenant_id and hot jsonb paths. Document jsonb schema per tenant in app layer.

PostgresVerse B2B SaaS

Enterprise tenant adds custom approval fields in metadata; startup tenant leaves {}.

Outcome: One schema serves both without forked databases.