Design Database Migrations You Can Survive in Production

A failed app deploy is annoying. A failed database migration can turn a 5-minute release into a 5-hour incident, because the code can roll back while the data shape cannot.

That is the uncomfortable truth most teams learn only after a table rewrite, a blocked write path, or a replication lag spike takes production sideways. If your deployment process treats schema changes like just another CI step, you are betting your uptime on the least reversible part of the stack.

Treat schema changes as one-way doors, not routine deploy tasks

Application artifacts are cheap to replace. Database state is not. Once you drop a column, backfill bad values, or rewrite a 2 TB table, you cannot rely on a simple rollback button.

The safest mental model is this: database migrations are forward-only operational events. Your design goal is not "how do we undo this?" but "how do we make the next step safe even if the previous one partially succeeded?"

Why rollbacks fail in practice

Three things usually break the rollback fantasy:

• Data loss: Dropping customer_tier after moving to plan_code means old code has nowhere to read from.
• Long-running locks: An ALTER TABLE on a hot PostgreSQL 17 table can block writes long enough to trigger API timeouts under load.
• Version skew: During a rolling deploy on Kubernetes, old and new app pods often run together for 5-20 minutes. If only one version understands the new schema, one side fails.

A common scenario looks like this:

1. You deploy app version 2026.03.18.
2. The release job runs a migration that renames a column.
3. 30% of pods still run the previous version during the rollout window.
4. Old pods start throwing column not found errors.
5. You roll back the app, but the renamed column stays renamed.

That is why mature teams separate code deployment, schema expansion, data migration, and schema contraction into distinct steps.

The 2026 baseline: zero-downtime means compatibility across versions

By 2026, most enterprise stacks already support rolling deploys, canary releases, and progressive delivery. The database side still lags because teams assume ACID semantics equal deployment safety. They do not.

If your service deploys across 200 pods over 12 minutes, your schema must support:

• old code reading and writing
• new code reading and writing
• replication catching up under peak load
• background backfills without saturating I/O

That is the real bar for zero-downtime migrations.

Use the expand-migrate-contract pattern to remove rollback risk

The most reliable approach is expand, migrate, contract. It trades speed for safety, which is usually the right trade in production.

Phase 1: Expand without breaking existing code

Add new structures first. Do not remove or rename anything yet.

Examples:

• Add nullable columns instead of renaming old ones
• Create new indexes concurrently
• Add new tables for reshaped entities
• Introduce feature flags before switching write paths

For PostgreSQL, a safe first migration often looks like this:

BEGIN;
ALTER TABLE orders ADD COLUMN fulfillment_status_v2 TEXT;
ALTER TABLE orders ADD COLUMN fulfilled_at TIMESTAMPTZ;
COMMIT;

CREATE INDEX CONCURRENTLY idx_orders_fulfillment_status_v2 ON orders(fulfillment_status_v2);

This does two useful things:

• It preserves compatibility with the current application version.
• It gives the next app release a place to write new data without forcing an immediate cutover.

For MySQL 9.0-compatible environments, use online DDL options where supported, but validate behavior per engine and cloud provider. "Online" still has edge cases around metadata locks and replication pressure.

Phase 2: Dual write and backfill deliberately

Once the schema can hold both shapes, deploy application code that writes to both old and new fields. Read from the old field first until the backfill is complete and verified.

A realistic application toggle might look like this:

migrationFlags:
  ordersFulfillmentV2:
    dualWrite: true
    readPreference: legacy
    backfillBatchSize: 5000
    throttleMs: 250

Then backfill in controlled batches. On a table with 180 million rows, a single UPDATE statement is not a migration plan. It is an outage plan.

Use chunking by primary key or time window:

last_id = load_checkpoint()
while True:
    rows = db.fetch_all("SELECT id, status, shipped_at FROM orders WHERE id > %s ORDER BY id ASC LIMIT 5000", [last_id])
    if not rows:
        break
    for row in rows:
        new_status = map_status(row["status"], row["shipped_at"])
        db.execute("UPDATE orders SET fulfillment_status_v2 = %s, fulfilled_at = %s WHERE id = %s", [new_status, row["shipped_at"], row["id"]])
        last_id = row["id"]
    save_checkpoint(last_id)
    sleep(0.25)

In production, teams often target backfill jobs to consume no more than 10-15% of baseline write IOPS and keep replica lag under 2-5 seconds. If your read replicas normally sit at 150 ms lag and jump to 40 seconds during migration, you are already outside a safe envelope for many systems.

Phase 3: Switch reads, then contract later

After validation, switch reads to the new field. Keep dual writes for one more release cycle. Only then remove the old field.

This delayed contraction is where you buy operational safety. If a hidden dependency still reads the old column, you discover it before deletion.

A practical rollout sequence:

1. Release A: add new columns and index
2. Release B: dual write, read old
3. Backfill and validate row counts, null rates, checksums
4. Release C: dual write, read new
5. Observe for 7-14 days
6. Release D: stop writing old
7. Release E: drop old column

That sounds slow. It is still faster than a major incident review.

Engineer migrations for observability, throttling, and failure recovery

The difference between a controlled migration and a production incident is often not SQL quality. It is operational design.

Add migration-specific telemetry

You need metrics that answer four questions in under a minute:

• Is the migration progressing?
• Is it harming production latency?
• Are replicas staying healthy?
• Can both app versions still function?

Track at least:

• rows processed per minute
• lock wait time
• deadlocks per minute
• replication lag by replica
• p95 and p99 write latency
• error rate by app version
• percentage of dual-write mismatches

A simple dashboard target for a high-traffic service might be:

• API write p95 stays below 180 ms
• database CPU stays below 70%
• replication lag stays below 3 seconds
• migration progress stays above 250k rows/hour

If one metric crosses threshold, the migration should throttle or pause automatically.

Build pause and resume into the process

Never design a backfill that only works if it runs to completion. Production always interrupts your ideal plan.

Use checkpoints, idempotent updates, and a control table:

CREATE TABLE IF NOT EXISTS migration_control (
  migration_name TEXT PRIMARY KEY,
  last_processed_id BIGINT,
  status TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

This lets you pause on elevated latency, resume after a maintenance event, and prove where the job stopped.

Separate DDL risk from data movement risk

DDL and data backfills fail differently. Treat them as separate concerns.

• DDL risk: locks, table rewrites, metadata contention
• Data movement risk: I/O saturation, replica lag, application inconsistency

For example, creating an index concurrently on PostgreSQL 17 may take 45 minutes on a 600 GB table but keep writes flowing. A backfill over the same table may finish in 8 hours with low lock risk but high I/O pressure. You monitor and gate those steps differently.

Choose tooling that matches your engine and traffic profile

No migration tool makes an unsafe migration safe. Good tools make unsafe plans more visible and safe plans easier to execute.

Practical tool choices in 2026

Common enterprise choices include:

• Flyway for versioned SQL migrations and governance
• Liquibase for change tracking and policy-heavy environments
• Atlas for schema-as-code workflows
• gh-ost for online MySQL table changes
• pt-online-schema-change for MySQL environments that still need mature online DDL support
• Skeema for MySQL schema review workflows

For PostgreSQL-heavy teams, native capabilities plus careful rollout logic often outperform adding another abstraction layer. For MySQL fleets, online schema change tools remain relevant because provider behavior still varies across managed offerings.

A Flyway example that marks an expansion migration separately from a destructive contraction:

flyway.locations=filesystem:sql
flyway.validateMigrationNaming=true
flyway.outOfOrder=false
flyway.placeholders.environment=prod
flyway.ignoreMigrationPatterns=*:pending

And a release policy worth enforcing in CI:

#!/usr/bin/env bash
set -euo pipefail
if grep -R "DROP COLUMN\|RENAME COLUMN\|ALTER COLUMN .* TYPE" sql/V*.sql; then
  echo "Destructive migration detected. Require architecture review and phased rollout plan."
  exit 1
fi

This kind of guardrail catches the migration that looked harmless in staging and dangerous in production.

Test against production characteristics, not toy datasets

A migration that succeeds on 5 million rows may collapse on 500 million. Cardinality, index bloat, and hot partitions matter.

At minimum, rehearse on:

• a production-like row count
• realistic write concurrency
• replica topology that matches production
• the same engine major version and parameter group

If your staging database is 1% of production size, use sampled clones or masked snapshots for migration rehearsal. In 2026, storage-efficient snapshotting in major cloud platforms makes this far cheaper than it was a few years ago.

Common Pitfalls

Teams rarely fail because they forgot SQL syntax. They fail because they made one of a handful of repeatable operational mistakes.

Renaming instead of adding

RENAME COLUMN feels tidy. It is hostile to rolling deploys.

Avoid it by adding the new column, dual writing, and deleting the old one later. The temporary mess is cheaper than version skew failures.

Backfilling in one transaction

A single giant transaction increases WAL or binlog volume, bloats storage, and makes failure recovery painful. On busy systems, it can also degrade vacuum or purge behavior for hours.

Use bounded batches with checkpoints. Aim for predictable progress, not theoretical maximum throughput.

Assuming online DDL means no user impact

Online schema operations still consume CPU, I/O, and internal locks. A concurrent index build can push a database from 45% CPU to 78% CPU and lift p99 write latency from 120 ms to 340 ms during peak traffic.

Schedule high-cost steps outside peak windows and predefine stop thresholds.

Dropping old columns too early

The old reporting job, ETL pipeline, or fraud model is often the last hidden dependency. If you drop a field the week you switch reads, you are testing your observability more than your design.

Wait at least one full business cycle for systems with weekly or monthly jobs.

Forgetting non-application consumers

Your app is not the only schema client. Also inventory:

• BI dashboards
• CDC pipelines
• search indexers
• ML feature jobs
• partner exports
• audit and compliance tooling

A migration is complete only when all consumers are compatible.

Key Takeaways

• Treat every production schema change as a forward-only event, not a rollback-friendly deploy step.
• Use expand-migrate-contract: add first, dual write second, switch reads third, delete last.
• Design backfills to pause, resume, and throttle based on replica lag and latency thresholds.
• Block destructive DDL in CI unless the change has a phased rollout plan and owner approval.
• Test migrations on production-like data volume and concurrency, not tiny staging datasets.
• Keep old schema paths alive long enough to cover rolling deploy windows and hidden downstream consumers.

This article was written by an AI system and published pending human review. Verify anything you intend to act on.