How production teams change the schema of a hot relational table without stopping writes, reconstructed from the incident trackers, migration tools, design records and safety libraries where fourteen organisations wrote down what they learned. A reader leaves able to name the failure mode that takes migrated systems down, choose between the three architectures that survive it, and set the two timeouts that decide whether a bad migration hurts customers or only the deploy queue.
The problem, who has solved it in production, and the one mechanism that explains most of the failures.
State the problem without naming a database: a team must change the shape of data that a live system reads and writes thousands of times a second, without stopping those reads and writes, without corrupting what is stored, and without breaking the application processes that still remember the old shape. Every relational store makes this hard in the same place. A structural change needs exclusive access to the table's definition, however briefly, and exclusive access to a hot table is exactly what a live system cannot give.
The surprise in this record is where the danger actually sits. It is not the duration of the exclusive lock; most schema changes hold it for milliseconds. It is the queue that forms while the lock is waited for. GoCardless's engineers put the mechanism in one sentence in the README of the library they built after it bit them:
“Even if the lock is only held briefly, it will block all other access to the table while it is in the lock queue, as it conflicts with all other locks.” GoCardless, activerecord-safer_migrations README [source], checked 2026-09-21
A migration that waits behind one long-running query becomes a wall: every ordinary read and write queues behind the waiter, and a table that nobody has locked yet is effectively offline. The industry's defence against this mechanism is the same everywhere it has been encoded: give the migration a short lock acquisition timeout and retry, so it fails instead of queueing traffic behind it. GoCardless defaults the timeout to 750 ms. GitLab starts at 100 ms and retries on a schedule bounded at roughly 40 minutes. Doctolib ships 5 s. Three companies, three codebases, one rule, arrived at independently; this guide treats that convergence as the field's core finding.
The second finding follows from the first. Where the timeout-and-retry rule is fully institutionalised, the failure mode changes address. GitLab.com's production tracker records four schema-migration incidents between April and September 2026, and all four read the same way: the migration could not get its lock, aborted safely, blocked the deploy pipeline, and customers never noticed. The guardrail did not eliminate the failure; it moved it from the request path to the deploy path and downgraded it from an outage to an operational chore. That is what a good containment design looks like in an incident tracker: boring, recurring, severity 3.
This guide covers structural change to a single hot table in a relational store: Postgres and MySQL in-place DDL, shadow-table migration tooling, the schema-versioning protocol used by distributed SQL engines, and the application-side protocol that spans all three. It deliberately excludes resharding and splitting databases, engine swaps and cross-store migrations (covered by the 2026-08-29 resharding, 2026-09-19 engine-swap and 2026-09-20 GitLab digs), event and API schema evolution, and document stores.
Evidence constraint, stated plainly: this research environment could reach only github.com and gitlab.com. Every claim here traces to repository-hosted material fetched on 2026-09-21: incident trackers, design docs, tool source, release notes and safety libraries. Engineering blog posts, conference talks and the papers hosted at vldb.org were unreachable; the F1 schema-change paper (Rae et al., PVLDB 2013) is cited through the two fetched implementations that quote it. Where a category is absent, that absence is the environment's, not the field's.
Three architectures recur across every published system, and they map to what the underlying engine makes cheap.
Postgres-lineage teams alter the real table. The engine makes most structural changes
metadata-only, and since Postgres 11 that includes adding a column with a default, so the
dangerous part is not the work; it is the lock acquisition. The Postgres reference
documentation states the default flatly: for ALTER TABLE, “an ACCESS
EXCLUSIVE lock is acquired unless explicitly noted”
(alter_table.sgml,
checked 2026-09-21). So the discipline is entirely about how you wait. Set
lock_timeout low, retry on failure, and decompose anything that must touch
every row into a non-blocking variant: CREATE INDEX CONCURRENTLY instead of a
blocking build, add a constraint NOT VALID and validate it in a second
statement, backfill in batches outside the migration's transaction. Doctolib's library
documents the split for foreign keys: “Adding the constraint itself is rather fast,
the major part of the time is spent on validating this constraint”
(safe-pg-migrations README).
Validation scans rows under a lock that permits reads and writes; the two-step form buys
the same end state without the outage.
MySQL-lineage teams historically could not alter large tables in place, so the tooling builds a second table. SoundCloud's Large Hadron Migrator README preserves the original problem statement from the early 2010s: “the locking nature of ALTER TABLE may take your site down for an hour or more while critical tables are migrated” (LHM README). The shadow-table architecture that answers it has five components, stable across fifteen years of tools: a ghost table in the target shape; a chunked row copy; a capture channel that replays concurrent writes onto the ghost; a throttle that watches replica lag and load; and a cut-over that atomically renames the tables. The divergence point is the capture channel, and it is the most argued-over component in this record. Facebook's OSC, Percona's pt-online-schema-change and LHM used triggers on the original table. GitHub rejected triggers in 2016 with production evidence: “We have evidenced near or complete lock downs in production, to the effect of rendering the table or the entire database inaccessible due to lock contention,” and, decisively, a trigger cannot be paused: “even as the online operation throttles, the master is brought down by the load of the triggers” (why-triggerless.md). gh-ost reads the binary log instead, which makes the migration's write load genuinely detachable from the table's workload.
Distributed SQL engines cannot take a table-wide lock at all, so they adopt the protocol
Google published for F1 in 2013: treat the schema as data that steps through intermediate
states, with the invariant that no two servers are ever more than one version apart.
CockroachDB's 2015 RFC implements it directly and states the safety argument: an index or
column moves DELETE_ONLY → WRITE_ONLY → PUBLIC, “it is invalid
to jump from the DELETE_ONLY to PUBLIC state or vice versa,” and with only two
consecutive descriptor versions live at once, “a cluster will not create invalid
data”
(CockroachDB RFC).
TiDB reimplemented the same machine independently in 2018 with the same invariant:
“there are at most two different versions of the schema on the same table for all
nodes of the system”
(TiDB design doc).
The backfill runs as many small transactions between state steps, which is the same chunked
copy as path two wearing a different coat.
None of the database-side machinery saves you from the application's memory of the old
schema. Rails caches the column list per process, so dropping a column crashes queries
until every process restarts; strong_migrations states it as the first dangerous operation
in its catalogue
(README).
GitLab's protocol for removing one column spans three releases: mark it ignored in release
M, drop it in a post-deployment migration in M+1, remove the ignore rule in M+2
(avoiding-downtime doc).
Xata's pgroll generalises the idea into infrastructure: it serves both schema versions at
once through views, so old and new application code run against the same physical table and
rollback is a search_path change
(pgroll README). Note
what that is: the F1 two-version invariant, rebuilt at the application boundary of a
single-node Postgres. The distributed protocol and the deploy-safety folklore are the same
idea at different layers, and neither source says so; that connection is this guide's
inference.
Each fork with the reason recorded by the team that took it, and the condition that flips the answer.
| Decision | Chosen | Rejected | Because | Evidence |
|---|---|---|---|---|
| Try native instant DDL first? | Yes, with fallback | Always shadow-copying | MySQL 8.0 metadata-only changes skip the copy entirely; residual risk is the metadata lock during long transactions | gh-ost flags doc |
| Cut-over: atomic or two-step rename? | Atomic swap behind a sentry table | Two-step rename (FB OSC) | Between the two renames “your table just does not exist, and queries will fail” | cut-over.md, issue #82 |
| Backfill inside the DDL transaction? | Batched, throttled, outside | One UPDATE in the migration | In-transaction backfill holds the lock for the whole backfill; GitLab throttles on WAL backlog, autovacuum and apdex | strong_migrations, GitLab batched migrations |
| Who may change a big table at all? | Hard size gates in CI | Reviewer judgement | GitLab.com refuses new indexes above 50 GB and new columns above 100 GB via RuboCop rules, with a formal exception process | large tables limitations |
| Safety by magic or by naming? | Explicit safe_/unsafe_ prefixes | Auto-rewriting migrations | Braintree: better that long-running operations “are not a surprise during your deploy cycle” than hidden by helpers | pg_ha_migrations |
| Lock ordering for FK removal | Lock referenced table first (reverse order) | Natural statement order | Prevents deadlocks against concurrent application transactions that lock parent then child | GitLab avoiding-downtime doc |
Five failure classes account for everything in this record: the queue behind the lock, the copy that diverges, the contested cut-over, the application that remembers, and the backfill that becomes a workload.
projects and deployments tables.reverse_lock_order: true, locking the referenced table before the source to match application ordering.insert ignore. A collation change made previously distinct values collide under a unique key, and the colliding rows were “ignored, then lose data,” per issue #1526 (2025). Issue #1039 (2021) shows a second variant: under semi-sync replication a transaction blocked in commit was invisible to both the binlog listener and the copy boundary query, so its row reached neither table.--panic-on-warnings; the deeper mitigation is GitHub's own practice of continuously migrating the production fleet on replicas and checksumming results.One reading across all six cards: no incident in this record was caused by the schema change itself being wrong. The failures live in the machinery around it: acquiring the lock, copying the rows, swapping the tables, and the application's memory. That matches what the resharding dig found about cutovers a month ago, and it says where review effort belongs: spend it on the migration's interaction with live traffic, not on the DDL statement in the diff.
Every figure is a shipped default or a published limit, which is what makes them planning numbers: they encode what their operators measured and survived.
| Metric | Value | At | Context | As of | Source |
|---|---|---|---|---|---|
| Migration lock_timeout default | 750 ms | GoCardless | Every Rails migration, statement_timeout 1500 ms | 2026 | README |
| First-attempt lock_timeout | 100 ms | GitLab | with_lock_retries schedule; escalates to 2 s | 2026 | with_lock_retries.rb |
| Bounded retry window, worst case | ~40 min | GitLab | Comment in the timing configuration | 2026 | with_lock_retries.rb |
| Migration timeouts default | 5 s | Doctolib | lock_timeout and statement_timeout, retried on failure | 2026 | README |
| Row-copy chunk size | 1,000 rows | gh-ost | Default; allowed range 10 to 100,000 | 2026 | main.go |
| Replication-lag throttle threshold | 1,500 ms | gh-ost | max-lag-millis default; copy pauses above it | 2026 | main.go |
| Cut-over lock timeout | 3 s | gh-ost | Max lock hold during swap attempt, then retry (60 retries default) | 2026 | main.go |
| Copy chunk target time | 0.5 s | Percona pt-osc | Chunk size auto-tuned to hit this; max-lag default 1 s | 2026 | tool docs |
| Cut-over retry backoff | 1/5/10/30 min | Vitess | Escalating intervals after failed cut-over attempts | 2023 | PR #14546 |
| New index size gate | 50 GB | GitLab.com | Larger tables need a formal exception | 2026 | limits doc |
| New column size gate | 100 GB | GitLab.com | Also the target ceiling for any table's total size | 2026 | limits doc |
| Backfill batch / sub-batch | 10,000 / 100 rows | GitLab | Example shipped defaults; jobs spaced 2 min apart, 10 min pause on a stop signal | 2026 | batched migrations doc |
All values are defaults or policy gates read from source on 2026-09-21, not benchmark results; they tell you what each operator considers survivable, not what your hardware will do. Two derived observations: the spread of lock timeouts (100 ms to 5 s) tracks how much each team's traffic can queue without user-visible errors, so derive yours from your p99 request budget rather than copying any of them. And nobody publishes end-to-end migration durations for named large tables; treat any such number you hear as anecdote. The 40-minute GitLab figure is a retry budget, not a migration duration.
Every source behind this page, graded. All are repository-hosted artifacts on github.com or gitlab.com, the only hosts reachable from this research environment; the full ledger with quotes ships alongside as sources.md.
ACCESS EXCLUSIVE on projects and deployments exhausted its
retry budget twice; deploys blocked, customers unaffected.
Same class three weeks later on deployment_merge_requests; the runbook
retry succeeded in minutes. “No customer-facing service impact has been
identified.”
Lock contention from database maintenance, not application queries; migration skipped under weekend deploy pressure and rescheduled by change request.
The migration and high-traffic application transactions locked the same tables in opposite orders; PostgreSQL chose a victim.
The design docs record the rejection of triggers with production evidence (lock downs, unthrottleable load, untestable on replicas) and the sentry-table cut-over that fails back to the original table (issue #82).
The recorded argument for the two-connection swap: a sentry table blocks premature rename, and “no matter what happens” a failed attempt leaves the original table serving.
True pause, dynamic reconfiguration, postponed cut-over, and continuous production testing by migrating the fleet on replicas with checksums. Defaults: 1,000-row chunks, 1,500 ms lag throttle, 3 s cut-over lock timeout.
INSERT IGNORE swallows unique-key collisions after a collation change; semi-sync commit stalls hide a row from both the binlog listener and the copy boundary.
A cut-over timing out mid-event-processing could hang the tool's own pipeline via an unreceived channel send; closed as already fixed by #1637. The failure mode is real either way.
The README explains the queue mechanism in two numbered points and ships the defaults (750/1500 ms) that every migration inherits. Built after roughly 15 seconds of unplanned API downtime from a routine migration, per the blog post it links.
Each unsafe operation with mechanism and safe recipe: column drops against ORM caches, in-transaction backfills, non-concurrent indexes. Blocks them in CI by default.
safe_/unsafe_/raw_ prefixes make every trade-off visible in the diff; automatic rollback is rejected in favour of rolling forward with a new change.
FKs and check constraints added NOT VALID then validated; indexes always concurrent; documents autovacuum's ShareUpdateExclusiveLock as a blocker you cannot beat.
The three-release column-drop protocol, with_lock_retries as the standard wrapper, reverse lock ordering for FK work, and hard 50/100 GB size gates enforced by RuboCop with a formal exception process.
The full retry schedule in code, bounded at about 40 minutes, and a backfill framework that pauses 10 minutes on WAL backlog, autovacuum activity or apdex breach, added “because there have been incidents.”
“An ACCESS EXCLUSIVE lock is acquired unless explicitly noted”; the subforms that take weaker locks (FK adds at SHARE ROW EXCLUSIVE, options at SHARE UPDATE EXCLUSIVE) are exactly the ones the safety libraries route through.
DELETE_ONLY, WRITE_ONLY, PUBLIC with a two-version lease invariant and backfill as small transactions; links and follows the F1 paper's state diagram.
One elected owner steps the cluster through none, delete only, write only, write reorganization, public; at most two schema versions live at once, enforced by lease.
Chunked copy tuned to 0.5 s per chunk, 1 s lag ceiling, triggers for capture, and an honest RISKS section; foreign keys break the atomic rename and get their own workaround flag.
LHM's README preserves the original hour-long ALTER TABLE problem; Facebook's OSC repo (copy mode, table swap) went read-only on 2026-08-04. The lineage that invented the pattern has exited maintenance.
v20 stops embedding the gh-ost binary; v22: “Vitess no longer recognizes the gh-ost and pt-osc Online DDL strategies. The vitess strategy is the recommended way to make schema changes at scale.”
Failed cut-overs stop retrying in a tight loop (1/5/10/30 min backoff); the terminal option kills queries and lock-holding transactions on the migrated table. MySQL 8.0 only, since it needs performance_schema.data_locks.
Views expose old and new shapes over one physical table; clients pin a version via search_path; rollback before contract is instant. The two-version invariant, rebuilt at the application boundary.
--attempt-instant-ddl covers MySQL 8.0 metadata-only changes; the residual risk is the metadata lock when long transactions are running, and the tool falls back to the copy automatically.
Each rung is buildable against a local Postgres or MySQL in hours; the line from toy to production crosses at rung four.
Three sessions on one Postgres table: session A runs a long SELECT (pg_sleep join), session B runs ALTER TABLE, session C runs one-row SELECTs. Watch C hang the moment B starts waiting.
Done when: pg_locks shows B waiting and C queued behind it while A holds only ACCESS SHARE. Teaches: the outage needs no lock to be held by the migration at all.
Wrap the ALTER in SET lock_timeout = '750ms' and a retry loop with
backoff, GoCardless-style. Re-run rung one and watch the queue drain instead of
growing.
Done when: the ALTER fails and retries while C's latency stays flat. Teaches: the timeout protects the traffic, not the migration.
Run two versions of a small app against one table. Do the rename as expand (add column, dual-write), migrate readers, contract (drop old), following GitLab's multi-release protocol; try the naive RENAME first and watch the old version break.
Done when: both app versions serve correctly at every intermediate step. Teaches: the schema change is a deployment protocol, not a statement.
Add a column, then backfill in batches of 10,000 with a sub-batch sleep, outside any transaction, while pgbench runs. Add one health check (replication lag or p99) that pauses the backfill.
Done when: pgbench p99 stays within budget for the whole backfill and the pause demonstrably triggers. Teaches: a backfill is a workload with its own SLO interaction.
On MySQL: create the ghost table, copy in chunks, capture concurrent writes (a trigger is fine for the toy), swap with RENAME TABLE. Then add a unique key that collides under the copy and watch INSERT IGNORE eat rows, reproducing gh-ost #1526.
Done when: your checksum step catches the row loss your swap step missed. Teaches: verification is a component, not a nicety.
Run gh-ost with --test-on-replica against a replica of a seeded database, then inspect the two tables it leaves for comparison. Throttle it mid-copy via the interactive socket and watch replication lag recover.
Done when: you have compared the tables, forced a throttle, and postponed then completed a cut-over. Teaches: the operational surface (pause, audit, postpone) is why these tools exist.
Add a migration linter (strong_migrations or a squawk-style checker) to CI so the unsafe forms fail review, plus a size gate that refuses DDL on tables above your threshold without an exception label, copying GitLab's model.
Done when: a teammate's naive remove_column is blocked with a printed safe recipe. Teaches: incident lessons persist only as enforced checks, which is how every library in this guide came to exist.
The queries that found this material, adapted for a normal network rather than this environment's repository-only view.
site:gitlab.com gl-infra/production migration lock"WithLockRetries::AttemptsExhaustedError""schema migration" postmortem "lock_timeout" OR "metadata lock"github availability report "schema migration"repo:github/gh-ost is:issue "data loss" OR "lose data"repo:github/gh-ost is:pr is:closed is:unmerged cut-overpath:docs/RFCS "schema change" repo:cockroachdb/cockroach"online DDL" design doc site:github.com"lock_timeout" migration gem README site:github.com"safe migrations" postgres "ACCESS EXCLUSIVE" "lock queue"strong_migrations OR pg_ha_migrations OR safe-pg-migrations comparevitess release notes gh-ost deprecated"pt-online-schema-change" "foreign keys" risk"ALGORITHM=INSTANT" metadata lock long transactionTwo vocabulary keys unlock most of this field: search the failure (“lock queue”, “metadata lock”, “AttemptsExhausted”) rather than the practice (“zero-downtime migration”, which returns tutorials), and read the READMEs of safety libraries as what they are: postmortems compiled into code.