The database migration that takes 200 milliseconds on your development machine takes 45 minutes on a production table with 80 million rows. During those 45 minutes, the table is locked. Every query that touches it queues. The application stalls. Users see errors. Revenue stops.

This is not a theoretical failure mode. It is the most common cause of unplanned downtime on growth-stage platforms — a migration that worked perfectly in staging, tested against a database with 50,000 rows, deployed to production where the same table holds tens of millions.

Why Standard Migrations Break at Scale

Most migration frameworks — Rails migrations, Django migrations, Alembic, Flyway — generate DDL statements designed for correctness, not for scale. They assume that schema changes are fast, that locks are brief, and that the migration will complete before any timeout fires. How much a given DDL statement actually locks depends on the database engine, its version, and the specific operation — which is exactly why a staging-scale test tells you so little about production behavior.

These assumptions hold until they don’t:

  • Adding a column with a default value has historically rewritten the entire table in some engines and versions (MySQL before instant ADD COLUMN support, PostgreSQL before 11) — a 100 million row table can take 30+ minutes
  • Creating an index on a large table can block writes for the duration of the build, depending on the engine and the options used
  • Changing a column type typically requires rewriting every row — and the locks held during the rewrite prevent concurrent access
  • Adding a foreign key constraint validates every existing row before the constraint takes effect, holding locks throughout unless the engine supports deferred validation

The failure is predictable: the migration that ran in 2 seconds on a 10,000-row staging table runs for 20 minutes on a 50 million-row production table, and every request that touches that table during those 20 minutes either blocks or fails.

The Expand-Contract Pattern

The fundamental technique for low-disruption migrations is the expand-contract pattern, a core transition technique in evolutionary database design. Instead of making a breaking change in a single step, you split it into a sequence of non-breaking steps:

Phase 1: Expand

Add the new schema alongside the existing schema. Both coexist without conflict:

  • Adding a new column? Add it as nullable with no default — in many engines and versions this is a fast metadata-only change, but verify the behavior for your specific engine and version before assuming it is
  • Renaming a column? Add a new column with the new name
  • Changing a column type? Add a new column with the new type
  • Splitting a table? Create the new table alongside the old one

Phase 2: Dual-Write

Deploy application code that writes to both the old and new locations. Reads continue from the old location. This ensures that all new data is captured in the new format while maintaining backward compatibility.

Phase 3: Backfill

Migrate existing data from the old format to the new format. This is a data migration, not a schema migration — it operates on rows, not on table structure. It must be:

  • Batched — process rows in chunks of 1,000-10,000 to avoid long-running transactions
  • Throttled — add delays between batches to avoid overwhelming replication or I/O
  • Resumable — track progress so a failed backfill can restart from where it stopped, not from the beginning
  • Idempotent — running the same batch twice produces the same result, so retries are safe

Phase 4: Switch Reads

Once backfill is complete and verified, switch reads to the new location. The old location continues to receive writes as a safety net.

Phase 5: Contract

Remove the old schema. This is the only step that is not backward-compatible, so it should only happen after the new schema has been in production for a sufficient validation period.

Each phase is an independent deployment. Each can be rolled back without affecting the others. The migration that would have required a 45-minute maintenance window becomes five small, safe deployments spread over days.

Online Schema Change Tools

For operations that inherently require table rewrites — changing column types, adding indexes on large tables — online schema change tools provide the mechanism:

MySQL: gh-ost and pt-online-schema-change

gh-ost (GitHub Online Schema Change) creates a shadow table with the desired schema, copies data in batches, captures ongoing changes via binlog streaming, and performs an atomic table rename when complete. It operates without triggers, which makes it safer for high-write-volume tables.

pt-online-schema-change from Percona Toolkit uses a similar approach but captures changes via triggers on the original table. It is well-tested but adds write amplification proportional to the trigger overhead.

Both tools allow schema changes on tables with billions of rows while keeping the table available for reads and writes — with only a brief lock during the final cutover rename.

PostgreSQL: Fast Metadata Changes Are Not the Same as Non-Blocking

Many PostgreSQL ALTER TABLE variants acquire strong locks — often ACCESS EXCLUSIVE, which blocks all access to the table. What saves you in practice is that some operations are metadata-only and therefore hold that lock very briefly. That is not the same as non-blocking: even a fast operation can queue behind a long-running query and stall everything behind it, which is why lock timeouts matter.

  • Adding a nullable column with no default is a metadata-only change (no table rewrite) — fast, but it still needs a brief exclusive lock
  • Adding a column with a default value avoids the table rewrite in PostgreSQL 11+ (the default is stored in the catalog, not written to every row)
  • Creating indexes on live tables generally uses CREATE INDEX CONCURRENTLY — see the index section below for its trade-offs
  • Changing column types usually requires a full table rewrite under an exclusive lock — use the expand-contract pattern for these

Data Migration Patterns for Large Tables

The backfill phase — migrating existing data — is where most online migrations fail. The common failure modes:

Single-Transaction Migrations

Wrapping a data migration in a single transaction seems safe — either all rows migrate or none do. But on a 50 million row table, that transaction holds locks for the entire duration, accumulates undo/redo log entries proportional to the data volume, and will likely be killed by a timeout or OOM condition.

Solution: Batch the migration. Process 5,000 rows per transaction. Track the last processed ID. Resume from that point if interrupted.

Unbounded Queries

A migration query like UPDATE users SET new_column = old_column WHERE new_column IS NULL scans the entire table to find rows that need migration. On the first run, this is efficient — most rows match. On subsequent runs after a failure, it still scans the entire table to find the remaining unmigrated rows.

Solution: Use cursor-based batching with the primary key: WHERE id > last_processed_id AND id <= last_processed_id + batch_size. This produces index-only scans regardless of migration progress.

Replication Lag

On replicated databases, large batch updates on the primary generate replication events that secondaries must process. If migration batches are too large or too fast, replication lag grows until secondaries fall behind — causing stale reads or failover delays.

Solution: Monitor replication lag between batches. Pause migration when lag exceeds a threshold (e.g., 5 seconds). Resume when lag recovers. This self-throttling ensures the migration completes as fast as replication allows without destabilizing the cluster.

Index Operations at Scale

Index creation is the most common way migrations violate availability requirements. In PostgreSQL, a standard CREATE INDEX on a 100 million row table can take many minutes, during which writes to the table are blocked.

The patterns for safe index operations:

  • PostgreSQL: CREATE INDEX CONCURRENTLY is the usual choice for live tables, but understand its trade-offs: it allows writes during most of the build, yet it must wait for existing transactions at certain points and still takes short-lived locks; it takes considerably longer than a standard build; it cannot run inside a transaction block; and it can fail partway, leaving an invalid index that must be dropped and retried. It is the right default for busy tables, not a universal replacement for CREATE INDEX
  • MySQL: InnoDB’s online DDL can build many index types without blocking writes, but support depends on the specific index type, the algorithm chosen (INSTANT, INPLACE, COPY), and the server version — consult the online DDL support matrix for your version rather than assuming an operation is online. Monitor for metadata lock waits during the operation
  • Large indexes: Build during low-traffic periods even with concurrent operations — the I/O impact of building a multi-gigabyte index can degrade query performance

Migration Verification

Every migration — schema or data — requires verification before the contract phase:

  • Row count verification — does the new location contain the same number of relevant rows as the old?
  • Data integrity checks — do aggregates (sums, counts, checksums) match between old and new?
  • Application-level validation — do reads from the new location return correct results for known test cases?
  • Performance validation — are queries against the new schema performing within acceptable latency bounds?

Automated verification should gate the contract phase. If verification fails, the system stays in dual-write mode until the discrepancy is resolved.

Key Takeaways

Meeting strict availability requirements during database migrations is not about finding the right tool — it is about choosing the right strategy. The expand-contract pattern is a broadly useful compatibility pattern for many breaking schema changes, across a wide range of engines and scales. The investment is in discipline: breaking a single dangerous change into multiple safe steps, and verifying each step before proceeding to the next.

The platforms that grow without maintenance windows are those that treat every migration as a multi-step deployment, not a single DDL statement.

If your platform is experiencing migration-related downtime or you’re planning a schema change that could affect availability, a Platform Intelligence Audit can assess your migration strategy and identify patterns that reduce downtime risk.