For a startup SaaS app, the simplest and most common PostgreSQL setup is:

• one PostgreSQL cluster
• one database
• one set of shared tables
• a tenant_id column on every tenant-owned row

This is usually called the shared database, shared schema model. It’s the best default when you have lots of small tenants and want low ops overhead.

Recommended architecture

1) Use one database

Create a single database for the app, for example:

• app_prod
• app_staging

Inside that DB, keep app tables together.

2) Add tenant_id to tenant-owned tables

Any row that belongs to a tenant should include a tenant identifier:

tenant_id uuid not null

Examples:

• users
• projects
• invoices
• orders
• documents

Each row belongs to exactly one tenant.

3) Use globally unique tenant IDs

Create a tenants table:

create table tenants (
  id uuid primary key,
  name text not null,
  created_at timestamptz not null default now()
);

Then every tenant-owned table references it:

create table projects (
  id uuid primary key,
  tenant_id uuid not null references tenants(id),
  name text not null,
  created_at timestamptz not null default now()
);

4) Enforce isolation with indexes and constraints

At minimum, add tenant-scoped uniqueness:

create unique index projects_tenant_name_unique
on projects (tenant_id, name);

This ensures one tenant can have a project named “Acme”, while another tenant can too.

5) Always query with tenant filters

Every application query should include tenant_id.

Good:

select * from projects
where tenant_id = $1
order by created_at desc;

Bad:

select * from projects;

6) Prefer Row-Level Security if you want stronger protection

If you want defense-in-depth, PostgreSQL Row-Level Security can prevent accidental cross-tenant access.

Example:

alter table projects enable row level security;

create policy tenant_isolation on projects
using (tenant_id = current_setting('app.tenant_id')::uuid);

Then, per request, set:

set app.tenant_id = '...';

This is very useful if you have a larger codebase or multiple services.

---

Practical setup steps

A. Tables you’ll likely need

Tenants

create table tenants (
  id uuid primary key,
  slug text not null unique,
  name text not null,
  created_at timestamptz not null default now()
);

Users

If users can belong to one tenant:

create table users (
  id uuid primary key,
  tenant_id uuid not null references tenants(id),
  email citext not null,
  password_hash text not null,
  created_at timestamptz not null default now(),
  unique (tenant_id, email)
);

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

create table users (
  id uuid primary key,
  email citext not null unique,
  password_hash text not null,
  created_at timestamptz not null default now()
);

create table tenant_memberships (
  tenant_id uuid not null references tenants(id),
  user_id uuid not null references users(id),
  role text not null,
  primary key (tenant_id, user_id)
);

This is common for SaaS apps.

---

Indexing strategy

Index every common tenant-scoped lookup.

Examples:

create index projects_tenant_created_at_idx
on projects (tenant_id, created_at desc);

create index invoices_tenant_status_idx
on invoices (tenant_id, status);

create unique index users_tenant_email_unique
on users (tenant_id, email);

Rule of thumb:

• always lead with tenant_id in composite indexes for tenant-scoped tables
• this improves query speed and keeps data access partition-friendly

---

Connection and app design

Use a request-scoped tenant context

In your app:

1. authenticate the user
2. determine their tenant
3. attach tenant_id to the request context
4. use that tenant for all DB queries

If using RLS, set the tenant context at transaction start.

Use a connection pool

For SaaS apps, use a pool like:

• PgBouncer
• your language’s standard pool

This helps keep PostgreSQL stable under load.

---

Backups, migrations, and ops

Backups

Because all tenants share the same DB:

• take regular logical and physical backups
• test restore procedures
• keep point-in-time recovery enabled if possible

Migrations

Be careful: schema changes affect everyone. Best practice:

• use backward-compatible migrations
• deploy code that works with both old and new schema
• avoid long exclusive locks

Monitoring

Track:

• slow queries
• index usage
• vacuum health
• connection count
• table growth

---

When this model is not enough

A single shared database works very well until:

• tenants become very large
• compliance requires data isolation
• one tenant causes heavy load
• you need per-tenant backups or restores

At that point, you may move to:

• separate schema per tenant
• separate database per tenant
• hybrid model: most tenants in shared DB, large tenants isolated

But for a startup, shared DB + tenant_id is usually the right starting point.

---

My recommendation

Start with:

• one PostgreSQL database
• shared tables
• tenant_id on all tenant-owned rows
• tenant-scoped unique indexes
• Row-Level Security if you want stronger safety
• tenant-aware application code

This gives you:

• simple ops
• easy scaling early on
• low cost
• room to grow

If you want, I can give you:

1. a complete SQL schema template,
2. a multi-tenant PostgreSQL setup with RLS, or
3. a recommended architecture for Rails / Django / Node / Go.