Zero-Downtime Database Migration: The Strategies That Work

The term covers three different situations: moving a database to another server or host with the same engine, changing engine (for example from MySQL to PostgreSQL, or from a self-hosted database to a managed service), and evolving the schema of a production database, such as renaming, restructuring or splitting tables.

Each situation has its tools, but all share the same principle: at no point may there be a period during which the application can neither read nor write. This requires the old and new systems to coexist during a transition phase, and the application to be able to work with both.

Migration with downtime, where you stop, copy and restart, remains legitimate for a small database and an acceptable maintenance window. The strategies described here are for systems where that window does not exist or costs too much in revenue or reputation.

To evolve a schema without downtime, you proceed in three phases. Expand: add the new structure (column, table) alongside the old one, without deleting anything. Migrate: have the application write to both structures and backfill the new one with historical data. Contract: once everything reads the new structure, remove the old one.

Each phase is deployed separately and remains compatible with the previous version of the application, which allows rollback at any time. It is also what lets progressive deployments, where two versions of the code coexist, run without errors during the transition.

Beware of operations that lock tables. On PostgreSQL, create indexes with the CONCURRENTLY option, add constraints in two steps (NOT VALID then VALIDATE) and avoid full table rewrites. On MySQL, tools such as gh-ost or pt-online-schema-change perform changes without a prolonged lock.

Dual writing means having the application write simultaneously to the old and the new database, while continuing to read from the old one. A backfill process copies historical data in the background. Once the new database is complete and verified, reads are switched, then writes to the old database stop.

This approach is simple to understand and gives full control to the application code, which makes it suitable for engine changes with model transformation. Its drawback is the risk of inconsistency: if one of the two writes fails, the databases diverge without anyone noticing.

To limit that risk, writes must be idempotent, failures must be logged and replayed, and a regular comparison of the two databases must detect discrepancies. This complexity is why change data capture is often preferred when the engine supports it.

Change data capture reads the transaction log of the source database (WAL on PostgreSQL, binlog on MySQL, oplog on MongoDB) and replays every change on the target database, continuously and with a delay of a few seconds. The application needs no modification, which greatly reduces the risk.

Tools such as Debezium, AWS Database Migration Service or native replication features cover this need, including between different engines with transformations. The initial copy (snapshot) is followed by continuous replication until the cutover, which you schedule at the quietest moment.

CDC is today the reference approach for changing server, host or engine. Its limits: exotic data types, objects that are not replicated (sequences, stored procedures, triggers) and the need to monitor replication lag at all times.

A migration is not finished when the data has been copied, but when you have proven that it is identical and that the application works on the target. Compare row counts per table, checksums per batch and randomly chosen row samples, and replay critical queries on both databases.

Run the application in read mode against the new database in a staging environment fed by replication, and execute your functional and load tests. Pay particular attention to encodings, time zones, decimal precision and sort orders, which often differ between engines.

Finally, prepare the cutover itself like a deployment: written procedure, timing, roles, success criteria, and above all a rollback procedure. The safest option is to keep reverse replication, from the new database to the old one, during the first days.

The first pitfall is underestimating the application code: engine-specific SQL queries, implicit behaviours (case insensitivity, NULL handling, dates), forgotten stored procedures. An audit of the code and queries before the project avoids late discoveries, which are always expensive.

The second is target performance: a schema copied as-is without reviewing indexes and the new engine's parameters often gives degraded response times at cutover, precisely when pressure is highest and tolerance lowest.

The third is the absence of an owner: a database migration touches code, infrastructure, operations and sometimes the business. Without one person responsible end to end, decisions drag on and the costly coexistence phase stretches out.