How online database migrations work without downtime

The Problem Statement

Interviewer: "Your team needs to rename a column in a table with 2 billion rows. The table serves 50K queries per second. You cannot take the service down. How do you do it? Walk me through every step, including what happens if something goes wrong halfway through."

This question tests whether you understand why database schema changes are dangerous at scale, whether you know the expand-contract pattern (a widely used approach for zero-downtime migrations), and whether you can reason about every failure mode at each step.

This question often separates shallow answers from mature ones. A shallow answer says "just run ALTER TABLE." A production-minded answer accounts for the possibility that an ALTER TABLE could lock a production table for 45 minutes and cause API requests to time out.

The same principles apply whether you are renaming a column, splitting a table, migrating from MySQL to PostgreSQL, or moving from a monolith database to separate service databases. The pattern is always the same: expand, migrate, contract. Never do it in one step.

Clarifying the Scenario

You: "A few questions before I design the migration plan."

You: "When you say 'rename a column,' are we literally renaming, or is this a proxy for a more complex schema change like changing a column type, adding a NOT NULL constraint, or splitting a table?"

Interviewer: "Start with the rename, then generalize to any schema change."

You: "Got it. And is this a single database, or are we migrating from one database to another entirely? Like from MySQL to PostgreSQL, or from a shared database to per-service databases?"

Interviewer: "Cover both. Start with the single-database schema change, then explain how you would switch from one database to another."

You: "Last question: what is the rollback tolerance? If something goes wrong at step 3 of 5, do we need to be able to roll back to the original state instantly?"

Interviewer: "Yes. Every step must be reversible."

You: "Perfect. That constraint rules out destructive operations like DROP COLUMN until the very end. I will walk through the expand-contract pattern for schema changes, then dual-write with shadow reads for database switches."

A Safe Migration Approach

I organize this into three scenarios, each with increasing complexity:

1. Schema change on a single database (rename column, add column, change type): The expand-contract pattern with no downtime
2. Database switch (MySQL to PostgreSQL, or monolith DB to microservice DBs): Dual-write with shadow reads and a gradual cutover
3. Backfilling billions of rows: How to populate new columns or tables without overloading the database

The unifying principle across all three: never make a change that cannot be undone. Every step is a small, reversible increment. If something breaks, you roll back one step, not the entire migration. This is not aspirational advice. This is the only way that works at scale.

Think of it like renovating a house while people are living in it. You do not tear down all the walls at once. You build the new wall next to the old one, move the furniture over, verify everything works, and then remove the old wall. At every step, the house is livable.

The timeline at a glance

The Architecture

Here is the full expand-contract migration lifecycle for a schema change.

Let me walk through each phase.

Phase 1: Expand. Add the new column email_address to the table. This must be a non-blocking operation. In PostgreSQL, ALTER TABLE ADD COLUMN with no default and NULLABLE completes in milliseconds regardless of table size because it only updates the catalog, not the data. In MySQL, ALTER TABLE ADD COLUMN on a large table can lock the table for hours, which is why tools like gh-ost exist (I cover this in the deep dive).

Phase 2: Dual Write. Deploy application code that writes to both email and email_address on every INSERT and UPDATE. From this point forward, every new row has both columns populated. This is a code-level change, not a database change, so you deploy it like any other release with canary rollout.

Phase 3: Backfill. Run a batch job that copies email to email_address for all rows that existed before Phase 2. This is the longest phase: hours to days for billions of rows. The batch job must be checkpoint-based so it can resume after failures.

Phase 4: Switch Reads. Flip a feature flag so all read queries use email_address instead of email. Monitor error rates aggressively. If anything breaks, flip the flag back instantly.

Phase 5: Contract. After running successfully for weeks, remove the old email column from the app code, then (weeks later) DROP the column from the database. This is the only irreversible step.

Every phase is independently deployable and reversible (except the final DROP). This is the expand-contract pattern, a widely used approach for safe schema evolution.

Deep Dive 1: Expand-Contract: The Safe Schema Change Pattern

The core of expand-contract is that your schema supports both the old and new format simultaneously during the transition. Here is a sequence diagram showing how the system handles a read and a write during the dual-write phase.

Notice that the API response looks identical to the client in both cases. The column rename is invisible to consumers. This is critical: the migration happens entirely behind the API boundary.

For an interview, name the "expand-contract" pattern, explain the five phases, and emphasize that every phase is reversible except the final DROP. That gives a complete answer while leaving room to discuss the relevant failure modes.

The Shadow-Table Copy Pattern

For heavyweight DDL within one database, an online schema-change tool can build a second table while the original continues serving traffic. The copy and change stream must converge before the tables are swapped:

The sequence is:

1. Create the shadow table with the new schema.
2. Record a binlog or change-stream position before copying so writes during the copy are not missed.
3. Copy rows in key-ordered chunks, with each chunk in its own transaction.
4. Apply concurrent inserts, updates, and deletes to the shadow table from the change stream.
5. Let the shadow table catch up after the bulk copy finishes.
6. Atomically rename the tables once the copy is validated and the change gap is small enough for the database's lock budget.

The atomic swap prevents the application from observing a partially migrated schema. The exact lock duration and safety checks depend on the database engine and tool, so measure them on a production-like replica before the final cutover.

Deep Dive 2: Dual Write with Shadow Reads for Database Switches

Schema changes within a single database are the simple case. The hard case is switching from one database to another entirely: MySQL to PostgreSQL, or a monolith database to separate per-service databases. This requires dual-write with shadow reads.

The process has four stages:

Stage 1: Dual write, read from old. Every write goes to both databases. The old database is the source of truth. Reads come from the old database only. If the write to the new database fails, log the failure but do not fail the request. The old database is still authoritative.

Stage 2: Shadow reads. For a percentage of read requests, also query the new database in the background. Compare the results. Log any discrepancies. Do not serve the new database's results to users. This catches data inconsistencies before you rely on the new database.

Stage 3: Flip reads. When the shadow read match rate exceeds 99.99% for 48+ hours, flip the read traffic to the new database via feature flag. The old database still receives writes as a fallback.

Stage 4: Decommission old. After the new database has served all reads successfully for weeks, stop writing to the old database and decommission it.

CDC as an alternative to application dual writes

When many services write to the same database, changing every writer to dual-write may be impractical. A CDC tool can tail the source database's binlog or WAL, publish change events to a stream, and apply them to the target. This removes application changes from the replication path, but introduces stream infrastructure, transformation logic, and replication lag that must be included in shadow-read comparisons.

Regardless of whether replication comes from application dual writes or CDC, validate before cutover and shift reads gradually—for example, 1%, 5%, 10%, 50%, then 100%. At each step compare error rates, latency, row counts, and sampled results. Keep the old write path available until the new database has completed its bake period.

A useful heuristic is: if the data fits in a dump that takes less than 1 hour and a maintenance window is acceptable, a scheduled migration may be viable. If the dump takes more than 1 hour or downtime is not acceptable, use dual-write with shadow reads. Validate the choice against recovery objectives and measured load rather than treating the threshold as universal.

Deep Dive 3: Backfill Strategies for Billions of Rows

Whether you are populating a new column (expand-contract) or seeding a new database (dual-write), you need to backfill existing data. At 2 billion rows, a naive approach takes forever or kills the database.

For an interview, mention checkpoints, adaptive rate limiting, and CDC reconciliation. These details show that the backfill plan accounts for restarts, production load, and concurrent writes.

Backfill mechanics that survive retries

Use keyset pagination instead of OFFSET, commit each batch separately, and advance the checkpoint only after the destination write succeeds:

SELECT id, email
FROM users
WHERE id > :last_checkpoint_id
ORDER BY id ASC
LIMIT 1000;

UPDATE backfill_checkpoints
SET last_id = :max_id_in_batch,
    rows_processed = rows_processed + :batch_size,
    updated_at = CURRENT_TIMESTAMP
WHERE job_name = 'users_email_migration';

The destination write should be idempotent—an upsert or a conditional update—so a retried batch cannot create duplicates. Monitor rows processed, rows remaining, rate, database latency, replication lag, error rate, and checkpoint age. Route transformation failures such as an unparseable value to a dead-letter queue for later repair; one bad row should not halt the entire migration.

The Tricky Parts

1. Writes during backfill create a race condition. Suppose the backfill processes user 12345 at 3:00 PM, copying email to email_address. At 3:01 PM, the user updates their email via the app. The dual-write code writes the new email to both columns. But the backfill already processed this row, so it does not revisit it. This is fine because the dual-write code handles it. The danger is if the backfill runs slower than expected and processes user 12345 at 4:00 PM, AFTER the user's update. The backfill copies the OLD email into email_address, overwriting the new value. This is why the backfill must check timestamps or use CDC to avoid overwriting fresher data.

2. Feature flag rollback after partial read migration. You flip the read flag to use email_address. Some requests read from the new column. Then you discover a bug and flip back. But during the time the flag was active, some cache entries were populated from the new column and some from the old. If the columns are out of sync for any rows, you now have inconsistent cached data. Solution: invalidate the relevant caches when you flip the flag in either direction.

3. Foreign key constraints and indexes. When you add a new column, you might need indexes on it for read performance. Creating an index on a 2-billion-row table can take hours and lock the table (in MySQL; PostgreSQL supports concurrent index creation). Plan the index creation as a separate phase of the migration, using CREATE INDEX CONCURRENTLY in PostgreSQL or an OSC tool in MySQL.

4. ORM and query builder compatibility. If your application uses an ORM that generates queries based on the schema definition, you must update the ORM model to include the new column BEFORE deploying dual-write code. But the ORM model change might also change SELECT queries to include the new column, which means reads start touching it before the backfill. Manage this by keeping the ORM model separate from the query builder configuration, or by using raw queries during the migration period.

5. Testing the migration against production data. You cannot test a 2-billion-row backfill on a staging environment with 1,000 rows. Production-like load testing is essential. Clone the production database to a test environment, run the full backfill, and measure the time and impact. This clone operation itself might take hours for large databases.

6. Auto-increment gaps after a shadow-table swap. Writes to the original table may advance its auto-increment counter while the shadow table is being filled. Before a swap, ensure the new table's next value is above all IDs that can still arrive from the change stream; migration tools handle this explicitly, while custom scripts must do so deliberately.

7. Timezone and encoding conversions. A source DATETIME may not carry timezone information, while a target timestamp type may interpret values in a session or server timezone. Define the conversion explicitly. Also verify character-set compatibility so values such as emoji and CJK text are not corrupted during transformation.

What Most People Get Wrong

How to Communicate This in an Interview

Here is a concise way to explain this in 90 seconds:

"Use the expand-contract pattern. It has five phases, and every phase except the last one is fully reversible.

Phase 1: expand the schema. Add the new column as NULLABLE with no default. In PostgreSQL, this is often a catalog-only change. In MySQL, an online schema-change tool such as gh-ost can avoid long table locks.

Phase 2: deploy dual-write code. Every INSERT and UPDATE now writes to both the old and new columns. From this point forward, all new data exists in both places.

Phase 3: backfill historical data. A batch job copies the old column's data to the new column for all existing rows. I process 1,000 rows at a time with checkpoint-based restart and adaptive rate limiting. Reads come from a replica to avoid impacting the primary. The job takes 12-24 hours for 2 billion rows.

Phase 4: switch reads. A feature flag flips all read queries to use the new column. I monitor error rates for 48 hours. If anything breaks, I flip the flag back in seconds.

Phase 5: contract. After weeks of successful operation, I remove the old column from the code, then DROP it from the database.

For a full database switch (like MySQL to PostgreSQL), I extend this with dual-write to both databases and shadow reads to verify consistency before flipping the read traffic over.

The critical principle: at no point during this entire process does the service go down, and at any point I can roll back to the previous state in seconds."

Interview Cheat Sheet

• "How do you rename a column without downtime?": Expand-contract pattern. Add new column, dual-write, backfill, switch reads (feature flag), drop old column. Every phase reversible except the last.
• "What about ALTER TABLE?": In PostgreSQL, adding a NULLABLE column with no default is instant (catalog-only). In MySQL, ALTER TABLE on large tables locks the table. Use gh-ost or pt-online-schema-change.
• "How do you backfill 2 billion rows?": Batch processing with 1,000-row chunks. Checkpoint-based restart. Adaptive rate limiting based on replica lag. Read from replica, write to primary. 12-24 hours for 2B rows.
• "How do you switch databases?": Dual-write to old and new. Shadow reads to verify consistency. Feature flag to flip reads when match rate exceeds 99.99%. Old database stays as hot standby for weeks.
• "What if the backfill overwrites a concurrent write?": Dual-write handles new activity. CDC reconciliation catches rows modified during the backfill window. Compare timestamps before overwriting.
• "How do you roll back?": Every phase has a rollback: Phase 1 (drop column), Phase 2 (revert code deploy), Phase 3 (ignore new column, it is not read), Phase 4 (flip feature flag back), Phase 5 (irreversible, which is why we wait weeks).
• "How do you verify the migration is correct?": Row count comparison between old and new. Checksum sampling (hash 10,000 random rows, compare). Shadow reads with automated comparison. Monitor 99th percentile latency for regression.
• "What about indexes?": Create indexes on new columns using CREATE INDEX CONCURRENTLY (PostgreSQL) or as part of the gh-ost migration (MySQL). Never create an index in the same DDL as the column addition on a large table.
• "How long does this take end-to-end?": Simple column rename: 1-2 weeks. Full database switch: 4-8 weeks. Most of the time is monitoring after the switch, not doing the migration itself.
• "What tools exist for this?": gh-ost (GitHub), pt-online-schema-change (Percona), Debezium (CDC), custom backfill scripts with checkpoint stores. Managed services like AWS DMS for database switches.

Test Your Understanding

Q1. You run ALTER TABLE users ADD COLUMN phone VARCHAR(20) on a PostgreSQL table with 500 million rows. The command returns in 2 milliseconds. Your colleague says "that is impossibly fast, something must be wrong." Are they right?

Q2. During Phase 3 (backfill), your batch job crashes at row 1,247,000,000. You restart it. How do you avoid reprocessing the first 1.2 billion rows?

Q3. You are running dual-write to MySQL (old) and PostgreSQL (new). A write succeeds on MySQL but fails on PostgreSQL due to a VARCHAR length difference. What happens?

Q4. Your backfill job runs at 5,000 rows/second. The table has 2 billion rows. How long does the backfill take, and what can you do to speed it up?

Q5. You are in Phase 4 (reads switched to the new column via feature flag). A customer reports that their email shows as NULL. What happened, and how do you fix it?

Q6. An architect proposes skipping the dual-write phase entirely. Instead, they will set up CDC (Change Data Capture) from MySQL's binlog to populate the new PostgreSQL database. What are the pros and cons?

Q7. The migration has been running for 3 weeks. Shadow reads show a 99.97% match rate (not 99.99%). Should you proceed with the cutover?

Q8. After the migration to PostgreSQL is complete and the old MySQL database is decommissioned, you discover that an internal analytics pipeline was reading directly from MySQL via a replica. It is now broken. How do you prevent this?

Quick Recap

1. Never run destructive schema changes (DROP COLUMN, RENAME COLUMN used by active queries) on a live production database. Use expand-contract instead.
2. Expand-contract has five phases: add new column, dual-write, backfill, switch reads (feature flag), drop old column. Every phase except the last is reversible.
3. For database switches (MySQL to PostgreSQL), use dual-write with shadow reads. The old database stays as source of truth until the new one proves itself.
4. Backfilling billions of rows requires batch processing with checkpoints, adaptive rate limiting, and CDC reconciliation for concurrent writes.
5. Feature flags are the control mechanism for every migration phase. They enable instant rollback without code deployments.
6. The final DROP is the only irreversible step. Wait 2-4 weeks after full cutover before executing it. The cost of carrying a dead column is near zero.
7. Online schema change tools (gh-ost, pt-online-schema-change) exist because MySQL's ALTER TABLE locks large tables. PostgreSQL is more forgiving but still needs OSC tools for type changes.
8. Shadow reads are your safety net during database switches. They catch schema incompatibilities, replication lag issues, and data transformation bugs before you serve production traffic from the new database.

Related Concepts

• Feature flags for gradual rollout: The feature flag pattern used in migration phases is the same pattern used for gradual feature rollouts. The migration is just a "feature" that happens to be a schema change.
• CQRS and event sourcing: The dual-write pattern is related to CQRS where the write model and read model are separate. In a migration, the old database is the write model and the new database is the read model (during shadow reads).
• Blue-green deployments: The database switch is conceptually similar to a blue-green deployment. The "blue" database serves traffic while the "green" database warms up. You flip traffic when the green is ready.
• Change Data Capture (CDC): CDC is a prerequisite for many migration strategies. Understanding Debezium, MySQL binlog, and PostgreSQL WAL is essential for database switches and real-time data pipelines.
• Distributed transactions and two-phase commit: Dual-write without distributed transactions means the writes can diverge. Understanding why systems choose eventual consistency over two-phase commit in this context is a valuable interview discussion point.