Shared schema, shared tables (row-level isolation) Every tenant's data lives in the same tables, distinguished by a tenant_id column. This is the cheapest to operate — one database, one schema, easy horizontal scaling. The risk is data leakage: one missing WHERE tenant_id = ? clause in one query and you've shown Company A's data to Company B. You mitigate this with Postgres Row-Level Security policies, application-level middleware that injects the tenant context, and rigorous query review.
This model works well when you have many small tenants and cost efficiency matters more than hard isolation guarantees.
Shared schema, separate schemas per tenant Each tenant gets their own Postgres schema (namespace) within the same database. Your queries hit tenant_a.orders, tenant_b.orders, etc. Schema migrations become more complex — you're running them across N schemas — but data isolation is stronger and you can give tenants customised table structures if needed.
This is the right choice when you expect to have tens to low hundreds of tenants and need stronger isolation without the operational cost of separate databases.
Separate database per tenant Each tenant has their own database instance. Maximum isolation. Easier compliance story (GDPR, HIPAA). Trivial to delete a tenant's data entirely. The cost is operational complexity: connection pooling becomes a serious concern, migrations must be orchestrated across instances, and your infrastructure costs scale linearly with tenant count.
Reserve this for enterprise-tier customers or regulated industries where the isolation requirement is contractual.
Whichever model you choose, the tenant_id must be established at authentication — not at the data layer. This means:
1. JWT tokens or sessions include the tenant_id after login 2. Every API handler reads tenant context from the verified token, not from request parameters the client can manipulate 3. A middleware layer enforces this on every route — it's never the responsibility of individual controllers
The most common multi-tenancy vulnerability I've seen is tenant_id coming from a request body or query param that the client controls. An attacker changes the ID in the request and reads another tenant's data. Don't do this.
If you're going with the shared-table model, RLS is your safety net. Here's the minimal setup:
Enable RLS on the table: ```sql ALTER TABLE orders ENABLE ROW LEVEL SECURITY; ```
Create a policy that restricts SELECT to the current tenant: ```sql CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.current_tenant_id')::uuid); ```
Set the tenant context at the start of each database session: ```sql SET app.current_tenant_id = '...'; ```
With connection pooling (PgBouncer, Prisma Accelerate), set this per-transaction, not per-connection. Use SET LOCAL inside a transaction block to scope it correctly and prevent context bleeding between pooled connections.
When you have 200 tenants and you need to add a column, what happens?
With shared tables: one migration, instant, done. With schema-per-tenant: you run the migration 200 times, ideally in a managed job that handles failures gracefully. With database-per-tenant: same as schema-per-tenant but the orchestration is harder.
Plan for this on day one. Build a migration runner that can apply schema changes across all tenants, report failures per tenant, and resume from a checkpoint. Running raw database migrations manually across 200 schemas at 2am because a deploy went wrong is a rite of passage I'd rather spare you.
For a new SaaS product with an unknown tenant count and mixed customer sizes: start with shared schema + RLS. It's the cheapest to operate, scales well to hundreds of tenants, and if you implement the auth layer correctly and use RLS policies as a backstop, it's secure.
Build the schema migration orchestration before you need it, not after. And make the tenant model a first-class concept in your codebase — a TenantContext that flows through every layer — not an afterthought bolted onto existing code.
When you land that first enterprise customer who demands dedicated infrastructure, you can offer them a separate schema or database without rewriting your entire application logic.
Ready to take action?