One of the first big decisions when building a SaaS product is how to keep each customer's data separate. Get it wrong and you either leak data between tenants or end up with an architecture that can't scale. Here are the three common approaches and when each one makes sense.
1. Shared database, shared tables
Every table has a tenant_id column, and every query filters on it.
Pros
- Cheapest to run and simplest to deploy
- One migration updates every tenant
- Easy cross-tenant reporting for your own analytics
Cons
- One forgotten
WHERE tenant_id = ...and you have a data leak - A single noisy tenant can slow everyone down
- Harder to offer per-customer backups or data residency
2. Shared database, schema per tenant
Each tenant gets its own schema (tenant_acme.invoices, tenant_globex.invoices), and you switch the search_path per request.
Pros
- Stronger logical separation
- Per-tenant restores are easier
Cons
- Migrations must run once per schema, which gets slow with thousands of tenants
- Connection pooling becomes trickier
3. Database per tenant
Each tenant gets a completely separate database.
Pros
- Strongest isolation, and a natural fit for enterprise customers and data residency requirements
- Noisy neighbours are no longer a problem
Cons
- Highest cost and operational overhead
- Fleet-wide migrations and monitoring need proper tooling
Making option 1 safer with Row-Level Security
Most early-stage products start with shared tables. You can remove the "forgotten WHERE clause" risk by letting PostgreSQL enforce isolation with Row-Level Security (RLS):
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.current_tenant')::uuid);
Then, at the start of each request's transaction, set the tenant:
BEGIN;
SET LOCAL app.current_tenant = '6f1c2a9e-...';
SELECT * FROM invoices; -- only this tenant's rows come back
COMMIT;
A few things to watch out for:
-
Table owners bypass RLS unless you use
FORCE ROW LEVEL SECURITY, and superusers always bypass it. Your app should connect as a separate, non-owner role. - Use
SET LOCALinside a transaction so the setting can't leak to the next request on a pooled connection. - Index
tenant_id(usually as the first column in composite indexes), or performance will suffer as you grow.
Which should you pick?
A sensible default for most new SaaS products:
- Start with shared tables plus RLS. It's cheap, simple and safe enough.
- Design your code so the tenant context is resolved in one place (middleware, not scattered queries). That makes moving to another model later much less painful.
- Offer database-per-tenant as an enterprise tier if customers ask for dedicated infrastructure.
How are you handling multi-tenancy in your projects? I'd love to hear what's worked, or not, in the comments.
I work at MACROGEN, where we build SaaS platforms. You can see some of our SaaS work here.
This article was created with the help of AI and reviewed for accuracy by the author.
Top comments (2)
Resolving tenant context in one place is the advice most teams skip and regret later. One addition: run migrations as a different role than the app role, because FORCE RLS on table owners will silently hide rows from backfill scripts. How are you testing policy changes as the schema evolves?
Thanks Jesse, that's a great addition and a really easy one to get caught by. I'll probably update the post to mention it. We keep three roles: an owner role for the schema, a migration role with BYPASSRLS for migrations and backfills, and a restricted app role that's subject to every policy. That way backfills see everything and the app never can.
On testing policy changes, a few things have worked well for us:
Isolation tests in CI: seed two tenants, connect as the app role (never a superuser, since that bypasses RLS and gives false passes), set tenant A and assert that none of tenant B's rows come back for select, update and delete.
A catalogue check: a query against pg_class that fails the build if any table with a tenant_id column doesn't have relrowsecurity and relforcerowsecurity enabled. This catches new tables where someone forgot to add a policy.
Failing closed: using current_setting('app.current_tenant', true) so an unset tenant returns no rows rather than something unexpected.
Tools like pgTAP make the first one fairly painless. Curious whether you've found a good way to test policies on tables that reference each other?