How do you run a schema migration with zero downtime?

Question

Accepted Answer

The key realization is that during a rolling deploy the old and new versions run side by side, sometimes for 10 or 15 minutes while pods roll, so every schema change has to be compatible with both at once. You cannot ship something the currently running old code would break on. The pattern for that is expand and contract, also called parallel change. First expand: add the new structure without touching the old, say a new nullable column or a new table, and deploy code that writes to both the old and new locations so new writes land in both. Then backfill the existing rows in batches, I usually do 1,000 to 5,000 rows per batch with a short pause between them, so I never hold a long lock or push replication lag past a second or two. Once the new structure is fully populated and consistent, I move reads over and verify. Only then, in a separate deploy days later, do I contract and drop the old column or table. Throughout I watch locking, because some operations take an exclusive lock that blocks every query, the classic example being adding a NOT NULL column with a default, which on older MySQL and Postgres rewrote the whole table. On a 50 million row table that is minutes of downtime, so I use online or safe migration techniques and cap any statement with a lock timeout of a few seconds. The contrast with a big ALTER in a maintenance window is that the window is itself downtime, and a long lock can still take the app down, whereas expand and contract ships the same change in small, reversible, always compatible steps.

Answer

Why can you not just rename a column in one migration?

During a rolling deploy, old code still references the old name and would break the moment you rename. Instead: add the new column, write to both, backfill, switch reads, then drop the old one, expand/contract across separate deploys.

Answer

What kinds of schema operations are dangerous because of locking?

Ones that take a long exclusive lock or rewrite the table, some ALTERs, adding a NOT NULL with a default on older engines, adding an index without the concurrent/online option. Use online DDL and batched backfills so traffic is never blocked.

How do you run a schema migration with zero downtime?

How do you run a schema migration with zero downtime?

The follow-ups they ask next

What the interviewer is listening for

What sinks the answer

If you genuinely do not know

Keep going with databases

Knowing the answer is not the same as recalling it under pressure