Zero-downtime database migrations: changing the schema of a live project

Zero-downtime database migrations happen in several steps: first the schema is expanded so both the old and new code understand it, then the code is deployed, data is moved in batches and only at the end is anything removed. The key rule: no migration should lock a table that users are working with for long.

Here is why migrations break releases, which operations are dangerous and how to make common schema changes safely.

Why do migrations break releases?

Migrations break releases for two reasons: long table locks and schema-code incompatibility. While a migration holds a lock, queries to that table wait, database connections run out and the site stops responding. And if a column is renamed before the code is updated, old application instances start failing.

During a release there is almost always a moment when the old and new versions of the application run side by side. So the schema must suit both at every moment. We covered the release process itself in zero-downtime deployments.

Which operations are dangerous?

Operations that rewrite the whole table or take an exclusive lock for a long time are dangerous. The exact list depends on the database and its version, so check the documentation and run the migration on a copy of the data before release.

Operation	Risk	Safe alternative
Add a NOT NULL column without a default	Error or table rewrite	Add as nullable, backfill, then add the constraint
Create an index the usual way	Writes are blocked	Create the index concurrently or online
Rename a column	Old code stops working	New column, dual writes, backfill, drop
Change a column type	Table rewrite	New column of the right type and a data move
Bulk UPDATE of the whole table	Long locks, log growth	Update in batches in the background

Photo: Unsplash

How do you rename a column without downtime?

Rename a column with the expand-contract pattern: add a new column, write to both for a while, move the old data and only then drop the old column. Each step is a separate release, and the system can be rolled back after each one.

1. Expand. Add a new nullable column.
2. Dual write. Deploy code that writes to both columns but reads from the old one.
3. Backfill. Fill the new column for old rows in batches with a background job.
4. Switch reads. Deploy code that reads from the new column.
5. Contract. Stop writing to the old column, then drop it in a separate migration.

It sounds long, but most changes fit into two or three ordinary releases.

How do you move data in large tables?

Move data in large tables in batches of a few thousand rows with pauses in between, not in one query. Each transaction stays short, locks do not pile up and the process can be stopped and resumed from the same point.

DB::table('orders')
    ->whereNull('customer_name')
    ->chunkById(5000, function ($orders) {
        foreach ($orders as $order) {
            DB::table('orders')
                ->where('id', $order->id)
                ->update(['customer_name' => $order->client_name]);
        }
        usleep(200_000);
    });

Jobs like this are best run through a queue — see job queues in a web application. Watch database load and replication lag while the backfill runs.

How do you check migrations before release?

Check migrations on a copy of the production database with real data volumes: only then can you see how long the operation takes and which locks it acquires. A lock timeout also helps — if a migration cannot get its lock, it fails instead of freezing the site.

What to check:

• how long the migration takes on real data volumes;
• that it takes no long exclusive locks;
• that the old code version works with the new schema;
• that every step has a rollback plan;
• that a fresh backup exists before release.

For more on indexes and slow queries, see PostgreSQL indexes.

In short

• At every moment of a release the schema must suit both the old and the new code.
• Avoid operations that rewrite a table or lock it for long.
• Rename columns and change types with the expand-contract pattern.
• Move data in large tables in background batches.
• Test migrations on a copy of production data and set a lock timeout.