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 (the industry standard for zero-downtime migrations), and whether you can reason about every failure mode at each step. It also tests whether you have done this in production or only read about it.

I find this question separates senior engineers from mid-level ones faster than almost any other topic. Mid-level engineers say "just run ALTER TABLE." Senior engineers wince, because they have been paged at 3 AM when an ALTER TABLE locked a production table for 45 minutes and every API request timed 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."

My 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, and it is the standard at every large tech company I have worked with.

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 your interview: the phrase "expand-contract" is the magic keyword. Say it, explain the five phases, and emphasize that every phase is reversible except the final DROP. That is a complete, senior-level answer.

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.

My rule of thumb: if the data fits in a dump that takes less than 1 hour, you can do a maintenance window. If the dump takes more than 1 hour, or if you cannot tolerate any downtime, use dual-write with shadow reads. For most production systems at scale, the second option is the only real option.

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 your interview: mention checkpoints, adaptive rate limiting, and CDC reconciliation. These three details signal that you have run a real backfill in production, not just read about it.

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.

What Most People Get Wrong

How I Would Communicate This in an Interview

Here is how I would actually say this in 90 seconds:

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

Phase 1: expand the schema. I add the new column as NULLABLE with no default. In PostgreSQL, this is a catalog-only change that takes milliseconds. In MySQL, I use gh-ost to avoid 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.

title: "How online database migration works without downtime" description: "How teams migrate database schemas and data online using expand-contract pattern, dual-write strategies, shadow tables, and backfill pipelines without taking the application offline." tags:

• "situational"
• "database"
• "migration"
• "zero-downtime" difficulty: "hard" category: "situational/architecture" order: 89 publishedAt: "2026-04-12" relatedArticles: []

The Problem Statement

Interviewer: "Your team needs to rename a column, change its type from string to integer, and backfill 500 million rows, all without any downtime. Users are actively reading and writing to this table. How do you do it?"

This question tests three things: your understanding of why naive ALTER TABLE operations are dangerous on large tables, your knowledge of the expand-contract (parallel change) pattern, and whether you can reason about the full lifecycle of a zero-downtime migration from schema change to data backfill to cutover.

Most candidates say "just run an ALTER TABLE." Strong candidates explain why that locks the table, walk through the shadow-table copy strategy, and address the tricky parts like backfill consistency and rollback safety.

Clarifying the Scenario

You: "Before I dive in, I want to scope this properly."

You: "When you say 'no downtime,' do you mean zero dropped queries, or is a brief period of elevated latency acceptable? Those are different constraints."

Interviewer: "Zero dropped queries. Users should not notice anything."

You: "Got it. And what database engine are we on? MySQL with InnoDB, PostgreSQL, or something else? The locking behavior differs significantly."

Interviewer: "Let's say MySQL, but I'd like you to mention where PostgreSQL differs."

You: "OK. And the table has 500 million rows. What is the write throughput? Hundreds of writes per second, or tens of thousands?"

Interviewer: "About 2,000 writes per second at peak."

You: "I will structure my answer in four parts. First, why ALTER TABLE is dangerous on large tables. Second, the expand-contract pattern for safe schema changes. Third, the shadow-table copy approach used by tools like gh-ost. Fourth, the data backfill and cutover strategy with rollback safety."

My Approach

I break online database migration into five phases:

1. Understand the lock: Why ALTER TABLE on a 500M row table blocks reads and writes for minutes or hours
2. Expand the schema: Add the new column alongside the old one, so both exist simultaneously
3. Dual-write: Update application code to write to both old and new columns
4. Backfill: Copy data from old column to new column for all existing rows, with checkpointing
5. Contract: Remove the old column and any dual-write code once the migration is verified

The mental model I use: think of online migration like renovating a kitchen while the restaurant stays open. You build the new kitchen next to the old one, gradually move operations over, verify everything works, then tear down the old kitchen. At no point does the restaurant close.

The Architecture

Here is how the phases connect:

Phase 1 (Expand) is the safe part. Adding a nullable column with no default is a metadata-only operation in most databases. It takes milliseconds regardless of table size. The real work starts when you deploy the dual-write code that populates both the old and new columns on every write.

Phase 2 (Backfill) is the slow part. You need to iterate over 500 million rows and copy data from the old column to the new one. This runs in the background while the application continues serving traffic. The key challenge is handling rows that get written to after the backfill worker already processed them (the dual-write code handles this).

Phase 3 (Contract) is the scary part. You switch reads from the old column to the new one, stop writing to the old column, and eventually drop it. Each step is behind a feature flag so you can roll back instantly.

Why ALTER TABLE Is Dangerous

The first thing to understand is why you cannot just run ALTER TABLE users MODIFY COLUMN age INT on a production table.

In MySQL with InnoDB, most ALTER TABLE operations acquire a metadata lock that blocks all concurrent reads and writes. For a 500M row table, the operation can take 30 minutes to several hours because MySQL rebuilds the entire table.

PostgreSQL is better here. Since version 11, many ALTER TABLE operations are metadata-only and near-instant. Adding a column with no default, renaming a column, and changing some types are fast. But adding a column with a non-null default still rewrites the table in versions before 11.

The key insight: even when the database claims an operation is "online," it still acquires a brief lock at the start and end. On a table with high write throughput, that brief lock can queue up thousands of transactions and cause cascading timeouts.

The Shadow-Table Copy Pattern

This is how tools like gh-ost and pt-online-schema-change actually work under the hood. Understanding the mechanics matters because you will need to debug failures.

Here is the step-by-step:

1. Create shadow table: gh-ost creates _users_gho with the new schema (the column type change, the rename, whatever you need).

2. Start tailing binlog: Before copying any data, gh-ost records the current binlog position. This ensures it captures every write that happens during the copy phase.

3. Chunk copy: gh-ost copies data from the original table to the shadow table in chunks of 1,000-10,000 rows. It uses SELECT ... WHERE id BETWEEN ? AND ? to avoid full table scans. Each chunk is a separate transaction.

4. Apply binlog events: While the copy runs, any INSERT, UPDATE, or DELETE on the original table gets recorded in the binlog. gh-ost reads these events and applies them to the shadow table. This keeps the shadow table in sync.

5. Catch-up phase: After the copy finishes, gh-ost continues applying binlog events until the shadow table is fully caught up. The gap shrinks as the copy reaches the end of the table.

6. Atomic rename: Once the shadow table is fully caught up (within a few milliseconds of the original), gh-ost performs an atomic RENAME TABLE that swaps the original and shadow tables. This operation holds a metadata lock for roughly 1ms.

Dual-Write Migration for Cross-Database Moves

The shadow-table approach works for schema changes within the same database. But what about migrating from MySQL to PostgreSQL, or from a monolith database to a microservice-owned database? That requires a dual-write strategy.

The dual-write migration has four stages:

Stage 1: Shadow writes. Deploy code that writes to both MySQL (primary) and PostgreSQL (shadow). All reads still go to MySQL. If the PostgreSQL write fails, log the error but do not fail the request. The old database is still the source of truth.

Stage 2: Backfill. Run a background worker that copies all 500M rows from MySQL to PostgreSQL. Use batched reads with checkpointing so you can resume if the worker crashes. The dual-write ensures any rows modified after the backfill passes them get the latest data.

Stage 3: Validation. Run a reconciliation job that compares MySQL and PostgreSQL row-by-row (or in random samples). Check row counts, spot-check recent writes, and flag any drift. Do not proceed to cutover until the reconciliation passes.

Stage 4: Cutover. Flip the feature flag to read from PostgreSQL. Monitor error rates for 30 minutes. If anything looks wrong, flip back to MySQL instantly. After the bake period, stop writing to MySQL and decommission it.

A common variant: CDC-based migration. Instead of dual-writes in application code, some teams use Change Data Capture (CDC) tools like Debezium. Debezium tails the MySQL binlog (similar to how gh-ost works) and streams every change to a Kafka topic. A consumer reads from Kafka and writes to PostgreSQL. This removes the need for application code changes during the migration, but adds Kafka as an infrastructure dependency and introduces a slight replication delay (typically 1-5 seconds).

The CDC approach is better when you have many services writing to the same database (changing all of them to dual-write is impractical). The dual-write approach is better when you have a single service and want full control over the write path without adding infrastructure.

Data Backfill with Checkpointing

The backfill worker is deceptively complex. Naively iterating over 500M rows sounds simple, but at scale you hit several problems.

Chunk sizing matters. Processing rows one at a time means 500M database round trips. Processing all 500M in one query means a multi-hour transaction that competes with production traffic. The sweet spot is batches of 1,000-5,000 rows, each in its own transaction.

Checkpointing enables resumability. The backfill worker stores its last processed primary key in a checkpoint table. If the worker crashes (and it will, across a multi-day backfill), it resumes from the checkpoint instead of starting over.

-- Backfill batch query pattern
SELECT id, name, age_string FROM users
WHERE id > :last_checkpoint_id
ORDER BY id ASC
LIMIT 1000;

-- After processing batch
UPDATE backfill_checkpoints
SET last_id = :max_id_in_batch,
    rows_processed = rows_processed + :batch_size,
    updated_at = NOW()
WHERE job_name = 'users_age_migration';

Throttling prevents overload. The backfill should sleep between batches based on replication lag or CPU usage. If replication lag exceeds 5 seconds, pause the backfill. If the database CPU exceeds 70%, reduce the batch size. gh-ost does this automatically. Custom backfill scripts need to implement it manually.

Idempotency handles retries. Every batch operation must be idempotent. Use INSERT ... ON DUPLICATE KEY UPDATE (MySQL) or INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) so reprocessing a batch after a checkpoint failure does not create duplicates.

Monitoring the backfill. You need visibility into backfill progress. Track rows processed, rows remaining, processing rate, and estimated time to completion. Expose these metrics so the on-call team can see backfill status at a glance.

Handling transformation errors. Not every row will transform cleanly. A string "not_a_number" cannot become an integer. The backfill worker needs a dead-letter queue for rows that fail transformation. Log them, skip them, and address them separately. Do not let one bad row halt the entire backfill of 500M rows.

The Tricky Parts

1. Race condition between backfill and dual-write. The backfill worker reads a row, transforms it, and writes it to the new location. But between the read and the write, the application might update that same row. The dual-write captures the update, but now the backfill overwrites it with stale data. Fix: use UPDATE ... WHERE updated_at <= :backfill_read_time so the backfill never overwrites a newer write.

2. Foreign key constraints during expand phase. If the new column has a foreign key constraint, you cannot add it until all referenced rows exist. You need to add the column without the constraint, backfill, validate referential integrity manually, then add the constraint. This adds a whole extra step to the migration.

3. Application code coordination across deploys. During the expand phase, some application servers are running old code (writing only to the old column) and some are running new code (dual-writing). You need the database to handle both patterns simultaneously. This is why the new column must be nullable with no default constraint until the expand phase is complete.

4. Binlog position tracking across failover. If your MySQL primary fails over to a replica during a gh-ost migration, the binlog position changes. gh-ost needs to reconnect to the new primary and find the correct binlog position. In practice, this means gh-ost reads from a replica, not the primary, so primary failover does not affect it.

5. Testing the migration on production-scale data. A migration that works on a development database with 1,000 rows can fail catastrophically on 500M rows. Differences in index selectivity, lock contention, and query plan changes make production behavior unpredictable. Always test on a production replica first.

6. Handling auto-increment gaps. When gh-ost copies data to the shadow table, INSERT operations on the original table continue incrementing the auto-increment counter. The shadow table has its own counter. After the atomic swap, the auto-increment value may have gaps or even overlap with values in the old table's binlog events. gh-ost handles this by setting the shadow table's auto-increment to the max of both tables plus a safety margin, but custom migration scripts often miss this.

7. Timezone and encoding edge cases in cross-database migration. MySQL stores DATETIME without timezone information while PostgreSQL's TIMESTAMPTZ stores with timezone. A value of "2025-01-15 14:00:00" in MySQL could mean UTC, US Eastern, or whatever the server timezone is configured to. The backfill must explicitly handle timezone conversion. Similarly, MySQL's utf8 charset only supports 3-byte UTF-8 (no emoji), while utf8mb4 supports full 4-byte UTF-8. Migrating to PostgreSQL means ensuring the encoding is correct, or emoji and CJK characters silently corrupt.

What Most People Get Wrong

How I Would Communicate This in an Interview

Here is how I would actually say this:

"Online database migration follows the expand-contract pattern. Instead of modifying the existing schema in place, which locks the table, I add the new structure alongside the old one.

For a schema change within the same database, I would use gh-ost. It creates a shadow copy of the table with the new schema, copies data in chunks while tailing the binlog for ongoing changes, and then atomically swaps the tables with a rename. The key is that it uses binlog-based change capture instead of triggers, so there is zero overhead on the production table.

For a cross-database migration, like moving from MySQL to PostgreSQL, I use a dual-write strategy. First, deploy code that writes to both databases. Then run a background backfill to copy historical data. Then validate with a reconciliation job that compares the two databases row for row. Finally, gradually shift read traffic to the new database using feature flags.

The tricky part is the race condition between the backfill and dual-writes. A row could get updated by the application between when the backfill reads it and writes it. I handle this with conditional updates that only write if the data is not newer than what the backfill read.

Rollback is built in at every stage. During expand, the old column still works. During backfill, the old database is still the source of truth. During cutover, the feature flag can flip reads back instantly. The old structure stays live for a week after cutover as a safety net."

Interview Cheat Sheet

• When they ask about schema changes: "Adding a nullable column is metadata-only and safe. Changing types or adding constraints requires shadow-table copy with gh-ost."
• When they ask about data migration: "Dual-write pattern: write to both, backfill historical, validate, then cut over reads gradually."
• When they ask about gh-ost: "Creates shadow table, copies in chunks, tails binlog for live changes, atomic rename at the end. No triggers, no locks."
• When they ask about backfill: "Keyset pagination (WHERE id > last_id), batches of 1,000-5,000, checkpointed progress, throttled by replication lag."
• When they ask about rollback: "Every phase is reversible. Old column/database stays live until the migration is verified. Feature flags control cutover."
• When they ask about validation: "Continuous reconciliation comparing old and new. Row counts, spot-check samples, and compare recent writes."
• When they ask about MySQL vs PostgreSQL: "PostgreSQL supports more online DDL natively (metadata-only for many operations). MySQL needs gh-ost for most non-trivial changes."
• When they ask about the rename step: "Atomic RENAME TABLE swaps old and shadow in ~1ms. Application sees old schema or new schema, never both."
• When they ask about CDC vs dual-write: "CDC (Debezium) tails the binlog and streams to the target database via Kafka. Better when many services write to the DB. Dual-write is better for single-service ownership."
• When they ask about coordination: "Feature flags decouple deploy from release. Deploy dual-write code, enable it gradually, and roll back by disabling the flag. No redeploy needed."

Test Your Understanding

Quick Recap

1. ALTER TABLE on large tables acquires locks that block production traffic for minutes to hours.
2. The expand-contract pattern adds new structure alongside old structure, migrates data, then removes the old structure.
3. gh-ost uses shadow tables and binlog tailing to migrate MySQL schemas with zero production overhead.
4. Cross-database migrations use dual-write, backfill, reconciliation, and gradual traffic cutover.
5. Backfill workers must use keyset pagination, checkpointing, throttling, and idempotent writes.
6. The contract phase (dropping old structures) should happen days after cutover, not immediately.
7. Feature flags control which database or column serves reads, enabling instant rollback.
8. Continuous reconciliation comparing old and new data is the safety net that catches every edge case.

Related Concepts

• Database replication and failover: Migrations interact with replication lag and failover. Understanding how binlog replication works is prerequisite to understanding gh-ost.
• Feature flags: The cutover strategy depends entirely on feature flags. Gradual rollout with instant rollback is the pattern that makes zero-downtime migration safe.
• Blue-green deployments: The expand-contract pattern for databases mirrors the blue-green pattern for application deployments. Both run old and new in parallel with a traffic switch.
• Event sourcing and CDC: Change Data Capture (CDC) is the generalized form of binlog tailing. Tools like Debezium use CDC for cross-database synchronization, which is the same mechanism gh-ost uses for shadow-table sync.

title: "How online database migrations run without downtime" description: "How tools like gh-ost and pt-online-schema-change copy tables, replay binlog changes, and atomically swap tables to alter schemas on live databases." tags:

• "situational"
• "database"
• "migration"
• "zero-downtime" difficulty: "hard" category: "situational/architecture" order: 92 publishedAt: "2026-04-12" relatedArticles: []

Stub

This article is planned but not yet written.