Changing Database Schemas in Production Without Downtime

Changing a large table on a production database without downtime comes down to three rules. First, know what kind of change you are making before you run it. In MySQL 8, adding a column at the end of a table or changing its default runs with ALGORITHM=INSTANT in a fraction of a second regardless of table size, while changing a column type or the primary key rebuilds the whole table, which takes minutes or hours. Second, for heavy changes use a tool that builds a shadow copy of the table, copies rows in batches and swaps the names in one instant, such as gh-ost or pt-online-schema-change. Third, never change something the current code depends on in a single step. Follow the expand, migrate, contract pattern instead: add the new thing next to the old, move data and code over gradually, then remove the old in a later deploy. The most important line before any migration is SET SESSION lock_wait_timeout = 5, because it prevents the worst scenario: an ALTER waiting on a lock, every query on the table queuing behind it, and the whole site stalling in under a minute.

Why ALTER TABLE locks a table with millions of rows

MySQL applies schema changes with one of three algorithms, and the gap between them is the gap between an unnoticed change and an hour of downtime:

Algorithm	What happens	20 million row table	Examples
INSTANT	Only table metadata changes	Under a second	Add a column, change DEFAULT, rename a column
INPLACE	Internal rebuild, writes usually allowed	5 to 40 minutes	Add or drop an index
COPY	Full table copy with writes blocked	20 minutes to hours	Change a column type or the primary key

These figures are rough for SSD storage and vary with row width and index count, but the ratio holds. The biggest danger is not the algorithm, though, it is the metadata lock. Every ALTER needs it, if only for a moment, and that includes INSTANT. If a long transaction is open on the table, such as a report that has been running for 10 minutes or a connection that never closed its transaction, the ALTER waits, and every new SELECT on that table queues behind the waiting ALTER. Within seconds the PHP-FPM pool fills up and 502 errors appear. The fix is a short timeout so the ALTER fails safely instead of freezing the site:

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN tracking_code VARCHAR(64) NULL, ALGORITHM=INSTANT;

Writing ALGORITHM=INSTANT explicitly has a second benefit. If the change cannot run instantly, MySQL rejects it with an error instead of silently falling back to COPY and locking the table. Do the same with ALGORITHM=INPLACE, LOCK=NONE when adding indexes.

The expand, migrate, contract pattern

Take a real example: you want to rename the phone column to mobile in a customers table. If you rename it directly and then deploy code, there will be seconds or minutes where old code runs against the new schema or the other way around, and every request in that window fails. The safe pattern splits the work into four separate deploys:

1. Expand: add an empty mobile column (INSTANT) and leave phone alone.
2. Dual write: deploy code that writes to both columns and reads from the old one.
3. Migrate: backfill old data in small batches, then deploy code that reads from the new column.
4. Contract: after a few clean days, drop phone in a final migration.

The backfill in step three should never be a single UPDATE across 20 million rows, since that opens a huge transaction that locks rows and bloats the undo log. Write it as an artisan command that processes 5,000 rows per batch with a short pause:

DB::table('customers')
    ->whereNull('mobile')
    ->chunkById(5000, function ($rows) {
        foreach ($rows as $row) {
            DB::table('customers')->where('id', $row->id)
                ->update(['mobile' => $row->phone]);
        }
        usleep(100000); // 100ms breather for replication
    });

At this pace 20 million rows finish in roughly one to two hours without anyone noticing, and the short pause lets replicas catch up so lag does not pile up.

gh-ost and pt-online-schema-change for heavy changes

When a change falls into the COPY category, such as moving a primary key from INT to BIGINT because it is approaching its ceiling of 2,147,483,647, you need a tool that does the copy itself without locking. The two best-known tools share the same idea: create a shadow table with the new schema, copy rows in batches, track ongoing changes, then swap the names atomically.

gh-ost --host=127.0.0.1 --database=app --table=orders \
  --alter="MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" \
  --chunk-size=1000 --max-load=Threads_running=25 \
  --critical-load=Threads_running=60 --cut-over=default --execute

The key difference between them is how they track changes:

Criterion	gh-ost	pt-online-schema-change
Change tracking	Reads the binlog	Triggers on the original table
Load on the table	Lighter, no extra synchronous writes	Every write happens twice
Pausing mid-run	Supported	Limited
Requirements	binlog_format=ROW	Works with most setups
Foreign keys	Not supported	Supported with special options

The --max-load and --critical-load options are your safety valves. The first slows the copy when active queries exceed 25, and the second aborts the whole run if they reach 60. Tune those numbers to your database's normal load, and watch disk space, because the shadow table needs roughly as much room as the original. A 40 GB table needs at least 40 GB of extra free space. If space is tight, adding 100 GB of SSD to your cloud server costs 500 SAR per year, far cheaper than a migration that died halfway because the disk filled up.

Ordering code deploys and migrations

The simple rule is that every migration must be compatible with both the current code and the new code, because there is a moment when both are running. In practice that means:

• Additions (nullable column, new table, index) run before deploying the code that uses them.
• Removals (column, table) run after the code no longer references them, in a separate deploy.
• A new NOT NULL column gets a DEFAULT, or inserts from the old code will fail.
• Never put a schema change and a large data change in the same migration file.

In Laravel, keep in mind that php artisan migrate in a deploy script runs with no timeout by default. Heavy migrations on large tables should be excluded from automatic deploys and run by hand with the tools above, then recorded in the migrations table so Laravel does not try to run them again.

A rollback plan for when things go wrong

The down() method in Laravel migrations is convenient in development, but on a 20 million row table it means a second heavy change at the worst possible moment. In production we prefer fixing forward: a small new migration that corrects the mistake instead of reversing a large one. The expand and contract pattern makes this easy by design, because the old column stays in place throughout the transition, so if the new code misbehaves you simply roll back the code with no database change at all. gh-ost leaves the original table behind after the swap under a name like _orders_del, so you can rename back within seconds if you spot a problem right after the cut-over, provided few new writes have landed in the new table. Keep that table for a day or two and drop it once you are confident, since it holds as much space as the full table.

Checklist before any migration on a large table

1. Check the table size and row count in information_schema.TABLES.
2. Run the change on staging with realistic data volume and record the time.
3. Write ALGORITHM and LOCK explicitly so MySQL refuses instead of surprising you.
4. Look for long-running transactions in information_schema.INNODB_TRX before starting.
5. Set lock_wait_timeout to 5 seconds for the session.
6. Confirm you have a recent backup whose restore has actually been tested.
7. Check free disk space if you are using gh-ost or pt-osc.
8. Run during low traffic and watch replication lag and connection count.

The backup in item six is not a formality. Plenty of incidents start with a contract migration that dropped a column, only for someone to discover an old report still needed it. Daily backup with 30-day retention is available as an add-on at 2,000 SAR per year, and every add-on is listed on the pricing page.

The practical decision

Before any migration ask one question: is this change INSTANT? If it is, run it with a short lock timeout and you are done. If not, and the table has fewer than a million rows, INPLACE with LOCK=NONE during a quiet window is usually enough. Above that, use gh-ost, and split any change that breaks compatibility into expand, migrate and contract across several deploys. These steps add a day or two to the task, but they turn migrations from a frightening event into routine work.