Three Ways to Store Multi-Tenant Data, and When Each One Breaks

Every multi-tenant design is a bet about which problem you would rather have in three years. There is no configuration that is simply better; there are three, and each one trades something you care about for something else you care about.

Choosing badly is expensive because this decision is load-bearing — it reaches into your queries, your migrations, your backups and your onboarding. Changing it later is a migration project, not a refactor.

The three options

shared table            schema per tenant         database per tenant

one database            one database              many databases
one schema              many schemas              one schema each
tenant_id column        identical tables          fully separate

  tenants: 10,000+        tenants: 10-500           tenants: 1-50
  isolation: weakest      isolation: good           isolation: total
  migration: one          migration: N schemas      migration: N databases
  noisy neighbour: yes    noisy neighbour: yes      noisy neighbour: no

Shared table with a tenant_id

Every table carries a tenant_id, and every query filters on it.

class TenantQuerySet(models.QuerySet):
    def for_tenant(self, tenant):
        return self.filter(tenant=tenant)

class Invoice(models.Model):
    tenant = models.ForeignKey(Tenant, on_delete=models.CASCADE)
    number = models.CharField(max_length=32)

    class Meta:
        # tenant_id leads every composite index. A query filtered
        # by tenant can then use the index; the other order cannot.
        indexes = [models.Index(fields=["tenant", "-created_at"])]
        # uniqueness is per tenant, never global — two customers
        # will both have an invoice numbered 1
        constraints = [
            models.UniqueConstraint(fields=["tenant", "number"],
                                    name="uniq_invoice_number_per_tenant"),
        ]

This scales furthest and costs least to operate. One database to back up, one migration to run, one connection pool.

Its weakness is that isolation is enforced entirely by your own code. One Model.objects.filter(...) that forgets .for_tenant() is a cross-tenant data leak, and it will not fail a test — it returns more data, silently.

Two defences are worth the effort. First, make the unfiltered manager hard to reach by accident. Second, push the rule into the database with row-level security, so a missing filter returns nothing rather than everything:

-- Postgres enforces it even if the application forgets
ALTER TABLE invoice ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoice
  USING (tenant_id = current_setting('app.tenant_id')::int);

Note that this requires a session variable set per request, which interacts badly with transaction-mode connection pooling — set it inside the transaction, not on connect.

Schema per tenant

One Postgres database, one schema per tenant, identical tables in each. Queries are unqualified; the search_path decides which tenant's data they hit.

SET search_path TO tenant_42, public;
SELECT * FROM invoice;   -- reads tenant_42.invoice

The appeal is real: no tenant_id in queries, so a forgotten filter cannot leak data across tenants. Per-tenant backup and restore is straightforward. It maps cleanly onto "each customer is a separate organisation", which is what enterprise buyers ask about in security reviews.

The costs show up in operations. Migrations run once per schema — at 300 tenants a five-second migration is a twenty-five minute deploy, and a failure halfway leaves you with schemas in two different states. Postgres also keeps catalog entries per table per schema; hundreds of schemas times fifty tables is a large catalog, and things like pg_dump and autovacuum get slower.

The search_path mechanism is also genuinely dangerous with PgBouncer in transaction mode. A SET outside a transaction applies to a pooled connection that is about to be handed to a different tenant's request. If you take this route with pooling, set the path inside every transaction or do not pool.

Database per tenant

Total isolation. Separate credentials, separate backups, separate everything. Restoring one customer to yesterday does not touch anyone else. Per-tenant data residency becomes possible.

It is also the most operationally expensive option by a wide margin — N migrations, N connection pools, N monitoring targets — and cross-tenant analytics stops being a query and becomes a pipeline.

This is the right answer for a small number of large, regulated customers. Hospitals, banks, government departments. It is the wrong answer for a self-service product where anyone can sign up, because onboarding now means provisioning a database.

How to actually choose

Answer these in order and the decision usually makes itself:

1. How many tenants in three years? Thousands means shared table; the other two do not survive it operationally.
2. Can anyone self-serve sign-up? If yes, onboarding must be a row insert, not a provisioning job.
3. Will a customer's security review ask where their data physically lives? If yes, budget for schema or database separation.
4. Do you need cross-tenant analytics? Trivial on shared table, a pipeline otherwise.
5. What happens when one customer wants a restore to last Tuesday? This is the question people forget, and it is the one that hurts most on shared table — restoring one tenant means extracting their rows from a full backup.

For most products the honest answer is shared table with rigorous scoping, plus row-level security as a safety net. It is the least glamorous option and the one that keeps working at scale.

Things to get right whichever you pick

• Resolve the tenant once, at the edge. Middleware reads the subdomain or token, puts the tenant in the request, and nothing downstream re-derives it.
• Never trust a tenant id from the client. It comes from the authenticated session, never from a parameter the browser can change.
• Scope background jobs too. Tasks run outside the request cycle, so the middleware that scoped everything is not there. Pass the tenant explicitly.
• Include the tenant in cache keys. A cache key of dashboard:{user_id} is fine until user ids are only unique per tenant.
• Plan the exit. "Export everything for one customer" will be asked for, by a customer leaving or a regulator. Design it early; retrofitting it into a shared table is unpleasant.