Multi-tenant SaaS architecture: shared, schema or database per tenant

Shared database, shared schema

Every tenant’s rows live in the same tables, distinguished by a tenant_id (or account_id) column. This is the default SaaS multi tenancy architecture for good reasons: one migration path, one connection pool, cross-tenant analytics are a simple query, and onboarding a tenant is an INSERT.

The risk is a missing WHERE clause. One query without tenant scoping leaks data across customers, which is the worst incident a B2B SaaS can have. So the model is only as good as its enforcement:

• Scope at the framework level, not per query. A default scope or tenant-aware manager in your ORM, set from the authenticated request – acts_as_tenant in Rails, a scoped manager or django-scopes in Django, a global scope in Laravel.
• Back it with Postgres row-level security (RLS). Enable RLS on tenant tables with a policy that compares tenant_id to a custom setting such as app.tenant_id (read with current_setting), and set that value at the start of each transaction. Now a forgotten scope returns nothing instead of everything.
• Make tenant_id part of every index and unique constraint that should be per-tenant, and lead composite indexes with it. Unique (email) across all tenants is usually a bug.
• Make the application role non-superuser and not the table owner, because both bypass RLS by default (FORCE ROW LEVEL SECURITY covers owners).

The RLS details that bite

Row-level security is the strongest cheap guarantee you can add, but a few details catch teams out:

• Connection poolers. With PgBouncer in transaction mode, a session-level SET leaks to the next transaction that gets the same server connection. Use SET LOCAL (or set_config with is_local true) inside each transaction so the setting dies with it.
• Background jobs don’t have a request. Every job must set the tenant explicitly from its payload; build this into the job base class so it can’t be forgotten.
• Admin and cross-tenant work needs a separate role with BYPASSRLS, used only by the code paths that genuinely need it, and audited.
• Query plans. RLS policies are added as predicates to every query. Keep them simple equality checks on an indexed column, and check plans with EXPLAIN after enabling it.
• Test it. A test that logs in as tenant A and attempts to read, update and delete tenant B’s records through every endpoint is cheap insurance.

Schema per tenant

Each tenant gets its own Postgres schema with an identical set of tables; the application sets search_path per request. Libraries like django-tenants and the older apartment gem for Rails implement this.

The appeal is stronger logical isolation and easy per-tenant export or deletion. The costs arrive with growth: every migration runs once per tenant, so a deploy with 5,000 tenants is 5,000 migrations, and a failure halfway leaves schemas at different versions. Thousands of schemas also bloat the system catalogs, slow down tools like pg_dump, and make cross-tenant reporting a union across schemas. Connection poolers and search_path need the same care as RLS settings.

I’d choose it for a product with tens or low hundreds of large tenants that each expect data separation – not for self-serve SaaS that may reach tens of thousands of accounts.

Database per tenant

Each tenant gets a separate database, sometimes on a separate server or in a separate region. It’s the strongest isolation short of separate deployments: no shared tables, per-tenant backup and restore, per-tenant encryption keys, and the ability to place data in a specific country for residency requirements.

You pay in operations. Provisioning, migrations, monitoring, backups and connection management all multiply by tenant count. It works well for a small number of high-value enterprise customers, ideally automated so that adding a tenant is a pipeline run, not a ticket.

The hybrid model most SaaS products end up with

The practical answer for B2B SaaS is usually tiered: a shared, RLS-enforced pool for self-serve and mid-market customers, and dedicated databases (or dedicated deployments) as an enterprise tier, priced accordingly. To make this possible later, design for it now:

• Route every database connection through one place that resolves tenant to connection.
• Keep tenant_id on every table even in dedicated databases, so moving a tenant between tiers is a data copy, not a schema change.
• Keep global data (plans, feature flags, users who belong to several tenants) in a separate control-plane database.
• Build tenant export and import tooling early. You’ll need it for tier moves, offboarding and data-deletion requests anyway.

Noisy neighbors

In any shared model, one tenant can degrade service for others: a giant CSV import, a report over five years of data, an integration hammering the API. The defenses:

• Per-tenant rate limits on the API and on expensive endpoints, enforced at the edge or in Redis.
• Per-tenant job queues or fair scheduling so one tenant’s 100,000 queued jobs don’t starve everyone else. Sidekiq, Celery and Laravel queues can all do weighted or per-tenant queues with some care.
• Statement timeouts on interactive queries, and heavy reporting moved to a read replica or analytical store.
• Per-tenant observability: tag logs, traces and slow-query reports with tenant_id so you can see who’s causing the load.
• Sharding by tenant once a single primary can’t keep up. Because tenant_id is on every table, tenant-based sharding (Citus is the common Postgres option) is a natural fit for the shared model.

What enterprise customers ask for

Enterprise deals bring security questionnaires, and the tenancy questions are predictable: how is our data separated, can we have a dedicated instance, can we choose the region, can we hold our own encryption keys, how do you handle deletion at contract end. A shared model with RLS, audit logging and per-tenant export answers most of them. Dedicated databases or deployments answer the rest, at a price that should reflect the operating cost.

Broader SaaS architecture decisions – billing, plans, entitlements – sit on top of tenancy, so it’s worth getting this layer right before you build usage-based pricing. See usage-based billing.

Retrofitting tenancy

If you’re running a product that was built single-tenant and now hosts many customers – often a platform built for one client that’s being resold – retrofitting is a well-trodden path: add tenant_id everywhere with expand/contract migrations, backfill it, add framework-level scoping, then switch on RLS table by table. It’s exactly the kind of work where zero downtime deployment techniques matter.

That retrofit, and scaling a platform past the limits of its original design, is the kind of work I take on in SaaS consulting and growth-stage SaaS engagements.

Related

• A SaaS consultant who builds and runs SaaS
• Scaling SaaS without stalling delivery
• A software architecture consultant who stays for the build
• Usage based billing and subscriptions, designed to stay correct
• PostgreSQL performance tuning when the database is the bottleneck
• Zero downtime deployment, including the database