Migrating Schemas Without Stopping the World

By NTh Hai - 7 min read

Schema changes look trivial in development: drop a column, rename a table, ship it. In production with continuous traffic, large tables, and multiple app versions during a rolling deploy, the same change can lock tables, break old pods, or corrupt data halfway through a backfill.

Solution architecture needs a migration strategy as deliberate as the schema itself.

Why Big-Bang Migrations Fail

A single deploy that:

  1. Changes the schema incompatibly
  2. Deploys new code that requires the new schema
  3. Assumes all instances flip at once

…collides with reality: rolling deploys, read replicas lag, long-running transactions, and human pause buttons. Zero-downtime evolution means old and new code must coexist for a while.

Expand / Contract

The standard pattern:

Expand. Add new structures without removing old ones. Nullable columns, new tables, new indexes created concurrently where the engine allows.

Migrate. Dual-write or backfill so both representations stay correct. Read from the old path until data is ready, then switch reads.

Contract. After old code is gone and data is verified, remove obsolete columns/tables in a later release.

This takes more PRs than a heroic weekend migration. It also lets you sleep.

Dual Writes and Backfills

Dual write: application writes both old and new fields during the transition. Simple for low-volume paths; beware partial failure — wrap in a transaction when both targets are the same database.

Backfill: batch job copies historical rows. Essentials:

  • Chunked updates to avoid long locks
  • Idempotent processing (safe to rerun)
  • Progress metrics and a kill switch
  • Verification queries (counts, checksums, sampled diffs)

Never backfill with an untested script on the primary during peak traffic without a rehearsal on a production-sized copy.

Index and Lock Awareness

On large tables:

  • Prefer online/concurrent index creation when available
  • Avoid rewriting the whole table for a default value when a backfill can do it gradually
  • Know your engine’s lock behavior for ALTER TABLE

A migration that is “correct” but holds an exclusive lock for twenty minutes is an outage with extra steps.

Multi-Service and Event Schemas

If other services consume your events or shared DB (avoid shared DB if you can):

  • Add fields as optional first
  • Version event payloads
  • Keep consumers tolerant of unknown fields
  • Coordinate contract tests across teams

Database migrations and API/event contracts are the same problem in different jackets.

Rollback Is a Feature

Before you expand:

  • Can you roll back the app without rolling back the schema?
  • Are expand migrations backward compatible with the previous app version?
  • Do you have backups / point-in-time recovery tested, not theoretical?

Contract steps should be delayed until you are confident you will not need the old columns for an emergency hotfix.

A Minimal Checklist for Every Migration PR

  1. Is this expand, migrate, or contract?
  2. What runs during rolling deploy with mixed versions?
  3. How long could locks last on production scale?
  4. How do we verify data after backfill?
  5. What is the rollback path?

If any answer is fuzzy, the architecture is not ready to ship the change.

Closing Thought

Databases are living systems under continuous load. Evolving them is less about clever SQL and more about compatible steps, verifiable backfills, and respect for locks. Architect migrations the way you architect features — incrementally, observably, and with a way out.