A good way to handle multi-tenant data in PostgreSQL depends on your isolation, cost, and operational needs. The main patterns are:

1) Shared database, shared schema

All tenants use the same tables, and each row has a tenant_id.

Example

CREATE TABLE invoices (
  id bigserial PRIMARY KEY,
  tenant_id bigint NOT NULL,
  amount_cents bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON invoices (tenant_id, created_at);

Pros

• Simple to start
• Lowest operational overhead
• Easy to scale many small tenants

Cons

• Highest risk of data leakage if queries are wrong
• Harder to do tenant-specific maintenance or customizations
• Large tenants can impact small ones

Best practices

• Always include tenant_id in every tenant-owned table
• Use composite unique constraints, e.g.:

  UNIQUE (tenant_id, invoice_number)
  

• Add indexes starting with tenant_id
• Consider PostgreSQL Row Level Security

---

2) Shared database, separate schema per tenant

Each tenant gets its own schema, e.g. tenant_a.invoices, tenant_b.invoices.

Pros

• Better isolation than shared schema
• Easier per-tenant customization
• Easier to backup/restore or migrate one tenant

Cons

• More complex migrations
• Can get unwieldy with many tenants
• Harder to manage at scale if you have hundreds or thousands of tenants

Best when

• You have a small-to-medium number of tenants
• Tenants need some customization
• You want moderate isolation without separate databases

---

3) Separate database per tenant

Each tenant gets its own PostgreSQL database.

Pros

• Strong isolation
• Easier compliance and tenant-level backup/restore
• No risk of cross-tenant query bugs affecting data access as much

Cons

• Operationally heavy
• More connection management
• Migrations and monitoring are more complex
• Expensive at large tenant counts

Best when

• You have enterprise customers
• Strong isolation/compliance is required
• Tenants are large enough to justify it

---

Recommended approach for most SaaS apps

For most SaaS products, start with shared schema + tenant_id + Row Level Security (RLS).

This gives good scalability and keeps ops simple, while reducing leakage risk.

---

Use Row Level Security

RLS makes PostgreSQL enforce tenant filtering automatically.

Example

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy
ON invoices
USING (tenant_id = current_setting('app.tenant_id')::bigint);

Then, for each request, set the tenant context:

SET app.tenant_id = '123';

Now queries only see rows for tenant 123.

Important

• RLS is only effective if all access goes through roles/policies correctly
• Make sure application roles do not bypass RLS unintentionally
• Test thoroughly

---

Model tenant ownership properly

Typical approach:

CREATE TABLE tenants (
  id bigserial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE users (
  id bigserial PRIMARY KEY,
  tenant_id bigint NOT NULL REFERENCES tenants(id),
  email text NOT NULL,
  UNIQUE (tenant_id, email)
);

If users can belong to multiple tenants, use a join table:

CREATE TABLE tenant_users (
  tenant_id bigint NOT NULL REFERENCES tenants(id),
  user_id bigint NOT NULL REFERENCES users(id),
  role text NOT NULL,
  PRIMARY KEY (tenant_id, user_id)
);

---

Indexing tips

For shared-schema multi-tenancy:

• Put tenant_id first in most indexes
• Use composite indexes for common filters

Examples:

CREATE INDEX ON orders (tenant_id, created_at DESC);
CREATE INDEX ON subscriptions (tenant_id, status);

This keeps tenant-specific queries fast.

---

Avoid common mistakes

• Forgetting tenant filters in queries
• Using globally unique fields when they should be tenant-scoped
• Not indexing tenant_id
• Storing tenant data in cache keys without tenant context
• Running admin jobs without tenant isolation checks
• Assuming app code alone is enough for isolation

---

Migration strategy

If you expect growth:

• Start with shared schema
• Use tenant-aware table design from day one
• Add RLS early
• If a tenant outgrows the shared model, move that tenant to a dedicated schema/database later

This hybrid approach is common.

---

Rule of thumb

• Few tenants, strong isolation needed → separate database
• Moderate tenants, some isolation/customization → separate schema
• Many tenants, standard SaaS → shared schema + tenant_id + RLS

If you want, I can also show:

1. a full PostgreSQL RLS setup,
2. a Prisma/Sequelize/SQLAlchemy example,
3. or a migration plan from single-tenant to multi-tenant.