Zero Downtime Schema Migration: Expand-Contract, gh-ost, pg-osc and Online DDL (2026)

The most dangerous statement in a production database is often the smallest one. An ALTER TABLE that finishes in two milliseconds on your laptop can take a busy table offline for ten minutes, not because the change itself is slow, but because of where it waits. The statement needs a strong lock, an old transaction holds a conflicting one, and every query that arrives afterwards queues up behind the waiting DDL. The application sees a total outage caused by a change that touched no data.

A zero downtime schema migration is the discipline of changing a live database so that no query ever waits long enough to matter and no running application version ever sees a schema it cannot handle. It rests on three ideas: take only locks that are held for milliseconds and abandon the attempt if they cannot be acquired, split any breaking change into backward-compatible steps across several deploys, and move bulk data in small throttled batches. This guide shows how each idea works in PostgreSQL and MySQL, which tools implement them, and how to choreograph a real change.

What this covers: why DDL blocks everything (the lock queue), PostgreSQL safe and unsafe operations by version, MySQL INSTANT and INPLACE algorithms, trigger-based and binlog-based online schema change tools, the expand-contract pattern with a worked timeline, backfill design, pgroll-class tooling, CI linting, rollback, and the failure modes that still bite.

Context and Background

For most of the history of relational databases, schema change meant a maintenance window. You drained traffic, ran the migration, and reopened. That model broke when products stopped having nights. A service that deploys several times a day, with rolling application releases and multiple versions running side by side, cannot pause to alter a table, and the database has to be changed while it is serving the code that depends on it.

Two separate problems hide inside that sentence, and conflating them is the root of most migration incidents. The first is a database problem: does the DDL statement block reads or writes, for how long, and what does it make everyone else wait for? The second is a compatibility problem: during a rolling deploy, version N and version N+1 of the application run at the same time against one schema, so the schema must satisfy both. A tool such as gh-ost solves the first problem and does nothing for the second. The expand-contract pattern solves the second and relies on safe DDL for the first.

Database vendors have steadily reduced the first problem. PostgreSQL 11 made ADD COLUMN with a constant default a metadata-only change, and later releases weakened the locks taken when validating constraints. MySQL 8.0.12 made ALGORITHM=INSTANT the default for adding a column, and 8.0.29 extended instant add and drop to any column position. Yet no release has removed the underlying hazard, which is lock acquisition under concurrency. The PostgreSQL explicit locking documentation states that a transaction waiting on a conflicting table lock will wait indefinitely, and that is the sentence behind a large share of migration outages.

Database changes also interact with the rest of your delivery system. Isolation behavior decides whether a long-running reader holds a lock that blocks your DDL, which is why our explainer on MVCC, snapshot isolation and write skew is useful background. Sharded systems multiply every migration by the shard count, which we cover in sharding, consistent hashing and resharding. And the application half of the choreography is a progressive-delivery problem, discussed in our Argo Rollouts versus Flagger comparison.

A note on scope and versions. The behaviors below are described for supported, current PostgreSQL and MySQL 8.0-series releases, and version thresholds are stated where they matter. Always confirm against the documentation for the exact version you run, including managed variants such as Aurora or Cloud SQL, which can differ in detail.

Why ALTER TABLE Takes Down Production: The Lock Queue

A schema change takes a database down when its lock request waits behind a long transaction and every later query queues behind that waiting request. The remedy is a short lock_timeout with retries, only weak locks for heavy work, and expand-contract sequencing so that application and schema never need to change in the same instant.

The statement that surprises engineers most is that DDL blocks queries it does not conflict with. Understanding that requires looking at how lock requests are ordered, because the outage is a queueing phenomenon, not a throughput one.

Lock modes and who conflicts with whom

PostgreSQL has eight table-level lock modes. Ordinary SELECT takes ACCESS SHARE, and INSERT, UPDATE and DELETE take ROW EXCLUSIVE. Most ALTER TABLE forms take ACCESS EXCLUSIVE, which per the documentation conflicts with every other mode, including a plain SELECT. Only ACCESS EXCLUSIVE blocks readers. At the other end, SHARE UPDATE EXCLUSIVE, taken by CREATE INDEX CONCURRENTLY, plain VACUUM, ANALYZE and constraint validation, conflicts only with itself and stronger modes, so normal reads and writes proceed. The whole migration game in PostgreSQL is to do as much work as possible under weak locks and to hold the strong lock for as short a time as possible.

The queue behind a waiting ALTER

Here is the failure in four steps. A reporting job opens a transaction and reads from orders, taking ACCESS SHARE that it holds until commit, which will not happen for 40 seconds. Your migration runs ALTER TABLE orders ADD COLUMN note text, requests ACCESS EXCLUSIVE, and must wait for the reporter. Then a normal request arrives: an INSERT into orders needs ROW EXCLUSIVE. That mode does not conflict with the reporter’s ACCESS SHARE, but it conflicts with the pending ACCESS EXCLUSIVE request, and PostgreSQL queues lock requests so that a waiting strong lock is not starved by an endless stream of weak ones. The insert waits behind the ALTER, and so does every query after it.

The consequences scale linearly with traffic. Illustratively, a table receiving 2,000 queries per second accumulates 80,000 waiting queries in the 40 seconds the migration waits, each holding a connection. A pool of a few hundred connections is exhausted within a fraction of a second, and from the application’s view the database is down even though it is idle. The ALTER itself, once it finally acquires the lock, runs for a millisecond.

Figure 1: The lock queue. A long reader blocks the pending ACCESS EXCLUSIVE request, and every later query that would otherwise be compatible queues behind it.

The diagram shows why the usual instinct, “the change is tiny, so it is safe”, is wrong. Safety is a property of the lock wait, not the statement duration. The same logic applies to pg_locks forensics: if you ever need to diagnose a stall, look for a waiting ACCESS EXCLUSIVE request and the oldest transaction in pg_stat_activity that holds a conflicting lock.

The fix: lock_timeout and retry

The standard defense is to set lock_timeout in the migration session, typically to a few seconds or less, so that the DDL gives up quickly if it cannot get the lock. A failed attempt releases its place in the queue, the backlog drains immediately, and a wrapper retries after a short pause with jitter. Done this way a migration that cannot get the lock costs a brief latency blip on one table rather than an outage. A companion setting, statement_timeout, bounds the work done once the lock is held, which protects you from a rewrite you did not expect.

-- Session settings for every online migration
SET lock_timeout = '3s';
SET statement_timeout = '15min';   -- only for lock-light steps, not backfills

-- Retry wrapper idea: attempt, and on lock_not_available, sleep and retry
ALTER TABLE orders ADD COLUMN note text;

Retrying is not optional decoration. Because the attempt can fail on a busy system, the migration runner must treat lock_not_available (SQLSTATE 55P03) as a normal, retryable outcome with a cap on attempts, rather than as a deploy failure. Linters and migration frameworks increasingly bake this in, a point we return to in the CI section.

MySQL has the same shape under a different name

MySQL’s equivalent is the metadata lock. A DDL statement needs an exclusive metadata lock on the table, at the start and again at the end of the operation, and it queues behind any open transaction that has touched the table. New statements then queue behind the waiting DDL, producing the familiar “Waiting for table metadata lock” pile-up. The mitigations rhyme with PostgreSQL: set a short lock_wait_timeout for the migration session, find and kill or avoid long transactions, and use algorithms that need the exclusive lock only briefly.

PostgreSQL Safe and Unsafe Operations, Version by Version

Most PostgreSQL migrations are made of about a dozen statement types, and each has a known safe form. The table below summarizes them. Version thresholds come from the PostgreSQL release notes and documentation as summarized in several vendor guides; verify against your exact version, since details such as lock levels have shifted across releases.

Operation	Naive form	Risk	Safe form
Add nullable column	`ADD COLUMN c text`	Brief ACCESS EXCLUSIVE; fine with lock_timeout	Same, with lock_timeout and retry
Add column with default	`ADD COLUMN c int DEFAULT 0`	Table rewrite before v11; metadata-only from v11 for non-volatile defaults	On v11+ fine; volatile defaults such as `random()` still rewrite, so add without default, then backfill
Add index	`CREATE INDEX`	SHARE lock blocks writes for the whole build	`CREATE INDEX CONCURRENTLY`, outside a transaction block
Add foreign key	`ADD FOREIGN KEY`	Scans table under a strong lock	`ADD CONSTRAINT ... NOT VALID`, then `VALIDATE CONSTRAINT`
Add CHECK	`ADD CHECK (...)`	Full scan under strong lock	`NOT VALID` then `VALIDATE`
Set NOT NULL	`SET NOT NULL`	Full scan under ACCESS EXCLUSIVE	Validated CHECK constraint first (v12+ then skips the scan), then `SET NOT NULL`, drop the CHECK; v18 adds `NOT VALID` NOT NULL directly
Change column type	`ALTER COLUMN TYPE`	Usually rewrites table and indexes	Expand-contract with a new column; a few changes such as widening varchar limits skip the rewrite
Drop column	`DROP COLUMN`	Brief lock, but breaks old app versions	Stop reading and writing first, then drop in a later deploy
Rename column or table	`RENAME`	Instant but breaks running code	Expand-contract with a new name

What the version notes actually change

Three version facts account for most real-world differences. First, since PostgreSQL 11 a non-volatile default is stored in the catalog, so ADD COLUMN ... DEFAULT 0 no longer rewrites the table. The statement still requests ACCESS EXCLUSIVE, so the lock queue hazard remains, but the lock is held for milliseconds. Second, CREATE INDEX CONCURRENTLY, available since 8.2, scans the table twice and waits for older snapshots, so it is slower and uses more I/O, and if it fails it leaves an invalid index that you must drop and rebuild. Third, VALIDATE CONSTRAINT has run under the weaker SHARE UPDATE EXCLUSIVE since 9.4, so the expensive scan no longer blocks writes.

The SET NOT NULL recipe deserves detail because it is the one most teams get wrong. A direct SET NOT NULL scans the whole table while holding ACCESS EXCLUSIVE. The workaround exploits an optimization added in PostgreSQL 12: if a validated CHECK constraint already proves the column is non-null, the scan is skipped. So you add CHECK (c IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT under the weak lock, then SET NOT NULL (instant, brief strong lock), then drop the now-redundant CHECK. As of PostgreSQL 18 you can add a NOT NULL constraint as NOT VALID directly, which makes the workaround unnecessary for new versions.

-- Safe NOT NULL on PostgreSQL 12 to 17
SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_cust_nn
  CHECK (customer_id IS NOT NULL) NOT VALID;      -- brief lock, no scan
ALTER TABLE orders VALIDATE CONSTRAINT orders_cust_nn;  -- weak lock, scans
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL; -- scan skipped
ALTER TABLE orders DROP CONSTRAINT orders_cust_nn;

-- Safe index and foreign key
CREATE INDEX CONCURRENTLY idx_orders_cust ON orders (customer_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_cust
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_cust;

Hidden lock hazards

Two traps catch experienced teams. Adding a foreign key takes locks on both tables, since the referenced table also needs protection against concurrent change, so even NOT VALID can queue behind a long transaction on the parent. And CREATE INDEX CONCURRENTLY must wait for every transaction that started before it to finish, so a single forgotten idle-in-transaction session can stall an index build indefinitely. Monitoring for sessions in idle in transaction state, and setting idle_in_transaction_session_timeout, removes a whole class of migration stalls.

MySQL Online DDL: INSTANT, INPLACE and COPY

MySQL gives you an explicit vocabulary for how a DDL statement runs. ALGORITHM=INSTANT changes only metadata in the data dictionary, with no table rebuild and no row-level work. ALGORITHM=INPLACE performs the change within InnoDB, often without copying to a new table, and permits concurrent DML for many operations. ALGORITHM=COPY builds a full new copy of the table and blocks writes. The useful property is that you can request an algorithm and lock level explicitly, and MySQL fails the statement rather than silently degrading, which makes a migration safe by construction.

-- Fails loudly instead of silently copying the table
ALTER TABLE orders ADD COLUMN note VARCHAR(200), ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX idx_cust (customer_id), ALGORITHM=INPLACE, LOCK=NONE;

What each algorithm covers

Per the MySQL 8.0 online DDL reference, adding a column uses INSTANT by default since 8.0.12 and can be placed at any position since 8.0.29, while adding a secondary index is in-place with concurrent DML and no table rebuild. Changing a column data type is different: the reference table lists it as requiring ALGORITHM=COPY, so it rebuilds the table and does not allow concurrent DML. That single row explains why type changes are the classic case for an external online schema change tool or for expand-contract.

INSTANT has limits worth knowing. It cannot be combined with other ALTER actions that do not support it, and does not apply to compressed tables or tables with a FULLTEXT index. Each instant add or drop creates a new row version, tracked in INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS, up to a documented maximum of 64. Once the limit is hit, further instant changes fail until the table is rebuilt with ALTER TABLE or OPTIMIZE TABLE, which resets the count. Teams that churn columns on one hot table can hit this ceiling unexpectedly.

Even an “online” in-place operation takes a brief exclusive metadata lock at the start and end, so the lock queue hazard from the previous section applies to MySQL too. A one-hour in-place index build is harmless if the final metadata lock acquisition is brief, and catastrophic if an old transaction makes it wait. Treat any DDL as having a two-ended lock risk, and put a lock_wait_timeout on the session.

Online Schema Change Tools: Triggers, Binlogs and Shadow Tables

When the native algorithm would rebuild the table under a blocking lock, the standard answer is an external online schema change tool. They all use one idea: build a new “shadow” or “ghost” copy of the table with the desired schema, copy existing rows in small chunks, keep the copy in sync with ongoing writes, and swap the tables with one short atomic rename. They differ in how they capture ongoing writes.

Figure 2: Online schema change tools. Both build a shadow table and copy rows; they differ in how concurrent writes reach the copy, and both finish with a brief atomic swap.

Trigger-based: pt-online-schema-change and pg-osc

Percona’s pt-online-schema-change pioneered the approach for MySQL. It creates a new table, adds triggers on the original so that every insert, update and delete is mirrored into the copy, copies rows in chunks, and renames at the end. The strengths are maturity and simplicity. The costs are that the triggers execute inside every user transaction, adding write latency and contention on the original table, and that triggers cannot be paused independently, so throttling the migration does not throttle the trigger overhead.

On PostgreSQL, pg-osc applies the same family of ideas with an audit table. Per its documentation it creates an audit table, takes a brief ACCESS EXCLUSIVE lock to add triggers that log changes, applies the ALTER to a shadow table, copies the rows, replays the audit log, and when only about 20 rows remain takes a final ACCESS EXCLUSIVE lock to swap names and re-add foreign keys as NOT VALID. Documented requirements include PostgreSQL 9.6 or later, a primary key on the table, and enough disk for a second copy, and it does not support partitioned tables. The parent table is locked exclusively twice, at setup and swap, so lock_timeout discipline still matters.

Binlog-based: gh-ost

GitHub’s gh-ost is triggerless. Instead of triggers it connects as a replica to the MySQL binary log stream, captures changes to the table, and applies them asynchronously to the ghost table while a separate process copies existing rows. Because nothing runs inside the user’s transactions, the production write path is untouched. The README describes controls that make it operationally attractive: it can throttle itself, stopping row copies and event processing on the master when load or replication lag demands; it supports --postpone-cut-over-flag-file to delay the final swap until you create or remove a flag file; it accepts interactive reconfiguration over a unix socket; and --test-on-replica runs the whole flow on a replica without replacing the original, leaving both tables for comparison.

The cut-over is the delicate moment. gh-ost needs to swap the original and ghost tables so no write is lost and no write lands in the wrong table, and it does this using a carefully ordered lock-and-rename sequence on the table rather than a plain rename. Because that step needs a metadata lock, it can still be delayed by long transactions, and you should plan to run cut-over at a quiet moment, which is exactly what the postpone flag is for. Foreign keys and existing triggers on the migrated table have documented restrictions; read the project’s requirements and limitations document before adopting it against a schema that uses them.

Choosing between them

Concern	Native INSTANT/INPLACE	Trigger-based tool	Binlog-based gh-ost	Postgres pg-osc
Write-path overhead	None	Triggers in every transaction	None on primary	Triggers in every transaction
Pausable mid-flight	Not applicable	Partially	Yes, full throttle	Limited
Disk during change	Low to moderate	2x table	2x table	2x table
Foreign keys	Native handling	Special handling needed	Restrictions apply	Re-added as NOT VALID
Needs replica access	No	No	Reads binlog	No

The honest rule of thumb: use the native algorithm whenever it is instant or in-place and non-blocking, and reach for an external tool only for the operations the database would rebuild under a write block. External tools also double the storage footprint temporarily and generate heavy replication traffic, which becomes the real constraint on large tables.

The Expand-Contract Pattern: Solving the Compatibility Problem

Safe DDL answers whether the statement blocks. It does not answer whether the application can survive the schema it just got. The expand-contract pattern, also called parallel change, answers that by never making a breaking change in one step. Every breaking change is decomposed into an expand phase that only adds, a migrate phase that moves data and traffic, and a contract phase that only removes. Between any two steps, both the previous and the next application versions are valid against the current schema.

Figure 3: Expand-contract across deploys. The schema only adds during expansion, the application dual-writes while data is backfilled, reads move, and removal happens last.

The standard choreography

Take the simplest breaking change, renaming users.fullname to users.display_name. A direct RENAME COLUMN is instant at the database level, but the instant it commits, every running instance of the old application that still selects fullname begins failing. The expand-contract version has six steps, and each is a separate deploy or migration that can be rolled back on its own.

Expand schema. Add display_name as a nullable column with no default and no constraint. This is a metadata-only change guarded by lock_timeout.
Dual-write. Deploy application version N+1 that writes both columns on every insert and update, and still reads the old column. Rolling this out gradually is where progressive delivery earns its keep.
Backfill. Copy fullname into display_name for existing rows in small batches, described in the next section. Rows written after step 2 are already correct.
Verify and switch reads. Compare the columns across all rows, then deploy N+2 that reads display_name but still writes both. Now the old column is no longer a source of truth for anything.
Stop writing the old column. Deploy N+3 that ignores fullname completely. In ORMs that cache column lists, this is also the step where you mark the column as ignored.
Contract. Only after the old column has been unused across a full release cycle, drop it. Add constraints on the new column (via the NOT VALID then validate path) at any point after the backfill completes.

The rules that make this safe are simple to state. The schema change is always backward compatible with the currently deployed code. The code is always compatible with the schema before and after its own deploy. And the destructive step comes last, after a soak period long enough that no instance, cron job, analytics export or on-call script still touches the old shape. Letting step 6 slip by a sprint is harmless, and that is deliberate, because early removal is the one step that cannot be undone.

Worked timeline with numbers

Consider a users table with 200 million rows, receiving about 1,500 writes per second at peak, with a rolling deploy taking about 15 minutes and a policy of keeping the old column for two weeks after the last read. Illustratively, the schedule looks like this.

Day	Step	Duration	Lock held	Risk if skipped
Mon 10:00	Add nullable `display_name`	under 1 second of lock	ACCESS EXCLUSIVE, ms	Outage via lock queue if no lock_timeout
Mon 11:00	Deploy N+1 dual-write	15 min rolling	none	Rows written after this point diverge
Mon 12:00	Backfill in batches	about 2 hours	row locks only	Hot-row contention, replica lag
Tue 10:00	Verify equality	minutes	weak	Silent data loss on switch
Tue 11:00	Deploy N+2 reads new	15 min rolling	none	Old readers get nulls
Wed	Deploy N+3 stops old writes	15 min rolling	none	Cannot drop safely
+2 weeks	Drop `fullname`	under 1 second of lock	ACCESS EXCLUSIVE, ms	Forgotten consumers break

The total elapsed time is two weeks, but the amount of time any query could be affected is a handful of milliseconds per lock-bearing step. That ratio is the point. Zero downtime migration trades calendar time and extra code for a bounded, rehearsed risk, and teams that cannot afford the calendar time are the ones who end up with maintenance windows.

Dual-writes done carefully

Dual-writes are the part that looks easy and is not. If the application writes both columns in separate statements outside a transaction, a crash between them leaves them inconsistent. Write both in the same statement, or use a database trigger or generated column to keep them aligned. Triggers are attractive because every writer, including scripts and other services you forgot about, is covered, but they add write cost and hide logic, so remove them in the contract phase. A related technique for cross-system migrations is change data capture, which streams committed changes to the new representation without touching the writers; see our guide to change data capture with Debezium for that route.

Backfills: Moving Data Without Hurting the Primary

The backfill is usually the longest step and the one most likely to hurt production, because unlike the metadata changes it does real, sustained work. A single UPDATE users SET display_name = fullname over 200 million rows is a catastrophe on three axes: it holds a very long transaction, generating one enormous write-ahead log burst that floods replicas, it creates 200 million dead tuples in PostgreSQL that vacuum must clean, and it holds row locks until commit, blocking user updates to those rows.

Batch size, pacing and arithmetic

The pattern is to process rows in primary-key ranges, committing between batches, and to pace the work against observed health. With a batch of 5,000 rows, 200 million rows is 40,000 batches. If each batch takes an illustrative 100 ms of database work and the runner sleeps 50 ms between batches, the whole backfill takes 40,000 x 0.15 s = 6,000 seconds, about 100 minutes. Doubling the sleep roughly doubles the time but halves the average pressure. The right batch size is empirical: large enough that per-batch overhead is negligible, small enough that a batch holds locks and generates log for well under a second.

-- Keyset-paginated backfill batch (run in a loop, one commit per batch)
WITH batch AS (
  SELECT id FROM users
  WHERE id > :last_id AND display_name IS NULL
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
)
UPDATE users u SET display_name = u.fullname
FROM batch WHERE u.id = batch.id
RETURNING u.id;   -- runner records max(id) as the next :last_id

Keyset pagination on the primary key avoids the quadratic cost of OFFSET. The display_name IS NULL predicate makes the job idempotent and restartable, so a killed runner resumes where it stopped. SKIP LOCKED avoids waiting on rows a user transaction currently holds; those rows get picked up on a later pass, or are already correct thanks to dual-writes.

Throttle on replication lag, not on a timer

A fixed sleep is a guess. The robust approach is feedback: before each batch, check replication lag and the primary’s own signals such as active connections or lock waits, and pause if any exceeds a threshold. gh-ost bakes this in for MySQL; for hand-written PostgreSQL backfills you must build it. A lagging replica is not just a read-freshness problem. If failover is part of your availability design, a replica that is minutes behind means a failover would lose or replay minutes of data, so backfills can silently erode your recovery objectives.

Hot rows and special cases

A few shapes need extra care. Tables with a hot row, such as a counter or a tenant-level settings record, will see backfill batches contend with live updates, so exclude hot keys and handle them with a targeted update. Tables with very wide rows or large TOASTed values multiply the write volume of an update that only changes a small column, because PostgreSQL writes a new row version; consider whether the backfill can instead be avoided by computing the value lazily on read until traffic migrates. Finally, a heap-only-tuple friendly update, one that changes no indexed column, is far cheaper than one that touches an index, so in PostgreSQL avoid adding indexes on the new column until after the backfill, then create them concurrently.

Tooling That Automates the Choreography: pgroll and Friends

Expand-contract is mechanical enough that tools now automate large parts of it. The most visible for PostgreSQL is pgroll from Xata. Per its README it follows the expand-contract workflow using virtual schemas: it creates views over the physical tables, and for a breaking column change it adds a new column, backfills it from the old one, and installs triggers that keep writes synchronized in both directions during the migration. Clients choose a schema version by setting search_path, so old and new application versions can run against the same physical tables at once.

Figure 4: A versioned-view design. Old and new application versions see different views onto one physical table, while bidirectional triggers keep the old and new columns in step until completion.

The lifecycle has three commands in spirit: start applies the additive changes and creates the new version, complete removes the old version and cleans up once no client uses it, and rollback discards the new version and leaves the old schema as it was. The README lists PostgreSQL 14 or later as supported, including managed services like RDS and Aurora, under an Apache 2.0 license. The appeal is that rollback before completion is a first-class, near-instant operation, because the old schema was never touched.

What such tools do not do

Be clear-eyed about the boundary. Versioned-view tooling automates the schema side of dual-writing, but your application still has to select the right search_path, and every connection pool, ORM and background job must do so consistently. The tool also does not decide when “no client uses the old version” is true; that is an operational judgment you must back with metrics. A related project, Reshape, took a similar approach earlier; I could not verify its current maintenance status for this article, so evaluate that independently before relying on it.

For MySQL shops, gh-ost remains the dominant choice for the table-rebuild cases, and platform-native alternatives exist in managed offerings. Whichever you choose, the principle is the same: prefer tools whose failure mode is “abort and leave the original table intact” over tools whose failure mode is a half-migrated original.

Guardrails: Linting Migrations in CI

The cheapest zero downtime control is a failing build. Static analysis of migration files catches the classic mistakes before they reach a review, let alone production. Two tools are widely used.

Squawk is a linter for PostgreSQL migrations. Its documentation describes rules such as require-concurrent-index-creation, which flags a plain CREATE INDEX because table updates are blocked during normal index creation. It can run as a pre-commit hook, a Docker image in a pipeline, or a GitHub integration that comments on pull requests, and supports several reporter formats. For the Rails ecosystem, strong_migrations intercepts unsafe operations at migration time and prints the safer procedure. It flags non-concurrent indexes, volatile defaults, SET NOT NULL on existing columns, foreign keys and checks added without validate: false, column removal, type changes, and renames, each with the recommended multi-step alternative. It also has an opt-in “safe by default” mode that runs the safe version of certain operations automatically.

What a good migration pipeline enforces

Linting should be one layer of several. Beyond static rules, a robust pipeline applies migrations to a production-sized copy of the schema to catch rewrites, sets lock_timeout and statement_timeout automatically so developers cannot forget, runs the expand and contract steps as separate deploy artifacts that cannot be merged into one, and blocks a contract step unless a query-log check proves the column is no longer read. The last control is the most valuable and the least common: a column that is unused according to the database’s own statistics is a much better signal than a calendar.

Two limits to remember. Linters reason about SQL text, so they miss problems that depend on table size, current traffic or what else is running, which is why they flag but do not replace judgment. And raw SQL in an escape hatch, such as an ORM’s execute, often bypasses the checks entirely, so treat every escape hatch as a review trigger.

Rollback Strategy

A migration plan without a rollback plan is a wish. The good news is that expand-contract makes rollback cheap for most of its life, and the bad news is that its structure also defines exactly where rollback ends.

Rollback by phase

During expansion, rollback is deleting what you added: dropping the new nullable column is safe because nothing depends on it. During dual-write and backfill, rollback means redeploying the previous application version, which still reads the old column that has remained correct throughout. After the read switch, rollback is redeploying N+1, which still reads the old column, provided the dual-writes continued to keep it current. This is the whole reason step 5 is separated from step 4: you keep the ability to roll reads back until you stop writing the old column.

The point of no return is the contract step. Once the old column is dropped, only a restore from backup, or a replay from a log, can bring it back. Treat the drop as a one-way door, take a logical snapshot of the column or table first if the data has any value, and make the drop its own deploy with its own approval. For tools such as gh-ost, the equivalent is that the original table is typically retained under a renamed “old” name after cut-over, which you can swap back, until you choose to drop it.

Data corrections are the harder rollback

Schema rollback is easy compared to data rollback. If a backfill transformed values incorrectly, for example by truncating strings or applying the wrong timezone conversion, you only learn after the read switch. The defense is the verification step in the timeline: compare old and new representations across every row, or a statistically large sample plus all rows modified recently, before reads move. Keep the old column authoritative until that comparison passes.

Trade-offs, Gotchas, and What Goes Wrong

Zero downtime techniques are not free, and several fail in ways that are worse than the problem they solve. Knowing the failure modes ahead of time is most of the skill.

Replication lag and the write amplification of “online”

Every online technique replaces one big blocking operation with many small writes, and those writes replicate. A table rebuild or a large backfill can generate more write-ahead log or binlog volume than normal traffic, pushing replicas minutes behind. Read-after-write consistency for users on replicas then degrades, and a failover during the migration risks losing the lag. Pace by lag, set a ceiling, and abort when it is crossed. Ghost-table tools also need roughly double the table’s storage for the duration, and a disk-full event mid-migration is an incident of its own.

Long transactions, again

Nearly every online technique has a step that waits for old transactions: the final lock acquisition, the snapshot wait in CREATE INDEX CONCURRENTLY, the cut-over in gh-ost. A single forgotten session, a stuck ORM transaction or a slow analytics query can stall them. Before starting, list transactions older than a threshold, and during the run alarm on any that exceed it. Setting idle_in_transaction_session_timeout globally is a cheap structural fix.

ORM and prepared statement caches

Applications cache schema knowledge. Some ORMs read the column list at boot and use it in every INSERT or SELECT *; dropping or renaming a column underneath them produces errors until restart. Server-side prepared statements in PostgreSQL can fail with “cached plan must not change result type” after a column change. This is why the contract steps require marking columns as ignored in the application first and why a rolling restart should follow the read switch. Treat the ORM’s metadata cache as part of the schema.

Forgotten consumers

The database is rarely used only by the service that owns it. Data pipelines, BI tools, reporting jobs, CDC connectors and ad hoc scripts all depend on column names. Dropping a column breaks them silently, often days later when a scheduled job runs. Query-level evidence, such as statement statistics and access logs, is a better oracle than asking teams. Schema registries for CDC streams help here; incompatible changes to a stream’s schema should fail a check before deploy.

Dual-write drift and the cost of complexity

Dual-writing is eventually consistent by accident unless done in a single statement or trigger. Any code path that writes only one column, such as a bulk import tool, a hotfix script or a service not yet upgraded, creates drift that the verification step must catch. More broadly, expand-contract multiplies the number of deploys, the temporary code, and the places a human can forget to finish. Unfinished migrations, where the old column lingers for years, are common technical debt. Track each migration as a ticket with an explicit contract date.

When not to bother

For a small table, a few thousand rows, or a service with a real maintenance window and a tolerant business, the plain statement with lock_timeout is fine. The expand-contract overhead pays off when the table is large, the breaking change touches live code paths, or the cost of an outage exceeds the cost of two weeks of choreography. Match the ceremony to the blast radius.

Practical Recommendations

Start by making the safe path the default path, because discipline decays under deadline pressure. Put lock_timeout and a bounded retry into your migration runner so no developer has to remember it, and lint every migration in CI with a tool such as squawk or strong_migrations. Require that any change classified as breaking, meaning rename, type change, column removal, or new NOT NULL on existing data, ships as separate expand, migrate and contract artifacts rather than one file.

Next, rehearse. Run the migration against a copy of production size, measure lock wait, duration and replication lag, and record the numbers in the pull request. For MySQL table rebuilds, dry run gh-ost with --test-on-replica and postpone cut-over until you have a quiet window and an owner watching. For PostgreSQL rebuild-class operations, prefer the expand-contract route over a trigger-based copy unless the table cannot be changed any other way.

Finally, close the loop. Make the contract step a scheduled, ticketed task with evidence that the old column is unused, and keep a rollback note for each phase. Document which tables are hot, which have large rows, and which are replicated downstream, since those are the ones where a routine change becomes an incident.

Pre-flight checklist

[ ] Session sets lock_timeout and statement_timeout; runner retries on lock failure with jitter.
[ ] No transactions older than the threshold are open on the target table.
[ ] Indexes use CONCURRENTLY (PostgreSQL) or an INPLACE algorithm with LOCK=NONE (MySQL).
[ ] Constraints added NOT VALID then validated; NOT NULL uses the check-constraint route on versions before 18.
[ ] Breaking changes split into expand, migrate and contract deploys.
[ ] Backfill is keyset-paginated, idempotent, restartable, and throttled on replication lag.
[ ] Old and new values verified equal before reads switch.
[ ] ORM column caches and prepared statements accounted for; columns ignored before drop.
[ ] Rollback plan written per phase; contract step scheduled with an owner.
[ ] Downstream consumers (CDC, BI, scripts) checked against query evidence.

Frequently Asked Questions

What is a zero downtime schema migration?

It is a change to a live database schema that never makes queries wait long enough to cause an outage and never leaves a running application version facing an incompatible schema. In practice it combines short-lock DDL with timeouts, backward-compatible expand-contract sequencing across several deploys, and throttled batch backfills. The result trades extra calendar time and temporary code for a bounded, rehearsed risk, instead of a maintenance window.

Why does a fast ALTER TABLE still cause downtime in PostgreSQL?

Because of the lock queue. The statement needs an ACCESS EXCLUSIVE lock, and if any transaction holds a conflicting lock, the request waits. While it waits, later queries, even ones that would normally be compatible, queue behind it. The ALTER finishes in milliseconds once it gets the lock, but the pile-up of waiting connections is what takes the application down. A short lock_timeout with retries prevents it.

What is the expand-contract pattern?

It is a way to make a breaking schema change in backward-compatible steps. In the expand phase you only add new structures; in the migrate phase the application dual-writes, data is backfilled, and reads move to the new structure; in the contract phase you remove the old one after it is unused. At every step, both the old and new application versions work against the current schema.

When should I use gh-ost instead of native MySQL online DDL?

Use native INSTANT or INPLACE operations whenever they are available and non-blocking, such as adding a column or a secondary index. Reach for gh-ost for operations MySQL would otherwise run with the COPY algorithm, such as changing a column data type, or when you want pausable, throttled, testable migrations with a controllable cut-over. gh-ost reads the binary log rather than using triggers, so it adds no overhead to the write path.

Is ADD COLUMN with a default safe in PostgreSQL?

On PostgreSQL 11 and later, a non-volatile default is stored in the catalog and the statement no longer rewrites the table, so the work is metadata only. It still takes a brief ACCESS EXCLUSIVE lock, so use lock_timeout. A volatile default such as random() still forces a rewrite, in which case add the column without a default and backfill in batches. Before version 11, it always rewrote the table.

How do I roll back a failed migration?

Roll back by phase. During expansion, drop what you added. During dual-write and before the read switch, redeploy the previous application version, since the old column stayed correct. After the read switch, redeploy the version that still reads the old column. The drop of the old column is the point of no return, so make it a separate, approved step and snapshot data first.