Design: a database migration

Zero Downtime Database Migrations

A database migration changes the representation that an application depends on. When you run a startup with 100 users, you can afford to take the application offline for 10 minutes to run an ALTER TABLE script. When you run a platform processing thousands of transactions per second, taking the database offline is not an option.

Running a heavy schema change (like changing a column type, or adding a column with a default on older engines) on a massive table can lock or rewrite the table for hours, causing total service outage. PostgreSQL 11+ adds a column with a non-volatile default as a metadata-only change, and MySQL 8.0 supports INSTANT ADD COLUMN.

The Expand and Contract Pattern

To achieve zero-downtime schema changes, we use the Expand and Contract pattern (also known as Parallel Runs). We break the migration into small, backward-compatible steps.

Suppose we want to rename the column first_name to given_name in the users table.

1. Expand (Schema): Add the new nullable column given_name. Whether this is metadata-only depends on the database engine, version, default, constraints, and DDL algorithm. Even an online operation may briefly acquire a schema lock or wait behind a long transaction, so test it on a production-like table and set lock timeouts.
2. Dual Write (App): Deploy new application code that writes to both first_name and given_name whenever a user is created or updated. It continues to read from first_name.
3. Backfill (Data): Run a background script to copy data from first_name to given_name for all old rows that existed before the dual write started.
4. Switch Read (App): Deploy new application code that now reads from given_name instead of first_name. It continues to write to both.
5. Stop Old Write (App): Deploy new application code that only writes to given_name. The first_name column is now completely unused by the app.
6. Contract (Schema): Safely drop the first_name column from the database.

Migrating Between Database Systems

What if we are moving from MySQL to PostgreSQL, or from a self-hosted DB to Amazon Aurora? The pattern is similar but requires migrating the actual data stream.

We use Change Data Capture (CDC) or Logical Replication. A tool reads the transaction log (e.g., MySQL binlog, Postgres WAL) of the old database and streams every INSERT, UPDATE, and DELETE to the new database in near real-time.

Once the new database has caught up and the replication lag is near zero, we pause the application traffic briefly, ensure the final transactions replicate, point the application configuration to the new database, and unpause. This cuts the downtime from hours to seconds.

Worked example: 6 TB MySQL to PostgreSQL

• Initial snapshot: copying 6 TB in parallel at about 250 MB/s takes about 7 hours. Record the binlog position (GTID) at the start of the snapshot so CDC can resume exactly from there.
• CDC catch-up: during those 7 hours, the source generates about 30 GB of binlog. The CDC pipeline (Debezium, AWS DMS) applies changes faster than they arrive and reaches a lag of seconds within an hour or so.
• Verification: split each table into primary-key ranges of 10,000 rows, hash every range on both sides, and re-copy ranges that differ. A mismatch rate of about 0.01% on the first pass is typical, usually caused by type mapping (MySQL DATETIME vs Postgres timestamptz, utf8mb4 collation vs ICU).
• Shadow reads: for a week, run the application's reads against both databases and compare the results, as in the parallel-run unit.
• Cutover: block writes (a maintenance flag on write endpoints, or setting MySQL to read-only), wait for CDC lag to reach 0, verify the final CDC position, switch the connection configuration, and re-enable writes. Typical write pause: 5 to 60 seconds. Reads never stop.
• Safety net: start reverse CDC from Postgres to MySQL before re-enabling writes, and keep it running for 1 to 2 weeks.

Strategies compared

Cutover sequence