Google AI ModeOct 11, 2026
Loading…
Data as of Oct 11, 2026A topic in Database Migration and Schema Management Tools.
Reviewed by Dimitry Apollonsky
AI summary
gh-ost has a narrow lead for changing large MySQL tables without moving databases, with AWS Database Migration Service close behind. Its stated fit is specifically a large MySQL table change, not a database move.
For changing a large MySQL table without moving databases.
Mentioned in · Jul 7 – Oct 11, 2026
Google AI ModeOct 11, 2026
Performing a zero-downtime database migration requires a fundamental shift in how you deploy schema changes. Instead of making breaking changes all at once, you must decouple code deployment from database structure changes using a backward- and forward-compatible methodology.
The Expand-Contract Pattern (Parallel Run) : This is the gold standard for zero-downtime migrations. You split the migration into distinct phases:
Backward-Compatible Schema Design : Never rename or drop a column in a single deploy. If you need to rename first_name to fname:
fname as a nullable column.first_name and fname.first_name to fname.fname.first_name.first_name in a later release.Blue-Green / Canary Deployments with Database Compatibility : Ensure your application layer can handle dual-version compatibility during the window where both old and new application instances might be hitting the database simultaneously.
Feature Flags : Use feature flags to gate access to newly modified database queries or structures until the underlying migration steps are fully completed and verified in production.
Flyway : An open-source database migration tool that robustly handles version-controlled SQL scripts or Java-based migrations. It integrates tightly into CI/CD pipelines to track which migrations have run.
Liquibase : An enterprise-ready, language-independent (XML, YAML, JSON, SQL) database change management tool. It excels at generating rollbacks and supporting multi-database environments.
gh-ost (GitHub's Online Schema Change for MySQL) : A triggerless, asynchronous online schema migration tool for MySQL. It operates by streaming the binlog and building a shadow table, minimizing performance impact and locking.
pt-online-schema-change (Percona Toolkit) : A classic MySQL tool that alters tables by creating a new empty table, copying data, building triggers, and swapping the tables. (Note: modern alternatives like gh-ost are often preferred to avoid trigger overhead).
Pg_repack / Alembic / Django Migrations : For PostgreSQL, tools like pg_repack help rebuild tables online without exclusive locks, while ORM-level tools like SQLAlchemy's Alembic or Django Migrations manage the schema evolution lifecycle within your application repository.
If you want to dive deeper, let me know:
I can provide a step-by-step expansion plan tailored to your stack.
ChatGPT SearchOct 11, 2026
The most reliable way to migrate a database without interrupting your application is to make the application compatible with both the old and new database structures during the transition, move the data while the application remains live, and switch over only after validating the new system.
This is often called a zero-downtime migration. In practice, you can usually avoid planned downtime, although absolute zero downtime cannot be guaranteed: network failures, replication lag, locking, and unexpected application behavior can still cause brief disruptions.
Use an expand–migrate–contract approach. It works especially well for schema changes, database engine upgrades, and migrations where your application must remain available throughout.
Phase 1 — Expand
Add the new tables, columns, indexes, or other structures without removing or breaking the existing ones. Make additive changes that are backward-compatible.
(Old application remains compatible)
Phase 2 — Migrate
Backfill existing data in small batches. If you are moving to another database, replicate ongoing changes using change data capture (CDC). Validate consistency while traffic continues.
Phase 3 — Switch
Deploy application code that can use the new structure or database. Gradually direct reads and, when safe, writes to the new destination. Monitor errors and latency.
Phase 4 — Contract
After confirming that old code paths and database structures are no longer needed, remove legacy columns, tables, compatibility code, or replication processes in a separate deployment.
Key principle: never combine a breaking schema change with the first application deployment that depends on it. Separate the steps so each intermediate state remains safe.
I need to move my database to a new system, but I cannot afford any service interruptions. What methods and software can help me achieve a zero-downtime migration?
Google AI ModeOct 7, 2026
AWS Database Migration Service (DMS) or Google Cloud Database Migration Service (DMS) are the industry standards for zero-downtime data migrations with built-in validation features.[](https://www.tinybird.co/blog/Data-Migration-Solutions)
For application-level schema and structural changes, standard tools like Flyway or Liquibase are used alongside the expand-contract (parallel run) design pattern rather than doing instant locks.[](https://www.harness.io/blog/zero-downtime-database-migrations-safe-schema-changes)[[2]()]()
The right tool depends on whether you're changing a schema within the same database or moving data to a different database engine or server.
gh-ost
(Best for MySQL schema changes)
Rebuilds large tables in the background and captures ongoing changes through the MySQL binary log. Supports throttling, pausing, and controlled cutover.
pt-online-schema-change
(Alternative for MySQL)
Copies a table into a replacement table and uses triggers to propagate ongoing writes. Useful for many large-table alterations, but foreign keys, existing triggers, and database load need careful review.
AWS Database Migration Service (DMS)
(Best for cross-database moves)
Performs an initial data load and can replicate ongoing changes with CDC, keeping the target synchronized while the source remains operational. Supports homogeneous and heterogeneous migrations.
Debezium
Useful when you need CDC pipelines built around transaction logs and streaming infrastructure, often with Kafka. It can help synchronize changes during a migration, but requires you to design the initial snapshot, event ordering, replay, and target-write logic correctly.
Flyway and Liquibase
Useful for versioning schema changes, reviewing them in pull requests, and deploying migrations through CI/CD. They manage migration execution; they do not, by themselves, make every DDL operation online or guarantee zero downtime.
Flyway ·
| Migration scenario | Recommended approach |
|---|---|
| Add a nullable column or new table | Expand–migrate–contract with versioned migrations |
| Rename or change the type of a column | Add a replacement column, dual-write or backfill, switch reads, then remove the old column |
| Alter a large MySQL table | gh-ost or pt-online-schema-change, subject to compatibility checks |
| Move between database servers or engines | Initial copy + CDC + validation + controlled cutover |
| Upgrade a managed database | Engine-supported online upgrade, replica promotion, or blue/green deployment where supported |
| Move a very large dataset | Chunked backfill, parallelized carefully, with CDC and lag monitoring |
For PostgreSQL, also investigate native online-capable DDL, concurrent index creation, and the locking behavior of the exact operation for your PostgreSQL version. Not every ALTER TABLE is safe to run without blocking application traffic.
Here is a runbook you can adapt for a real application.
of
Suppose you need to replace users.name with users.display_name.
A safer rollout is:
display_name without removing name.display_name in batches, accounting for concurrent updates.display_name.name after every application instance has moved to the new path.name in a later deployment.For index creation, PostgreSQL supports CREATE INDEX CONCURRENTLY, which avoids the normal index-build lock that blocks writes. It still has operational caveats, so monitor the build and check for invalid indexes if it fails.
Current database engine
PostgreSQL
MySQL / MariaDB
SQL Server
Oracle
Other / NoSQL
What are you changing?
Schema only, same database
Moving to a new server or database engine
Major database version upgrade
Approximate database size
Small — under 10 GB
Medium — 10–500 GB
Large — over 500 GB
Infrastructure
Managed cloud database
Self-managed / on-premises
[Recommend my migration plan]
My default recommendation: use expand–migrate–contract for schema changes, online schema migration tooling for large table alterations, and initial load plus CDC for moving between database servers or engines. Test the full cutover and recovery procedure before production.
For reference, these are useful primary sources:
CREATE INDEX — concurrent index building and caveats.If you can share what source and target databases you are using (e.g., PostgreSQL to PostgreSQL, Oracle to AWS Aurora) and whether you mean schema changes or bulk data transfer , I can recommend the exact configuration or tool pairing you need.
If your requirements are (1) validate data during/after migration and (2) avoid application downtime, I’d shortlist these:
| Tool | Data validation | Zero/near-zero downtime | Best fit |
|---|---|---|---|
| DBShifts | Strong — row-level/type-aware checksums, reconciliation | Yes — CDC + staged cutover | Moving data between databases |
| Atlas | Strong for schema safety — detects destructive/data-loss changes | Good, but depends on DB/change | Schema migrations in CI/CD |
| Bytebase | Strong review/approval workflow | Good with online DDL strategies | Teams wanting governance |
| gh-ost | Can compare/validate via replica workflows | Excellent for MySQL | Large MySQL schema changes |
If you mean migrating data from one database to another, I'd look hardest at DBShifts. It explicitly combines checksum-based data verification, continuous CDC synchronization, and a guided cutover that verifies the target before switching traffic.
If you mean ongoing production schema migrations—e.g. adding columns, indexes, constraints—Atlas is probably the better starting point. It plans migrations, detects potentially destructive changes, and supports PostgreSQL, MySQL, SQL Server, Oracle, and several other databases.
For large MySQL tables, I'd specifically consider gh-ost. It performs online schema changes while the table remains available, copies data incrementally, propagates ongoing writes, and lets you control the final cutover.
One important caveat: “zero downtime” doesn't automatically mean “safe migration.” For critical systems, I'd require pre-migration validation → continuous synchronization → row/checksum reconciliation → controlled cutover → post-migration validation. Tools can automate much of this, but application compatibility usually still requires an expand-and-contract deployment strategy.
If you tell me source DB → target DB (e.g. MySQL → PostgreSQL or Postgres → Postgres) and roughly how much data you have, I can narrow this to 2–3 concrete choices.
