Running Database Migrations Without Taking Your SaaS Down

Every backend engineer eventually learns that "just add a column" can take a production database down for twenty minutes. Locks, blocked writes, exhausted connections — the failure modes look identical to a full outage from the customer's side. Zero-downtime migrations aren't magic, but they take discipline. This is what we've learned running them across a lot of client projects.

Why simple migrations aren't always simple

The three ways a migration takes down production:

Long-held locks

An ALTER TABLE that rewrites the table takes an exclusive lock. On a large table, that lock is held for the entire rewrite, blocking every read and write. On a busy app, twenty seconds of blocked writes is enough to overflow connection pools and cause a cascade.

Lock queues

Even a fast lock can pause everything if there's a slow query holding a competing lock. Your migration waits behind that query, and every new query behind your migration. Postgres and MySQL will happily queue thousands of blocked transactions until connections run out.

Backfills that never finish

Adding a column with a computed default forces the database to rewrite every row. On a 100M row table, that's hours. In a single transaction, that's a permanent lock. Migrations that seem to hang are usually this.

The expand-and-contract pattern

The single most important idea in zero-downtime migrations. Instead of one atomic change, split it into three deploys.

1. Expand — add the new column, table, or index. Keep the old one working.
2. Migrate — write to both, backfill data, verify.
3. Contract — remove the old column, table, or index.

Each step is safe on its own. If any deploy needs to roll back, the previous state still works. This is slower than a single-deploy migration but survives production reality.

Specific patterns for common changes

Adding a nullable column

Safe in Postgres 11+ and MySQL 8+ without a rewrite, as long as the default is NULL or a constant. Fast metadata-only change.

ALTER TABLE users ADD COLUMN preferred_language TEXT;

Rewrite is triggered by volatile defaults (like gen_random_uuid()) or by NOT NULL without a default. Avoid those in a single ALTER on large tables.

Adding a NOT NULL column with a default value

The classic footgun. Do it in three steps:

1. Add the column as nullable with no default.
2. Backfill in batches: UPDATE users SET flag = false WHERE flag IS NULL LIMIT 5000; repeated.
3. Add the NOT NULL constraint after backfill completes.

Changing a column type

Never change a column type in place on a large table. Instead:

1. Add a new column with the target type.
2. Backfill in batches, converting values.
3. Update the app to write both columns and prefer the new one on reads.
4. Verify.
5. Drop the old column.

Renaming a column

The pattern rarely worth the effort. Rename requires application coordination — every deployed instance of the app needs to know both names during the transition. Simpler path: keep the old name.

If you must rename, use the double-write pattern: add new column, write to both, backfill, switch reads, drop old.

Adding an index

Always CREATE INDEX CONCURRENTLY in Postgres. Never a plain CREATE INDEX on a busy table — it takes a lock that blocks writes for the duration of the build. Concurrent creates take longer but don't block.

MySQL 5.6+ supports online DDL for most index operations. Confirm the operation is "In Place, Instant" or "Online" before running.

Dropping a column

Fast in Postgres (metadata-only) but risky. If the app still references the column, dropping breaks queries immediately. Two-step:

1. Deploy the app version that no longer reads or writes the column.
2. Wait until you're sure no old instances are running (24-48 hours typical).
3. Drop the column.

Foreign key constraints

Adding a foreign key on a large table takes a table lock in older Postgres and long lock waits in others. Use NOT VALID to add the constraint without checking existing rows, then VALIDATE CONSTRAINT in a separate transaction.

ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

Backfilling large tables safely

The other place migrations go wrong. Never backfill a large table in a single UPDATE.

Batch by primary key

UPDATE users
SET migrated_flag = compute_value(users)
WHERE id BETWEEN $start AND $end
AND migrated_flag IS NULL;

Iterate through the ID range in chunks of 1,000-10,000 rows. Between batches, sleep briefly to let replication catch up and to give the database breathing room.

Watch replication lag

Large batches on the primary create large writes that replicas have to catch up on. If replicas fall behind, reads served from replicas return stale data. Monitor lag and slow down when it grows.

Run backfills from a background job

Not from the deploy pipeline. Backfills can take hours or days. A deploy that runs one is a deploy that blocks on it. Trigger the backfill from an admin action or a background worker.

Statement timeout — the safety net

Set a statement timeout on every migration. If a query takes longer than expected, it aborts instead of holding a lock indefinitely.

SET LOCAL statement_timeout = '30s';
SET LOCAL lock_timeout = '2s';
-- migration here

A 2-second lock timeout means your migration fails fast if it can't acquire the lock, rather than queuing behind a long-running query. Retry with backoff.

Tools that help

Don't write raw migrations for every project. Frameworks do a lot of the safety work.

• pg_squawk / squawk — lints Postgres migrations for known unsafe patterns.
• Prisma, Drizzle, Kysely — TypeScript migration tools with reasonable defaults.
• gh-ost / pt-online-schema-change — for MySQL, run alters on a shadow table and swap.
• Reshape, Xata — attempts at fully zero-downtime schema tooling.

These aren't substitutes for understanding what the database is actually doing. They're guardrails.

Common mistakes

Running migrations during peak traffic. The failure modes are worse under load. Run migrations during your quietest window.

No lock timeout. A migration without a lock timeout can freeze the app for the duration of an unrelated slow query. Two-second lock timeout, always.

Migrations inside long-running transactions. The transaction holds locks longer than the operation needs them. Break migrations into small transactions.

Rolling back schema changes. Rollbacks look attractive on paper but often make the situation worse — the app has already written to the new schema. Roll forward: fix and reapply, don't roll back.

Not testing on a production-sized dataset. A migration that takes 200ms on staging can take 20 minutes on production. Test against a copy of production data or accept the risk.

Trade-offs

Zero-downtime migrations are slower to write. A single-deploy migration takes an hour; the expand-migrate-contract pattern takes a week and three deploys. For high-traffic products, the safety is worth it. For a product with 10 active users during a maintenance window, an announced downtime might be simpler.

The pattern also complicates the codebase temporarily. Between expand and contract, the app has to handle both the old and new schemas. That's real code, real tests, and real risk of confusion. Keeping the migration window short reduces the exposure.

Common misconceptions

"ORMs handle migrations safely." They handle the SQL generation. They don't stop you from writing an unsafe migration. Every serious project needs a human to review the generated SQL.

"Adding a column is always instant." True in some cases, false in others. Nullable columns are fast. NOT NULL with default triggers a rewrite in older Postgres versions.

"Migrations are a solved problem in cloud databases." Not really. Managed Postgres services still run standard Postgres. The same lock and rewrite behavior applies. Some services (like Xata) do more, but they're the exception.

Frequently asked questions

Final take

Safe database migrations are a discipline, not a feature. Expand-and-contract, batched backfills, short transactions, lock timeouts, and human review of every migration will get you most of the way there. Testing on production-sized data covers the rest. The alternative — hoping a migration finishes before the timeout — is how outages happen.

If you're scoping a migration on a large table and want a review before you run it, book a call. We've done a lot of these and the failure modes are predictable.