How to build a multi-tenant SaaS on PostgreSQL: three models compared

Short answer: for most small and mid-size SaaS products, start with one database and shared tables that carry a tenant_id column, protected by row-level security and a restricted application role. Move to schema-per-tenant or database-per-tenant only when a customer demands hard isolation, very different data volumes make sharing painful, or you need per-tenant restore.

What are the three models?

1. Shared tables with a tenant ID

Every table has a tenant_id. Every query filters on it. One set of tables serves everyone.

• Pros: simplest to operate, one migration for everyone, cheap per tenant, easy cross-tenant analytics for you.
• Cons: a missed filter leaks data (row-level security reduces this risk), one noisy tenant can slow others, restoring one tenant's data alone is awkward.

2. Schema per tenant

One database, one PostgreSQL schema for each tenant, with the same table definitions repeated in each. The connection sets search_path to the tenant's schema.

• Pros: stronger separation, per-tenant dump is easy, tenant-specific customisation is possible.
• Cons: migrations must run in every schema, thousands of schemas strain the catalog and tooling, and connection pooling gets harder when search_path is per session.

3. Database per tenant

Each tenant gets its own database, possibly on its own server.

• Pros: the strongest isolation, simple per-tenant backup and restore, easy to place a large customer on separate hardware, easy to delete a tenant.
• Cons: the highest operating cost, migrations across hundreds of databases, connection counts multiply, and cross-tenant reporting needs extra work.

How do you choose?

• Many small customers, similar needs: shared tables.
• A few dozen mid-size customers who want stronger separation: schema per tenant can work.
• Regulated customers, contractual isolation, or a handful of very large ones: database per tenant, or a hybrid where most tenants share and large ones get their own.

A hybrid is common and healthy. Build the application so that tenant resolution is one function. That lets you move a tenant to its own database later without rewriting queries.

How do you make shared tables safe?

• Add tenant_id uuid NOT NULL to every tenant-owned table, including join tables.
• Make composite foreign keys include the tenant: FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id). This stops a row in tenant A pointing at a customer in tenant B.
• Enable and force row-level security with a policy on tenant_id.
• Run the app as a role that does not own the tables and cannot bypass RLS.
• Set the tenant per transaction, never per session.
• Put tenant_id first in indexes.
• Write a test that tries to read another tenant's data on every important endpoint.

What else belongs to a tenancy design?

• Unique constraints are per tenant: UNIQUE (tenant_id, invoice_number).
• Background jobs must carry the tenant explicitly. A job queue is a classic place where context is lost.
• File storage needs tenant-scoped paths and access checks.
• Caches must include the tenant in the key.
• Logs and error reports should include the tenant ID so support can find things, and avoid logging other tenants' data.
• Backups: with shared tables, plan how you would restore one customer after their own mistake. Often the answer is restoring to a side database and copying rows back.

What did I choose?

In Rechvix I used shared tables with an organisation identifier, row-level security, an application-layer organisation filter on every lookup, and separate database roles for migration and runtime. That fits a self-hosted product where one installation holds several organisations. It also taught me that the policy is only one layer, which is why RLS is not your only tenant boundary.