Multi-Tenancy and Isolation Questions

Direct answer

I would never let anyone, analysts or tenants, query raw multi-tenant data for benchmarks. Instead a privileged pipeline builds a separate aggregate layer that only ever contains cohort statistics, and every query against it goes through a gatekeeper that enforces three things: a minimum number of tenants per result, a cap on how much any one tenant can contribute, and, for ad-hoc queries, calibrated random noise with a per-analyst privacy budget (differential privacy: a mathematical guarantee that the noisy answer would look almost the same whether or not any single tenant's data was included, so no result can be traced back to one tenant). Tenants get curated, precomputed benchmarks; analysts get ad-hoc access through the noisy interface. The guarantee holds over time because it is enforced by the query interface and an accounting ledger, not by reviewing each query by eye.

Why "only show aggregates" is not enough

Three failure modes erode a naive design:

1. Differencing: two queries that each look safe (each covers 10 or more tenants) can be subtracted to isolate one tenant.
2. Order statistics are real values (order statistics: values defined by sorting the data, such as the median, maximum or minimum, rather than by combining many values): the median of an odd-sized cohort is exactly one tenant's number, and a maximum or minimum is always someone's value.
3. Drift: cohorts shrink as tenants churn, new filter dimensions get added, and the queries accumulate. A threshold that was safe at launch stops being safe without anyone changing it.

The third part of this demo adds differential privacy: instead of returning the exact sum, it returns the sum plus random noise drawn from a Laplace distribution (a distribution sharply peaked at zero, so most of the noise is small but occasionally larger; here it is generated as the difference of two exponential draws). Two knobs control it: sensitivity is the most any single tenant's data could change the result, bounded here by clamping each tenant's contribution at CLAMP; epsilon is the privacy-loss parameter, where a smaller epsilon means more noise and stronger protection. The noise scale is sensitivity divided by epsilon, so halving epsilon doubles the noise.

A small runnable demonstration with 11 retail tenants and a "minimum 10 tenants" rule:

import random
import statistics

# 11 retail tenants: (tenant, region, monthly_api_calls)
TENANTS = [
    ("t01", "US", 120_000), ("t02", "US", 95_000), ("t03", "US", 210_000),
    ("t04", "US", 88_000),  ("t05", "US", 150_000), ("t06", "APAC", 99_000),
    ("t07", "APAC", 130_000), ("t08", "US", 175_000), ("t09", "APAC", 60_000),
    ("t10", "US", 140_000), ("t11", "EU", 480_000),   # the only EU retail tenant
]
K_MIN = 10  # refuse any cohort smaller than 10 tenants

def cohort(pred):
    return [calls for (_, region, calls) in TENANTS if pred(region)]

def exact_sum(pred):
    rows = cohort(pred)
    if len(rows) < K_MIN:
        return None  # "cohort too small"
    return sum(rows)

# 1. Threshold-only defence: two individually "safe" queries
all_retail = exact_sum(lambda r: True)             # 11 tenants
non_eu     = exact_sum(lambda r: r != "EU")        # 10 tenants
print("threshold-only: sum(all)=", all_retail, " sum(non-EU)=", non_eu,
      " difference=", all_retail - non_eu)

# 2. The median of an odd-sized cohort IS one tenant's real value
med = statistics.median(cohort(lambda r: True))
print("median of 11 =", med, "which is exactly tenant", [t for t, _, c in TENANTS if c == med])

# 3. Differential privacy: clamp each tenant's contribution, add Laplace noise
CLAMP = 250_000             # contribution bound = sensitivity of a sum: the most one tenant's data can move the total, after clamping
rng = random.Random(7)

def laplace(scale):
    return rng.expovariate(1 / scale) - rng.expovariate(1 / scale)

def dp_sum(pred, epsilon):
    rows = cohort(pred)
    if len(rows) < K_MIN:
        return None
    return sum(min(c, CLAMP) for c in rows) + laplace(CLAMP / epsilon)

eps = 0.5
a, b = dp_sum(lambda r: True, eps), dp_sum(lambda r: r != "EU", eps)
print(f"DP (epsilon={eps} per query): difference = {a - b:,.0f}  "
      f"(true clamped value {min(480_000, CLAMP):,}, noise scale {CLAMP/eps:,.0f} per query)")

Output:

threshold-only: sum(all)= 1747000  sum(non-EU)= 1267000  difference= 480000
median of 11 = 130000 which is exactly tenant ['t07']
DP (epsilon=0.5 per query): difference = -124,753  (true clamped value 250,000, noise scale 500,000 per query)

Both sums passed the 10-tenant rule, yet their difference is exactly the EU tenant's 480,000 calls. The median returned tenant t07's real value. With noise added, the difference no longer reveals the tenant, but note the cost: at a cohort of 11 the noise (scale 500,000 per query) swamps a true total of about 1.7 million, so noisy answers are only useful for larger cohorts. That is a design input, not a flaw: small cohorts get suppressed or combined into broader ones.

The design

flowchart LR
    R[(Raw tenant data)] --> P[Aggregation pipeline]
    P --> A[(Aggregate layer)]
    A --> B[Precomputed benchmarks]
    A --> G[Query gatekeeper]
    B --> T[Tenant UI]
    G --> AN[Analysts ad-hoc]
    G --> L[(Budget and audit ledger)]

1. The aggregate layer

• Built by a pipeline running under a service identity; no human role can read raw data from other tenants through it.
• Contains per-tenant metrics already clamped (each tenant's contribution capped, for example usage above the 95th percentile truncated), which bounds how much any single tenant can move a result.
• Carries cohort attributes (industry, size band, region) at a coarse grain only; free-text or fine-grained attributes are excluded because they make tiny cohorts easy.

2. Tenant-facing benchmarks (curated)

• A fixed catalog: "your usage percentile within your industry and size band", "median and interquartile range of the cohort" (interquartile range: the middle 50% of values, from the 25th to the 75th percentile, a measure of spread).
• Minimum cohort size (for example 20 tenants) and a dominance rule (no single tenant contributes more than, say, 30% of a sum).
• Percentiles published as noisy or interpolated values, never as a raw order statistic; a tenant sees "you are around the 70th percentile", not another tenant's exact number.
• Refreshed on a schedule, so there is a small, known number of releases per period instead of an open query surface.

3. Ad-hoc access (analysts and, optionally, tenants)

• Queries go through a gatekeeper that only allows aggregate functions (counts, sums, means, noisy quantiles) and group-bys on approved dimensions.
• Every answer gets noise calibrated to the clamp and to an epsilon (the privacy-loss parameter: smaller epsilon means more noise and stronger protection), and each query's epsilon is charged to a per-analyst, per-period budget. When the budget is spent, further queries wait for the next period. Budgeting is exactly what defeats differencing: each noisy answer leaks a small amount of information, an attacker trying to cancel that noise out would need to average many independent answers to the same or overlapping query, and the budget caps how many such queries an analyst can run, so the repeated slicing that would be needed to converge on one tenant's exact value drains the budget first.
• A ledger records every query, its epsilon and the analyst, for audit.
• Tenants who want ad-hoc benchmarking get the same gatekeeper with a smaller budget.

4. Warehouse data sharing (the managed-platform variant)

On a warehouse that separates storage from compute, such as Snowflake, the same model maps onto platform features: the aggregate layer lives in its own database, and it is exposed to consumers through secure views (views whose definition is hidden and which the optimizer will not rewrite in ways that expose underlying rows) and Secure Data Sharing, which grants another account read access to specific objects without copying the data. What sharing does not give you on its own is a minimum-cohort rule, a noise mechanism or a budget; those still live in the gatekeeper or in the views' logic.

Keeping the guarantee from eroding

• Automated checks on every release: recompute cohort sizes on each refresh and suppress any published figure whose cohort has fallen below the threshold since last time.
• Dimension review: adding a new group-by dimension requires re-running a small-cohort analysis, because each new dimension multiplies the number of cells.
• Budget reset policy: budgets per period are fixed in advance and cannot be raised by the analyst who spent them.
• Red-team the interface: periodically try differencing and repeated-query attacks against it, as with any other security boundary.

Trade-offs and pitfalls

• Accuracy versus protection: noise makes small cohorts useless. Commit to broader cohorts for published benchmarks and accept that "retail, EU, 10 to 50 employees" may simply not be available.
• Differential privacy is harder to explain to analysts than a threshold. Invest in showing confidence intervals with each noisy answer so people trust the numbers.
• Pitfall: thresholds on row counts instead of tenant counts. 10,000 rows from one tenant is still one tenant.
• Pitfall: exporting the aggregate layer's inputs (the clamped per-tenant table) to a notebook environment, where the gatekeeper no longer applies.

Direct answer

Row-level security (RLS: a policy the database attaches to a table so every query silently gets an extra filter, for example team_id = <caller's team>) is applied before aggregation. So SELECT team_id, sum(amount) ... GROUP BY team_id against a table with RLS returns only the caller's team's total, and that part is safe. The leaks come from everything around it: views and pre-aggregated tables built by an owner the policy does not apply to, a table owner that is exempt by default, and connection pools that carry one request's tenant setting into the next. The efficient, safe pattern is RLS on the base table, rollup tables that carry team_id and have their own RLS policy, views created with security_invoker, and the tenant set per transaction.

How RLS interacts with an aggregate

The database rewrites the query as if the filter were in the WHERE clause, then aggregates. That means:

• Aggregates are correct per caller: the total covers the rows the caller may see, never more.
• A "grand total across all teams" query silently returns only your team's total. Correct from a security view, surprising from a reporting view.
• The filter must be cheap, because it runs on every query. Index on team_id (or a composite starting with it, such as (team_id, sale_date)), and keep the policy expression simple so the planner can use that index.

A runnable demonstration (PostgreSQL 16)

The script creates a sales table owned by app_owner, enables RLS with a policy on a per-session setting, and then reads team revenue as app_user (the role the application connects as) through four paths: the base table, a plain view, a security_invoker view, and a materialized view (a stored, precomputed query result). It then checks the table owner, and finally a pooled connection where one request forgot to set its team.

The demo uses two roles on purpose: RLS is skipped for a table's own owner (and for superusers) by default, so if the application connected as app_owner itself, every RLS policy on tables it owns would silently be bypassed. app_user is a separate, low-privilege role created specifically so RLS applies to it.

Two ways to set the per-request team value matter here: SET LOCAL scopes a setting to the current transaction only, so it is automatically cleared at commit or rollback; plain SET scopes it to the whole database session, so on a pooled connection (one physical database connection reused by many requests in turn, to avoid the cost of opening a new one each time) a value set by one request can leak into the next request that reuses that connection, if that next request forgets to set its own value. set_config('app.team_id', '1', true) is the function form of SET LOCAL (the trailing true argument means "local to the transaction").

-- as superuser: set up an owner role and an app role
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_user NOLOGIN;
GRANT CREATE, USAGE ON SCHEMA public TO app_owner;
CREATE TABLE sales (
  id        bigserial PRIMARY KEY,
  team_id   int NOT NULL,
  amount    numeric(12,2) NOT NULL
);
ALTER TABLE sales OWNER TO app_owner;
CREATE INDEX sales_team_idx ON sales (team_id);
INSERT INTO sales (team_id, amount) VALUES
  (1, 100.00), (1, 250.00), (2, 900.00), (2, 50.00), (3, 7000.00);
ALTER TABLE sales ENABLE ROW LEVEL SECURITY;
CREATE POLICY team_isolation ON sales
  USING (team_id = current_setting('app.team_id')::int);
GRANT SELECT ON sales TO app_user;

-- a convenience view and a pre-aggregated rollup, both created by the owner
SET ROLE app_owner;
CREATE VIEW team_revenue_v AS
  SELECT team_id, sum(amount) AS total_revenue FROM sales GROUP BY team_id;
CREATE VIEW team_revenue_inv WITH (security_invoker = true) AS
  SELECT team_id, sum(amount) AS total_revenue FROM sales GROUP BY team_id;
CREATE MATERIALIZED VIEW team_revenue_mv AS
  SELECT team_id, sum(amount) AS total_revenue FROM sales GROUP BY team_id;
GRANT SELECT ON team_revenue_v, team_revenue_inv, team_revenue_mv TO app_user;
RESET ROLE;

-- 1. the app user, scoped to team 1
BEGIN;
SET LOCAL ROLE app_user;
SET LOCAL app.team_id = '1';
SELECT 'base table' AS path, team_id, sum(amount) AS total_revenue FROM sales GROUP BY team_id;
SELECT 'owner view' AS path, * FROM team_revenue_v ORDER BY team_id;
SELECT 'invoker view' AS path, * FROM team_revenue_inv ORDER BY team_id;
SELECT 'materialized view' AS path, * FROM team_revenue_mv ORDER BY team_id;
COMMIT;

-- 2. the table owner itself is exempt unless FORCE is set
BEGIN;
SET LOCAL ROLE app_owner;
SET LOCAL app.team_id = '1';
SELECT 'owner, no FORCE' AS path, count(*) AS visible_rows FROM sales;
COMMIT;
ALTER TABLE sales FORCE ROW LEVEL SECURITY;
BEGIN;
SET LOCAL ROLE app_owner;
SET LOCAL app.team_id = '1';
SELECT 'owner, FORCE' AS path, count(*) AS visible_rows FROM sales;
COMMIT;

-- 3. pooled connection: request A sets the team at SESSION scope, request B forgets to set it
SET ROLE app_user;
SET app.team_id = '2';                         -- request A (team 2), session scope
SELECT 'request B, forgot to set' AS path, team_id, sum(amount) AS total_revenue
  FROM sales GROUP BY team_id;                     -- request B (team 1) reuses the connection
RESET ROLE;

Run it:

docker run -d --name pg-demo -e POSTGRES_PASSWORD=pw postgres:16-alpine
sleep 6
docker exec -i pg-demo psql -U postgres -v ON_ERROR_STOP=1 -q < rls.sql
docker rm -f pg-demo

Output:

    path    | team_id | total_revenue 
------------+---------+---------------
 base table |       1 |        350.00
(1 row)

    path    | team_id | total_revenue 
------------+---------+---------------
 owner view |       1 |        350.00
 owner view |       2 |        950.00
 owner view |       3 |       7000.00
(3 rows)

     path     | team_id | total_revenue 
--------------+---------+---------------
 invoker view |       1 |        350.00
(1 row)

       path        | team_id | total_revenue 
-------------------+---------+---------------
 materialized view |       1 |        350.00
 materialized view |       2 |        950.00
 materialized view |       3 |       7000.00
(3 rows)

      path       | visible_rows 
-----------------+--------------
 owner, no FORCE |            5
(1 row)

     path     | visible_rows 
--------------+--------------
 owner, FORCE |            2
(1 row)

           path           | team_id | total_revenue 
--------------------------+---------+---------------
 request B, forgot to set |       2 |        950.00
(1 row)

What it shows, line by line:

1. Base table: team 1 sees only its own total, 350.00. RLS plus aggregation works.
2. Plain view: leaks all teams. A view runs its query with the privileges of the view's owner by default, and the owner here is the table owner, to whom RLS does not apply.
3. security_invoker = true view (PostgreSQL 15 and later): the view runs as the caller, so RLS applies again.
4. Materialized view: leaks all teams. It stores the owner's result at refresh time, and PostgreSQL cannot attach RLS policies to a materialized view.
5. Table owner without FORCE ROW LEVEL SECURITY sees all 5 rows; with it, 2. Applications that connect as the owner are unprotected until you force it. Superusers (the database's highest-privilege role type) and roles with the BYPASSRLS attribute (a role privilege that exempts it from every RLS policy) skip RLS regardless of FORCE.
6. Pooled connection: a session-level SET from request A (team 2) survived, and request B, which forgot to set its team, got team 2's data. With SET LOCAL (transaction-scoped) the setting disappears at commit, and a request that forgets to set it gets an error ("unrecognized configuration parameter"), which fails closed (the safe failure mode: when something is misconfigured, access is denied by default rather than silently granted).

Patterns for efficient, leak-free aggregates

Setting the tenant: at the start of each transaction run SET LOCAL app.team_id = ... (or set_config(..., true)), taking the value from the authenticated identity, never from a request parameter.

Trade-offs and pitfalls

• Performance: RLS predicates are evaluated as security barriers (a mode where the database guarantees the RLS filter runs before any function you supply gets a chance to run, so a malicious function in a query cannot read rows it should not see, even by timing or errors), so the query planner (the part of the database that decides how to execute a query, including which index to use) will not push user-supplied functions that are not marked LEAKPROOF (a flag telling the planner a function has no side effects and cannot leak information, so it is safe to run before the security filter) below them; this occasionally blocks an index plan. Check EXPLAIN (the command that shows the plan the database chose for a query) on the hot dashboard queries. If the policy calls a function, write it so it is evaluated once per query rather than once per row (recomputed separately for every row scanned, which is far more expensive), for example by wrapping it in a scalar subquery (a subquery that returns a single value, computed once and reused, instead of being re-evaluated for every row).
• Cross-team totals for admins need a separate role with an explicit policy (or a separate reporting store), not BYPASSRLS on the application role.
• Small groups leak through aggregates too: if a team member can filter to a group of one person, a "sum" is that person's salary. RLS does not address that; minimum group sizes do.
• Pitfall: assuming RLS covers everything downstream. Exports, caches and replicas to an analytics warehouse do not inherit PostgreSQL's policies; each needs its own filter.

Direct answer

Treat a schema change across thousands of tenant databases as a rollout, not a script: every change is expand then contract (add the new shape first, remove the old shape only after everything uses the new one), the application supports both schema versions at once, each tenant's current schema version is recorded in a central registry, and a migration orchestrator moves tenants through the phases in waves, gated by health checks. Per-tenant feature flags switch reads and writes to the new shape tenant by tenant, shadow writes prove the new shape before it is trusted, and backfills are batched, throttled, idempotent jobs. Rollback before the contract step is a flag flip; after it, it is a restore, so the contract step waits until the new shape has baked (run in production, under real traffic, long enough to be trusted).

The core pattern: expand, migrate, contract

Example change: split customers.address (free text) into a new addresses table.

1. Expand (schema only, backward compatible): create addresses. Old code ignores it. Safe to run everywhere, any time.
2. Dual write / shadow write: code writes the old column and the new table. Reads still come from the old column. A shadow write means the new path's writes are done and compared but not yet relied on: a checker compares old vs new for a sample of rows and reports mismatches.
3. Backfill: copy historical rows into the new table.
4. Switch reads: a per-tenant flag moves reads to the new table.
5. Stop old writes, bake for a defined period.
6. Contract: drop the old column in a later release.

stateDiagram-v2
  [*] --> Expanded
  Expanded --> DualWrite: flag on
  DualWrite --> Backfilled: backfill job done + verified
  Backfilled --> ReadNew: read flag on
  ReadNew --> Backfilled: rollback (flag off)
  ReadNew --> NewOnly: stop old writes
  NewOnly --> Contracted: bake period passed
  Contracted --> [*]

Each tenant has its own position in this state machine, stored in the schema registry.

Online migrations

"Online" means the table keeps serving reads and writes while it changes.

• PostgreSQL: adding a nullable column or a column with a constant default is a catalog-only change on PostgreSQL 11 and later (the database only updates its internal catalog entry for the table, instead of rewriting every existing row, so it finishes instantly and does not hold a long lock); build indexes with CREATE INDEX CONCURRENTLY (builds without holding the lock that blocks normal reads and writes); add constraints as NOT VALID (the constraint applies to new rows immediately, at the cost of only a brief lock, because PostgreSQL does not yet check the rows already in the table) and validate them separately (a following statement scans the existing rows for violations without blocking writes for the whole scan); always set a short lock_timeout so a DDL statement that cannot get its lock fails instead of queueing all traffic behind it.
• MySQL: online tools such as gh-ost or pt-online-schema-change build a shadow copy of the table (an empty copy with the new schema), sync changes into it while the original keeps serving traffic, and swap it in (an atomic rename that makes the shadow copy the live table in a fraction of a second, instead of rewriting the original table in place).
• Never combine a table rewrite with a traffic peak; the orchestrator schedules heavy steps per tenant in low-traffic windows.

Feature flags, per tenant

A feature flag is a runtime switch evaluated in code. Here it is keyed by tenant and by migration phase (addresses.dual_write, addresses.read_new). Flags let you:

• move one tenant, then 1%, then 10%, then everyone, independently of deploys;
• roll back a single tenant instantly;
• keep the application able to run against a tenant at any phase, which is what makes mixed-version fleets safe.

Remove the flags after the contract step; leftover migration flags are dead branches nobody tests.

Backfill strategies

• Batched by primary key (keyset pagination: "next 5,000 rows after id X"), never one giant UPDATE, which holds locks and bloats the log.
• Idempotent: re-running a batch produces the same result (use upserts: a write that inserts the row if it is new or updates it in place if it already exists), so a crashed job resumes from its last checkpoint.
• Throttled on health: pause when replica lag (how far behind a read replica's copy of the data is from the primary), CPU or p99 latency (the 99th-percentile latency) crosses a limit.
• Ordered after dual writes start, so rows changed during the backfill are already being written in both shapes.

Arithmetic for the plan. Suppose the pooled tier (the shared-schema group of tenants, as opposed to tenants with their own dedicated schema or database) holds 2 billion rows across 20 database shards (independent database instances that together hold the whole pooled tenant set, each with its own slice of the rows), and each shard can absorb 2,000 backfilled rows a second without replica lag rising:

Shards run in parallel, so the pooled backfill takes about 14 hours; the per-tenant databases, each small, finish in minutes each but number in the hundreds, so the limit there is orchestrator concurrency.

Coordinating tenants on different isolation levels

Rules that make this work:

• A schema registry records every tenant's schema version and phase. The application reads it to know which shape to use, and the orchestrator reads it to decide what to do next.
• Application compatibility window: each release supports schema N and N+1. The contract step for N is only allowed once no tenant is below N+1.
• Waves with canaries (a small trial group that goes first, so a problem is caught on a few tenants before it reaches everyone): internal tenants, then a small share, then the rest, then dedicated tenants. A failure rate above a threshold halts the wave automatically.
• Per-tenant failure isolation: tenant 1,712 failing does not stop the fleet; it is recorded, retried, and escalated, while others continue.

Rollback considerations

• Before contract: rollback is flipping the read flag back; the old column is still written, so nothing is lost.
• Dual-write drift: if the shadow comparison finds mismatches, stop and fix before switching reads; switching reads on unverified data is the most common cause of silent corruption in these migrations.
• After contract: the old shape is gone. Rollback means restoring from backup or a forward fix. That is why the contract step is separate, delayed and manual for high-value tenants.
• Schema rollback scripts for every expand step (DROP TABLE addresses) are cheap and should exist, but data rollback is the flag, not the script.

Trade-offs and pitfalls

• Expand/contract triples the number of releases per change; the reward is that nothing requires downtime or a lockstep deploy.
• Running migrations inside the application's startup path means thousands of instances racing to migrate; run them from the orchestrator.
• Letting dedicated tenants lag without a limit makes the compatibility window unbounded.
• What would change the approach: a purely pooled fleet can migrate with far less machinery (one migration per shard); the registry and waves pay off once per-tenant databases exist.

Direct answer

I would track four families of per-tenant health: resource fairness (is any tenant taking more than its share, and are others suffering for it), per-tenant SLO health (SLO: service-level objective, the target a tenant was promised; here latency, errors, throttling measured against each tenant's objective), data consistency (replication lag and divergence between regions or replicas), and isolation integrity (any sign of one tenant touching another's data). Keep full per-tenant series (a time series: one continuously tracked stream of values for one specific combination of metric name and label values, so adding a tenant_id label multiplies one metric into one series per tenant) only for tenants that need them (top tier and top consumers) and aggregate the long tail by cohort (tier, shard, region), because 10,000 tenants times every metric overwhelms a time-series database. Alert on SLO burn rate, on any cross-tenant access event at zero tolerance, and on lag against the replication objective, and give on-call a dashboard that goes from "which cohort" to "which tenant" to "which neighbour" in three clicks.

The metric set

Aggregation strategy: per-tenant vs cohort

Worked cardinality example. 10,000 tenants, one latency histogram with 12 buckets (each histogram emits one series per bucket plus a sum and a count, so 14 series), broken down by 5 routes, in one region:

• Fully per-tenant: 10,000 x 14 x 5 = 700,000 series for that single metric.
• Top 200 tenants individually plus 40 cohorts for everyone else: (200 + 40) x 14 x 5 = 16,800 series.

That is a 42x reduction. The long tail is still visible through cohorts, and a tenant can be promoted to individual tracking automatically when it enters the top consumers or files a ticket. For deep investigation of a small tenant, use logs or traces (which are indexed per event, not per series) rather than metrics.

Alerting: burn rate, not raw thresholds

An error budget is the failure allowed by an SLO: 99.9% success over 30 days allows 0.1% failures. The burn rate is how fast you are spending it; burn rate 1 spends exactly the whole budget in 30 days. Page when two windows agree, so a brief blip does not page:

14.4 is not an arbitrary number: it is chosen backwards from a policy decision ("I am willing to see a tenant burn at most 2% of its monthly budget in a single hour") by solving burn rate = 2% x (720h / 1h) = 14.4. So "burn rate above 14.4 over the last hour and over the last 5 minutes" means a tenant lost 2% of a month's budget in one hour: page. The two windows do different jobs: the 1-hour window has to be long enough to confirm the burn is real and not noise, and the paired 5-minute window has to ALSO be above 14.4, which confirms the tenant is burning that fast right now, not that it did for a minute ten minutes ago and has since recovered. Without the short window, a spike that already ended would keep paging until it aged out of the 1-hour lookback. "Burn rate above 6 over 6 hours" is 6 x 6 / 720 = 5% of budget: ticket. Evaluate these per individually tracked tenant and per cohort.

The key isolation alert combines two signals: tenant A's share of a resource jumped AND other tenants on the same shard started burning budget. Either alone is normal (A is busy, or the shard has a problem); together they mean noisy neighbour: one tenant's load degrading another tenant's experience on the same shared resource.

Multi-region monitoring and automated remediation

• Measure lag both ways for each region pair, and per tenant for tenants with a regional residency or RPO promise.
• Run a scheduled divergence checker that hashes each tenant's recent rows on both sides; lag explains a temporary difference, a mismatch that persists after lag clears is divergence.
• Track per-tenant SLA (service-level agreement: the external, often contractual, promise built on top of one or more internal SLOs, with financial or contractual consequences if missed, unlike an SLO which is just the internal operating target) breaches per region, since a tenant can be healthy in one region and failing in another.
• Automated remediation, bounded and reversible: throttle a noisy tenant to its quota automatically; route reads away from a replica whose lag exceeds the freshness promise; pause non-urgent bulk jobs on a lagging shard. Anything destructive (failover, data repair) stays human-approved.

Visualizing it for on-call

flowchart TD
  OV[Overview: SLO burn by cohort and region] --> CO[Cohort drilldown: tenants ranked by burn]
  CO --> TE[Tenant page: latency, errors, throttling, lag]
  TE --> NB[Neighbours on same shard: resource share heat map]
  OV --> ISO[Isolation panel: cross-tenant denials, canary hits]
  OV --> REP[Replication: lag by region pair, divergence]

• Heat map: tenants (rows) by time (columns), colour = share of shard resources. A noisy tenant is a bright horizontal band that lines up with the time other tenants' latency degrades.
• Top-k table: top 10 tenants by resource share with their quota and current throttle state.
• Replication panel: lag per region pair against the RPO line; divergence count.
• Isolation panel: should be flat at zero; any bar is an incident.

Trade-offs and pitfalls

• Per-tenant everything explodes cardinality and cost; cohorts plus promotion is the practical middle.
• Static thresholds on latency page on every busy afternoon; burn-rate alerts tie paging to what tenants were promised.
• Averages across tenants hide the one tenant being starved; always look at the worst tenant in a cohort, not the mean.
• Treating cross-tenant denials as noise misses probing attacks and bugs; they should be near zero, so every one deserves a look.

Direct answer

I would put the 10,000 small tenants in a shared-schema pool (one set of tables with a tenant_id column, protected by row-level security, a database feature that automatically filters every query down to the current tenant's own rows) spread across about 10 database shards (independent database instances, each holding a slice of the small-tenant pool, so no single instance stores or serves all 10,000), put the 50 large tenants on per-tenant schemas inside a few dedicated "large tenant" clusters, and promote a large tenant to its own database when it crosses a size or throughput threshold or has a compliance requirement (data residency, customer-managed keys, audit scope). A tenant catalog records where each tenant lives and drives routing, backups, migrations and lifecycle automation, so the three models are managed by one control plane rather than three sets of scripts.

Terms: pool means tenants share infrastructure; silo means a tenant gets its own. RLS (row-level security) is a database feature that filters every query to the current tenant's rows even if application code forgets the WHERE clause.

Placement rules

Compliance and key management as a placement driver. Tenants at a higher trust or compliance level (for example requiring customer-managed encryption keys or a specific region) get a separate database encrypted with their own KMS (key management service) key. To stop these from exploding cost, compliance-bound tenants that are small go into a separate regulated pool: a shared-schema shard in the required region with tighter access controls and per-tenant keys at the application layer, rather than 1 instance each.

Routing

flowchart LR
  REQ[Request with tenant ID] --> RT[Router]
  RT --> CAT[(Tenant catalog, cached)]
  RT -->|pool| SH[Shard N: shared schema + RLS]
  RT -->|schema| LC[Large cluster: schema tenant_x]
  RT -->|dedicated| DB[Tenant database]

The catalog row per tenant holds: placement type, connection target, schema name, schema version, key ID, region, and state (active, migrating, suspended). The router caches it (short TTL, time-to-live, plus invalidation on change). For pool tenants the connection sets a session variable (SET app.tenant_id = ...) that the RLS policy reads; for schema tenants it sets the search path (the ordered list of schemas Postgres checks to resolve an unqualified table name) to the tenant's schema; for dedicated tenants it picks the tenant's connection pool. Connection pooling (for example PgBouncer) sits in front of each cluster, since 50 schemas or databases each holding their own idle connections exhausts the server.

Backup strategy

• Pool shards: continuous backup with PITR (point-in-time recovery: restore the database to any second within the retention window). Restoring one small tenant means restoring the shard to a side instance at the target time, then copying that tenant's rows back. Automate this runbook, because it is the most common restore request and the most error-prone.
• Schema-per-tenant clusters: PITR per cluster plus nightly logical dumps per schema (pg_dump --schema, a plain-SQL export of just that one schema's data and structure, so it can be restored on its own without touching any other tenant), which make single-tenant restore a straightforward load.
• Dedicated databases: their own PITR and snapshots, retention per contract, encrypted with the tenant's key.
• Test restores on a schedule for each placement type; an untested restore path is an assumption.

Schema-evolution tooling

• One migration framework (for example Flyway or Liquibase) with expand/contract migrations: add the new column or table (expand), deploy code that writes both, backfill (a batch job that fills the new column or table for rows that already existed before the change), switch reads, then drop the old (contract). Every step is backward compatible, so tenants on different schema versions are all served by the same code.
• A migration orchestrator reads the catalog and applies migrations in waves: internal tenants, then 1 pool shard, then all shards, then schema tenants, then dedicated databases, recording each tenant's schema version in the catalog. A failed tenant stops its wave and is retried, not the whole fleet.
• Custom schemas for large tenants: keep the core tables identical everywhere, and allow customization only through extension tables (tenant_x.custom_orders_ext) or a validated JSONB (PostgreSQL's binary JSON type) custom-fields column. Free-form per-tenant DDL (data definition language: CREATE/ALTER TABLE statements) makes every core migration a bespoke project.

Lifecycle automation

• Provision: API creates the catalog row, picks the least loaded shard or creates the schema or database, applies the current schema version, sets the key and quota.
• Promote (pool to schema, or schema to dedicated): set up logical replication (Postgres streaming individual row changes to the target instead of copying whole database files, which is what lets a tenant move into a differently-shaped destination, like a pool table moving into its own schema) of the tenant's rows into the new placement, let it catch up, briefly block writes, flip the catalog entry, resume. Because replication has already brought the target to within moments of the source before the cutover, the write-block only has to cover the last few seconds of changes since the last sync, not a full data copy, which is why downtime is seconds, not hours.
• Rebalance: move tenants between shards the same way when a shard runs hot.
• Offboard: export on request, delete rows or drop the schema or database, and destroy the tenant key to crypto-shred any backups encrypted with it (destroying the key makes every copy encrypted under it, including every backup, permanently unreadable, without needing to touch the backups themselves).

Expected operational costs (illustrative prices, stated as assumptions)

Of the 50 large tenants, assume (illustratively, to be measured from your own tenant base) that 35 fit the schema tier and the remaining 15 trip the compliance-or-heavy-load trigger from the placement rules above and get dedicated databases. Assume a pool shard (primary plus replica) costs 2,000 US dollars a month, a large-tenant cluster 4,000 dollars, and a dedicated database 3,000 dollars.

• Pool: 10 shards x 2,000 = 20,000 dollars, or 2 dollars per small tenant (20,000 / 10,000).
• Schema tier: 35 large tenants on 4 clusters: 4 x 4,000 = 16,000 dollars, about 457 dollars per tenant (16,000 / 35).
• Dedicated tier: 15 tenants x 3,000 = 45,000 dollars, 3,000 dollars per tenant.
• Total infrastructure: 20,000 + 16,000 + 45,000 = 81,000 dollars a month, before engineer time.

The hidden cost is operational: every dedicated database is another thing to patch, monitor, back up and migrate. That is why promotion to dedicated should require a reason (compliance, size, contract) and be priced into that tenant's plan.

Trade-offs and pitfalls

• Schema per tenant for all 10,050 looks uniform but gives 10,050 copies of every table and migrations that take hours to fan out.
• Everything in shared schema makes the 50 large tenants' custom needs and load a noisy-neighbour problem for the pool.
• Routing hardcoded in application config instead of a catalog makes promotion and rebalancing a deploy instead of a data change.
• Single-tenant restore from a pooled shard is slow and manual unless automated in advance.