Zero-downtime database migrations

Zero-downtime database migrations

Database migrations are often the riskiest part of a deployment. Here is how to run them without any interruption.

Application code is easy to deploy without downtime: start the new version, switch traffic, stop the old one. The database, however, is shared by both versions during that switch. Two risks follow: a schema that is incompatible with one of the code versions, and locks that block queries for the duration of an ALTER TABLE. A zero-downtime migration has to avoid both.

Why the schema must stay compatible

During a blue-green or rolling deployment, the old and new versions of the code run at the same time, sometimes for several minutes. If the migration renames a column, the old version crashes as soon as it reads it. If it adds a NOT NULL column with no default value, the old version crashes as soon as it inserts a row. The golden rule: every migration must be compatible with the code currently in production and with the code about to replace it. Rollback must remain possible at all times.

Fundamental rules

• Never drop a column that production code still uses
• Always add new columns as nullable
• Keep schema migrations separate from data migrations
• Test migrations on a copy of the production database

A column can also be added with a default value, as in the example below: what matters is that the old code can keep inserting rows without knowing about it. Testing on a production copy is essential, because a migration that is instant on a development database of a few thousand rows can take many minutes on a table with tens of millions of rows.

The Expand-Contract pattern

The Expand-Contract pattern (or parallel change) breaks any incompatible change down into compatible steps, spread across several deployments.

Phase 1 - Expand: Add the new structure

-- Migration 1: Add the new column
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

-- Migration 2: Backfill the data
UPDATE users SET email_verified = TRUE WHERE verified_at IS NOT NULL;

Phase 2 - Deploy the code that uses both columns

During this phase, the code writes to both columns and reads the new one. Rows created by the old version during the switch do not have the right value yet: run the backfill again once the deployment is finished to catch them up.

Phase 3 - Contract: Remove the old structure

-- Migration 3: Drop the old column
ALTER TABLE users DROP COLUMN verified_at;

This last migration ships in a later deployment, once no version running in production reads the old column any more. Also remove it from the Doctrine mapping before dropping it, otherwise the ORM will keep including it in its queries.

The same principle applies to renaming a column, which you never rename directly:

1. add the new column;
2. deploy code that writes to both columns;
3. copy existing data in batches;
4. deploy code that reads the new column;
5. deploy code that no longer writes to the old one;
6. drop the old column.

Backfilling data in batches

A single UPDATE on a large table opens a long transaction, locks many rows and increases replica lag. On MySQL, which accepts LIMIT in an UPDATE, process the rows in small batches instead:

$batchSize = 1000;

do {
    $affected = $connection->executeStatement(
        'UPDATE users SET email_verified = TRUE
         WHERE verified_at IS NOT NULL AND email_verified = FALSE
         LIMIT ' . $batchSize
    );

    // Give the database and replicas room to breathe
    usleep(100_000);
} while ($affected > 0);

Each batch is a short transaction. The script can be interrupted and restarted safely, because the email_verified = FALSE condition skips rows already processed. Put this code in a Symfony command rather than in a Doctrine migration: it can run for a long time and must be restartable independently of the schema.

Optimized Doctrine migrations

public function up(Schema $schema): void
{
    // Use non-blocking operations
    $this->addSql('ALTER TABLE orders ADD COLUMN status VARCHAR(50) DEFAULT NULL');

    // For large tables, use pt-online-schema-change
    // or gh-ost to avoid locks
}

On MySQL 8.0, adding a column usually uses the INSTANT algorithm, with no table copy. For other operations, explicitly request a lock-free algorithm: if MySQL cannot honour it, the query fails immediately instead of blocking the table.

ALTER TABLE orders ADD INDEX idx_orders_status (status), ALGORITHM=INPLACE, LOCK=NONE;

On PostgreSQL, an index can be created without blocking writes using CREATE INDEX CONCURRENTLY, which cannot run inside a transaction. You therefore need to disable Doctrine Migrations' wrapping transaction for that migration:

final class Version20241012120000 extends AbstractMigration
{
    public function isTransactional(): bool
    {
        return false;
    }

    public function up(Schema $schema): void
    {
        $this->addSql('SET lock_timeout = \'5s\'');
        $this->addSql('CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status)');
    }
}

The lock_timeout protects against a classic trap: an ALTER TABLE, even a fast one, must wait for ongoing transactions on the table to finish, and every subsequent query piles up behind it. With a short timeout, the migration fails cleanly and can be retried, instead of freezing the whole application. The MySQL equivalent is SET SESSION lock_wait_timeout = 5.

Online migration tools

When MySQL has to rebuild the table (changing a column type, for instance), pt-online-schema-change and gh-ost create a copy of the table with the new schema, keep it in sync during the copy, then swap the two tables in a very short operation:

# Percona Toolkit: test, then execute
pt-online-schema-change --alter "MODIFY status VARCHAR(100) DEFAULT NULL" D=app,t=orders --dry-run
pt-online-schema-change --alter "MODIFY status VARCHAR(100) DEFAULT NULL" D=app,t=orders --execute

# gh-ost: relies on the binlog rather than triggers
gh-ost --host=db.internal --user=app --ask-pass --database=app --table=orders \
  --alter="MODIFY status VARCHAR(100) DEFAULT NULL" --allow-on-master --execute

Recommended tools

• pt-online-schema-change: lock-free migrations for MySQL
• gh-ost: GitHub's alternative for online migrations
• Doctrine Migrations: versioned migration management
• Flyway: multi-database migration tool

Common pitfalls

• doctrine:schema:update --force in production: this command can drop or rename columns without warning. In production, only versioned and reviewed migrations should touch the schema.
• Review the generated SQL: doctrine:migrations:diff sometimes produces a DROP followed by an ADD where you expected a rename.
• Foreign keys and indexes on large tables: creating them can take a long time. Measure on the production copy.
• The down() method: do not rely on it for a production rollback. Thanks to Expand-Contract, reverting to the previous version of the code is enough, without touching the schema.

Checklist before every migration

1. Is the migration compatible with both the current code and the new code?
2. Has it been timed on a copy of the production database?
3. Are heavy operations done online or in batches?
4. Is a lock wait timeout set?
5. Is the removal of old structures deferred to a later deployment?

These rules mean a few more deployments for a given change, but each one becomes routine, reversible and invisible to users.