Promoting a Tenant from Shared to Dedicated Database

Promotion is the operation that decides whether a tiered platform is usable, because it is the one customers experience directly. This page is a focused part of Hybrid & Tiered Isolation Models: moving one tenant's data out of a shared database into its own, with a write freeze measured in seconds rather than the tenant's data size.

Problem Framing

The obvious approach — export the tenant, import it, switch — has a downtime proportional to data volume. That is exactly backwards: the tenants being promoted are the largest ones, so the customers paying most for isolation get the longest outage. A tenant with two hundred gigabytes would face hours offline for an upgrade they requested.

Logical replication inverts the cost. A bulk copy runs against a consistent snapshot while the tenant keeps working, changes made during the copy stream continuously to the target, and the only synchronous step is a final catch-up once lag is already under a second. The freeze is short because almost nothing is left to do when it starts.

Two properties matter more than speed. Atomicity of the switch: reads must never be served from both databases at once, so placement has to flip in one observable step and every application node must see it. And reversibility: the source rows stay in place, fenced from writes, for long enough that a bad promotion is one registry update away from being undone.

Only the second-to-last box is downtime. Everything expensive happens while the tenant is fully online.

Step-by-Step Guide

1. Provision the target and apply the identical schema

// promote-step1.ts
export async function prepareTarget(tenantId: string, region: string) {
  const target = await provisionTenant(tenantId, 'enterprise', region);   // Terraform + migrations
  const srcVer = await schemaVersion(sourceCluster());
  const dstVer = await schemaVersion(target.cluster, target.database);
  if (srcVer !== dstVer) throw new Error(`schema skew: source ${srcVer}, target ${dstVer}`);
  return target;
}

Refusing to proceed on schema skew is not pedantry — replicating into a target with a different shape produces errors halfway through the copy, at which point you have consumed hours for nothing.

2. Copy the tenant's rows from a consistent snapshot

-- On the source: one snapshot for every table, so referential integrity holds across the copy.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();          -- pass this to every parallel worker
-- worker per table:
COPY (SELECT * FROM invoice WHERE tenant_id = '…') TO STDOUT (FORMAT binary);
COMMIT;

# Parallel copy, one process per table, all sharing the exported snapshot.
psql "$SRC" -c "\copy (SELECT * FROM invoice WHERE tenant_id='$T') TO STDOUT (FORMAT binary)" \
  | psql "$DST" -c "\copy invoice FROM STDIN (FORMAT binary)"

A shared snapshot is what makes the copy self-consistent. Copying tables one at a time from separate transactions produces a target where a child row exists without its parent, and the foreign keys will tell you so at the worst possible moment.

3. Stream the changes made since the snapshot

-- On the source: publish only this tenant's rows (PostgreSQL 15+ row filters).
CREATE PUBLICATION tenant_acme FOR TABLE invoice, ticket, document
  WHERE (tenant_id = '00000000-0000-0000-0000-00000000a001');

-- On the target: subscribe from the snapshot position, no initial copy.
CREATE SUBSCRIPTION tenant_acme_sub
  CONNECTION 'host=shared-primary dbname=saas'
  PUBLICATION tenant_acme
  WITH (copy_data = false, create_slot = true, slot_name = 'tenant_acme');

Row filters on the publication are what keep this a tenant migration rather than a database migration. Without them you would replicate every tenant's changes into a database meant for one.

4. Freeze, drain, verify, flip

// cutover.ts
export async function cutover(tenantId: string, target: Placement) {
  await waitForLag(tenantId, { belowMs: 1000, timeoutMs: 300_000 });

  await freezeWrites(tenantId);                        // 503 with Retry-After on write paths
  const t0 = Date.now();
  try {
    await waitForLag(tenantId, { belowMs: 0, timeoutMs: 30_000 });   // fully drained
    const ok = await verifyCounts(tenantId, target);                  // per-table row counts
    if (!ok) throw new Error('row count mismatch — aborting cutover');

    await setPlacement(tenantId, target);              // single row: the atomic switch
    await placementCache.invalidateEverywhere(tenantId);
    await waitForAllNodesAck(tenantId, { timeoutMs: 5000 });
  } finally {
    await unfreezeWrites(tenantId);
  }
  return { frozenMs: Date.now() - t0 };
}

Freezing writes but not reads is the choice that keeps the freeze tolerable: the tenant's dashboards keep rendering, and only mutations get a retriable 503 for a few seconds.

5. Fence the source and keep it for a week

-- The tenant's rows in the shared database become read-only, not deleted.
CREATE POLICY promoted_tenant_readonly ON invoice
  AS RESTRICTIVE FOR ALL
  USING (true)
  WITH CHECK (tenant_id <> '00000000-0000-0000-0000-00000000a001');

// cleanup.ts — runs seven days later, and not before
export async function cleanupSource(tenantId: string) {
  const p = await getPlacement(tenantId);
  if (p.tier !== 'siloed' || !p.migratedAt) throw new Error('not promoted');
  if (Date.now() - p.migratedAt.getTime() < 7 * 864e5) throw new Error('rollback window still open');
  await dropSubscription(tenantId);
  await dropPublication(tenantId);
  await deleteSourceRows(tenantId);                    // batched, off-peak
}

Six seconds, of which the database accounts for two. The rest is waiting for every application node to confirm it re-resolved placement.

Verification

-- 1. Row counts match per table, run inside the freeze before flipping.
SELECT 'invoice' AS t, count(*) FROM invoice WHERE tenant_id = $1   -- on the source
UNION ALL SELECT 'ticket', count(*) FROM ticket WHERE tenant_id = $1;
-- compare against the same query on the target; any difference aborts the cutover

-- 2. Checksums for a stronger guarantee on the tables that matter most.
SELECT md5(string_agg(md5(t.*::text), '' ORDER BY t.id)) FROM invoice t WHERE tenant_id = $1;

-- 3. After cutover: the source must receive no further writes.
SELECT count(*) FROM invoice
WHERE  tenant_id = $1 AND updated_at > (SELECT migrated_at FROM tenant_placement WHERE tenant_id = $1);
-- expected: 0 on the source

The third check is the one that catches a stale application node still writing to the shared database after the flip — a split-brain that silently loses those writes when the source is finally cleaned up.

The seven-day fence is not caution for its own sake. It is the difference between a two-minute revert and a repeat of the entire migration.

Failure Modes & Gotchas

Symptom: the target has orphaned child rows. Cause: tables were copied in separate transactions rather than from one exported snapshot. Fix: export a snapshot and have every parallel copy worker use it.
Symptom: some writes land in the shared database after cutover. Cause: an application node had a cached placement and never saw the invalidation. Fix: require acknowledgement from every node before unfreezing, and fence the source with a restrictive policy so late writes fail instead of succeeding.
Symptom: replication lag never reaches zero. Cause: a long-running transaction on the source holds back the replication slot, or the tenant's write rate exceeds apply throughput. Fix: find and end long transactions before cutting over, and schedule the freeze outside the tenant's peak.
Symptom: disk fills on the shared primary during the copy. Cause: the replication slot retains write-ahead log while a slow copy runs. Fix: monitor slot lag, set max_slot_wal_keep_size, and abort the promotion rather than risking the shared database everyone else is using.

FAQ

How long should the write freeze be allowed to last? Set a hard budget — ten seconds is achievable — and abort if the drain or verification exceeds it. Aborting is cheap: unfreeze, keep replicating, and try again later. What you must not do is let the freeze extend "just a bit longer" while a slow verification finishes, because that is how a planned six-second interruption becomes a five-minute incident during business hours.

Can promotion run without any freeze at all? Only by accepting either lost writes or split-brain reads, both of which are worse than a few seconds of retriable errors. A truly zero-freeze cutover requires dual-write with conflict resolution, which is far more complex to build and to reason about than a short freeze. Clients that retry on 503 with Retry-After make the freeze invisible to most users anyway.

Should the customer be told? Yes, briefly and in advance, but do not oversell it. A short notice that the workspace may reject saves for a few seconds during a stated window is honest and sets the right expectation. Promotion is usually something the customer asked for, so framing it as the upgrade it is — dedicated infrastructure, per-tenant backups, their own encryption key — makes the interruption a non-event.

What happens to background jobs already queued for the tenant during the cutover? They must resolve placement when they run rather than when they were enqueued, which is the same rule that governs everything else. A job holding a captured connection string will write to the old database after the flip; a job that resolves placement from the registry at execution time follows the tenant automatically. Pausing the tenant's queue for the freeze window is a reasonable extra precaution for high-volume tenants, and it costs nothing because the freeze is measured in seconds.

Is it worth promoting a tenant preemptively rather than on a threshold? Occasionally, and the case for it is commercial rather than technical. A customer about to onboard several thousand users will cross every threshold at once, and doing the promotion beforehand turns a reactive migration during their busiest week into a scheduled one during a quiet one. The signal to act on is usually a conversation rather than a metric — which is an argument for making the promotion pipeline routine enough that running it early costs almost nothing.