One database per tenant: isolating customer data in SaaS

Every multi-tenant product has to decide where one customer’s data ends and the next one’s begins. Most start with shared tables and a tenant_id column on every row. That works, until a single missing filter shows one customer another customer’s records. For some products, that risk is too high to manage with discipline alone, and the boundary needs to sit lower down.

In SoulCare, our home-care platform, each care provider has its own PostgreSQL database. Care records, medication charts and pay data belong to one provider and nobody else. This article covers why we would choose that model, how to run it, and when shared tables are the better answer.

Three ways to separate tenants

There are three common patterns in PostgreSQL:

• Shared tables. Every tenant’s rows sit in the same tables, separated by a tenant_id column. Row-level security can enforce the filter in the database.
• A schema per tenant. One database, with a separate schema for each tenant holding identical tables.
• A database per tenant. Each tenant gets its own database, either on a shared server or on its own instance.

They differ in where isolation is enforced. With shared tables, it depends on every query and policy being right. With separate databases, a query cannot reach another tenant’s data, because the connection is to a different database altogether.

Why a database per tenant

Isolation you can explain

A provider’s data protection officer can be told, plainly, that their records sit in a database of their own. That is easier to reason about, audit and explain than a set of row-level policies, however well written.

Contained mistakes

A bad query, a slow report or an accidental bulk update affects one tenant. Restoring a backup for one customer does not mean rolling back everyone else. You can export or delete a tenant’s data by handling one database rather than filtering every table.

Room for different needs

A larger customer can move to its own instance without a data migration project. If a customer needs data held in a particular region, the database can be placed there.

How to run it

The model only works if creating and changing databases is routine. Treat the list of tenants and their databases as data, and build tooling around it from the start.

Provisioning

Creating a tenant should be one controlled operation: create the database, apply every migration, seed reference data and register the tenant in a central catalogue. In SoulCare, setting up a provider, including its database, is a job for the admin console rather than a manual step.

The central catalogue is the one shared database. It holds tenants, their connection details and the migration version each one is on. It should hold no customer data.

The following is a simplified example, not our production code:

-- Central catalogue: one row per tenant database.
CREATE TABLE tenants (
  id            uuid PRIMARY KEY,
  slug          text UNIQUE NOT NULL,
  database_name text UNIQUE NOT NULL,
  host          text NOT NULL,
  schema_version integer NOT NULL DEFAULT 0,
  status        text NOT NULL DEFAULT 'provisioning'
);

-- Run against the server, outside a transaction.
CREATE DATABASE tenant_example;

Migrations across many databases

With shared tables, a migration runs once. With a database per tenant, it runs once per tenant, and some of those runs can fail. Plan for that:

• Make migrations backwards compatible. Application code will meet databases on the old and new version during a rollout. Add columns before using them, and remove old ones in a later release.
• Record the version per tenant. The catalogue should show which tenants are on which version, so a failure is visible and can be retried.
• Run in batches. Migrate a few tenants first, check them, then continue. A long-running migration on one large tenant should not hold up the rest.
• Keep migrations in the repository. The same files create a new tenant and upgrade an existing one, so new and old databases end up identical.

Connection handling

Each tenant database needs its own connections. A process that serves many tenants cannot hold an open pool for all of them, and PostgreSQL has a fixed limit on connections per server.

Resolve the tenant from the authenticated request, look up its database in the catalogue, and open or reuse a connection for that database only. Keep pools small and close idle ones. The tenant identifier must come from a trusted source, such as the signed token, never from a value the client can change.

Serverless functions make this sharper. SoulCare’s Go services run on AWS Lambda, and many concurrent function instances can each open connections. A connection proxy in front of the database, small per-instance limits and short idle timeouts all help. Whatever the approach, test it with realistic concurrency before customers arrive.

Here is a simplified sketch of resolving a tenant connection in Go, again not production code:

// tenantDB returns a connection pool for the tenant named in a verified token.
func (s *Server) tenantDB(ctx context.Context, tenantID string) (*sql.DB, error) {
	s.mu.Lock()
	defer s.mu.Unlock()

	if db, ok := s.pools[tenantID]; ok {
		return db, nil
	}
	t, err := s.catalogue.Lookup(ctx, tenantID)
	if err != nil {
		return nil, err
	}
	db, err := sql.Open("pgx", t.DSN())
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(2)
	db.SetConnMaxIdleTime(time.Minute)
	s.pools[tenantID] = db
	return db, nil
}

The trade-offs

This model costs more to run, and the cost is mostly operational rather than hosting.

• More moving parts. Backups, monitoring and migrations all multiply by the number of tenants. Each needs automation and an alert when one tenant falls behind.
• Cross-tenant reporting is harder. Questions like “how many visits happened across every provider last week” need a separate reporting pipeline, because there is no single table to query.
• Server limits. Many databases on one server share its memory and connection limit. You will need a plan for moving tenants between servers as they grow.
• Slower onboarding at very high volume. If customers sign up by the thousand through self-service, creating a database for each one becomes a burden.

When shared tables are the better choice

A database per tenant suits a product with a modest number of business customers, sensitive data and customers who ask where their data lives. It is often the wrong choice for:

• products with many small or free accounts, where most tenants hold little data;
• products where users move between organisations or share data across them;
• early prototypes, where the schema changes daily and the customer list is short.

Shared tables with row-level security in PostgreSQL are a sound option for these. Enforce the tenant filter in the database rather than only in application code, and test that one tenant cannot read another’s rows. If isolation needs grow later, a move to separate databases is easier when every table already carries a tenant identifier.

When to bring in help

If you are choosing a tenancy model before launch, or your shared-table product now has customers asking for stronger separation, it helps to review the decision against your data, your expected customer numbers and your team’s capacity to operate it. We are happy to talk through the options and what each would mean for your migrations, hosting and support, whichever way you go.