The problem: the table never stops
Snip wants to rename links.long_url to target_url, and add a title column. On a laptop that's two statements and
a second. On a 600-million-row table taking thousands of reads a second, the wrong statement can lock the table
while the database rewrites every row, and every redirect waits behind it.
Two things make schema changes risky in production:
- Locks. Some changes need an exclusive lock on the table; some rewrite the whole table while holding it.
- Old and new code at once. During a deploy, servers running the previous version and the new version both talk to the same database. Every intermediate state of the schema has to work for both.
Know which changes are cheap
What locks and what doesn't varies by database and version, so check your database's documentation. The usual shape, for Postgres:
| Change | Typically |
|---|---|
| Add a nullable column, no default | Instant: only the table's description changes |
| Add an index | Blocks writes for the whole build, unless built CONCURRENTLY |
| Change a column's type | Often rewrites the whole table under an exclusive lock |
Add a NOT NULL constraint | Scans the table to check every row |
| Rename a column | Instant for the database, but breaks every running app that uses the old name |
Two habits avoid most outages: build indexes concurrently, and set a short lock timeout on migration sessions. Even an instant change must first acquire its lock; if a long query holds the table, the migration waits, and every new query queues behind the waiting migration. A lock timeout makes the migration give up and retry instead of freezing the table.
Expand and contract
A rename looks atomic, but code that reads long_url breaks the moment it's gone. The safe method splits the change
into steps, each compatible with the code before and after it:
- 01
Expand
Add target_url, nullable. Old code ignores it.
- 02
Dual write
Deploy code that writes both columns.
- 03
Backfill
Copy old rows' values, in small batches.
- 04
Switch reads
Deploy code that reads target_url.
- 05
Stop old writes
Deploy code that writes only target_url.
- 06
Contract
Drop long_url once nothing uses it.
Each step can be deployed, watched and rolled back on its own. It's slower than one ALTER, and it's the only way that
never breaks a running request.
Backfilling 600 million rows deserves care of its own: update in batches of a few thousand rows by primary key
range, pause between batches, watch replication lag (04.07 Replication: leaders, followers and lag), and make the job resumable. One giant
UPDATE would hold locks for hours, bloat the table (04.05 MVCC) and leave replicas far behind.
The same pattern moves data between tables or even databases: write to both, backfill, compare, switch reads, switch writes, remove the old.
Seen in production
Stripe
2017Subscriptions data model
Stripe moved subscriptions out of customer records into their own table, for hundreds of millions of objects. It used four phases: dual write to the old and new tables, change all reads to the new table, change all writes to only the new table, and remove the old data. Reads were compared during the switch, and no single change touched more than a few hundred lines of code.
What we foundHundreds of millions of objects were moved to a new table with no downtime by dual writing, then switching reads, then writes.
Online migrations at scalestripe.com ↗GitHub
2016gh-ost
GitHub's tool creates a "ghost" table with the new schema, copies rows into it slowly and incrementally, and keeps applying ongoing inserts, updates and deletes by reading MySQL's binary log rather than using triggers. When the tables are in sync it swaps them. GitHub built it because trigger-based tools had caused near or complete lockdowns in production, and some migrations could only run off-peak or at weekends.
What we foundChange a big table by building a copy alongside it, keeping the copy in sync, and swapping them at the end.
gh-ost: GitHub's online schema migration tool for MySQLgithub.blog ↗A migration runs ALTER TABLE links ADD COLUMN title text (an instant change in Postgres). Yet every query on links hangs for two minutes. What's the most likely cause?
Recap
Interview questions
How would you rename a column on a large, busy table without downtime?