Multi-tenant database design: shared, schema, or separate

Every B2B application arrives at the same fork within its first month: two customers, both with users, both with data, and one database. How you separate them is a decision you make once and live with for years — because unlike almost every other architectural choice, this one is extremely expensive to reverse.

There are three answers. They are all correct, for different sizes.

The three models

Shared schema — one set of tables, a tenant_id column on everything. Cheapest to run and to migrate, and it scales to numbers the other two can't reach. The isolation lives entirely in your queries, which is exactly why it's dangerous.

Schema per tenant — one Postgres schema per customer, identical tables inside each. Real isolation at the database level, and DROP SCHEMA genuinely deletes a customer. Falls over somewhere in the low thousands: every migration is N migrations, and Postgres itself gets unhappy with tens of thousands of schemas.

Database per tenant — total separation, per-tenant backups and restores, per-tenant encryption keys. This is what regulated customers ask for by name. It's also a connection-pool problem the moment you pass a couple of hundred tenants.

Shared schema: the quiet failure

Almost everyone starts here, and it's usually right. The whole model rests on one thing:

SELECT * FROM orders WHERE tenant_id = $1 AND id = $2;

Every query. Every single one. Forever.

The failure mode is not dramatic — nothing crashes. One developer, one afternoon, writes a query without the tenant filter:

// The bug. It works perfectly in dev, where there is one tenant.
func (r *OrderPostgres) ByID(ctx context.Context, id string) (*model.Order, error) {
	return r.queryOne(ctx, `SELECT ... FROM orders WHERE id = $1`, id)
}

In development you have one tenant, so it passes. In staging you have two, and nobody clicks the exact row. In production, customer A opens an order and sees customer B's. You find out from a support ticket.

That's why "just remember the WHERE clause" is not a strategy. You need the database to enforce it.

Row-Level Security, and the mistake everyone makes

Postgres can apply the filter for you:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

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

Then set the tenant once per request, and every query is filtered whether or not you remembered:

// Set on the connection at the start of the request, inside the transaction.
tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return err
}
defer tx.Rollback()

if _, err := tx.ExecContext(ctx,
	`SELECT set_config('app.tenant_id', $1, true)`, tenantID); err != nil {
	return err
}
// every query on tx is now scoped — forgetting the filter returns zero rows,
// not someone else's data

Note the true in set_config: it makes the setting local to the transaction. Without it the value sticks to the pooled connection and leaks into the next request that borrows it — which is the same bug you were trying to prevent, only harder to find.

-- Either force it on the owner...
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- ...or, better, connect as a role that doesn't own anything.
CREATE ROLE app_user LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Verify it rather than assume it. One test, run in CI:

func TestTenantIsolation(t *testing.T) {
	seedOrder(t, tenantA, "order_1")

	ctx := withTenant(context.Background(), tenantB)
	_, err := repo.ByID(ctx, "order_1")

	if !errors.Is(err, sql.ErrNoRows) {
		t.Fatal("tenant B could read tenant A's order")
	}
}

That test is worth more than the policy, because it fails when someone connects as the owner again.

Indexes: tenant_id goes first

With shared schema, a query is never "find order X" — it's always "find order X for this tenant". So composite indexes must lead with the tenant column:

CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at DESC);
CREATE UNIQUE INDEX idx_orders_tenant_number ON orders (tenant_id, order_number);

That second one carries a rule people miss: uniqueness is almost always per-tenant, not global. Two customers can both have an order number 1001 and both be right. A global unique index on order_number will look fine until your second customer signs up.

Migrations: what actually differs

This is where the models separate in daily life, more than in any architecture diagram.

Shared schema — one migration, one run. A large ALTER TABLE locks one big table, so on Postgres you write it the careful way: add a nullable column, backfill in batches, add the constraint after. Standard practice, well-documented, one execution.

Schema per tenant — the same migration N times, in a loop, and now you own a distributed-transaction problem you did not ask for:

for _, schema := range tenantSchemas {
	if _, err := db.Exec(fmt.Sprintf("SET search_path TO %q", schema)); err != nil {
		return err
	}
	if err := migrate.Up(db); err != nil {
		// tenant 400 of 900 just failed. Now what?
		return fmt.Errorf("tenant %s: %w", schema, err)
	}
}

You need it resumable, you need to know which tenants are on which version, and you need your application to tolerate both versions while the loop runs. It's all doable. It's just a real system you now maintain, and nobody budgets for it up front.

Database per tenant — same loop, plus N connection pools.

The connection-pool ceiling

The reason database-per-tenant stops scaling is not disk, it's connections. Postgres allocates a process per connection; a few hundred is a lot. With one pool of ten connections per tenant, you hit the wall at roughly 50 tenants.

The fixes are real but they're work: one shared pool that re-points per request, a proxy like PgBouncer in transaction mode, or lazily opening pools and closing idle ones. Each adds a moving part. Factor that in before choosing this model for anything but a small number of large customers.

A hybrid that actually works

The most common production shape at scale isn't one of the three — it's two of them:

• Shared schema by default. Everyone self-serve lands here.
• Dedicated database on request. The enterprise customer who needs their own encryption keys, their own region, or a contractual guarantee gets one, and pays for it.

This works only if you build it in the right order: write the code as if the database might be anywhere from day one — tenant resolved from the request, connection selected per tenant, no global singleton db variable — and then serving a tenant from a different database later is a routing change instead of a rewrite.

// The shape that keeps the door open. Note that nothing above this line
// knows whether tenants share a database or not.
type TenantDB interface {
	For(ctx context.Context, tenantID string) (*sql.DB, error)
}

If you hardcode a package-level db today, that door is closed and reopening it is a quarter of work.

Choosing, in one paragraph

If your customers are self-serve and numerous: shared schema with RLS, forced on the owner, with the isolation test in CI and tenant_id leading every index. If you sell to a handful of large regulated accounts: database per tenant, and accept the pooling work. Schema per tenant is the middle option that looks appealing on a diagram and delivers the migration cost of the third model with the noisy-neighbour problems of the first — pick it only when you have a specific reason to.

Whatever you pick, resolve the tenant once at the edge, put it in the request context, and never let a repository take a tenantID string as an ordinary argument that a caller can forget to pass.