Log in
SkillPilotAI
Plans

Zero-downtime DB Migration and Schema Changes

Adding or renaming a column in a table while the live website keeps running, or moving the whole DB to a new system, without downtime and without data loss.

58.1 Why is it used?

  • If you run ALTER TABLE directly on a big table (50 crore rows), the table can lock and the site can be down for minutes or hours.
  • For 24x7 businesses (Amazon, Paytm, Swiggy) even a 'Sunday 2 AM maintenance window' is not acceptable.
  • In a rolling deployment the old and new code run together. The schema has to be compatible with both.
  • Moving from on-prem Oracle to cloud Aurora/PostgreSQL, or from MySQL to a sharded cluster: traffic must keep flowing while data is migrated.
  • When schema changes are version-controlled, every environment (dev, staging, prod) gets the same schema and rollback is possible.

58.2 How it works

Expand-Contract: orders.phone -> mobile_number (50 crore rows)
  1. 1.R1 Expand: ADD a nullable column with gh-ost, no lock (phone = 98xxxx, mobile_no = NULL)
  2. 2.R2 Dual write: app writes to both + backfill 10K rows/batch, then verify (reads still from phone)
  3. 3.R3 Read new: reads from mobile_number, feature flag 1% -> 100%
  4. 4.R4 Stop old: stop writing to phone, monitor for a few days
  5. 5.R5 Contract: drop the phone column with Flyway V5__drop_phone.sql

After verifying R2: COUNT(*) WHERE mobile_number IS NULL = 0 and sample checksums match. Every step can be rolled back. Mistake: rename + code change in one release, and old pods crash during the rolling deploy

  • Expand-Contract (parallel change): first add the new column/table (expand), the code writes to both, backfill the data, move reads to the new one, then remove the old one (contract).
  • The safe way to rename: never a direct RENAME. Add new_col -> dual write -> backfill -> read new -> stop old write -> drop old_col; each step is a separate deploy.
  • Backward compatible changes: add a nullable column or one with a default. Add the NOT NULL constraint later, once the data is filled.
  • Online schema change tools: create a shadow table, copy data in chunks, sync live changes, and finish with an atomic rename (cut-over).
  • gh-ost reads live changes from the binlog (no triggers), while pt-online-schema-change uses triggers. Both can throttle.
  • Migration tools (Flyway/Liquibase): versioned scripts like V1__create.sql, V2__add_col.sql; a history table in the DB tells which ones were applied.
  • Full DB migration: snapshot copy + continuous sync via CDC (binlog/WAL) -> verify data (row counts, checksums) -> compare shadow reads -> traffic switch.
  • Dual writes: the app writes to both DBs. It looks simple but a partial failure causes inconsistency; CDC based sync or an outbox is safer.
gh-ost online ALTER (MySQL, triggerless)
  1. 1.The live orders table keeps running, gh-ost creates the _orders_gho ghost table + new column
  2. 2.Rows are copied in chunks
  3. 3.Live changes are synced into the ghost table from the binlog
  4. 4.Cut-over: atomic RENAME, a pause of a few ms
  5. 5.Pause / throttle control keeps the prod load low

pt-online-schema-change syncs with triggers, so write load increases. In Postgres you use CREATE INDEX CONCURRENTLY and pgroll

Moving the whole DB: Oracle on-prem -> Aurora
  1. 1.Snapshot copy + AWS DMS full load, then continuous CDC sync
  2. 2.Verify: row counts, checksums
  3. 3.Shadow reads: compare the results from both DBs
  4. 4.Traffic switch: a cut-over of a few seconds

Rollback plan: keep the old DB ready and in sync

58.3 Tools you can use

ToolTypeHow it is used
gh-ost (GitHub)MySQL online schema changeTriggerless; reads the binlog to sync the ghost table, with pause/throttle/cut-over control.
pt-online-schema-change (Percona)MySQL online schema changeCopies live changes with a new table + triggers, copies data in chunks, and ends with RENAME TABLE.
FlywayMigration versioning toolSQL files named V1__, V2__; flyway migrate runs at app startup or in CI/CD.
LiquibaseMigration versioning toolXML/YAML/SQL changelogs, rollback support, diff and multiple DB types.
AWS DMS / Azure DMS / GCP Database Migration ServiceManaged DB migrationFull load + ongoing CDC replication from source to target DB, with a minimal-downtime cut-over.
DebeziumCDC (open-source)Reads the MySQL binlog / Postgres WAL and sends changes to Kafka; keeps a new DB/store in sync.
pgroll / PostgreSQL nativePostgres online changeCREATE INDEX CONCURRENTLY, ADD COLUMN with default (fast in PG 11+); pgroll automates expand-contract.

58.4 Comparison: gh-ost vs pt-online-schema-change vs Flyway/Liquibase vs AWS DMS

Pointgh-ostpt-online-schema-changeFlyway / LiquibaseAWS DMS
JobOnline ALTER of a big MySQL tableOnline ALTER of a big MySQL tableVersioning and applying schema changesMigrate data from one DB to another
MechanismBinlog streaming, triggerlessTriggers on the original tableOrdered scripts + history tableFull load + CDC
Prod load impactLow, can pause/throttleTriggers increase write loadDepends on the script (can lock)Read load on the source
DowntimeNear zero (short cut-over)Near zero (short rename)Not zero-downtime by itself, follow expand-contractA few seconds/minutes at cut-over
DB supportMySQL onlyMySQL onlyMany DBsMany DB pairs, including heterogeneous ones
Best forAdding a column/index on a huge tableWhere there is no binlog access or you want a simple setupEvery schema change in CI/CDOn-prem -> cloud or an engine change

58.5 When to choose which

  • Flyway/Liquibase: in every project. Schema changes live in git with the code and are applied through the pipeline.
  • gh-ost: an ALTER on a MySQL table with crores of rows, when you want to keep prod load low and want control.
  • pt-online-schema-change: MySQL, simple to use, triggers are acceptable, no binlog setup.
  • AWS DMS / Debezium: moving a whole database from one place to another, with live sync through CDC.
  • In every case the pattern is the same: expand-contract + backward compatible code + verify + gradual switch + rollback plan.

58.6 Real-world example

Scenario: renaming the 'phone' column to 'mobile_number' in the orders table (50 crore rows).

  1. Release 1 (expand): add a nullable mobile_number column with gh-ost. No table lock, the copy happens in the background.
  2. Release 2: the app writes to both columns (phone and mobile_number), reads still come from phone.
  3. A backfill job fills mobile_number = phone in the old rows in chunks (10,000 rows/batch, with sleeps).
  4. Verify: count WHERE mobile_number IS NULL = 0, and sample checksums match.
  5. Release 3: reads come from mobile_number; traffic goes 1% -> 100% through a feature flag, and if there is a problem the flag is turned off.
  6. Release 4: stop writing to phone; monitor for a few days.
  7. Release 5 (contract): drop the phone column with a Flyway script. Every step was reversible, and downtime was zero.
  8. GitHub said in a public blog that they built gh-ost for exactly this reason: trigger-based tools caused load issues on busy MySQL tables.

58.7 Interview tips

  • Column rename + code change in a single release: old pods crash during a rolling deploy.
  • Ignoring locks: in Postgres, CREATE INDEX without CONCURRENTLY, or an ALTER stuck behind a long transaction building a lock queue, which blocks the whole table.
  • A migration without a rollback/verification plan; not comparing checksums after the data copy.
  • Interview tip: explain the expand-contract steps clearly and explain the trade-off of 'dual write vs CDC'.