Online schema migration: an interactive expand and contract simulator

Changing a database schema while the application keeps serving is hard for one reason: during a rolling deploy, old and new code run side by side against the same tables. Every step of an online schema migration must therefore work with both versions. The standard way is expand and contract: add the new shape alongside the old, move code and data over in steps, and remove the old shape last.

The playground renames users.fullname to display_name during an hour of traffic on four app servers, three ways, and counts failed requests, stale reads and lost changes.

Why a one-step rename breaks

Renaming the column first breaks every server still running code that uses the old name, until the deploy reaches it: 1,040 failed requests here. Deploying first breaks the new code instead, because the column it expects does not exist yet. With one database and a fleet of servers there is no single moment to switch; the change has to be split so that every intermediate state works for every running version.

Expand and contract, step by step

• Expand. Add the new column, nullable, with no code change.
• Dual write. Deploy code that writes both columns but still reads the old one.
• Backfill. Copy existing values into the new column, in batches, once every server writes both.
• Switch reads. Deploy code that reads the new column while still writing both, so servers on the previous version keep seeing current data.
• Stop the old writes, then contract: drop the old column when nothing uses it.

Order matters. Backfilling before every server writes both columns copies values that old servers then change in the old column only: in the playground that produces 95 reads of an outdated name and, once the old column is dropped, 36 users whose latest change is gone, with no error anywhere. Done in order, there are no failures and no lost changes, at the cost of three deploys instead of one, and each step can be rolled back.

Locks and big tables

• Know which DDL rewrites the table. In PostgreSQL, adding a nullable column or one with a constant default is instant; changing a column’s type usually rewrites the table under an exclusive lock. Set lock_timeout so a migration waiting for a lock does not stall all traffic queued behind it.
• Build indexes concurrently (CREATE INDEX CONCURRENTLY) and add constraints as NOT VALID, validating them afterwards.
• Backfill in batches with pauses, so locks stay short and replicas keep up.
• For MySQL, online DDL covers many changes, and tools such as gh-ost and pt-online-schema-change copy the table in the background and swap it in.

The rename as expand and contract in SQL

-- 1. expand: adding a nullable column only changes metadata
ALTER TABLE users ADD COLUMN display_name text;

-- 2. deploy code that writes both columns and still reads fullname

-- 3. backfill in small batches: short locks, little replication lag
UPDATE users SET display_name = fullname
WHERE id IN (SELECT id FROM users WHERE display_name IS NULL LIMIT 1000);
-- repeat until it updates 0 rows

-- 4. deploy code that reads display_name, still writing both
-- 5. deploy code that writes only display_name

-- 6. contract
ALTER TABLE users DROP COLUMN fullname;

In a system design interview

When a design changes a data model that is already live, describe the migration: “We add the new column, deploy dual writes, backfill in batches of a thousand rows with throttling, switch reads behind a feature flag, verify by comparing both columns on a sample, then stop the old writes and drop the column a release later. Each step is backward compatible and can be rolled back.” Expect the follow-ups: how long a backfill of a billion rows takes, migrating to a different database entirely, and how you verify nothing was lost.

Read the guide to online schema migrations or schema evolution, see graceful shutdown for the deploys themselves, or watch a strong candidate design Reddit.

Questions people ask