The Deploy That Froze Everything

It usually starts the same way. A developer adds a column, writes a migration script, and pushes it through the deploy pipeline. In staging, the table has 200 rows and the migration finishes in milliseconds. In production, the table has 14 million rows. The ALTER TABLE statement grabs a metadata lock, every query behind it starts queuing, and the application grinds to a halt for 40 minutes. By the time the migration completes, the on-call engineer has aged a year and the incident channel is on fire.

If your team ships daily — or wants to — this is not an edge case. It is the inevitable result of treating database schema changes the same way you treat application code deploys. The database is stateful, the application is (mostly) not, and the strategies that work for one will break the other. A solid database migration strategy is foundational work that pays dividends on every future deploy.

Why Traditional Migrations Break Under Load

Most ORMs and migration frameworks generate DDL statements that are structurally correct but operationally dangerous at scale. An ALTER TABLE ... ADD COLUMN in MySQL (InnoDB) before version 8.0 would rebuild the entire table. Even with instant DDL in newer versions, adding a column with a default value or changing a column type can still require a full table copy. PostgreSQL handles many ALTER operations without a rewrite, but acquiring an ACCESS EXCLUSIVE lock — even briefly — can create a pileup of blocked queries that cascades into visible downtime.

The root issue is that schema changes and data changes are fundamentally different operations, but teams often run them in the same transaction, at deploy time, with the application waiting. This coupling is the design flaw. Fixing it means separating when the schema changes from when the application starts using the new schema.

The Expand and Contract Pattern

The expand and contract pattern is the most reliable framework for zero-downtime database migration. It breaks every schema change into at least two phases, and often three. Treating migrations as phased work instead of atomic events is the single biggest shift a team can make.

Phase 1: Expand

Add the new structure alongside the old one. If you are renaming a column, add the new column without removing the old one. If you are splitting a table, create the new table and begin dual-writing. The critical rule: the existing application code does not need to change yet. The old schema still works exactly as before.

In practice, this means your migration script for this phase only adds — it never removes, renames destructively, or changes types in place. Adding a nullable column with no default is one of the cheapest operations in both PostgreSQL and MySQL. It typically requires only a brief metadata lock, not a table rewrite.

Phase 2: Migrate (Code and Data)

Deploy application code that writes to both the old and new structures. Backfill the new column or table with historical data. This backfill runs as a background process — batched, throttled, and monitored — not as part of a deploy transaction.

This is where most of the engineering care lives. A backfill that updates 10 million rows in a single UPDATE statement will lock the table just as badly as a naive ALTER. Instead, process rows in small batches (1,000 to 10,000 rows at a time), with brief pauses between batches to let replication catch up and to avoid saturating I/O. Track progress in a separate state table so the process is resumable if interrupted.

Phase 3: Contract

Once all data is migrated and the application reads exclusively from the new structure, remove the old column or table in a subsequent deploy. This cleanup migration is low-risk because nothing depends on the old structure anymore. The contract phase is where you earn back the schema simplicity — but only after the transition is fully verified.

The discipline here matters: do not skip the contract phase. Leaving ghost columns and abandoned tables in your schema creates confusion, bloats backups, and makes the next migration harder to reason about.

Online Schema Change Tools

For changes that genuinely require a table rewrite — changing a column from INT to BIGINT, for instance, before your auto-incrementing primary key overflows — the expand and contract pattern alone is not enough. You need an online schema change tool that performs the rewrite without holding a lock for the duration.

How They Work

Tools like pt-online-schema-change (Percona Toolkit, for MySQL) and pg_repack (for PostgreSQL) follow a similar strategy internally:

1. Create a new table with the desired schema.
2. Set up triggers (or logical replication) to capture ongoing writes to the original table.
3. Copy rows from the original table to the new table in batches.
4. Once the copy is complete and the new table is caught up, atomically swap the tables by renaming them.

The atomic swap still requires a brief lock, but it lasts milliseconds, not minutes. The bulk of the work — copying millions of rows — happens in the background while the application continues reading and writing normally.

GitHub's gh-ost is another option for MySQL that avoids trigger-based replication in favor of reading the binary log, which reduces contention on the original table. Each tool has trade-offs in complexity, replication compatibility, and foreign key support. Choosing the right one depends on your engine version, replication topology, and whether your schema uses foreign keys heavily.

When Not to Use Them

Not every migration needs an online schema change tool. Adding a nullable column, creating an index with CREATE INDEX CONCURRENTLY (PostgreSQL), or dropping an unused column are operations that can often be done safely with careful planning and short lock timeouts. The overhead of setting up and monitoring an online schema change is real — use it for operations that genuinely require a table rewrite, not for every migration out of habit.

Designing Your Pipeline for Schema Safety

Zero-downtime database migration is not just a technique — it is a design decision that shapes your entire CI/CD pipeline. Here is how to engineer the process so it holds under daily shipping cadence.

Decouple Schema Deploys from Application Deploys

Run migrations as a separate, controlled step — not embedded inside your application startup. If your app runs migrations on boot, a rolling deploy means multiple application instances competing to run the same migration simultaneously, or one instance holding a lock while others time out trying to start. Separating the migration step into its own pipeline stage gives you explicit control over timing, monitoring, and rollback.

Set Lock Timeouts Aggressively

In PostgreSQL, set lock_timeout to a low value (one to five seconds) for migration sessions. If the migration cannot acquire its lock within that window, it fails fast instead of queuing behind long-running queries — or worse, causing long-running queries to queue behind it. A failed migration that you retry in five minutes is far less damaging than a migration that blocks production traffic for an unpredictable duration.

Use Advisory Locks or Migration State Tables

Ensure only one migration process runs at a time. Most migration frameworks handle this, but verify it under the actual conditions of your deploy environment — containerized deploys, autoscaling groups, and blue-green environments all introduce concurrency that a local test will not surface.

Test Against Production-Scale Data

A migration that takes 3 milliseconds on a table with 500 rows will behave completely differently on a table with 50 million rows. If you cannot test against a full production-size dataset, at least test against a table with representative row counts and index configurations. The difference between a metadata-only change and a full table rewrite is invisible in a small dataset and catastrophic in a large one.

Build Observability Into the Migration Process

Instrument your migration pipeline the same way you instrument your application. Track migration duration, lock wait time, replication lag (if applicable), and row-processing rate for backfills. An observability stack that surfaces these metrics before users feel the impact is the difference between a controlled migration and a surprise outage.

Common Mistakes That Undo Good Strategy

Even teams that understand the expand and contract pattern make mistakes that reintroduce risk.

Running backfills during peak traffic. Schedule data migrations during low-traffic windows. This is not the same as scheduling downtime — the application stays up — but it reduces the I/O contention that can slow both the backfill and normal queries.

Forgetting about read replicas. A schema change on the primary propagates to replicas through replication. If the change triggers a table rewrite on the replica, replication lag spikes and the replica becomes stale. Online schema change tools need to be replica-aware, and your pipeline needs to monitor replication lag as a migration health signal.

Adding NOT NULL constraints too early. Adding a NOT NULL constraint with a default value can trigger a table rewrite in some database versions. Add the column as nullable first, backfill, then add the constraint in a separate migration once all rows have values.

Skipping rollback planning. The expand phase is inherently rollback-safe — the old schema still works. But the contract phase is destructive. Before dropping the old structure, verify that no queries, background jobs, or reporting tools still reference it. A column that nobody reads in the application code might still be referenced by an analytics query running on a read replica.

The Bigger Picture: Migrations as Infrastructure

Teams that ship daily treat database schema changes as infrastructure work, not application work. The migration is designed, reviewed, and executed with the same rigor as a networking change or a security policy update. It is version-controlled, reproducible through infrastructure as code practices, tested at scale, and observable in production.

This is soil work — the foundational engineering that nobody sees in a demo but everyone feels when it is missing. A team that has built this discipline deploys without flinching. A team that has not lives in fear of the next ALTER TABLE.

Getting this right does not require a massive re-architecture. It requires a clear pattern (expand and contract), the right tools for your database engine, and a pipeline that treats schema changes as first-class deployment events. The investment pays for itself the first time a migration runs against a 50-million-row table and nobody notices.

Build the Foundation Before the Next Migration Breaks

If your team is shipping daily — or trying to — and database migrations still feel like a game of roulette, the problem is not the migration. It is the absence of a scalable process underneath it. At Figtree Development, we architect CI/CD pipelines and database migration strategies designed for teams that need to move fast on infrastructure that does not crack under pressure. We have spent over 15 years engineering environments where deploys are boring and predictable, not heroic and stressful.

If you want to talk through your specific situation — table sizes, deploy cadence, database engine — we are happy to dig into the details. Book a free 20-minute discovery call and let us look at what is slowing you down.