Database Migration Zero Downtime Strategies

Every growing company eventually faces a database migration that cannot afford downtime: a schema change on a table with billions of rows, a move from a monolithic Postgres instance to a sharded architecture, or a full engine migration from MySQL to a distributed SQL database. Doing this without downtime is a well-understood engineering discipline in 2026, but it requires discipline in sequencing—skip a step and you either lose data or take an outage.

Why “Just Run the Migration” Doesn’t Work at Scale

A schema migration that runs instantly on a development database with a thousand rows can take hours and lock a production table with a billion rows, especially operations like adding a NOT NULL column with a default value on older database engines, or building an index without the CONCURRENTLY option in Postgres. At scale, every migration needs to be decomposed into steps that are individually fast, individually safe, and individually reversible.

Pattern 1: Expand-Contract (Parallel Change)

The expand-contract pattern is the foundation of nearly every zero-downtime schema change. It has three phases:

1. Expand: Add the new schema element (column, table) without removing or modifying anything the old code depends on. Both old and new code can run simultaneously against this schema.
2. Migrate: Backfill data into the new structure, and update application code to write to both old and new (dual-write) or read from new while still writing old, depending on direction of migration.
3. Contract: Once all application instances are confirmed running the new code path and backfill is verified complete, remove the old schema element.

This pattern trades speed for safety—it takes multiple deploys instead of one, but each individual deploy is safe and independently reversible, which is why it remains the default approach for schema changes in 2026 production systems.

Pattern 2: Dual-Write with Verification

When migrating between two different database systems entirely (e.g., MySQL to CockroachDB, or a monolithic database to a sharded one), dual-write means the application writes every mutation to both the old and new systems simultaneously during a transition period. Critically, this requires a verification job that continuously compares data between old and new systems and alerts on drift—dual-write without verification is how silent data corruption creeps in, because application bugs in the new write path won’t be caught until the old system is decommissioned.

The main risk with dual-write is partial failure: write succeeds to system A, fails to system B. Production implementations handle this with either a transactional outbox pattern (write intent to a local table in the same transaction, then a separate process reliably delivers to both targets) or by treating one system as source-of-truth and the other as best-effort with reconciliation.

Pattern 3: Change Data Capture (CDC) Based Migration

Rather than dual-writing from the application layer, CDC tools (Debezium, AWS DMS, or native replication) tail the write-ahead log of the source database and stream changes to the target in near-real-time. This is generally preferred over application-level dual-write in 2026 because it doesn’t require touching application code at all, and it captures every write path including ones the application team might have forgotten about (background jobs, admin scripts, other services writing directly to the database).

The typical cutover sequence: (1) initial bulk data copy/snapshot, (2) CDC stream catches up to near-zero lag, (3) briefly pause writes (often sub-second with well-tuned CDC), (4) verify lag is zero, (5) cut reads and writes over to the new system, (6) keep CDC running in reverse temporarily as a rollback safety net.

Pattern 4: Shadow Traffic and Read-Path Validation

Before fully cutting over reads to a new database, teams increasingly run “shadow reads”—every production read request is also sent to the new system in parallel (results discarded, only compared for correctness/latency), without affecting the response the user actually receives. This surfaces data or performance discrepancies before they’re customer-visible, and has become standard practice for any migration touching a system handling meaningful transaction volume.

Comparison Table: Zero-Downtime Migration Strategies

The Backfill Problem

Backfilling historical data into a new column or table without locking the source table or overwhelming it with load is its own sub-problem. The standard approach is batched backfill: process rows in chunks (e.g., 1,000-10,000 rows per batch) using the primary key as a cursor, with a small sleep between batches to avoid saturating the database’s I/O capacity, and idempotent batch logic so a failed/restarted job doesn’t double-process or skip rows.

How to Discuss This in Interviews

When asked to design a “migrate this database without downtime” scenario, strong candidates immediately identify which category of migration this is—same-engine schema change (expand-contract) versus cross-engine/cross-topology migration (CDC or dual-write)—because the right pattern depends entirely on that distinction. They also proactively raise the verification/reconciliation question rather than waiting to be asked, since “how do you know the migration succeeded correctly” is usually the follow-up interviewers are probing for.

This structured, failure-mode-first approach to answering infrastructure questions is covered extensively in The 0-to-1 SWE Interview Playbook (available on Amazon), including a full worked example of a zero-downtime migration interview question end to end.

Common Mistakes

The most damaging mistake is skipping the verification/reconciliation step to save time, assuming dual-write or CDC “just works.” In practice, edge cases—triggers, stored procedures, application bugs in the new write path, timezone handling differences between database engines—reliably cause silent drift that verification catches and manual QA misses.

The second common mistake is underestimating contract-phase risk: teams often rush to drop old columns/tables the moment the new path looks stable, without confirming that every service, background job, and reporting/BI pipeline has actually stopped reading the old structure. A stale BI dashboard querying a dropped column is a common self-inflicted incident.

FAQ

Q: How long should the dual-write/CDC transition period last before cutting over fully? A: Long enough to observe at least one full business cycle (typically 1-4 weeks) including peak traffic periods and batch/reporting jobs that may run weekly or monthly, so you catch write paths that don’t fire daily.

Q: What’s the safest way to add a NOT NULL column to a huge Postgres table without downtime? A: Add the column as nullable first, backfill in batches, add a CHECK constraint as NOT VALID then validate it separately (Postgres allows this without a full table lock), and only convert to a true NOT NULL constraint once validation passes—this avoids the long exclusive lock a direct NOT NULL addition with a default would require on older Postgres versions.

Q: Do I still need CDC-based migration patterns now that databases like CockroachDB and Spanner offer built-in live migration tools? A: Often yes for cross-vendor moves (e.g., MySQL to CockroachDB), since built-in tools typically only handle migration from a specific limited set of source engines. For same-vendor version upgrades, native tools have become more capable through 2025-2026 and increasingly reduce the need for custom CDC pipelines.