The Migration That Took the Site Down

The deploy went out at ten past four. The pipeline was green, the migration reported success, and the site was returning errors for six minutes in the middle of it.

Nothing failed. A table was locked while it was rewritten, every request that touched it queued behind the lock, and the queue grew faster than it drained until the connection pool ran out.

Which operations are free and which are not

The distinction that matters is whether the database can change the table's metadata or has to rewrite its rows.

Generally safe, on both major engines:

• Adding a nullable column with no default
• Dropping a column
• Renaming a table
• Adding a constraint that is not validated immediately, where supported

Generally not safe on a large table:

• Adding a column with a default value, on older engines
• Changing a column's type
• Adding an index without the concurrent or online option
• Adding a foreign key, which validates every existing row
• Anything that changes the primary key

The versions matter here, and so do the details: recent MySQL and MariaDB handle instant column addition for many cases, and Postgres has added a nullable column with a default cheaply since version 11. The point is not to memorise the matrix but to know which side of it your migration sits on before it runs against production.

The one that surprises people

Schema::table('orders', function (Blueprint $table) {
    $table->index('customer_id');
});

Adding an index looks like reading, not writing. On MySQL without an online DDL path, and on Postgres without CONCURRENTLY, it takes a lock for the duration of the build - which on a large table is minutes.

Postgres offers the concurrent option, and Laravel does not emit it, so it needs to be written by hand:

public function up(): void
{
    DB::statement('CREATE INDEX CONCURRENTLY orders_customer_id_index ON orders (customer_id)');
}

Two things follow from that statement. It cannot run inside a transaction, so the migration has to opt out of the wrapper. And it can fail partway, leaving an invalid index behind that has to be dropped before retrying - which is worth knowing before it happens rather than during.

Changing a column without rewriting the table

Most type changes - widening an integer, changing a varchar's length, moving a status from a string to a reference - can be done without a single locking operation, in more steps than it looks like they need:

1. Add the new column, nullable, with no default. Instant.
2. Write to both columns in the application, deploy that, and let it run.
3. Backfill the old rows in batches on a queue, with a pause between batches so replication and other traffic can breathe.
4. Verify they agree - a count of rows where they differ should be zero.
5. Switch reads to the new column. Deploy. Wait.
6. Stop writing the old one. Deploy.
7. Drop it, in a later release.

Seven deploys instead of one line. It is also seven deploys during which the site stays up, each of which can be stopped or reversed, which is the trade being made whether or not anybody names it.

The backfill is where the care goes. A single UPDATE over ten million rows is exactly the lock you were trying to avoid, wearing a different hat:

Order::whereNull('customer_uuid')
    ->select('id')
    ->chunkById(1000, function ($orders) {
        Order::whereIn('id', $orders->pluck('id'))
            ->update([/* ... */]);
 
        usleep(100_000);
    });

Rules worth having

Every migration declares whether it rewrites the table. A comment at the top, answered honestly, forces the question to be asked in review rather than in production.

Anything that rewrites runs separately from the deploy. Watched, by somebody, with the ability to stop it.

Nothing that removes is deployed with the code that stops using it. A dropped column breaks every process still running the previous release - including queue workers, which hold their code until they are restarted.

Know your lock timeout. A migration that gives up after thirty seconds is enormously better than one that waits indefinitely while requests pile up behind it. Setting one turns an outage into a failed migration.

The last is the cheapest insurance in this entire article, and it is a line of configuration that most applications have never set.

Which operations lock, and for how long at your row counts, is something a database engagement measures on your data. Estimates are not much use here. If you are still choosing an engine and this is what decides it, the differences are set out per engine.