Migrating a Production Database Without a Maintenance Window

The maintenance window is a fiction most SaaS platforms can no longer afford. A retail tenant in Sydney is opening while a tenant in Dallas is closing out the day. A field crew uploads photos at 5:40 a.m. local time. There is no hour where nobody is writing, and the moment you schedule one anyway you have committed to a plan where a failed migration means an outage plus a rollback plus an apology email.

The other reason to drop the window: it makes you lazy. A migration you can run behind a locked door does not need to be reversible, incremental, or observable. So it isn't. Then the table turns out to be 400 million rows, the ALTER takes a full lock, and the window you booked for 20 minutes is now the longest hour of the quarter.

We have migrated over a hundred tenant databases off a platform we then shut down, and we have shipped hundreds of schema changes to live fleets of per-tenant Postgres running behind Node services on Kubernetes. Nothing below is clever. It is the same small set of rules applied without exceptions, which is the only thing that actually works.

Two different problems that get called the same thing

"Database migration" covers two jobs with almost no shared mechanics, and conflating them is the first mistake.

Schema migration changes the shape of data in place: add a column, split a table, change a type, add a constraint. The database stays where it is. The risk is locking, long-running rewrites, and application code that expects a shape the database no longer has.

Data migration moves data somewhere else: a new cluster, a new provider, a new tenancy model, a new platform entirely. The risk is drift between source and destination, and the cutover moment where writes have to switch sides exactly once.

Schema migrations are solved by expand-and-contract. Data migrations are solved by dual writes plus reconciliation. If you try to solve either with the other's tooling you will be up all night.

Expand and contract, with no shortcuts

The rule we hold to: every schema change ships as at least two deploys, and every intermediate state must be valid for both the old and new application code. No deploy is ever allowed to require that the database and the code change at the same instant, because they never will — rolling deploys mean both versions run concurrently for minutes.

A column rename, which looks trivial, is five steps:

1. Add the new column, nullable, no default that forces a table rewrite.
2. Deploy code that writes both columns and reads the old one.
3. Backfill the new column in batches, throttled, with a resumable cursor.
4. Deploy code that reads the new column and still writes both.
5. Deploy code that writes only the new column. Drop the old one in a later release, once you are confident you will not need to roll back.

Step five is the one teams skip, and it is the one that saves you. Keeping the old column around for a release costs disk. Dropping it in the same deploy that starts using the new one costs you the ability to roll back at all.

The Postgres operations that will bite you

• ADD COLUMN with a volatile default rewrites the table. A constant default is fine on modern Postgres; a now() or gen_random_uuid() default is not.
• Adding a NOT NULL constraint validates every row under an exclusive lock. Add a CHECK ... NOT VALID constraint, backfill, then VALIDATE CONSTRAINT — which takes a much weaker lock.
• CREATE INDEX locks writes. Always CREATE INDEX CONCURRENTLY, and always check pg_index.indisvalid afterwards, because a concurrent build can fail and leave an invalid index behind.
• Foreign keys should be added NOT VALID first, then validated separately.
• Set a lock_timeout on your migration connection. A migration that waits behind a long transaction will queue every other query behind it and take the service down without ever touching a row.

-- migration session defaults we set on every schema deploy
SET lock_timeout = '3s';
SET statement_timeout = '15min';

-- safe NOT NULL in two phases
ALTER TABLE orders
  ADD CONSTRAINT orders_tenant_id_not_null
  CHECK (tenant_id IS NOT NULL) NOT VALID;

-- ... backfill in batches, then:
ALTER TABLE orders VALIDATE CONSTRAINT orders_tenant_id_not_null;

A three-second lock_timeout means a blocked migration fails fast and gets retried, instead of becoming an incident. It is the single highest-value line in that block.

Moving a database while it is being written to

For a whole-database move — new cluster, new region, new provider, or off a platform you are decommissioning — the sequence is dull and it works:

1. Stand up the destination and replicate. Logical replication where the versions and schema allow it. Where they do not, an idempotent copier keyed on primary key plus updated_at, run to completion, then run again to catch what moved.
2. Turn on dual writes in the application, behind a flag, per tenant. The source stays authoritative. Destination write failures are logged loudly but do not fail the user's request — yet.
3. Reconcile. Compare row counts per table, checksums over ordered key ranges, and spot totals that a human would recognize: sum of order lines, open invoice balances, active user count.
4. Read from the destination for a small slice. Read-only endpoints first, one tenant, observe.
5. Flip authority per tenant. Destination becomes primary, source keeps receiving dual writes as a hot rollback path.
6. Stop writing to the source after a defined soak period. Snapshot it. Decommission.

When we retired a platform and moved its tenants, no cutover was allowed to proceed without a per-tenant reconciliation report that a person signed off. Not a green dashboard for the fleet — a document per tenant, listing counts and money totals on both sides with the deltas explicit. Where a delta existed, it had to be explained before the flip, not after.

A cutover you cannot describe in a one-page report per tenant is a cutover you do not understand.

Per-tenant staging is the reason this is survivable at all. If you run a database per tenant, a migration is a hundred small independent events, each with its own rollback. If you run one shared schema, the migration is a single event affecting everybody, and your only lever is how carefully you sequence expand-and-contract. Both are workable. The blast radius is not the same, and it is worth knowing which one you signed up for before the night of.

Dual writes are a lie you have to manage

Dual writing from the application is not atomic. Two writes to two systems in one request will diverge: the process dies between them, the destination times out, a retry duplicates. Anyone who tells you dual writes are consistent has not run them.

What makes them usable is accepting divergence and designing for detection:

• Make destination writes idempotent — natural keys or client-supplied idempotency keys, never auto-increment identity on the destination during the copy.
• Queue destination writes rather than performing them inline. An outbox row committed in the same transaction as the source write, drained by a worker, converges. An inline HTTP call does not.
• Run the reconciler continuously during the dual-write period, not once at the end. A drift of four rows found on day one is a bug you fix; the same drift found at cutover is a decision you have to make under pressure.
• Keep a per-tenant migration_state value — off, dual_write, dual_write_read_new, cutover, source_retired. Every code path branches on it. Nothing is inferred from config files.

Billing data deserves its own paragraph. Subscriptions, usage counters and AI spend wallets are the tables where a five-row delta is a customer refund and a support conversation. We reconcile money separately, with tighter tolerance — exact, not approximate — and we freeze plan changes for a tenant during its cutover window. It is a few minutes of degraded capability instead of a mid-cycle proration computed against two different sources of truth.

When a maintenance window is the right answer

This advice has limits, and pretending otherwise is how people end up doing three weeks of dual-write engineering to avoid a nine-minute outage.

Take the window when all of these are true: the tenant has a genuine closed period (a single-region B2B tool with no weekend users), the dataset is small enough that the copy finishes inside the window with room to spare, and the change is genuinely hard to do incrementally — a primary key type change, a tenancy model rewrite, a move to a database engine with different semantics. A 30-minute scheduled stop, announced a week ahead, with a tested rollback, is cheaper and safer than a two-week dual-write project.

Take the window also when the alternative is unmeasurable. If you cannot write a reconciliation query that would prove the two sides agree, you do not have a zero-downtime plan; you have optimism with extra moving parts.

Where the window stops being an option is offline-capable clients. Our offline-first point of sale accepts sales with no connection and reconciles when it returns, which means a device can hand you a write created 40 minutes before your cutover and delivered 10 minutes after it. Your destination has to accept that write, deduplicate it, and route it to the right side of the flip. Client-generated IDs and server-side idempotency keys are not a nice-to-have there; they are the only thing that makes the flip safe.

What to build before you need it

Most migration pain is missing infrastructure, not missing skill. The things that turn a scary migration into a boring one are all buildable on a normal Tuesday:

• A batched, resumable backfill runner with a stored cursor, a rate limit, and a kill switch. Not a one-off script in someone's terminal.
• A reconciliation harness that takes two connection strings and a table list and emits per-tenant deltas as an artifact you can attach to a ticket.
• Per-tenant flags so migration state is data, not deploys. This is the same machinery as entitlement and feature gating, and it should be the same system.
• An admin console your support team can safely use to see which tenant is in which migration state, and to pause a tenant's cutover without an engineer.
• A restore drill you have actually performed. Backups you have never restored are a feeling, not a plan.

Across our own products and the client platforms we run — 7,489+ services in production against 700+ tenant databases — the migrations that went badly were never the ones with the hardest SQL. They were the ones where a single deploy had to land at the same moment as a single ALTER, and where nobody could answer the question "how would we know if this went wrong?" until it already had. If you want the whole sequence walked through against your own schema, tell us what you're running and we'll go through it.