Multi-tenant SaaS: one shared database or one per customer

Written By: BrainSoft In Backend

Every SaaS product eventually hits the same fork in the road. You have ten customers, then fifty, then a few hundred, and someone in sales asks whether customer data lives in the same database as everyone else's. The answer you give that day shapes your backups, your migrations, your support tickets and your pricing for years. I have built both models and migrated between them, and the choice is rarely about technology purity. It is about how much operational pain you can absorb per customer.

Start with a shared database and a tenant_id column on every table, and only split a customer out when a real constraint forces you to. That single decision keeps migrations to one run, keeps your connection pool small, and keeps your hosting bill predictable. Here is how the two models actually differ once you are running them in production.

What a shared database really costs you

The shared model means one schema, one migration history, one set of indexes. Adding a column is a single deploy. That sounds trivial until you have done it forty times across forty databases at 2am.

The cost is that isolation becomes your job. Every query needs a tenant filter, and forgetting one is a data leak, not a bug. I enforce it at the connection level rather than trusting developers to remember. With Postgres you can lean on row-level security and set the tenant per request:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- per request, inside the transaction
SET LOCAL app.tenant_id = '8f14e45f-ea4c-4b1e-9c1a-2f0b7c3d9e21';

Now a missing WHERE clause returns zero rows instead of someone else's invoices. That one policy has saved me more than any code review. Noisy neighbours are the other real cost. One tenant running a heavy report can slow everyone down, so you need per-tenant rate limits or a separate read replica for analytics.

When a database per customer earns its keep

Splitting databases makes sense when isolation is a contractual requirement, not a preference. Banks, healthcare and public sector buyers often insist their data never shares storage with another company. If that clause is in the contract, the argument is over before it starts.

It also helps when customers have wildly different data volumes or compliance regions. A tenant in the EU can live in an EU database while the rest stay in your primary region. Restores get simpler too: you can restore one customer to a point in time without touching anyone else, which turns a scary incident into a routine task.

Migrations run per tenant, so you need a runner that tracks schema version per database and can pause on failure.
Connection pooling gets harder. Hundreds of databases means hundreds of pools unless you route through something like PgBouncer in transaction mode.
Cross-tenant reporting needs a separate warehouse or a fan-out job. You cannot just JOIN anymore.
New customer provisioning becomes a real workflow: create database, run migrations, seed defaults, register the tenant.

That last point is where teams underestimate the work. Provisioning is a background job with retries, not a line in a signup handler. If you are weighing this against a wider platform build, our services cover the architecture and the migration work.

A middle path that usually wins

You do not have to pick one model for the whole product. The pattern I keep coming back to is a shared database with a schema per tenant, plus the option to promote a large customer to a dedicated database later. Postgres schemas give you a cleaner namespace than a tenant_id column and still let you run one migration script across all of them.

CREATE SCHEMA tenant_acme;
SET search_path TO tenant_acme;

-- migrations loop over schemas, one transaction each
SELECT schema_name FROM information_schema.schemata
WHERE schema_name LIKE 'tenant_%';

Your application code stays the same because it just sets search_path at connection time. Promote a tenant by moving its schema to a dedicated server with pg_dump and a restore. It is a few hours of work for one customer, not a rewrite. The trick is to design the tenant resolution layer early, so the storage decision is a configuration value rather than something baked into every query. If you want a second opinion before you commit, get in touch and we can look at your schema and access patterns.

Frequently asked questions

Is a shared database less secure than one per customer?

Not inherently. A shared database with row-level security enforced at the connection level is often safer than a split model where a routing bug sends a request to the wrong database. The risk in both cases is application code, not the storage layout. What a dedicated database buys you is a simpler audit story and a contract clause you can point at.

How do I run migrations across hundreds of databases?

Track a schema version per database in a small registry table, then run migrations in batches with a worker that records success or failure per tenant. Run them in a transaction where the engine supports transactional DDL, and stop the batch on the first failure so you do not spread a broken migration. Never run migrations synchronously during a deploy.

When should I move a customer to a dedicated database?

Move when a contract requires it, when their data volume starts affecting other tenants, or when they need a restore that does not touch anyone else. Do not move them just because they are your biggest customer. Measure query latency and lock contention first, because a dedicated database will not fix a missing index.

#Backend