Database Migration With Minimal Downtime: A Practical Sequence

Most database migration downtime does not come from copying data. It comes from the stretch when the application has to be switched off because the old and new databases cannot both be right at once: the dump is running, the restore is running, or the code and the schema do not match yet. Remove that stretch and the outage shrinks to the time it takes to change a connection string.

The sequence below does that in five moves: keep the new database continuously in sync, make schema changes backward compatible, rehearse and verify, cut over in a short scripted window, and keep a way back. It applies to MySQL, MariaDB and PostgreSQL, with the differences noted.

Where database migration downtime comes from

There are two kinds of migration, and projects often contain both. A data move takes the database to a new server, a new major version, a managed cloud service or a different engine. A schema change alters tables in place. Both fail the same way: the application and the database must change at the same instant, and the only way to guarantee that is to stop the application.

The naive data move is maintenance page, mysqldump or pg_dump, restore, switch, and its downtime grows with the size of the data. The naive schema change is one deployment that renames a column while old code is still running against it. Every step below exists to break one of those two couplings.

Keep the new database in sync

Replication, when both ends support it

The cleanest approach is to make the new database a replica of the old one. You load an initial copy, replication applies every later change, and the new server stays just behind production while you test.

• MySQL and MariaDB have built-in replication driven by the binary log. It is asynchronous by default, so watch the lag. On current MySQL, SHOW REPLICA STATUS reports Seconds_Behind_Source and whether the receiver and applier threads are running.
• PostgreSQL offers logical replication, with CREATE PUBLICATION on the old server and CREATE SUBSCRIPTION on the new one, and it works across major versions. Read its documented restrictions first: schema changes are not replicated, and neither is sequence data, so sequences have to be set by hand before the switch.
• A different engine or a cloud move usually calls for a change-data-capture tool or your cloud provider's migration service. The principle is the same: a full load, then a continuous stream of changes.

Dual writes, when they do not

If replication is not possible, for instance because the new database has a different structure, the application can write every change to both. Dual writes are harder than they sound. One write can succeed while the other fails, two requests can land in a different order on each side, and existing rows still need a backfill. Keep the old database as the source of truth, send the second write through a queue so a failure can be retried, and run a comparison job that reports rows that differ.

Change the schema with expand and contract

The rule for changing tables under a running application: never make a change that the currently deployed code cannot live with. Split every breaking change into steps that are each harmless. Renaming fullname to display_name becomes:

1. Expand. Add the new column, nullable. Old code ignores it.
2. Write both. Deploy code that writes to both columns and still reads the old one.
3. Backfill. Copy existing values across in small batches, so replicas keep up and locks stay short.
4. Switch reads. Deploy code that reads the new column.
5. Stop writing the old column.
6. Contract. Drop the old column in a later release, after a backup.

-- 1. Expand
ALTER TABLE customers ADD COLUMN display_name VARCHAR(190) NULL;

-- 3. Backfill one key range at a time
UPDATE customers
SET display_name = fullname
WHERE id BETWEEN 1 AND 5000
  AND display_name IS NULL;

-- 6. Contract, once no deployed code touches the old column
ALTER TABLE customers DROP COLUMN fullname;

Each step can be deployed and rolled back by itself, and none of them needs a maintenance window.

The individual statements matter too, because some ALTER TABLE operations block writes for as long as they run:

• In MySQL's InnoDB, adding an ordinary column or a secondary index permits concurrent writes, while changing a column's data type copies the whole table and blocks them. The online DDL reference lists every operation. For the blocking ones, use a new column with the steps above, or an online schema change tool such as gh-ost or pt-online-schema-change, which build a shadow copy of the table and swap it in.
• In PostgreSQL, build indexes with CREATE INDEX CONCURRENTLY, add constraints as NOT VALID and run VALIDATE CONSTRAINT afterwards, and remember that changing a column's type normally rewrites the table.
• On both, even a fast change needs a brief exclusive lock. It queues behind any long-running transaction, and new queries queue behind it. Set a short lock_wait_timeout (MySQL) or lock_timeout (PostgreSQL) for the session, so the statement gives up and can be retried instead of stalling the site.

Rehearse on a copy, then verify

Restore a recent backup to a server the same size as the target and run the whole plan against it with a stopwatch. The rehearsal tells you how long the initial load takes, which statement is unexpectedly slow, and how far replication falls behind during a backfill. Run the application's tests against the new database too. Version and engine changes bring differences that only show under real queries: stricter SQL modes, a different default collation (which changes sort order and can break unique indexes), new reserved words, changed query plans.

"Replication is running" is not proof that the data matches. Before the switch, compare the two sides:

• row counts for every table;
• checksums per table or per key range (CHECKSUM TABLE in MySQL, or a tool such as pt-table-checksum);
• the highest primary key and the next auto-increment or sequence value for each table;
• business totals someone would notice: order value per day, open balances, enrollments per course;
• a sample of full records, including text with accents and emoji, dates near time zone boundaries and very long values.

Every difference is explained before cutover. An unexplained difference of three rows is a bug you have not found yet.

Cut over in a short, scripted window

Because the data is already in place, the switch is brief. Do it at the quietest hour, from a written runbook, with two people:

1. Stop queue workers and scheduled tasks, and let running jobs finish.
2. Put the application into read-only or maintenance mode, so no new writes reach the old database.
3. Wait until replication lag is zero and the new server has applied the last transaction.
4. Run the quick checks: counts and highest IDs on the busiest tables.
5. Set sequences or auto-increment values on the new database if your method does not carry them.
6. Point the application at the new database and reload it.
7. Run smoke tests: sign in, read a record, create one, run one background job.
8. Re-enable writes, workers and scheduled tasks, then watch the error log and slow query log.

Keep a rollback you can use

Before the switch, rolling back means doing nothing, because the old database is untouched. After it, every minute adds writes that exist only on the new side. The usual options are to set up replication in the reverse direction at the moment of cutover, so the old server follows the new one, or to keep the old database read-only and accept that a rollback would mean re-entering what happened since.

Decide in advance which errors trigger a rollback, who makes the call, and the point after which you fix forward instead. For schema changes, the contract step is the point of no return, which is the reason to delay it by at least one release and to take a backup just before it.

When a maintenance window is the better choice

All of this is extra work, and not every system needs it. An internal application used in office hours, with a database that dumps and restores in a few minutes, is better served by a planned, announced window on a quiet evening. Simpler is safer.

The full sequence earns its cost when the application takes writes around the clock, when tables are large enough that a copy runs for hours, when the engine or major version is changing, or when the data is money, orders or learner records. If your team has not run replication or an online schema change before, rehearse with someone who has. We plan and run these moves as part of our migration and modernization work, often together with a Laravel upgrade or a PHP version migration, and a new server is a good moment for performance optimization of slow queries. Related reading: planning a Laravel version upgrade without downtime.

Frequently asked questions

Can a database migration have zero downtime?

For schema changes done with expand and contract, yes. For a move to a new server there is normally a short read-only moment while the last changes arrive and connections switch. With replication in place that moment is brief.

Do framework migration tools make schema changes safe?

No. Tools such as Laravel migrations or Doctrine Migrations run the statements you write, in order, and record that they ran. Whether a statement locks a large table is decided by the database. You still have to split breaking changes into expand and contract steps across releases.

Is it safe to run schema changes during business hours?

Additive changes on small tables usually are. On large or busy tables, even an online change adds load and needs a brief lock, so run it at a quiet time with a lock timeout set, after timing it on a production-sized copy.