Multi-Tenancy and Isolation Questions

Direct answer

Give every tenant a deterministic, ranked list of partitions (computed by hashing the tenant with each partition number), and let a tenant use the first k entries of its list, where k is 1 for normal tenants and larger for hot tenants. Within that set, pick the slot by hashing the message ID. The same inputs always give the same partition, a normal tenant stays on one partition, a hot tenant spreads over k, and raising k keeps the tenant's existing partitions because the ranking never changes. The timestamp selects which version of the "who is hot" configuration applies, so replaying old events routes them exactly as they were routed originally.

Terms used below

• Partition: one of a fixed number of independent append-only logs in a message system such as Kafka (a widely used distributed message queue). A consumer reads a partition in order, and order is only guaranteed within one partition.
• Partition key: the value that decides which partition a message goes to. Here the function returns the partition index directly (a producer can send to an explicit partition), which avoids a second hash scattering things.
• Hot tenant: a tenant producing far more traffic than others; if all of it lands on one partition, that partition's consumer falls behind (lag) while others sit idle.
• Deterministic: same inputs, same output, on every machine and every process restart.
• Rendezvous hashing (highest random weight): for a key, score every candidate bucket with hash(key, bucket) and rank buckets by score. The ranking for a key never changes, so taking "the top k" and later "the top k+1" only ever adds buckets.

Approach

1. Stable hash. Use hashlib.blake2b, never Python's built-in hash(), which is randomly salted per process for strings. Two producers using hash() would disagree.
2. Spread per tenant, versioned by time. A small config says how many partitions each hot tenant may use, with an effective_from timestamp. spread_for(tenant, ts) picks the entry that was live at the event's timestamp.
3. Tenant's partition set. Rank all partitions by hash(tenant, partition) and take the first k. Normal tenants (k = 1) get one partition; different tenants get different ones, spreading tenants evenly.
4. Slot within the set. hash(tenant, message_id) % k picks one of the k partitions. Hashing the message ID (not a random number) means a retried message goes to the same partition, which keeps duplicate detection simple.

Code

import hashlib
from collections import Counter

NUM_PARTITIONS = 32

# Spread config: how many partitions each hot tenant may use.
# Each entry is (effective_from_epoch_seconds, {tenant_id: spread}).
# Versioning by time keeps the function deterministic on replay:
# an event is always routed by the config that was live at its timestamp.
SPREAD_CONFIG = [
    (0,             {"tenant-A": 4}),
    (1_700_000_000, {"tenant-A": 8}),   # tenant-A got hotter; widened to 8
]
DEFAULT_SPREAD = 1


def _h(*parts: str) -> int:
    """Stable 64-bit hash. Never use built-in hash(): it is salted per process."""
    data = "\x1f".join(parts).encode()
    return int.from_bytes(hashlib.blake2b(data, digest_size=8).digest(), "big")


def spread_for(tenant_id: str, timestamp: int) -> int:
    spread = DEFAULT_SPREAD
    for effective_from, table in SPREAD_CONFIG:
        if timestamp >= effective_from:
            spread = table.get(tenant_id, DEFAULT_SPREAD)
    return max(1, min(spread, NUM_PARTITIONS))


def tenant_partitions(tenant_id: str) -> list[int]:
    """Rendezvous ranking: every partition gets a score for this tenant,
    highest first. A tenant with spread k uses the first k entries, so
    widening k from 4 to 8 keeps the original 4 partitions."""
    return sorted(range(NUM_PARTITIONS),
                  key=lambda p: _h(tenant_id, str(p)), reverse=True)


def partition_key(tenant_id: str, message_id: str, timestamp: int) -> int:
    k = spread_for(tenant_id, timestamp)
    candidates = tenant_partitions(tenant_id)[:k]
    slot = _h(tenant_id, message_id) % k
    return candidates[slot]


if __name__ == "__main__":
    T_OLD, T_NEW = 1_690_000_000, 1_710_000_000
    for tenant in ("tenant-A", "tenant-B"):
        print(tenant, "allowed set (new config):",
              sorted(tenant_partitions(tenant)[:spread_for(tenant, T_NEW)]))
        for i in range(5):
            mid = f"msg-{i}"
            print(f"  {mid}: old={partition_key(tenant, mid, T_OLD):2d}"
                  f"  new={partition_key(tenant, mid, T_NEW):2d}")

    # Load shape over 10,000 messages per tenant under the new config.
    for tenant in ("tenant-A", "tenant-B"):
        c = Counter(partition_key(tenant, f"m{i}", T_NEW) for i in range(10_000))
        print(tenant, "partitions used:", len(c),
              "max share: %.1f%%" % (100 * max(c.values()) / 10_000))

    # Widening 4 -> 8 keeps the old set: old partitions are a subset of new.
    old = set(tenant_partitions("tenant-A")[:spread_for("tenant-A", T_OLD)])
    new = set(tenant_partitions("tenant-A")[:spread_for("tenant-A", T_NEW)])
    print("old set:", sorted(old), "subset of new:", old <= new)
    moved = sum(partition_key("tenant-A", f"m{i}", T_OLD) !=
                partition_key("tenant-A", f"m{i}", T_NEW) for i in range(10_000))
    print("messages whose partition changed at widening:", moved, "of 10000")

    # Determinism: the same inputs always give the same answer.
    print("deterministic:", all(
        partition_key("tenant-A", f"m{i}", T_NEW) ==
        partition_key("tenant-A", f"m{i}", T_NEW) for i in range(1000)))

Output (from running the file above; identical when re-run with different PYTHONHASHSEED values)

tenant-A allowed set (new config): [0, 7, 14, 16, 22, 23, 25, 29]
  msg-0: old= 0  new=16
  msg-1: old= 7  new= 7
  msg-2: old=29  new=29
  msg-3: old=25  new=25
  msg-4: old=25  new=25
tenant-B allowed set (new config): [29]
  msg-0: old=29  new=29
  msg-1: old=29  new=29
  msg-2: old=29  new=29
  msg-3: old=29  new=29
  msg-4: old=29  new=29
tenant-A partitions used: 8 max share: 13.1%
tenant-B partitions used: 1 max share: 100.0%
old set: [0, 7, 25, 29] subset of new: True
messages whose partition changed at widening: 4957 of 10000
deterministic: True

Key points, read from the output

• Requirement 1 (locality): tenant-B, a normal tenant, puts every message on partition 29. Tenant-A's traffic is confined to 8 of the 32 partitions, not all of them, so its consumers and caches deal with a known subset.
• Requirement 2 (hot spread): tenant-A uses all 8 partitions in its set, and the busiest one takes 13.1% of its messages, close to the ideal 1/8 = 12.5%.
• Requirement 3 (determinism): repeated calls agree, and the whole output is byte-identical across processes with different hash salts.
• Widening is incremental: tenant-A's old set [0, 7, 25, 29] is contained in the new set. But 4,957 of 10,000 message IDs changed partition, about half, which is what the arithmetic predicts. The old slot is h % 4 and the new slot is h % 8 for the same hash h, and since both index the same ranked list, a message stays put exactly when h % 8 is below 4, which is half of all hash values.
• A collision to notice: tenant-B's single partition (29) is also in tenant-A's set. Small tenants hashed onto a hot tenant's partition share its lag. Mitigation below.

Complexity

• Each call ranks all P partitions: O(P log P) time, O(P) memory. With P = 32 that is trivial, but on a hot path you would cache tenant_partitions(tenant) (one list per tenant, invalidated only when P changes), making a call O(1) hashing work.
• Config lookup is O(number of config versions); keep it small or index it by time.

Edge cases

• Ordering: a hot tenant's events are no longer totally ordered, because they sit on k partitions. If per-entity order matters (all events for one order or one user in sequence), hash the entity ID instead of the message ID for the slot, so each entity stays on one partition.
• The widening moment: at the config's effective_from time, roughly half of a tenant's keys move partition (measured above). Events for one entity near the boundary can be consumed out of order. Schedule changes, and let consumers tolerate a short reorder window or drain before switching.
• Changing P (the partition count) reshuffles every tenant's ranking. Treat it as a migration, not a config tweak.
• Unknown tenant or spread larger than P: clamped to the range 1 to P by spread_for.
• Small tenants sharing a hot tenant's partition: either reserve a block of partitions for hot tenants and rank small tenants only over the rest, or have consumers process per tenant fairly within a partition.
• Empty or very long IDs: hashing handles both; the \x1f separator stops ("ab","c") and ("a","bc") from hashing the same.

Trade-offs

Using k = 1 for everyone gives the best locality and strict per-tenant order but lets one tenant saturate a partition. Plain hash(message_id) % P spreads load perfectly but destroys tenant locality and makes per-tenant lag invisible. The ranked-set design sits between them and lets you move along that spectrum per tenant, with a config change rather than a code change.

Direct answer

Cross-tenant leaks almost never come from a broken isolation model. They come from one code path that forgot which tenant it was working for: a query without the tenant filter, a cache key without the tenant in it, a background job that lost the tenant context, or a log line that copied customer data somewhere shared. So the short-term fix for each cause is a targeted patch, and the long-term control is always the same idea: make the tenant identity travel with every request automatically and get enforced at the layer that stores or serves the data, so a developer cannot forget it.

Where the tenant identity should come from

A tenant context is the answer to "which customer is this request for?". The rule that prevents most leaks: derive it from the authenticated identity (for example a claim inside the signed login token), never from a URL parameter, header or request body the caller controls. Then pass it down explicitly to every database call, cache call, queue message and log statement.

Causes, immediate mitigations and architectural controls

Worked example: the cache-key leak

A SaaS (software as a service) product caches user profiles under profile:{user_id}. User IDs are per-tenant sequences, so tenant A and tenant B both have user 17. Tenant A's user 17 logs in, the profile is cached, and ten seconds later tenant B's user 17 sees A's name and email.

1. Contain: flush profile:*, deploy the key change to t{tenant_id}:profile:{user_id}, and pull cache-hit logs for the exposure window to list affected tenants for disclosure.
2. Fix the class, not the instance: replace direct cache calls with cache.get(tenant, key), which prepends the tenant itself, and add a lint rule that bans the raw client outside that wrapper.
3. Prove it: a test suite that creates two tenants with deliberately colliding IDs (same user ID, same invoice number) and asserts that every API read as tenant A returns nothing of tenant B's. Run it in CI (continuous integration) on every change.

Trade-offs and pitfalls

• Enforce at the lowest layer you can afford. Application-level filters depend on every developer remembering; RLS and wrapper clients do not. The cost is some performance and debugging friction, which is worth it for customer data.
• Admin and support tools are a common leak path, because they are built to see across tenants. Give them an explicit "act as tenant X" mode with audit logging rather than blanket read access.
• Do not trust tenant IDs from the client. A tenant ID in a header is an input to validate against the token, not a source of truth.
• Detection matters as much as prevention. Emit an alarm when any query returns rows whose tenant_id differs from the request's tenant; this catches the bug you did not anticipate.
• Stronger isolation (separate databases or keys) reduces blast radius but does not remove these bugs: a shared cache or log pipeline can still leak across dedicated databases.

Direct answer

PostgreSQL Row Level Security (RLS) lets you attach a policy to a table, a boolean condition such as tenant_id = <current tenant>, that the database silently adds to every query from ordinary roles. In a shared-schema design (all tenants in the same tables, each row tagged with tenant_id), that turns "every developer must remember the WHERE tenant_id = ? clause" into "the database refuses to show or write other tenants' rows". The application sets the current tenant per transaction, and the policy reads it. RLS is a strong safety net, not a complete isolation strategy: superusers, table owners and some views bypass it, and connection pooling can leak the tenant setting unless it is scoped correctly.

How the policy is defined

Three pieces:

1. Turn it on for the table: ALTER TABLE invoices ENABLE ROW LEVEL SECURITY; With RLS enabled and no policy, ordinary roles see zero rows (default deny).
2. Write the policy. USING filters which existing rows a query can see, update or delete. WITH CHECK validates rows being inserted or updated, so a tenant cannot write a row stamped with another tenant's ID.
3. Tell the database who the tenant is. The usual pattern is a custom setting: the application runs SET LOCAL app.tenant_id = '42' at the start of each transaction, and the policy reads it with current_setting('app.tenant_id', true). The true means "return NULL instead of erroring if the setting does not exist".

Worked example (executed)

Run against postgres:16-alpine with docker run -d --name pg-demo -e POSTGRES_PASSWORD=pw postgres:16-alpine, then docker exec -i pg-demo psql -U postgres -q < rls.sql:

-- run as the superuser "postgres" (the table owner in this demo)
CREATE TABLE invoices (
  tenant_id int     NOT NULL,
  id        int     NOT NULL,
  amount    numeric NOT NULL,
  PRIMARY KEY (tenant_id, id)
);
INSERT INTO invoices VALUES (1,1,100),(1,2,250),(2,1,999);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int);

CREATE ROLE app_user LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;
CREATE VIEW invoice_totals AS SELECT tenant_id, sum(amount) AS total FROM invoices GROUP BY tenant_id;
GRANT SELECT ON invoice_totals TO app_user;

\echo '--- 1. superuser/owner, no tenant set: policy does not apply'
SELECT count(*) FROM invoices;

SET ROLE app_user;
\echo '--- 2. app_user, no tenant set: fails closed (NULL matches nothing)'
SELECT count(*) FROM invoices;

\echo '--- 3. app_user as tenant 1'
BEGIN;
SET LOCAL app.tenant_id = '1';
SELECT tenant_id, id, amount FROM invoices ORDER BY id;
\echo '--- 4. tenant 1 tries to write a tenant 2 row'
-- SAVEPOINT marks a point to roll back to. Without it, the failed INSERT below
-- would abort this whole transaction and every later statement in it would also
-- error with "current transaction is aborted"; rolling back to the savepoint
-- undoes just the failed insert so the script can keep running and COMMIT.
SAVEPOINT s;
INSERT INTO invoices VALUES (2, 50, 1);
ROLLBACK TO SAVEPOINT s;
COMMIT;

\echo '--- 5. SET LOCAL ended with the transaction: next request on this pooled connection sees'
SELECT count(*) FROM invoices;

\echo '--- 6. plain SET (session scope) leaks into the next request on the same connection'
SET app.tenant_id = '2';
SELECT 'request A (tenant 2)' AS who, count(*) FROM invoices;
SELECT 'request B (forgot to set tenant)' AS who, tenant_id, amount FROM invoices;
RESET app.tenant_id;

\echo '--- 7. a view owned by the superuser bypasses the policy'
SET app.tenant_id = '1';
SELECT * FROM invoice_totals ORDER BY tenant_id;
RESET ROLE;
ALTER VIEW invoice_totals SET (security_invoker = true);
SET ROLE app_user;
\echo '--- 8. same view with security_invoker = true'
SELECT * FROM invoice_totals ORDER BY tenant_id;
RESET ROLE;

Output:

--- 1. superuser/owner, no tenant set: policy does not apply
 count 
-------
     3
(1 row)

--- 2. app_user, no tenant set: fails closed (NULL matches nothing)
 count 
-------
     0
(1 row)

--- 3. app_user as tenant 1
 tenant_id | id | amount 
-----------+----+--------
         1 |  1 |    100
         1 |  2 |    250
(2 rows)

--- 4. tenant 1 tries to write a tenant 2 row
ERROR:  new row violates row-level security policy for table "invoices"
--- 5. SET LOCAL ended with the transaction: next request on this pooled connection sees
 count 
-------
     0
(1 row)

--- 6. plain SET (session scope) leaks into the next request on the same connection
         who          | count 
----------------------+-------
 request A (tenant 2) |     1
(1 row)

               who                | tenant_id | amount 
----------------------------------+-----------+--------
 request B (forgot to set tenant) |         2 |    999
(1 row)

--- 7. a view owned by the superuser bypasses the policy
 tenant_id | total 
-----------+-------
         1 |   350
         2 |   999
(2 rows)

--- 8. same view with security_invoker = true
 tenant_id | total 
-----------+-------
         1 |   350
(1 row)

What each step shows:

• Steps 1 and 2: the superuser sees all 3 rows; the application role with no tenant set sees 0. Failing closed (showing nothing) is the behaviour you want when context is missing.
• Step 3: tenant 1 sees only its own 2 rows, with no WHERE clause in the query.
• Step 4: WITH CHECK blocks tenant 1 from inserting a row labelled tenant 2. The SAVEPOINT around it is not part of the isolation mechanism; it only lets this script recover from the expected error and keep running the remaining steps in one transaction.
• Step 5: SET LOCAL vanished at COMMIT, so the next request on the same pooled connection starts with no tenant and sees nothing.
• Step 6: a plain SET lasts for the whole session, so request B, which forgot to set a tenant, silently read tenant 2's 999 invoice.
• Steps 7 and 8: a view runs with its owner's rights by default, and the owner here bypasses RLS, so the view leaked tenant 2's total. PostgreSQL 15 added security_invoker = true for views, which applies the caller's policies.

The NULLIF(..., '') in the policy is there for a reason found while running this: once a transaction has used SET LOCAL app.tenant_id, later reads of the setting in that session return an empty string rather than NULL, and without NULLIF the cast ''::int raises invalid input syntax for type integer on the next request. That fails closed, but as an error on every pooled connection's next query rather than an empty result.

Performance implications

• The policy predicate is added to the query like an ordinary WHERE clause, so the cost is whatever that filter costs. With an index that leads with tenant_id, it is usually an index lookup. The follow-on script below (run in the same container after rls.sql, with docker exec -i pg-demo psql -U postgres -q < rls2.sql) loads 200 tenants with 501 invoices each and checks the plan, and also checks the table-owner bypass covered under gotchas:

CREATE ROLE app_owner LOGIN;
CREATE TABLE notes (tenant_id int NOT NULL, body text);
ALTER TABLE notes OWNER TO app_owner;
INSERT INTO notes VALUES (1,'a'),(2,'b');
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY p ON notes USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int);
SET ROLE app_owner;
SET app.tenant_id = '1';
\echo 'owner, ENABLE only'
SELECT count(*) FROM notes;
RESET ROLE;
ALTER TABLE notes FORCE ROW LEVEL SECURITY;
SET ROLE app_owner;
\echo 'owner, FORCE'
SELECT count(*) FROM notes;
RESET ROLE;
-- index use (loads 200 tenants x 501 invoices)
INSERT INTO invoices SELECT t, g, 1 FROM generate_series(1,200) t, generate_series(1000,1500) g;
ANALYZE invoices;
SET ROLE app_user;
SET app.tenant_id = '7';
EXPLAIN (COSTS OFF) SELECT sum(amount) FROM invoices;
EXPLAIN (COSTS OFF) SELECT * FROM invoices WHERE id = 1200;

owner, ENABLE only
 count 
-------
     2
(1 row)

owner, FORCE
 count 
-------
     1
(1 row)

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on invoices
         Recheck Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::integer)
         ->  Bitmap Index Scan on invoices_pkey
               Index Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::integer)
(5 rows)

                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using invoices_pkey on invoices
   Index Cond: ((tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::integer) AND (id = 1200))
(2 rows)

Reading the two shapes: the first query (sum(amount), no row filter beyond tenant) matches many rows, tenant 7's whole slice of the table, so the planner picks a Bitmap Index Scan: walk the index once and build an in-memory list ("bitmap") of the matching rows' physical locations, without fetching them yet. That feeds a Bitmap Heap Scan, which fetches exactly those locations from the table in one pass ordered by physical position, cheaper than jumping to each row individually. Index Cond under the bitmap index scan is the condition used to build that list; Recheck Cond on the heap scan is PostgreSQL re-checking that same condition against each fetched row, something bitmap scans always do because the in-memory bitmap can be a lossy approximation when a great many rows match. The second query adds id = 1200. Combined with the policy's tenant condition, that pins down both columns of the (tenant_id, id) primary key, so at most one row can match, and the planner instead uses a plain Index Scan: walk the index and fetch that one matching row immediately, which beats building a whole bitmap when only a single row is expected. Either way, the policy's tenant condition becomes part of the Index Cond on the (tenant_id, id) primary key, so the database reads only tenant 7's slice of the index.

• Without a tenant_id-leading index, every query scans all tenants' rows and then filters, which gets worse as tenants grow.
• Policies that call subqueries or functions (for example "tenant is in the user's membership table") run that logic for queries on every protected table; keep the policy a simple comparison against a setting.
• The query planner (the part of PostgreSQL that chooses the plan, the kind of plan shown by EXPLAIN above) will not push (reorder to run earlier than) user-supplied conditions that use non-leakproof functions ahead of the policy check. A function is leakproof if PostgreSQL can prove it never reveals anything about its input, not through its result, an error message, a log line, or even how long it takes to run; plain comparisons like = are leakproof, but an arbitrary user-defined function might, for example, raise an error that quotes the value it was checking, revealing a row's content even though the row was never returned. PostgreSQL cannot verify this for every function, so a non-leakproof user condition is never allowed to run before the tenant policy check, only after it. That ordering (policy check first, always) can occasionally produce a worse plan than letting a cheap-looking user condition run first. Check EXPLAIN on hot queries after enabling RLS.

Production gotchas

When RLS is the right choice, and when it is not

Right choice: a shared-schema SaaS (software as a service) product with many small tenants on PostgreSQL, where the risk you most want to remove is a developer forgetting a tenant filter; internal analytics databases where many teams share tables but must see only their own rows.

Not the right choice (or not enough on its own):

• Tenants that need a separate encryption key, separate backup and restore, or proof of physical separation: use a database or infrastructure per tenant.
• Workloads where one tenant's queries overload the database: RLS isolates data, not performance.
• Heavy reporting through views, materialized views or ETL (extract, transform, load) jobs that run as privileged roles: those paths bypass RLS and need their own tenant filtering.
• Very complex policies (hierarchical sharing across tenants): the per-query cost and the difficulty of reasoning about them usually argue for authorization in the application plus a simple RLS tenant check underneath.

Direct answer

Copy the tenant's rows from the shared schema into a new dedicated database, keep the copy current with change-data-capture (CDC: reading the database's change log and replaying inserts, updates and deletes), prove the two sides match, then cut over with a short per-tenant write freeze and a routing flip. Keep CDC running in reverse after cutover so rollback is a routing flip too. I prefer CDC plus a brief freeze over application dual-writes, and I use dual-writes only in a narrow, verified form if the tenant cannot tolerate even a few seconds of paused writes.

The usual trigger: an enterprise customer is onboarding and their security review requires stronger isolation than a shared table filtered by tenant_id gives them, for example their own database with its own encryption key.

The plan

flowchart LR
    A[Provision dedicated DB] --> B[Snapshot copy at log position L0]
    B --> C[CDC replays changes after L0]
    C --> D[Validate counts and checksums]
    D --> E[Freeze tenant writes]
    E --> F[Drain CDC, final check]
    F --> G[Flip routing]
    G --> H[Reverse CDC for rollback]

1. Provision

Create the dedicated database with the same schema version as the shared one, its own credentials and its own encryption key. Create tables and primary keys, but defer secondary indexes and foreign keys (constraints that say "this order's customer_id must exist in customers") until after the bulk copy; loading into a table that has to maintain five indexes row by row is several times slower than building them once at the end.

2. Full copy plus CDC

• Take a consistent snapshot of the tenant's rows, recording the log position L0 the snapshot corresponds to. In Postgres, creating a logical replication slot (a named, durable bookmark the server keeps, guaranteeing it retains every change made after a given point until you have streamed it, even if your consumer disconnects for a while) gives you both a snapshot and the position to stream from.
• Copy WHERE tenant_id = X for each table from that snapshot.
• Start a CDC stream from L0, filtered to this tenant's rows, and apply it to the new database. Apply is idempotent (an upsert, a write that inserts the row if it is new or updates it in place if it already exists, keyed on primary key), so replaying the same change twice is harmless.

3. Referential integrity and indexes

• Copy parent tables before child tables, or load everything and add foreign keys afterwards with a validation pass. In Postgres, ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT checks existing rows without blocking writes for the whole check.
• CDC applies changes in commit order across tables, so a transaction that inserts an order and its line items arrives together. Apply it as a transaction on the target, otherwise a reader of the new database can briefly see line items with no order.
• Check that no row references another tenant's data (in a shared schema this is a real bug class, for example a lookup table row shared across tenants). Shared reference data must be copied as a separate, explicit step.
• IDs: if the shared schema uses one global sequence (a single counter the database uses to generate the next id, shared across all tenants rather than per-tenant), copy IDs unchanged and set the new database's sequences (the database objects that hand out the next id) above the highest copied value. Never renumber, because IDs leak into URLs, exports and the customer's integrations.

4. Schema changes during the window

The safest rule is a DDL freeze (DDL: data definition language, meaning CREATE/ALTER/DROP) for the migration window. If the window is long, the migration tooling must apply each migration to the new database first (additive changes like a new nullable column are safe there), then to the shared schema, and CDC must be paused around the change, because most logical CDC streams carry row changes, not schema changes. A destructive change (drop or rename a column) waits until after cutover.

5. Validation

Before cutover and again during the freeze:

• Row count per table for the tenant, both sides.
• Checksum per table: hash of every row sorted by primary key, computed in chunks (for example 10,000 keys at a time) so a mismatch is located to a chunk and not rescanned whole.
• Application checks: sums of balances, counts per status.

6. Cutover

1. Confirm CDC lag (how far behind the CDC stream is from the source's latest committed writes) is under one second.
2. Freeze writes for this tenant only (the app returns a retryable "try again shortly" or queues writes). Other tenants are unaffected.
3. Wait until CDC has applied up to the log position at the moment of the freeze. Run a final count check on the hot tables (the tables that see the most writes, and so are most likely to have changed since the last check).
4. Flip routing for the tenant to the new database, with a bumped version so any app instance holding a stale route is rejected.
5. Unfreeze. Start reverse CDC (new to old) so the shared copy stays current.

Expected write pause: a few seconds to tens of seconds.

If dual-writes are required (no freeze allowed), concretely: (1) the app keeps writing to the shared schema as before, the still-authoritative source, and mirrors each write to the new database asynchronously; (2) once the continuous comparison has matched for a sustained period, flip reads only, so the app reads from the new database while still writing to both, and any disagreement is now visible to users, which is why step 1's verification has to be solid first; (3) once reads have run clean through a bake period, flip the source of truth, so the app now treats the new database as authoritative and the shared-schema write becomes the safety net instead of the other way around. Dual-writes from the application are hard to make correct: if one write succeeds and the other fails, the sides diverge, and retries can reorder updates. That is why I treat them as a fallback and still verify with checksums.

7. Rollback

Before deleting anything: rollback is freeze, drain reverse CDC, flip routing back to the shared schema, unfreeze. Keep the tenant's shared-schema rows (read-only for others, untouched) for a defined period, say two weeks, then delete them and record the deletion for the customer's audit.

Worked example

Tenant: 200 GB in the shared schema, 300 writes per second. Measured copy throughput 80 MB/s; CDC apply rate 3,000 changes per second.

• Full copy: 200,000 MB / 80 MB/s = 2,500 s, about 41.7 minutes.
• Changes that pile up during the copy: 2,500 s x 300/s = 750,000.
• Catch-up: CDC closes the gap at the apply rate minus the incoming rate, 3,000 - 300 = 2,700 changes per second, so 750,000 / 2,700 = about 278 s, roughly 4.6 minutes. (Dividing by 3,000 would understate it, because new changes keep arriving while you catch up.)
• Index and foreign key build afterwards, then validation, then a cutover freeze that in practice lasts 5 to 15 s.

Trade-offs and pitfalls

• Freeze versus dual-write: a few seconds of retryable errors for one tenant is almost always cheaper than debugging divergence. Choose dual-writes only when the contract forbids any pause.
• Pitfall: validating only row counts. Counts match while values differ (an update CDC missed). Checksums are what catch it.
• Pitfall: forgetting non-database state: cache keys, search index entries, files in object storage, and scheduled jobs that still target the shared schema.
• Pitfall: tenant filter on CDC done in the application after the fact. Filter at the source and assert every applied row's tenant_id equals the migrating tenant, so another tenant's data can never land in this customer's dedicated database.

Direct answer

The core change is to stop putting all tenants' work in one first-in first-out (FIFO) queue. I would give each tenant its own bounded queue, have workers pull from those queues with a weighted fair-share scheduler (deficit round robin), cap each tenant's in-flight work with a quota, and push back on a tenant whose queue is full with a retryable rejection instead of letting its backlog grow. Per-tenant circuit breakers stop one tenant's failing dependency from tying up shared workers. Together these let one tenant's burst use idle capacity, while making it impossible for that burst to delay everyone else, so you do not need to provision for every tenant's peak at once.

Why a shared FIFO queue fails

With one queue, a tenant that drops 500 jobs at once puts every other tenant's next job behind all 500. Nobody did anything wrong; the queue discipline simply gives the earliest arrivals all the capacity. Adding workers only shortens the delay in proportion to the extra spend, which is the over-provisioning we want to avoid.

Backpressure is the general answer: when a system is full, it signals producers to slow down instead of accepting unbounded work. In a multi-tenant system, the signal must be per tenant, so that only the tenant causing the pressure is slowed.

Architecture

flowchart LR
  C[Tenant clients] --> A[Admission: auth, tenant ID, quota check]
  A -->|queue full| R[429 + Retry-After]
  A --> Q1[Queue tenant A]
  A --> Q2[Queue tenant B]
  A --> Q3[Queue tenant C]
  Q1 --> S[Fair-share scheduler]
  Q2 --> S
  Q3 --> S
  S --> W[Shared worker pool]
  W --> CB[Per-tenant circuit breakers]
  CB --> D[Downstream dependencies]

1. Admission. Identify the tenant from its credentials (never from a client-supplied field), check its rate quota (for example a token bucket: a per-tenant allowance that refills at a fixed rate), and check its queue depth. If the queue is at its bound, reject with HTTP 429 and a Retry-After header. This is where backpressure becomes visible to the producer.

2. Per-tenant bounded queues. Physically these can be one table or stream partitioned by tenant, or a set of logical queues in the scheduler. The bound per tenant is set by tier (e.g. 200 queued jobs for a standard tenant, 2,000 for enterprise), which is what caps any one tenant's claim on memory and latency.

3. Fair-share scheduler. Deficit round robin (DRR) visits each non-empty tenant queue in turn and gives it a "quantum" of work credit per round (its weight); a tenant spends credit to dispatch jobs, and unspent credit carries over only while it has jobs waiting. With equal weights it is plain round robin; with weights 1:2:4 for free, standard and enterprise tiers, capacity divides in that ratio when everyone is busy. Crucially, it is work-conserving: if only one tenant has work, that tenant gets all the workers. That is how one tenant's burst uses idle capacity without taking capacity other tenants need. If job costs vary a lot, charge the quantum in estimated cost units (seconds of work) rather than job count.

4. Per-tenant concurrency quota. Independently of the scheduler, cap how many jobs a tenant can have running at once (e.g. 20% of workers for a standard tenant). This protects against a tenant whose jobs are individually slow, which fair ordering alone does not.

5. Per-tenant circuit breakers. A circuit breaker watches calls to a dependency and, once the failure rate crosses a threshold, fails further calls immediately for a cool-down period instead of letting them hang. Keyed per tenant (and per dependency), it handles the case where one tenant's jobs call something tenant-specific that is broken (its webhook endpoint, its connected database, its bad configuration). Without it, that tenant's jobs sit on shared workers waiting on timeouts. With it, the tenant's jobs fail fast (and are retried later or dead-lettered, i.e. moved to a DLQ, a dead-letter queue for jobs that keep failing) and the workers go back to everyone else.

6. Load shedding under global overload. If the whole system is saturated, shed in order: free-tier background work first, then retries, then new standard work; never an enterprise tenant's in-flight job.

Worked example (executed)

A pool completes 10 jobs per tick. Tenant a submits a burst of 500 jobs at tick 0; tenants b, c, d, e each submit 2 jobs per tick, steadily. Compare one FIFO queue with per-tenant queues (bound 200) served round robin.

from collections import deque

CAPACITY = 10          # jobs the worker pool finishes per tick
TICKS = 100
SMALL = ["b", "c", "d", "e"]   # steady tenants: 2 jobs per tick each
BURST = 500            # tenant "a" dumps 500 jobs at tick 0
QUEUE_CAP = 200        # per-tenant queue bound (used by fair mode only)

def arrivals(t):
    jobs = [("a", t)] * BURST if t == 0 else []
    for s in SMALL:
        jobs += [(s, t)] * 2
    return jobs

def pct(values, p):
    v = sorted(values)
    return v[min(len(v) - 1, int(p / 100 * len(v)))]

def run_fifo():
    q, waits, rejected = deque(), {}, 0
    for t in range(TICKS):
        q.extend(arrivals(t))
        for _ in range(min(CAPACITY, len(q))):
            tenant, arrived = q.popleft()
            waits.setdefault(tenant, []).append(t - arrived)
    return waits, rejected, len(q)

def run_fair():
    queues = {x: deque() for x in ["a"] + SMALL}
    waits, rejected = {}, 0
    for t in range(TICKS):
        for tenant, arrived in arrivals(t):
            if len(queues[tenant]) >= QUEUE_CAP:
                rejected += 1          # backpressure: caller gets 429 + Retry-After
            else:
                queues[tenant].append((tenant, arrived))
        served = 0
        while served < CAPACITY and any(queues.values()):
            for tenant in queues:      # round-robin, quantum = 1 job per visit
                if served < CAPACITY and queues[tenant]:
                    _, arrived = queues[tenant].popleft()
                    waits.setdefault(tenant, []).append(t - arrived)
                    served += 1
    return waits, rejected, sum(len(q) for q in queues.values())

for name, fn in [("FIFO", run_fifo), ("fair-share", run_fair)]:
    waits, rejected, backlog = fn()
    small = [w for s in SMALL for w in waits.get(s, [])]
    print(f"{name:10s} small-tenant p99 wait={pct(small, 99)} ticks, "
          f"small jobs done={len(small)}, burst jobs done={len(waits.get('a', []))}, "
          f"rejected={rejected}, backlog={backlog}")

Output (Python 3):

FIFO       small-tenant p99 wait=50 ticks, small jobs done=500, burst jobs done=500, rejected=0, backlog=300
fair-share small-tenant p99 wait=0 ticks, small jobs done=800, burst jobs done=200, rejected=300, backlog=0

Reading it: under FIFO the four well-behaved tenants waited up to 50 ticks at the 99th percentile (p99) and had 300 jobs still stuck at the end, because the burst went first. Under fair share their p99 wait was 0 ticks and all 800 of their jobs finished. Tenant a still got the 2 spare slots per tick (10 capacity minus 8 from the steady tenants), finishing 200 jobs, and its other 300 were rejected at admission and must be retried later. Same worker count, no extra capacity: the only thing that changed is who absorbs the burst. Tenant a does.

Avoiding over-provisioning

Provision for the sum of guaranteed shares plus a modest burst buffer, not the sum of peaks. Because DRR is work-conserving, idle capacity is still lent to whoever is bursting, so utilisation stays high. Scale out the worker pool on the metric that means "tenants inside their quota are waiting" (queue age of in-quota work), not on total queue length, which a single bursting tenant can inflate on its own and which would make you buy capacity for that tenant's backlog.

Trade-offs and pitfalls

• Rejecting work moves the problem to the client. Clients must honour Retry-After with backoff and jitter (random spread), or the rejections come straight back as a retry storm. Offer an asynchronous submit-and-poll path for tenants who legitimately send large batches.
• Thousands of tenants and a naive round robin scans many empty queues; keep an "active tenants" list containing only non-empty queues.
• Fairness by job count is unfair when job sizes differ. Charge cost units, or one tenant with 10-minute jobs still dominates.
• Circuit breakers keyed globally punish every tenant for one tenant's broken webhook; key them by tenant and dependency.
• Priority tiers without a floor starve the free tier. Give every tier a minimum weight so low-tier work always progresses.