Hybrid & Tiered Isolation Models

A hybrid isolation model runs several isolation strategies side by side — pooled rows for self-serve volume, a dedicated schema for mid-market, a dedicated database for the regulated few — and it is the pragmatic end state of the Multi-Tenant Database Isolation Models framework rather than a compromise of it. Almost every platform that reaches a few thousand customers ends up here, because no single model prices correctly across a three-order-of-magnitude range of customer size.

The reason is economic, not technical. Pooled tenants cost fractions of a cent each and scale to hundreds of thousands; dedicated databases cost tens of dollars a month before a single query runs. Serving a two-seat trial account from its own database destroys the unit economics of the free tier, and serving a hospital system from a shared table with everyone else fails procurement. A tiered model lets each customer pay for the isolation they actually require.

What makes hybrids hard is not running three models — it is making the rest of the system unaware that there are three. This page builds the tier registry, the routing layer that hides the difference from application code, the promotion path between tiers, and the operational model for running all of it without triplicating your migration tooling.

Prerequisites

• [ ] Working implementations of at least two isolation models, e.g. Shared Database with Row-Level Security and Schema-Per-Tenant Architecture.
• [ ] A tenant registry that is itself never sharded, holding tier, placement, and connection target per tenant.
• [ ] A pooling tier able to hold separate pools per placement target, sized as described in Connection Pooling in Multi-Tenant Systems.
• [ ] A migration runner that can apply the same DDL to a shared schema, N tenant schemas, and M tenant databases from one source of truth.
• [ ] Request-scoped tenant context resolved before any query executes.
• [ ] Per-tenant cost attribution — at minimum storage bytes, query time, and connection count.
• [ ] A dual-write or logical-replication capability for moving a live tenant between tiers without downtime.

Tier Definitions & Assignment Rules

Three tiers is the sweet spot; four is usually one tier of operational overhead too many. Define them by the boundary they enforce, then attach commercial and compliance triggers that move a tenant between them.

Assignment must be a property of the tenant record, evaluated by a rule engine, not an ad-hoc decision made during onboarding. The rule set is short and should be readable by a non-engineer: any tenant with a signed BAA is siloed; any tenant on the enterprise plan is at least bridged; any tenant that exceeds a storage or query-time threshold in a shared database is promoted to bridged regardless of plan, because at that point they are a noisy neighbour whether or not they are paying for isolation.

Step-by-Step Implementation

Step 1 — Make the tenant registry the single source of placement truth

Every request resolves placement from one authoritative, cached, never-sharded table. If placement can be inferred from two places, they will disagree, and the disagreement will be a cross-tenant read.

CREATE TYPE isolation_tier AS ENUM ('pooled','bridged','siloed');

CREATE TABLE tenant_placement (
  tenant_id     uuid PRIMARY KEY,
  tier          isolation_tier NOT NULL,
  cluster_key   text NOT NULL,           -- logical cluster name, resolved to DSN at runtime
  schema_name   text,                    -- NULL for pooled
  database_name text,                    -- NULL unless siloed
  region        text NOT NULL DEFAULT 'eu-west-1',
  migrated_at   timestamptz,
  CONSTRAINT placement_shape CHECK (
    (tier = 'pooled'  AND schema_name IS NULL     AND database_name IS NULL) OR
    (tier = 'bridged' AND schema_name IS NOT NULL AND database_name IS NULL) OR
    (tier = 'siloed'  AND database_name IS NOT NULL)
  )
);

The CHECK constraint prevents the single most common hybrid bug: a tenant marked siloed with a null database name, which silently falls back to the pooled connection and writes a regulated customer's data into the shared table.

Step 2 — Resolve a connection through one router, never in application code

Application code must ask for "a connection for the current tenant" and receive one that is already correct. The moment a feature team writes if (tenant.tier === 'siloed'), the abstraction has failed and the next feature will get it wrong.

// placement-router.ts
import { Pool, PoolClient } from 'pg';

interface Placement { tier: 'pooled' | 'bridged' | 'siloed'; clusterKey: string; schemaName?: string; databaseName?: string; }

const pools = new Map<string, Pool>();

function poolFor(p: Placement): Pool {
  const key = p.tier === 'siloed' ? `${p.clusterKey}/${p.databaseName}` : p.clusterKey;
  let pool = pools.get(key);
  if (!pool) {
    pool = new Pool({ ...dsnFor(p.clusterKey), database: p.databaseName ?? 'saas', max: maxFor(p.tier) });
    pools.set(key, pool);
  }
  return pool;
}

export async function withTenantConnection<T>(
  tenantId: string, work: (c: PoolClient) => Promise<T>,
): Promise<T> {
  const p = await placementCache.get(tenantId);          // in-memory, refreshed asynchronously
  const client = await poolFor(p).connect();
  try {
    await client.query('BEGIN');
    if (p.tier === 'bridged') {
      // Identifier, not a value — validate against the registry, never interpolate raw input.
      await client.query(`SET LOCAL search_path TO ${quoteIdent(p.schemaName!)}, public`);
    }
    await client.query('SELECT set_config($1, $2, true)', ['app.current_tenant_id', tenantId]);
    const out = await work(client);
    await client.query('COMMIT');
    return out;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Setting app.current_tenant_id on every tier — including siloed, where it is technically redundant — is deliberate. It means the RLS policies are identical everywhere, so a tenant promoted out of the pool does not lose a layer of defence, and a bug in the router that sends a siloed tenant to the pooled cluster still fails closed instead of leaking.

Step 3 — Apply one migration source to three physical shapes

The operational nightmare of hybrids is divergent schemas. Keep a single ordered migration set and a runner that enumerates every target from the registry, so "applied everywhere" is a computed fact rather than a hope.

# migrate_all.py
import psycopg

def targets(registry_dsn: str):
    with psycopg.connect(registry_dsn) as conn:
        rows = conn.execute(
            "SELECT DISTINCT tier, cluster_key, schema_name, database_name FROM tenant_placement"
        ).fetchall()
    seen, out = set(), []
    for tier, cluster, schema, database in rows:
        key = (cluster, database or "saas", schema if tier == "bridged" else "public")
        if key not in seen:
            seen.add(key)
            out.append(key)
    return out

def apply(migration_sql: str, registry_dsn: str) -> dict[str, str]:
    results = {}
    for cluster, database, schema in targets(registry_dsn):
        dsn = dsn_for(cluster, database)
        with psycopg.connect(dsn, autocommit=False) as conn:
            conn.execute(f'SET search_path TO "{schema}", public')
            conn.execute(migration_sql)
            conn.commit()
        results[f"{cluster}/{database}/{schema}"] = "ok"
    return results

Migration fan-out across large siloed fleets deserves its own treatment — batching, partial failure, and version skew are covered in running migrations across thousands of tenant databases.

Step 4 — Detect promotion candidates from real usage, not from sales pressure

Promotion should be triggered by measured impact on neighbours. A tenant consuming a disproportionate share of a shared database's buffer cache or lock time is a promotion candidate whether or not their contract says so.

-- Candidates: pooled tenants whose share of a shared database exceeds the noisy-neighbour ceiling.
WITH usage AS (
  SELECT tenant_id,
         sum(total_exec_time) AS ms,
         sum(rows_written)    AS rows_w,
         sum(bytes_stored)    AS bytes
  FROM   tenant_usage_daily
  WHERE  day >= current_date - 30
  GROUP  BY tenant_id
), totals AS (
  SELECT sum(ms) AS ms_all, sum(bytes) AS bytes_all FROM usage
)
SELECT u.tenant_id,
       round(100.0 * u.ms    / t.ms_all, 2)    AS pct_query_time,
       round(100.0 * u.bytes / t.bytes_all, 2) AS pct_storage
FROM   usage u CROSS JOIN totals t
WHERE  u.ms    > 0.05 * t.ms_all
   OR  u.bytes > 0.05 * t.bytes_all
ORDER  BY pct_query_time DESC;

A tenant taking more than five percent of a database shared by thousands is, by definition, no longer a pooled workload.

Step 5 — Promote with logical replication and a short cutover

Moving a live tenant between tiers is the operation that decides whether a hybrid model is usable. Copy first, catch up with change data capture, then flip placement inside a brief write freeze.

// promote.ts — pooled → siloed, with a bounded write freeze
export async function promote(tenantId: string, target: Placement) {
  await provisionDatabase(target);                       // DDL from the same migration set
  const watermark = await bulkCopy(tenantId, target);    // COPY out / COPY in, no locks held
  await streamChanges(tenantId, target, watermark);      // logical decoding until lag < 1s

  await freezeWrites(tenantId);                          // reject writes with a retriable 503
  try {
    await drainChanges(tenantId, target);                // final catch-up, verify row counts
    await setPlacement(tenantId, target);                // registry flip — single source of truth
    await placementCache.invalidate(tenantId);           // all app nodes re-resolve
  } finally {
    await unfreezeWrites(tenantId);
  }
  await scheduleSourceCleanup(tenantId, { afterDays: 7 });
}

The seven-day delay before deleting the source rows is not laziness — it is the rollback path. Placement is a single row, so reverting a bad promotion is one update, but only while the old data still exists.

Tier Selection Reference

Dynamic Query Scoping & Connection Handling

The routing layer has to make three physically different things behave identically, and the mechanism is uniformity of the scoping contract: every query runs inside a transaction that has both a search_path appropriate to its tier and a transaction-local tenant variable. Pooled queries are filtered by RLS on app.current_tenant_id; bridged queries are additionally namespaced by search_path; siloed queries are namespaced by the connection itself. Application code sees one contract.

Connection budgeting is where hybrids bite. A pooled cluster serving 30,000 tenants needs one pool sized to aggregate arrival rate; a siloed fleet of 200 tenants needs 200 pools, each of which must be small — typically two to five connections — or the aggregate blows past that instance's max_connections. Size siloed pools by the tenant's traffic, not by the platform's, and let them idle down to zero, since most siloed tenants are quiet most of the time. The connection arithmetic behind these numbers is worked through in database-per-tenant vs schema-per-tenant connection pool cost.

Cross-tier queries are the operation to design out rather than support. A platform-wide report that needs data from pooled, bridged, and siloed tenants cannot be one SQL statement; it becomes a fan-out that queries each placement and aggregates in the application, or — better — a per-tenant export into a warehouse where the tenant identifier is a column again. Accepting that constraint early is what keeps the model honest; trying to preserve cross-tenant SQL is what drags teams back into a single shared database with a compliance problem.

Security Enforcement & Access Control

A hybrid has more boundaries to enforce, and the dangerous ones are the seams between tiers.

The nightly reconciler is the highest-value control here. It walks the registry, connects to each declared placement, and asserts that the tenant's data exists there and only there — that a siloed tenant has no rows in the pooled tables, and that a pooled tenant has no orphaned schema left over from a rolled-back promotion. Hybrid systems drift, and drift in this system means data in the wrong isolation domain.

Operational Overhead & Scaling Metrics

Charting marginal infrastructure cost per tenant against tier makes the pricing consequence obvious. The three tiers do not differ by a percentage; they differ by orders of magnitude, which is precisely why one price cannot cover all of them.

The cost row is the one that decides whether the hybrid was worth building. Siloed tenants should carry a price that covers an instance floor plus operational overhead with margin; if the enterprise tier is priced from a pooled cost model, every promotion loses money and the platform quietly subsidises its largest, most demanding customers. The comparative cost method is worked through in Cost vs Security Tradeoff Analysis.

Pitfalls & Anti-Patterns

• Tier logic leaking into application code: Once feature code branches on tenant.tier, every new query becomes a place to get isolation wrong. Confine the branch to a single router function and give application code one connection-acquisition API.

• Dropping RLS on siloed tenants because "the database is already dedicated": The policies are your safety net when the router misroutes. Keeping them uniform costs nothing at runtime and converts a would-be leak into an empty result set.

• Promotion by restore instead of replication: Dumping and restoring a live tenant means an outage proportional to their data size, which is exactly backwards — the biggest customers get the longest downtime. Use logical replication with a short freeze at the end.

• Deleting the source data immediately after cutover: Rollback becomes impossible at the precise moment you are most likely to need it. Keep the source for a week, fenced off from reads, then clean up.

• Unbounded schema growth on the bridged tier: Postgres handles a few thousand schemas well and tens of thousands badly — catalogue scans, autovacuum and dump times all degrade. Cap schemas per cluster and shard across clusters rather than growing one.

• Cross-tier reporting queries: A join that spans placements either does not work or forces you to keep a shared copy of everything, which reintroduces the boundary you paid to remove. Export per tenant into a warehouse instead.

Frequently Asked Questions

How many isolation tiers should a platform actually run? Two if you can, three at most. Every tier multiplies migration targets, backup procedures, runbooks, and the number of ways a router can be wrong. Pooled plus siloed covers the majority of commercial needs; the bridged middle tier earns its place only when a meaningful segment of customers demands demonstrable logical separation without paying for dedicated infrastructure.

When should a tenant move from pooled to a dedicated tier? On measurement or on contract, never on request. The measurement trigger is resource share — a tenant consuming more than a few percent of a shared database's query time or storage is degrading thousands of neighbours and should be promoted regardless of plan. The contract trigger is a signed obligation you cannot satisfy in a shared table: a BAA, a residency commitment, customer-managed keys, or an audit right that includes physical separation.

Does a hybrid model triple the operational burden? Only if the tooling is written three times. Keep one migration source applied by a fan-out runner, one backup policy expressed per placement, one monitoring pipeline keyed by placement rather than by tier, and the burden grows sub-linearly. The genuinely irreducible extra cost is the reconciler and the promotion pipeline, which together are a few weeks of work and are what make the model safe.

Can a tenant be demoted from siloed back to pooled? Technically yes — the promotion pipeline runs in reverse — but it is rarely wise. Demotion means moving data from a stronger boundary to a weaker one, which usually contradicts whatever contractual or regulatory reason put the tenant there. Reserve it for tenants who downgraded plan without any compliance obligation, and treat it as a change requiring the customer's written acknowledgement.

How do we keep per-tenant backups consistent across tiers? Match backup granularity to the boundary. Siloed tenants get ordinary per-database backups and can be restored independently. Bridged tenants get per-schema logical dumps on a schedule, plus cluster-level point-in-time recovery for disasters. Pooled tenants cannot be restored individually from a physical backup at all, so the honest answer is a per-tenant logical export on a cadence, restored into a staging schema and copied back — which is slow enough that it should be part of the case for promotion.

Related

• Multi-Tenant Database Isolation Models
• Schema-Per-Tenant Architecture
• Database-Per-Tenant Isolation
• Cost vs Security Tradeoff Analysis