Schema migrations without taking every tenant down

Single-tenant software can take a maintenance window. Multi-tenant software generally cannot, because there is no hour that is off-peak for everyone. That constraint rules out the migration style most teams learn first — change the schema, deploy the matching code — and replaces it with something slower and considerably safer.

Why a single deploy cannot do both

During any rolling deploy there is a period, however brief, when old and new application code are both running against the same database. A migration that renames a column breaks whichever version was not expecting it — and on a rollback, the surviving version is the one that is now wrong.

That fourth row is the one that causes incidents. A migration you cannot roll back is a migration you cannot safely deploy, because it removes your only recovery option at exactly the moment you need it.

Expand and contract, in five releases

Splitting a rename across separate releases means every intermediate state is one that both code versions tolerate. It feels laborious for a column rename. It is the reason the rename does not cause an outage.

1. 01Expand: add the new column, nullable, with no constraint. Nothing reads it yet. This is always safe.
2. 02Dual-write: deploy code that writes both columns and still reads the old one. Rollback remains safe.
3. 03Backfill: copy historical rows in batches, throttled, outside the deploy. Resumable and idempotent.
4. 04Switch reads: deploy code that reads the new column. The old one is still written, so rollback still works.
5. 05Contract: in a later release, stop writing the old column, then drop it once you are confident.

-- Release 1 — expand. Instant, no table rewrite, no lock of consequence.
ALTER TABLE invoices ADD COLUMN customer_reference text;

-- Release 3 — backfill in bounded batches so no single statement
-- holds locks or bloats WAL. Re-runnable after an interruption.
UPDATE invoices
SET    customer_reference = legacy_customer_ref
WHERE  customer_reference IS NULL
  AND  id IN (
         SELECT id FROM invoices
         WHERE customer_reference IS NULL
         ORDER BY id
         LIMIT 5000
       );

-- Release 5 — contract, only once nothing reads the old column.
ALTER TABLE invoices DROP COLUMN legacy_customer_ref;

Knowing which operations lock

Most migration outages are not caused by long-running work. They are caused by a brief operation that needs an exclusive lock, queuing behind a slow query, and then blocking every subsequent request on that table while it waits.

-- Fail fast rather than queueing behind a slow transaction.
SET lock_timeout = '3s';
SET statement_timeout = '30s';

-- Indexes on a live table must be built concurrently, which means
-- this statement cannot be wrapped in a transaction.
CREATE INDEX CONCURRENTLY idx_invoices_tenant_created
  ON invoices (tenant_id, created_at DESC);

When every tenant has its own schema

Schema-per-tenant and database-per-tenant trade one blast radius for a different problem: the migration now runs N times, it will fail partway through, and your application must tolerate tenants on two different schema versions simultaneously.

• Record migration state per tenant, so a resumed run skips those already done
• Migrate in batches with a concurrency limit — all tenants at once will saturate the database
• Migrate a canary tenant first, ideally an internal one, and verify before proceeding
• Make the application version-tolerant, since a mixed-version window is guaranteed rather than exceptional
• Alert on tenants left behind; a tenant stuck two versions back is a silent bug waiting to surface

This is the operational cost that the tenancy-model decision was really about. It is entirely manageable — but it has to be built, and it is considerably more work than a single migration against a shared schema.

Rehearse against production-shaped data

A migration that completes in 200 milliseconds against a development database with 50 rows tells you nothing about its behaviour against 40 million. Rehearse on a restored production snapshot, and measure rather than estimate.

1. 01Restore a recent production backup into an isolated environment — which doubles as a restore rehearsal you needed anyway.
2. 02Run the migration and record wall-clock duration and lock waits.
3. 03Run the rollback path and confirm it actually works.
4. 04If duration exceeds your lock timeout, redesign it as a batched background job rather than a migration.

The rule, in one line

Every deployed state must be one that both the previous and the next version of the application can run against. If a migration breaks that property, it is not one migration — it is two releases that have not been separated yet.