It’s 3:17 AM on a Wednesday. Jana, a senior backend engineer, has been asleep for exactly 47 minutes when her phone goes off. Every API endpoint on the dashboard is red - users.get, orders.create, auth.verify. All 504. The error rate has been climbing for 41 minutes. She drills into the slow query log and finds it immediately: a single ALTER TABLE accounts ADD COLUMN last_login_at TIMESTAMP NULL that has been running since 10:36 PM. It isn’t slow. It’s holding an exclusive metadata lock on the accounts table, and every query that touches that table has been queueing behind it ever since.

The migration was kicked off by a developer who added the column, pushed the deploy script, watched the first progress line, and went to bed. Routine operation: add a nullable column, no default. On a table with 10,000 rows this finishes in under a second. On a table with 400 million rows, MySQL is doing a full in-place table rebuild - copying every row into a new structure before swapping. By the time Jana is paged, the copy is 73% done. Killing it means starting over. Not killing it means 13 more minutes of total outage.

She kills it. The connection queue drains. The APIs start responding. She writes “DO NOT RUN THIS MIGRATION” in Slack and spends the next hour learning what she should have done instead.

Database schemas change constantly. New columns, dropped columns, renamed indexes, type promotions - no production system stays frozen. The difference between a migration that runs silently in the background and one that takes down the site for an hour is a matter of technique, not willpower. This is the online schema migration problem: how do you restructure a live table without stopping the world?

Why This Happens

Most databases implement DDL operations - CREATE TABLE, ALTER TABLE, DROP INDEX - as metadata-level changes that require exclusive ownership of the affected object. The specific behavior differs between MySQL and PostgreSQL, but the failure mode is identical at scale.

In MySQL’s InnoDB, many ALTER TABLE variants trigger what the engine calls an in-place rebuild: the original table is copied row by row into a new internal structure, then the old table is discarded. During this copy, the engine holds a metadata lock that blocks conflicting DML - inserts, updates, and deletes - from committing. It isn’t that your queries are being refused. They’re being queued. And those queued queries hold database connections. And the connection pool has a ceiling. When that ceiling is hit, new requests aren’t even queued - they fail immediately.

The failure chain looks like this:

ALTER TABLE accounts (400M rows)
  → exclusive metadata lock acquired by InnoDB
    → all DML against accounts tables into a wait queue
      → waiting queries each hold a connection slot
        → connection pool ceiling reached (100 slots)
          → new incoming requests cannot connect
            → APIs return 504 / connection timeout
              → total service outage

PostgreSQL handles DDL more gracefully in most cases - ALTER TABLE is transactional and short-lived for many operations. But operations that require a table rewrite (adding a column with a non-null default pre-PG11, changing a column’s type, rebuilding an index) still take an AccessExclusiveLock that blocks everything. The same failure chain applies.

The root cause isn’t a bug. Correctness guarantees require exclusivity. Exclusivity at the table level does not scale to hundreds of millions of rows.

The Naive Solution (and Where It Breaks)

The most common first response is the maintenance window: schedule the migration for 2 AM Sunday, put up an “under maintenance” page, run ALTER TABLE, wait. On a table under a million rows this is completely reasonable. It finishes in seconds and nobody notices.

At 400 million rows, the math breaks:

Small scale - 100K rows:
  ALTER TABLE → completes in ~0.3s → zero observable impact

Large scale - 400M rows:
  ALTER TABLE → runs 45-90 minutes depending on row width
  Every read and write to the table is blocked for the duration
  Maintenance window = 90 minutes of hard downtime
  Miss the window or miscalculate time? Back to the 3 AM page.