The problem: an instant change that froze redirects
Snip adds a nullable title column to links. In Postgres that's a metadata-only change that completes instantly.
Yet when it ran in production, every redirect query hung for several seconds and the error rate spiked. The table was
never rewritten. What happened was the lock queue.
The lock queue, demonstrated
ALTER TABLE needs an ACCESS EXCLUSIVE lock, which conflicts with everything, even plain reads. If any transaction
is using the table, the ALTER waits. And while it waits, every new query on the table queues behind it, because
the database won't let new readers jump ahead of a waiting exclusive lock.
Tested on PostgreSQL 16 with a 100,000-row links table:
| Step | Without lock timeout | With lock_timeout = '1s' |
|---|---|---|
| A 6-second report query holds the table | Running | Running |
ALTER TABLE links ADD COLUMN title text starts | Waits for the report | Waits up to 1 s, then gives up with an error |
A one-row redirect SELECT arrives | Waited about 4 seconds, behind the ALTER | Finished in about 0.7 s, once the ALTER gave up |
At 100,000 redirects a second, four seconds of queueing is 400,000 stuck requests, from a change that itself does no work at all.
The safety rules
1. Always set a lock timeout for migrations, and retry. SET lock_timeout = '2s' makes the migration fail fast
instead of freezing the table; the migration tool retries a few seconds later, ideally when no long query is running.
Also set a statement_timeout so a migration that turns out to be slow doesn't hold its lock for minutes.
2. Avoid long transactions on hot tables. Reports and analytics go to replicas (04.07 Replication: leaders, followers and lag), so the primary rarely has a long-running query to wait behind.
3. Build indexes concurrently. CREATE INDEX CONCURRENTLY builds without blocking writes, taking longer and
needing a retry if it fails partway (it leaves an invalid index to drop).
4. Add constraints in two steps. Adding a foreign key or check constraint normally scans the whole table under a
heavy lock. Add it NOT VALID first, which skips the scan and applies only to new rows, then run VALIDATE CONSTRAINT, which scans under a much weaker lock (SHARE UPDATE EXCLUSIVE) that doesn't block reads or writes.
5. Backfill in throttled batches. Update a few thousand rows per transaction by primary-key range, pause between batches, and back off when replication lag grows (04.09 Schema changes and online migrations).
When the table must be rebuilt
Some changes really do rewrite the table: changing a column's type, or reorganising a huge table. For those, online schema change tools build a new copy beside the old one, keep it in sync, and swap at the end. GitHub's gh-ost, for MySQL, creates a "ghost" table, copies rows into it incrementally, follows ongoing changes by reading the database's binary log rather than using triggers, throttles itself on load and replication lag, and cuts over by swapping the tables, a moment the operator can postpone until they're ready.
Migrations in the deploy pipeline
- Migrations deploy separately from code, each step backward-compatible (14.05 Safe deploys): add the column, deploy code that uses it, and only drop old columns in a later release.
- Run them at quiet times when they need any heavy lock, and watch the same dashboards as a code deploy.
- Rehearse on a copy of production-sized data: a migration that's instant on 1,000 rows can take an hour on 600 million.
Seen in production
PostgreSQL
DocsALTER TABLE locks
An ACCESS EXCLUSIVE lock is acquired unless explicitly noted for a particular form. Adding a constraint with NOT VALID skips the potentially lengthy scan of existing rows while still applying it to subsequent inserts and updates, and VALIDATE CONSTRAINT later checks the existing rows while acquiring only a SHARE UPDATE EXCLUSIVE lock.
What we foundALTER TABLE takes ACCESS EXCLUSIVE unless noted; NOT VALID skips the scan, and VALIDATE takes a weaker lock.
ALTER TABLE (PostgreSQL documentation)postgresql.org ↗GitHub
2016gh-ost
gh-ost is GitHub's triggerless online schema migration tool for MySQL. Like other online tools, it creates a ghost table and slowly, incrementally copies data into it; unlike trigger-based tools, it intercepts ongoing changes by tailing the binary logs. It throttles on load, replication lag or a flag file, and the cut-over, the moment the tables are swapped, can be postponed with a flag file until the operator chooses.
What we foundCopy into a ghost table, follow changes from the binary log, throttle, and cut over when the operator is ready.
gh-ost: GitHub's online schema migration tool for MySQL (Shlomi Noach)github.blog ↗A migration adding a nullable column (an instant change) has been 'running' for 3 minutes, and the API is timing out. What's most likely happening?
Recap
Interview questions
How do you run schema migrations on a large, busy production database without downtime?