The Deploy That Takes Down the Whole App

It usually starts small. A product manager requests a new column. An engineer writes the migration, tests it against a dev database with twelve rows, and merges the pull request. Friday afternoon, the deployment runs against production — where that table holds forty million rows. The migration locks the table for nine minutes. The application queue backs up. Health checks fail. The load balancer pulls every pod out of rotation. Customers see a white screen.

The postmortem identifies the root cause as a schema migration that acquired a table-level lock in production. The proposed remedy: a maintenance window, scheduled for 2 AM on a Sunday. And just like that, a team that ships daily is now coordinating deployments around a calendar invite.

This is not a tooling problem. It is an architectural one. And solving it requires understanding how databases actually acquire locks, how application code interacts with schema state, and how to design migrations that never assume the luxury of downtime.

Why Traditional Migrations Break Under Load

Most migration frameworks — whether you are using Rails, Django, Flyway, Liquibase, or raw SQL scripts — operate on a simple model: run a DDL statement, wait for it to finish, move on. That model works fine when the table is small or the database is idle. It falls apart in production for two reasons.

Lock Contention

In PostgreSQL, an ALTER TABLE ... ADD COLUMN with a default value (prior to version 11) acquires an ACCESS EXCLUSIVE lock. In MySQL, many ALTER TABLE operations trigger a full table copy internally. While that lock is held, every read and write against the table blocks. The duration scales with table size. On a table with tens of millions of rows, that can mean minutes of unavailability — not seconds.

Coupling Between Schema and Application State

A migration that adds a required column, removes a column, or renames a column assumes that every running instance of the application understands the new schema immediately. In any real deployment — rolling updates, blue-green, canary — old and new application code run simultaneously. If the old code tries to insert a row without the new required column, or references a column that no longer exists, queries fail. The deploy itself becomes the outage.

These two failure modes are distinct, and each demands its own solution.

The Expand and Contract Migration Pattern

The most reliable approach to zero-downtime database migration is the expand and contract pattern. The core idea: never make a destructive or incompatible schema change in a single step. Instead, break every migration into phases that maintain backward compatibility throughout.

Phase 1: Expand

Add the new structure alongside the old one. This means adding a new column (nullable, no default constraint enforced at the database level), creating a new table, or building a new index concurrently. The critical constraint: the existing application code must continue to function without any awareness of the new structure. Nothing breaks. Nothing locks for more than milliseconds.

Phase 2: Migrate Data

Backfill existing rows to populate the new column or table. This is done as a background process — batched updates, throttled to avoid saturating I/O or spiking replication lag. This phase can take hours on large tables, and that is fine. The application is live the entire time. Writes to the old structure continue. A dual-write layer (either in application code or via database triggers) ensures new data lands in both old and new locations.

Phase 3: Transition Application Code

Deploy updated application code that reads from the new structure and writes to both old and new. This is a normal rolling deployment. Old instances still write to the old structure. New instances write to both. Reads gradually shift. Once every instance is running the new code, you move to the next phase.

Phase 4: Contract

Remove the old structure. Drop the column, remove the trigger, delete the legacy table. This is a separate deploy, days or even weeks later, once you have confirmed the new structure is correct, backfills are complete, and no application path references the old schema.

Each phase is a separate, independently deployable change. Each phase is safe to roll back. The migration might span three or four pull requests instead of one. That is the point.

Online Schema Change Tools: When the Database Engine Needs Help

Some schema changes — particularly on MySQL before version 8.0, or on very large PostgreSQL tables — cannot be done without additional tooling, even with the expand and contract approach. This is where online schema change tools earn their place.

gh-ost (developed for MySQL) works by creating a shadow copy of the table, applying the schema change to the copy, then streaming binlog events to keep the copy synchronized with the original. Once the copy is caught up, it performs an atomic rename. The original table is never locked for more than a brief cutover window — typically under a second.

pt-online-schema-change (Percona Toolkit) takes a similar approach using triggers instead of binlog streaming. It is more battle-tested on older MySQL versions but introduces trigger overhead during the migration.

pg_repack and tools built on PostgreSQL's logical replication can achieve similar results for Postgres environments, rebuilding tables without holding exclusive locks for the duration.

These tools are not magic. They add operational complexity, increase disk I/O during the migration, and require careful monitoring of replication lag. But for teams shipping daily against large datasets, they are foundational infrastructure — not optional extras.

Concrete Patterns That Hold Under Pressure

Adding a Column

Add the column as nullable with no default. Deploy application code that handles the null case. Backfill in batches. Once backfill is complete, deploy code that treats the column as authoritative. Optionally add a NOT NULL constraint later (in PostgreSQL 12+, this is near-instant if a default is set; in older versions, it triggers a full table scan).

Renaming a Column

Never rename directly. Add the new column. Dual-write to both. Backfill. Shift reads to the new column. Drop the old column in a later deploy. A direct ALTER TABLE ... RENAME COLUMN will break every query that references the old name while old application instances are still running.

Changing a Column Type

Same discipline. Add a new column with the target type. Dual-write with a conversion function. Backfill and convert existing data. Transition reads. Drop the old column. Attempting an in-place type change on a large table will lock it for the duration of the rewrite.

Removing a Column

First, deploy code that no longer references the column. Confirm no query plan, no ORM mapping, no reporting job touches it. Then drop it. If you drop first and deploy second, you will see errors from every instance still running old code — even if that window is only thirty seconds during a rolling update.

Adding an Index

In PostgreSQL, use CREATE INDEX CONCURRENTLY. It takes longer but does not lock the table for writes. In MySQL, most index additions in recent InnoDB versions are online, but verify with your specific version and table engine. Always test index creation time against a production-sized dataset before deploying.

The CI/CD Pipeline as a Migration Safety Net

Zero-downtime database migrations are not just a schema design problem. They require pipeline infrastructure that supports multi-phase rollouts.

A well-designed CI/CD pipeline for this workflow separates migration execution from application deployment. Migrations run as a distinct pipeline stage — before the new application code deploys, not bundled inside it. This means the migration must be backward-compatible with the currently running code. If it is not, the design is wrong, and the pipeline should fail the build.

Automated testing against a staging database that mirrors production table sizes catches lock duration issues before they reach production. This is not about running migrations against an empty test database. It is about running them against a dataset large enough to expose real timing behavior. A migration that completes in 200 milliseconds on an empty table and takes eight minutes on a full one is not a passing test — it is a ticking incident.

Observability stacks that surface replication lag, lock wait times, and query latency during migration execution give engineering teams the signal they need to pause or abort a migration mid-flight. Without that instrumentation, you are flying blind.

Trade-Offs Worth Naming

The expand and contract pattern is not free. It trades speed of implementation for safety. A migration that could be one SQL statement becomes three or four coordinated changes across multiple deploys. That is more pull requests, more code review, more coordination.

For small teams shipping fast, that overhead can feel heavy. The question is whether you can afford the alternative. A single locked table in production — even for two minutes — can cascade into a full outage if your connection pool saturates, your health checks fail, and your orchestration layer starts killing pods. The cost of one incident like that, measured in customer trust and engineering hours spent in a war room, dwarfs the cost of writing migrations carefully.

There is also a cultural dimension. Teams that adopt this pattern internalize a principle: the database schema is a shared, versioned, contractual surface — as important as the API contract. Treating it that way changes how engineers think about backward compatibility, deployment ordering, and the boundary between application logic and data structure.

When the Foundation Is Right, Everything Else Gets Easier

Zero-downtime database migrations are one expression of a broader principle: infrastructure should never be the reason a team cannot ship. When the soil work is done — when the deployment pipeline, the schema change process, and the observability layer are designed correctly — the team focuses on building features, not coordinating maintenance windows.

This is the kind of foundational engineering that does not make headlines but determines whether a product can scale without cracking. It is architecture that holds under real pressure, not just during a demo.

At Figtree Development, this is the work we do. We design and build cloud infrastructure and CI/CD pipelines for teams that need to ship daily without holding their breath. If your deploys are bottlenecked by schema changes, if your team is scheduling maintenance windows instead of shipping features, or if your migration process is one bad Friday away from an outage — that is exactly the kind of problem we are built to solve.

Book a free 20-minute discovery call and let us look at what is slowing your team down. No sales deck, no pressure — just a grounded conversation about your infrastructure and where it needs to go.