The migration that locked the table for eight minutes

The deploy looked boring. One migration, adding a last_seen_at column to the users table with a default of now(). I'd written a hundred like it. I ran it at 2pm on a Tuesday because it was "just a column," and for eight minutes the entire application couldn't log anyone in.

Here's the migration. Read it and see if it looks dangerous to you, because it didn't to me.

Schema::table('users', function (Blueprint $table) {
    $table->timestamp('last_seen_at')->default(now())->nullable();
});

The users table had 40 million rows. On our MySQL version, adding a column with a default value that way meant the database rewrote the entire table — every row, copied — while holding a lock that blocked writes. Every login updates a user row. So every login queued behind the migration, the connection pool filled with waiting queries, and the app fell over. Not because it crashed. Because it was politely waiting for a lock that took eight minutes to release.

sec

That flat line at 30 seconds isn't the real latency — it's the connection timeout. Everything past it was just failing.

What actually locks

The trap is that not all ALTER TABLEs are equal. Adding a nullable column with no default is nearly instant on a modern MySQL — it's a metadata change. Adding a column with a default, on the version we were on, was a full table copy holding a lock the whole time. The syntax difference is three words. The production difference is eight minutes of downtime.

I did not know which operations were cheap and which were catastrophic, because on my laptop's 200-row users table, every migration is instant. Small data hides the lock completely. There is no way to feel this in development. You have to know it.

The safe recipe

The fix isn't "be careful." It's a repeatable procedure for any schema change on a big, hot table. Split the one dangerous migration into cheap steps, each of which holds the lock for milliseconds or not at all.

Step one becomes trivially safe:

// Migration 1 — instant, no lock worth mentioning
Schema::table('users', function (Blueprint $table) {
    $table->timestamp('last_seen_at')->nullable();
});

Step two backfills without ever locking the whole table — small chunks, a breath between them, so ongoing writes always get a turn:

User::whereNull('last_seen_at')
    ->orderBy('id')
    ->chunkById(2000, function ($users) {
        User::whereIn('id', $users->pluck('id'))
            ->update(['last_seen_at' => now()]);
        usleep(100_000); // 100ms, let other writes through
    });

Each update touches 2,000 rows and releases. Logins slip between the chunks instead of queuing behind one giant transaction. It takes longer in wall-clock time — and that's the point. You're trading a fast operation that stops the world for a slow one that never does.

Online DDL, when the database offers it

Newer MySQL and Postgres support online/concurrent DDL for many operations — ALGORITHM=INPLACE, LOCK=NONE on MySQL, or tools like pt-online-schema-change and gh-ost that build a shadow table and swap it in. If your database and version support it for the change you need, that's the cleanest path. But you have to check for your specific operation and version — "MySQL supports online DDL" is not the same as "this ALTER, on this version, takes no lock." That gap is exactly the one that bit me.

The lesson I actually took

A migration is code that runs against production data at production scale, with a lock, in the middle of live traffic. I'd been treating migrations as a build step — something that just needs to succeed. They're a runtime event on your busiest table.

Now, before any schema change on a table over a million rows, I ask one question: how long does this hold a lock, and what's queued behind it while it does? If I don't know the answer, I don't run it at 2pm. I find out first — because the demo will run it in 4 milliseconds every single time, and tell me nothing.