Database per Tenant

The strongest data boundary

In database-per-tenant, each tenant's data lives in its own database, either on a shared database server or on dedicated servers. Isolation comes from the database itself: a query on tenant A's connection simply cannot see tenant B's tables, so a missing WHERE clause does not leak data. Backups, restores and deletion are per tenant, which makes "restore only Acme to yesterday" or "delete everything for Zen" straightforward. Big tenants can be moved to bigger hardware independently. The costs grow with tenant count: every schema migration must run against every database, monitoring and backups multiply, and connection pools are per database, so a server hosting thousands of databases can run out of connections or memory. This pattern fits tens to low thousands of fairly large tenants, or tenants whose contracts require dedicated storage, better than it fits huge numbers of tiny free tenants.

Three data isolation patterns

From left to right, isolation inside the database gets weaker and density gets higher: separate databases, separate schemas in one database, and shared tables with a tenant_id column.

Routing to a tenant's database

The catalogue maps a tenant to its database; a small cache keeps one connection (or pool) per tenant. SQLite in-memory databases stand in for real servers.

import sqlite3

# One connection per tenant: the database-per-tenant (silo) model
CATALOG = {"acme": ":memory:", "zen": ":memory:"}
_pool = {}

def conn_for(tenant_id: str) -> sqlite3.Connection:
    if tenant_id not in CATALOG:
        raise LookupError(tenant_id)
    if tenant_id not in _pool:
        c = sqlite3.connect(CATALOG[tenant_id])
        c.execute("CREATE TABLE invoices (id INTEGER PRIMARY KEY, amount INTEGER)")
        _pool[tenant_id] = c
    return _pool[tenant_id]

conn_for("acme").execute("INSERT INTO invoices (amount) VALUES (500), (700)")
conn_for("zen").execute("INSERT INTO invoices (amount) VALUES (90)")

for t in CATALOG:
    total = conn_for(t).execute("SELECT SUM(amount) FROM invoices").fetchone()[0]
    print(t, total)

Output:

acme 1200
zen 90

Watch the connection maths

If each app instance keeps a pool of 10 connections per tenant database, 20 instances and 500 tenants means up to 100,000 connections. Use small or lazily created pools, close idle ones, or put a connection pooler such as PgBouncer in front.

Quick check: What is the main reason database-per-tenant becomes hard with very many small tenants?

• Databases cannot store more than one table
• Migrations, backups, monitoring and connections all multiply with the number of databases
• Cross-tenant data leaks become more likely
• SQL queries must include tenant_id in every WHERE clause