Database per tenant: notes on a different kind of multi-tenancy

When many customers share one system, the first design question is tenancy: how do you keep each customer's data apart? There are three common answers:

Most SaaS products choose the shared schema, and for good reason: it is the cheapest to run. On a point-of-sale and stock system I built for retail shops, I chose database per tenant instead. These are my notes on why, and how I made it work.

When database per tenant makes sense

It's the right choice when most of these are true:

• The data is sensitive and business-critical. Sales, stock and money. A leak between two shops is the worst bug the product could have.
• Customers want their data in one piece. Restore one shop to last night without touching the others, or hand a leaving customer their whole database.
• There are many tenants, but not millions. Hundreds or thousands of databases are manageable; millions are not.
• Tenants rarely need to be queried together. Each shop mostly works on its own data.

The biggest benefit is that isolation becomes part of the architecture instead of a rule every query has to remember. Application queries never mention a tenant, because the connection itself can only reach one tenant's data.

Problem 1: creating a tenant safely

Creating a tenant now means creating a database, migrating it, seeding it and registering it in a control database. These are several steps on two databases, so they can't run in one transaction.

The rule I used: a tenant only exists once every step has succeeded. Register the tenant in the control database last, in one transaction. If any earlier step fails, drop the new database and report which step failed. So there is never a "half created" customer in the system.

Problem 2: routing every request to the right database

Every request has to reach exactly one tenant's database. The pattern:

1. Authenticate, and check the tenant first. The token says which tenant the caller belongs to. If the request asks for a different tenant, reject it before any tenant data is touched.
2. Look the tenant up once in the small control database: its status, enabled features and where its data lives.
3. Get that tenant's connection pool and give the handler something that can only query that one database.

// simplified: one lazily created, small pool per tenant database
func (p *Pools) Get(ctx context.Context, dbName string) (*pgxpool.Pool, error) {
	p.mu.Lock()
	defer p.mu.Unlock()
	if pool, ok := p.pools[dbName]; ok {
		return pool, nil
	}
	cfg := p.base.Copy()
	cfg.ConnConfig.Database = dbName  // only the database changes
	cfg.MaxConns, cfg.MinConns = 4, 0 // idle tenants cost nothing
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		return nil, err
	}
	p.pools[dbName] = pool
	return pool, nil
}

Small pools that open lazily, and keep no connections for idle tenants, are what make many quiet customers cheap on one database server.

A nice side effect: with generated, typed queries (I used sqlc), tenant queries and platform queries can live in separate packages, so the compiler stops you from mixing them up.

Problem 3: migrating every database

A schema change is now a change to every tenant's database. What made this safe:

• One migration run at a time, protected by a lock.
• Tenants one by one, each with a timeout. A failure is recorded and the run continues, so one bad tenant doesn't block everyone else.
• Record each tenant's schema version centrally, so "who is behind?" is one query.
• Refuse traffic for a tenant that is behind with a clear "updating, retry soon" answer, instead of running new code against an old schema.

This also changes how you write migrations. A slow migration runs once for every tenant, so each change must be small, fast and backwards-compatible.

Problem 4: anything across tenants

The platform still needs numbers across customers: sizes, activity, health. Opening every tenant database on every admin request would be too slow. Instead, a background job collects the few numbers the platform needs into the control database on a schedule. The admin screens read only from there.

The trade-offs, honestly

Database per tenant isn't free:

• Connections grow with active tenants. Lazy, small pools help. A connection pooler and closing idle pools are the next steps as you grow.
• Migrations run many times. Every schema change costs more and needs more care.
• Cross-tenant analytics need their own pipeline.

For business data with strict separation and a moderate number of tenants, I'd make the same choice again. The general lesson I took from it: when a mistake would be very expensive, make it impossible by design, not just unlikely because everyone is careful.