The migration window that forced the decision

A logistics client arrived last year with 340 tenants, a database-per-tenant layout inherited from a 2021 prototype, and a schema migration that took four hours and forty minutes to run because every ALTER TABLE had to execute 340 times against 340 logical databases. Two of those runs failed halfway. The Postgres instance was a db.r6g.2xlarge in Cape Town, and the connection pooler fleet in front of it existed only because PgBouncer allocates pools per logical database, so 340 tenants times a modest pool size had already blown past max_connections twice.

The architecture was not wrong on paper. Each tenant had genuine physical isolation. It was wrong for the number of tenants they actually had, and for the region they were running in.

That is the real shape of this decision. All three multi-tenancy models work. You are not choosing the correct one, you are choosing which failure mode you are willing to operate for the next five years, at the prices your region actually charges.

What each model costs you inside Postgres

Start with database-per-tenant, because it is the one that sounds safest and bites hardest. Every CREATE DATABASE clones the template database, which is roughly 8 MB of catalog before a single row of customer data exists. Multiply by a thousand tenants and you are carrying gigabytes of pure overhead, each logical database maintaining its own system catalogs, its own autovacuum bookkeeping, its own entry in every backup plan. Cross-tenant queries are simply not possible in SQL. You need a warehouse or an application-layer join, which means you now run a second data system to answer the question "how many active users do we have."

Connections are the harder ceiling. Postgres connection management is per-database by design, and transaction-mode pooling does not rescue you because the pooler cannot share a server connection across different databases. In our experience, this model starts hurting somewhere between 100 and 300 tenants and becomes an operations job of its own past 500.

Schema-per-tenant looks like the sensible middle. One database, one connection pool, isolation by namespace, and a very clean offboarding story: DROP SCHEMA tenant_acme CASCADE and the customer is gone, which is a genuinely useful property when a data subject exercises deletion rights under a national data protection act.

The cost shows up in the catalog. Every table, index, constraint, sequence and column across every schema lives in the same shared system catalogs. Forty tables per tenant across 2,000 tenants is 80,000 relations, and pg_attribute grows into the millions of rows. The planner consults those catalogs on every query. DDL slows down. pg_dump slows down. Connection startup slows down, because each new backend populates its relcache from a larger catalog. None of this is a cliff, which is what makes it dangerous: it degrades smoothly until someone notices that deploys take forty minutes and nobody can say exactly when that started.

Database-per-tenant multiplies pools, not just storage. This is the constraint that usually ends the model, not disk cost.

Shared schema with a tenant_id column on every table is the only one of the three that is genuinely multi-tenant in the database sense, and it is the only one with no structural ceiling. One set of tables, one migration, one vacuum strategy, cross-tenant analytics in plain SQL. The tradeoff is that isolation is now a property of your code rather than your schema, and code has bugs.

Why we no longer treat RLS as the safety net

The standard advice is to add Postgres row-level security so that a forgotten WHERE clause cannot leak data. We do use RLS, but we have stopped describing it as the thing that keeps tenants apart, because teams then write their data layer as though the database will save them.

Here is the setup we actually ship:

-- Application role must NOT be superuser or table owner,
-- or policies are silently bypassed.
CREATE ROLE app_user LOGIN PASSWORD 'rotate-me';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

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

-- tenant_id must lead every index that matters
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at DESC);

And at request time, inside a transaction, never on the raw connection:

BEGIN;
SELECT set_config('app.tenant_id', $1, true);  -- true = transaction scoped
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;
COMMIT;

Three failures we have seen with this pattern in production. First, SET instead of SET LOCAL or set_config(..., true): with PgBouncer in transaction mode the setting survives on the server connection and the next request, belonging to a different tenant, inherits it. That is a cross-tenant read, and it will not appear in any test suite that uses a direct connection. Second, the policy evaluates but the planner picks a sequential scan because the index did not lead with tenant_id, so a query that was 0.5 ms at 100,000 rows becomes a seconds-long scan at 50 million. Third, the migration user owns the tables and therefore bypasses the policy, so a backfill script cheerfully updates every tenant at once.

RLS catches the forgotten filter. It does not catch the owner role, the pooled session variable, or the missing composite index.

Enforce the tenant filter in a single data access layer that no query can route around, then turn on RLS behind it as the second lock. Two independent mechanisms, neither trusted alone.

Residency is a placement problem, not an isolation model

This is where most tutorials written elsewhere stop being useful to us. More than 36 African countries now have data protection legislation on the books, and the direction of travel is towards localisation rather than away from it. Nigeria's data protection act has been operational since 2023 with its general implementation directive in force from September 2025, and the national IT agency has pushed for certain categories of data held by international cloud providers to sit inside the country. Ghana has been consulting on a replacement for the 2012 act with explicit cross-border transfer and localisation provisions. Kenya's regulator has been actively enforcing since 2019.

Teams read this and conclude they need database-per-tenant. They do not. A Ghanaian bank and a Ghanaian insurer can share a cluster quite legally. What neither can do is have their rows sitting in Frankfurt when the regulator asks where the data lives.

Residency partitions by jurisdiction, not by customer. One shared-schema cluster per country or per regulatory bloc, with a routing table that maps tenant to cluster, satisfies the law while keeping the tenant count per cluster high enough that shared schema stays efficient. We run that routing lookup once at authentication and cache it in the session token.

The pricing makes the argument for you. Compute in the Cape Town region runs roughly 15% above the average AWS region, and egress out of Cape Town is about $0.15 per GB against roughly $0.09 from the large US regions. Every cluster you create in an African region is a fixed cost paid at a premium. Fragmenting 400 tenants into 400 databases in af-south-1 is not an isolation strategy, it is a budget deletion strategy. Consolidating those same tenants into two clusters, one in Cape Town and one wherever the rest of the customers legally may sit, cut the client's database spend by just over half and brought the migration window down from 280 minutes to under four.

The shape we keep landing on

Default to shared schema with a BIGINT tenant_id leading every index, enforced in one data access layer and backed by RLS. Partition the genuinely large tables by tenant list once a single table passes a few hundred million rows, which also gives you fast offboarding via DETACH PARTITION instead of a DELETE that leaves millions of dead tuples for autovacuum. Shard by jurisdiction, not by customer. Keep a per-tenant isolation_level column in the control-plane tenants table from day one, so when a bank's procurement team demands a dedicated instance, routing a single tenant out is a configuration change rather than a rewrite.

The tiered model: shared schema carries the long tail, and the handful of tenants whose contracts demand separation get routed out individually.

The uncomfortable part is that the premium-isolation tier is a sales artefact more often than a security one. It rarely makes the data safer than a correctly built shared schema. It makes the deal close. Price it accordingly: a dedicated cluster in an African region should carry a line item that reflects what it actually costs you to run, because unlike storage, that cost never amortises across your other customers.

Build the routing table before you need it. Tenant number three is a five-minute change. Tenant number three hundred is a quarter.