Database per Tenant vs Shared Schema: How Each Scales

A shared schema with a tenant_id column and row-level security scales to thousands of tenants on one database, one connection pool and one migration run. A database per tenant scales isolation instead: each tenant gets its own backups, limits and region, but connections, migrations and monitoring multiply by the tenant count. Most SaaS products should pool by default and move only the few tenants that need it.

This is the database-level follow-up to the tenancy section of the multi-tenant SaaS guide, which introduces the three models. Here the focus is what happens as the tenant count grows: where each model runs out of room, what the arithmetic looks like, how you restore one tenant, how you stop one tenant slowing the rest, and how you move a tenant from the pool into its own database. Examples use PostgreSQL and node-pg; behaviour is checked against the current PostgreSQL documentation.

Which model scales further: database per tenant or shared schema?

Shared schema scales further in tenant count; database per tenant scales further per tenant. The table sets the three models side by side on the things that break first.

What grows	Shared schema + RLS	Schema per tenant	Database per tenant
New tenant	Insert a row	Create a schema and run every migration in it	Provision a database, run migrations, register credentials
Connections	One pool for all tenants	One pool, but search_path must be set per request	A pool per tenant database, per app instance
Schema migrations	Run once	Run once per schema	Run once per database, with drift between them
Restoring one tenant	Restore elsewhere, copy rows back	Dump and restore one schema, with caveats	Native: restore that database
Noisy neighbour	Shared CPU, memory and I/O; needs limits	Same as shared schema	Isolated, if databases sit on separate servers
Cross-tenant reporting	A normal query, run by a privileged role	UNION across schemas or a pipeline	A pipeline into a warehouse
Per-tenant region or keys	No; per pool only	No	Yes
How isolation fails	A missing policy or a privileged connection	A wrong search_path	The app connects to the wrong database

Schema per tenant looks like a middle ground but inherits most costs from both sides: migrations multiply like database per tenant, while CPU, memory and I/O are still shared like a pool. Choose it deliberately, not as a compromise.

How many connections does a database per tenant need?

Multiply tenants by pool size by app instances. That product grows quickly, and PostgreSQL's default max_connections "is typically 100 connections", according to the connection settings documentation.

A worked example with assumed numbers: 200 tenant databases, a pool of 5 connections per database, and 3 app instances. If every instance keeps a pool open to every tenant, that is 200 × 5 × 3 = 3,000 connections. Put all 200 databases on one server and you are 30 times past the default. The same product on a shared schema needs 5 × 3 = 15.

The usual fixes, each with a cost:

• Open tenant pools lazily and close idle ones, so each instance only holds connections for tenants it is actively serving. Cold tenants then pay a connection set-up on their first request.
• Use a pooler in transaction mode in front of the servers. It caps real server connections, but session-level settings stop being reliable, so any per-request setting must be scoped to the transaction.
• Spread tenant databases across several servers, which adds a placement table and more servers to run.

Serverless hosting makes the arithmetic worse, because the number of app instances is not fixed. Plan the connection budget before the first 50 tenants, not after the first outage.

How do you run migrations across hundreds of tenant databases?

With a runner that treats the fleet as data: it records the schema version of each tenant, applies a migration one tenant at a time, and keeps going when one fails. The app must work with both the old and the new schema during the rollout.

import { Client } from 'pg'

type Tenant = { id: string; databaseUrl: string }

// control: a client for the control-plane database that lists tenants.
export async function migrateFleet(control: Client, tenants: Tenant[], version: string, sql: string) {
  const failed: string[] = []
  for (const t of tenants) {
    const db = new Client({ connectionString: t.databaseUrl })
    await db.connect()
    try {
      await db.query('BEGIN')
      await db.query(sql)
      await db.query('INSERT INTO schema_migrations (version) VALUES ($1)', [version])
      await db.query('COMMIT')
      await control.query('UPDATE tenants SET schema_version = $1 WHERE id = $2', [version, t.id])
    } catch (err) {
      await db.query('ROLLBACK')
      failed.push(t.id) // Report and retry later; never stop the whole fleet half-way.
    } finally {
      await db.end()
    }
  }
  return failed
}

Two rules make this safe. First, use expand-and-contract changes: add the new column, deploy code that writes both, backfill, then remove the old column in a later release, so a tenant that has not migrated yet still works. Second, alert on version drift: a query on the control plane for tenants whose schema_version is behind the latest, every day. A shared schema needs neither rule, because there is only one database to migrate.

How do you restore one tenant from a shared schema?

Restore the whole backup into a scratch database, export the tenant's rows from it, and write them back into production inside that tenant's row-level-security context. You cannot restore one tenant with pg_dump alone, because its documentation says it "only dumps a single database", and a shared database holds everyone.

Step one, in the scratch database restored from last night's backup, export one tenant's rows, parents before children. Run it as a maintenance role that can read every row, and filter explicitly:

-- Run in the scratch database, for each table in foreign-key order.
COPY (SELECT * FROM customers WHERE tenant_id = '3f9a0c1e-5b7d-4e21-9c44-2a8f6d0b1e73')
  TO STDOUT WITH (FORMAT csv, HEADER);
COPY (SELECT * FROM invoices WHERE tenant_id = '3f9a0c1e-5b7d-4e21-9c44-2a8f6d0b1e73')
  TO STDOUT WITH (FORMAT csv, HEADER);

Step two, in production, you cannot simply COPY the files back. PostgreSQL's COPY reference says "COPY FROM is not supported for tables with row-level security. Use equivalent INSERT statements instead." So load each file into a temporary table, which has no policies, and insert from it inside the tenant's context, connected as the normal app role:

BEGIN;
SELECT set_config('app.tenant_id', '3f9a0c1e-5b7d-4e21-9c44-2a8f6d0b1e73', true);

CREATE TEMP TABLE restore_customers (LIKE customers) ON COMMIT DROP;
CREATE TEMP TABLE restore_invoices  (LIKE invoices)  ON COMMIT DROP;
COPY restore_customers FROM STDIN WITH (FORMAT csv, HEADER);  -- customers file
COPY restore_invoices  FROM STDIN WITH (FORMAT csv, HEADER);  -- invoices file

-- Children first on delete, parents first on insert.
DELETE FROM invoices;    -- RLS limits this to the current tenant
DELETE FROM customers;
INSERT INTO customers SELECT * FROM restore_customers;
INSERT INTO invoices  SELECT * FROM restore_invoices;
COMMIT;

Running the write under the tenant's context is the safety net. With WITH CHECK on the policy, a row carrying another tenant's ID is rejected and the whole transaction rolls back, and a DELETE without a WHERE still touches only this tenant. Rehearse it on staging first; the policy setup itself is in the Postgres row-level security guide.

For a partial restore, such as only the records a user deleted by mistake, copy into a staging table and INSERT ... SELECT the missing rows rather than replacing everything. Time the full drill on real data sizes, and write the number down; that is your honest per-tenant recovery time.

What about schema per tenant?

pg_dump -n dumps one schema, but the documentation warns that it "makes no attempt to dump any other database objects that the selected schema(s) might depend upon", so a one-schema dump may not restore cleanly on its own. Shared extensions, types and functions in public need to be restored first.

How do you stop one tenant slowing the others in a shared database?

Cap what each tenant's queries can use, make heavy tenants visible, and move a tenant out when limits are not enough. All three fit into the helper that sets the tenant context.

await db.query('BEGIN')
await db.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId])
// Both are transaction-scoped and reset at COMMIT or ROLLBACK.
await db.query("SET LOCAL statement_timeout = '5s'")
await db.query("SELECT set_config('application_name', $1, true)", ['tenant:' + tenantId])

• statement_timeout stops a runaway report holding CPU and locks. Give exports and reports a separate, longer path that runs in a background queue.
• application_name shows the tenant next to each running query in pg_stat_activity, so you can see who is heavy right now.
• Tenant-first indexes keep one large tenant's rows from dragging every query into wider scans.
• Partitioning by tenant helps once one table is very large: PostgreSQL's table partitioning supports list partitions, so a very large tenant can get a partition of its own, with a default partition for everyone else.

Microsoft's noisy neighbour guidance lists the same sequence at the platform level: monitor per tenant, apply resource governance, rebalance tenants across instances, and restrict operations such as queries without a time limit.

How do you move one tenant from the shared schema into its own database?

Copy the tenant's rows into a new database on the same schema version, stop writes briefly, copy what changed, then switch the tenant's placement record. Keep tenant_id in the new database so the same code runs in both places.

1. Provision the new database and run every migration, so it matches the pool's schema version exactly.
2. Bulk copy the tenant's rows with the same filtered COPY ... TO as the restore, parents first, while the tenant keeps working. If the new database also has row-level security, load through temporary tables and INSERT, as above.
3. Freeze writes for that tenant only, by flipping a read-only flag the app checks, and copy rows changed since the bulk copy (an updated_at column makes this a short query).
4. Verify row counts and a checksum per table, such as SELECT count(*), md5(string_agg(t::text, ',' ORDER BY t.id)) FROM invoices t WHERE t.tenant_id = '...' run on both sides.
5. Switch the placement record in the control plane and lift the freeze. The app's placement lookup now returns the new database.
6. Delete the tenant's rows from the pool after the rollback window your contract allows.

Globally unique IDs make this easy; per-table sequences that restart at 1 in the new database will collide the first time the tenant moves back. Use UUIDs, or keep sequence values in step when you copy.

What does each model cost at scale?

A shared schema has a single cost floor; a database per tenant has one floor per tenant, plus the engineering time to run a fleet. Microsoft's tenancy models guide sums up the dedicated end: "100 tenants probably require 100 times that cost."

The build side follows the same shape. Kanopy Labs' May 2026 multi-tenant SaaS cost guide puts a shared-schema tenancy layer at about $8,000 to $20,000 and database per tenant at about $30,000 to $80,000 in market prices. The extra is provisioning, the migration runner, per-tenant monitoring and backups: all the code above that a shared schema does not need.

Why RAITHub for multi-tenant databases

Because RAITHub runs shared-schema isolation in production and treats every database boundary as something a test must attack.

• Proven at schema size. Sundor Skin has 146 PostgreSQL tables with row-level security. CI replays all 76 migrations on an empty database and fails if a buyer-scoped table lacks a policy, among 530+ automated tests.
• Restore and move procedures written down, timed and rehearsed, not improvised on the day a customer asks.
• Honest about proof. RAITHub's shipped platforms use a shared schema; the database-per-tenant fleet code above is engineering guidance, not a case study.
• Fixed scope. A free 15-minute technical audit, then a written quote; see the SaaS development service, or the API and backend service for a retrofit on an existing product.

When you don't need us

• You have a few tenants and a disciplined team. The procedures above are complete enough to follow yourself.
• You already run database per tenant and it works. Moving to a shared schema is only worth it when connections, migrations or cost are hurting.
• You have a live leak today. Start with users can see another tenant's data, then send it to the fix one issue page.

To review your tenancy model, connection budget or restore procedure, book the free technical audit with your schema and your tenant count.

Written 29 September 2026. PostgreSQL behaviour checked against the current documentation on 29 September 2026.

Frequently asked questions

Is database per tenant better than a shared schema?

For isolation per tenant, yes; for running many tenants cheaply, no. A shared schema with row-level security suits most SaaS products, with a few tenants moved to their own databases when contracts or load demand it.

How many tenants can a shared schema handle?

Thousands, as long as every query carries the tenant condition, indexes start with tenant_id, and heavy tenants are limited or moved out. The practical limit is usually the largest tenant's load, not the tenant count.

Does database per tenant need row-level security?

It is not required for isolation, because the separate database already provides it. Keeping tenant_id and the same policies still helps: one code path runs everywhere, and tenants can move between models.

How do I restore one tenant's data from a shared PostgreSQL database?

Restore the backup into a scratch database, export the tenant's rows with a filtered COPY TO, then load them into temporary tables in production and INSERT them inside that tenant's row-level-security context, in foreign-key order. COPY FROM does not work on tables with RLS. Rehearse the procedure on staging.

Is schema per tenant a good compromise?

Rarely. Migrations multiply like database per tenant, while CPU, memory and I/O are still shared, and a wrong search_path becomes the isolation failure. It fits when you need easy per-tenant export and have few tenants.

Why do connections run out with database per tenant?

Because every app instance can hold a pool to every tenant database. 200 databases, 5 connections each and 3 instances is 3,000 connections, against a PostgreSQL default of about 100 per server.