How to Migrate a Multi-Tenant SaaS Schema Without Breaking Tenants

For: A CTO or lead engineer at a 20–80 person B2B SaaS company running a shared-schema multi-tenant Postgres database, who needs to roll out a breaking schema change but cannot apply it atomically across all tenants because different customers are on different release tracks, feature flags, or contractual data-isolation requirements

The safe way to migrate a shared-schema multi-tenant Postgres database when tenants are on different release tracks is to treat the tenant cohort — not the schema change — as your migration unit, and let the old and new columns coexist until every tenant's application layer has crossed the boundary on its own timeline. That means expand-migrate-contract, dual-write and dual-read behind a per-tenant feature flag, and a migration state machine that tracks which tenants are on which side of the fence. Anything else — even a well-tested ALTER TABLE in a maintenance window — will break the tenants who happen to be on last month's application build.

This post is a playbook. It assumes you're running Postgres, shared-schema multi-tenancy (a tenant_id column on every table, not schema-per-tenant), somewhere between a few dozen and a few thousand tenants, and at least some contractual pressure to isolate release cadence per customer.

The exact situation this applies to

You have one Postgres database. Every table has a tenant_id. You have customers on three or more application versions at once — because some are opted into a beta, some are pinned by contract, some are on a slower release ring, and some just haven't gotten around to the update. Now product wants to rename customer_status from a string to an enum, or split full_name into first_name and last_name, or move billing_address out of users into its own table. A single atomic migration would work if all app instances deployed together. They don't. This is the playbook.

Step 1: Classify the change before you write a single line of SQL

Not every schema change needs the full ceremony. Before you build a migration pipeline, put the change in one of three buckets:

• Additive-only: new nullable column, new table, new index. Old code ignores it. Ship it directly, no cohorting needed.
• Semantic change: same column, new meaning. Old code will read or write it and produce wrong data. This is the dangerous middle. Requires dual-write.
• Structural change: rename, type change, drop, split, merge. Old code will error or silently corrupt. Requires the full expand-migrate-contract cycle.

The anti-pattern here is running every migration through the same heavyweight process. It slows the team down and trains everyone to shortcut the process the one time it actually matters. Be honest about which bucket you're in.

You'll know this step is done when every engineer on the team can look at a proposed migration and name its bucket in under thirty seconds, and your migration PR template forces the author to declare the bucket explicitly.

Step 2: Expand the schema so old and new can coexist

For semantic and structural changes, the first migration you write is purely additive. It never removes or renames anything. If you're splitting full_name into first_name and last_name, you add the two new nullable columns. If you're changing the type of status from text to an enum, you add status_v2 as the enum column and leave status untouched.

Two rules for the expand migration:

1. New columns are nullable, no defaults. A NOT NULL DEFAULT on a large table will rewrite the table and lock it. On Postgres 11+ you get instant defaults for scalar types, but the moment you touch an expression or a large text column you're back to rewrites. Just make it nullable and backfill later.
2. No foreign keys yet. Adding an FK with VALIDATE takes an ACCESS EXCLUSIVE lock. Add the column, add the FK as NOT VALID, validate it after backfill in a separate transaction.

The anti-pattern: bundling the expand with anything else. If your expand migration also renames a column or drops an index, you've defeated the point. Expand migrations should be so boring they're almost not worth reviewing.

You'll know this step is done when the migration has run in production, all application versions currently deployed still pass their integration tests against the new schema, and no tenant has noticed anything.

Step 3: Teach the application to dual-write behind a per-tenant flag

This is where the playbook diverges from the standard expand-migrate-contract advice you'll find in most Postgres blogs. In a single-tenant system, you'd flip dual-writes on globally. In a multi-tenant system, you flip them on per tenant, driven by the same feature-flag system that gates the application-level feature.

Concretely: your write path checks a flag like schema.name_split.dual_write for the current tenant. If enabled, every write to full_name also writes to first_name and last_name. Reads still come from full_name. This state is invisible to the tenant but keeps the new columns in sync from the moment the flag flips.

Three things to get right:

• Dual-write must be idempotent and side-effect free. If the split logic throws, do not fail the primary write. Log it, alert on it, but don't let a shadow column corrupt a real user action.
• The flag lives in one place. LaunchDarkly, Unleash, a tenant_settings table — pick one. Don't have three sources of truth about which tenant is in which migration phase.
• Track the flag state in your migration ledger. You need a table (or an external system) that records, per tenant, which schema migrations have moved them through which phases. Without this, you cannot answer 'is it safe to drop full_name?' six weeks from now.

The anti-pattern: dual-writing globally on deploy. This works until the first tenant on an old app version writes to full_name only, drifting the two columns out of sync. Now your backfill logic has to handle drift and you've turned a clean migration into a data reconciliation project.

You'll know this step is done when you can flip dual-write on for a single canary tenant, watch writes hit both old and new columns in production, and flip it off cleanly if something goes wrong.

Step 4: Backfill in tenant-sized batches, not table-sized ones

Once dual-write is on for a tenant, new rows are correct. Existing rows are not. You need to backfill — but not the whole table at once. Backfill per tenant, in the same order you'll move tenants through the rest of the migration.

Some practical points from doing this on real Postgres databases:

• Use a background worker with a bounded batch size — 1,000 to 10,000 rows per transaction, tuned to your table size and lock behavior. UPDATE ... WHERE tenant_id = $1 AND id BETWEEN $2 AND $3 is your friend.
• Between batches, sleep long enough that autovacuum can keep up. Runaway backfills that outpace vacuum will bloat the table and quietly degrade every query touching it.
• Verify per tenant, not per table. After backfill for tenant X completes, run a COUNT(*) WHERE new_column IS NULL AND tenant_id = X check and record it. This is the checkpoint that lets you promote a tenant to the next phase.
• Do not use pg_repack or table rewrites during backfill unless you've tested it against your exact write load. It's fine in theory and unpleasant in practice.

The anti-pattern: a single UPDATE across the entire table. Even chunked, if you do it in tenant_id-agnostic ranges, you can't stop or resume on a per-tenant basis. And the moment one tenant demands you pause their migration, you're stuck.

You'll know this step is done when every tenant with dual-write enabled has a verified zero-null count on the new column, and you have a ledger row per tenant recording backfill completion with a timestamp.

Step 5: Cut reads over, one tenant cohort at a time

Now you have two synchronized copies of the data. The application still reads from the old column. Flip reads to the new column per tenant, using a separate flag from dual-write. Never combine them — dual-write off and reads-from-new on is a data-loss configuration, and if the two flags share a code path you'll eventually get there by accident.

Move in cohorts:

1. Canary: one or two internal or friendly tenants. Watch error rates for at least a full business cycle — a week, sometimes two.
2. Early ring: 5–10% of tenants, ideally ones on the newest application version. This is where you'll catch the bugs that only show up with real user data.
3. Main ring: the bulk of tenants, moved in batches sized to what your on-call can support.
4. Slow ring: contractually pinned tenants and anyone still on old app versions. These may sit in dual-write mode for months. That's fine. That's the point.

The anti-pattern: pressure from product to 'just cut everyone over.' The whole reason you did this work is so you don't have to. Tenants in dual-write mode cost you a small amount of write overhead and a fraction of storage. That's a cheap price for optionality.

You'll know this step is done when more than 95% of your tenants are reading from the new column, error rates on those tenants match or beat the old code path, and the remaining tenants have named owners and target dates.

Step 6: Contract — drop the old column, but only when the ledger says so

This is the step everyone wants to skip and everyone regrets skipping. Do not drop the old column until:

• Every tenant has been reading from the new column for at least one full billing or reporting cycle. Monthly close, quarterly reports, whatever your customers actually run against your data.
• Your migration ledger shows zero tenants in dual-write-only mode.
• You have grepped the codebase — application code, background jobs, analytics pipelines, exports, webhooks — for every reference to the old column name. Analytics is where this bites you. A Looker dashboard nobody has opened in eight months will start throwing errors the day after you drop the column.
• You have a rollback plan that does not depend on the old column existing.

When you're ready, drop in stages: first, rename the old column to something obviously deprecated (full_name__deprecated_2024_q3). Wait two weeks. If nothing screams, drop it.

The anti-pattern: dropping the column the same sprint you finish read cutover. The whole point of expand-migrate-contract is that contract is optional and reversible for as long as possible.

You'll know this step is done when the column is gone, no alerts have fired for two weeks, and you have removed the dual-write code from the application. Leaving dual-write code in place 'just in case' is how you accumulate the technical debt this playbook was supposed to prevent.

Step 7: Codify the pattern so the next migration is boring

The first time you run this playbook, it feels heavy. By the third or fourth migration, most of the machinery should be library code:

• A migration-phase abstraction in your ORM or data layer that reads flags and routes reads/writes to old or new columns.
• A backfill worker framework that takes a tenant ID and a migration ID and handles batching, checkpointing, and verification.
• A ledger table with a schema like (tenant_id, migration_id, phase, entered_at, verified_at, notes).
• A dashboard showing, for each active migration, how many tenants are in each phase. Your on-call needs this to answer 'is it safe to deploy?' at 2 a.m.

You'll know this step is done when an engineer can propose a new breaking schema change and, without writing custom migration tooling, ship the expand, wire dual-write, and start a canary — all using existing platform primitives.

Failure modes we've seen

The forgotten reader. An analytics job, ETL export, or third-party integration reads the old column directly from a replica. Nobody flagged it because it wasn't in the application repo. Fix: maintain a registry of every consumer of your database, not just services in your monorepo.

Flag drift. Someone flips dual_write off for a tenant to debug something, forgets to flip it back, then a week later the read cutover proceeds and pulls from a stale column. Fix: alert on any tenant where dual_write=off and read_from_new=off both hold — that's the invalid state.

Backfill outrunning vacuum. Aggressive backfill on a hot table causes bloat, slow queries, and eventually a page. Fix: rate-limit the worker and monitor pg_stat_user_tables.n_dead_tup during backfills.

The pinned tenant that never moves. A large customer is contractually pinned to an old application version and sits in dual-write mode indefinitely. This is not a bug — it's the design. But if you don't plan for it, you'll drop the old column too early. Fix: contracts and migration state need to be visible in the same place.

Silent divergence. Dual-write fails silently for some rows (usually because of a nullable-vs-not-nullable mismatch or a serialization edge case), and nobody notices until read cutover. Fix: a scheduled reconciliation job that samples rows and compares old and new columns during the dual-write phase.

How CodeNicely can help

Most of the SaaS platforms we've helped scale hit this exact wall somewhere between their first ten and first hundred tenants. When we worked with GimBooks, a YC-backed accounting SaaS, one of the recurring themes was that the data model had to evolve continuously without disrupting SMB customers who lived inside the product every day for tax filing and bookkeeping — the same constraint you're facing. Accounting data has hard cutoff dates; you cannot ask a tenant to 'wait an hour' during quarter-end.

Our work spans legacy modernization and platform re-architecture for SMB and enterprise SaaS teams — migration tooling, tenant isolation strategies, and the operational scaffolding (feature flags, ledgers, dashboards) that makes these playbooks repeatable rather than heroic. If you're staring at a breaking migration and your team hasn't run one at this scale before, talk to us — we'll help you scope the specific change, not sell you a framework.

Frequently Asked Questions

Can I skip dual-write if my tenants are small and I have a maintenance window?

Sometimes, yes — but only if you can guarantee every application instance consuming the database is on the new version before the window ends. In a multi-tenant SaaS with contractual version pinning, that guarantee rarely holds. When in doubt, expand-migrate-contract is cheaper than the incident.

What if we're on RDS or Aurora — does that change anything?

The playbook is the same. Managed Postgres changes your operational tooling (you'll use RDS Blue/Green for major version upgrades, not schema changes), but the multi-tenant cohorting logic lives in your application and feature-flag layer, not the database. Aurora's faster DDL helps with expand-phase locking but doesn't remove the need for per-tenant coordination.

How do we handle migrations that require rewriting historical data (e.g., recomputing a derived field)?

Treat the recompute as a per-tenant backfill job with its own idempotency key. Run it after dual-write is on so new writes stay consistent while historical data catches up. Verify per tenant, and gate the read cutover on backfill completion for that tenant specifically.

Is schema-per-tenant a better model to avoid this problem entirely?

It trades one problem for another. Schema-per-tenant makes per-tenant migrations trivial but makes cross-tenant queries, connection pooling, and operational work harder — and it scales badly past a few hundred tenants. Shared-schema with disciplined migration tooling is the more common answer for growing B2B SaaS.

How long should we plan for a migration like this to take?

It depends heavily on tenant count, data volume, and how many tenants are on old application versions. Rather than guess, contact CodeNicely for a personalized assessment — we'll walk through your specific tenant topology and give you an honest read.

The point

The reason multi-tenant schema migrations feel impossible is that most guides assume the database and the application deploy together. They don't in your world. Once you accept that the tenant cohort is the migration unit and that old and new must coexist for as long as any tenant needs them to, the mechanics — expand, dual-write, backfill, cutover, contract — are just engineering. Boring, careful engineering. Which is exactly what you want.