Zero-Downtime Database Schema Migrations: Expand-Contract, pgroll, gh-ost and Online DDL

The riskiest line in most deployments is not application code. It is a single ALTER TABLE that takes a lock the team did not know it was taking, queues behind one slow transaction, and then blocks every query that arrives after it. The application did not crash; it simply stopped answering, because the database was politely waiting its turn. Anyone who has watched connection pools fill during a “five-second” migration knows the feeling.

Zero downtime schema migration matters more in 2026 than it did when deploys were weekly. Teams ship many times a day, run several application versions at once during rolling deploys, and operate tables large enough that a full rewrite takes hours. A schema change is now a distributed-systems problem: a database, a fleet of old and new clients, and a window in which both must work.

You will leave with a mental model of why migrations block, a six-phase expand-contract recipe, an honest comparison of native online DDL, pgroll and gh-ost, and a decision record you can adapt to your own stack.

What this covers: lock mechanics in PostgreSQL and MySQL, the expand-contract pattern, how pgroll and gh-ost work internally, a decision matrix, failure modes, and a practical checklist.

Context and Background

Schema migrations fail for three reasons, and tools address them unevenly. The first is locking: the statement needs a lock that conflicts with ordinary reads or writes. The second is rewriting: the statement rebuilds the table, which takes time proportional to table size and doubles storage temporarily. The third is compatibility: even a perfectly online change breaks a client that still expects the old shape. Most outage postmortems blame the first, but the third is what makes rollbacks hard.

Who owns the problem

Historically a DBA owned migrations and scheduled them for a maintenance window. Continuous delivery removed the window. Application teams now author migrations alongside code, which is healthy for velocity and dangerous for lock awareness. The mismatch is the reason an entire ecosystem of linters, wrappers, and ghost-table tools exists. If you run many services, the related question of how data is partitioned across nodes matters too; our comparison of Vitess, Citus and CockroachDB horizontal sharding shows how sharded systems turn one migration into many coordinated ones.

The three families of solutions

Solutions fall into three families. Native online DDL relies on the database engine to apply a change with minimal locking, for example PostgreSQL’s CREATE INDEX CONCURRENTLY or MySQL’s ALGORITHM=INSTANT. Shadow-table tools such as gh-ost and pt-online-schema-change build a copy of the table with the new schema, keep it in sync, and swap. Versioned-schema tools such as pgroll expose old and new shapes simultaneously so that application rollouts and schema changes are decoupled. The expand-contract pattern sits above all three: it is a discipline for sequencing changes, not a tool.

PostgreSQL’s own documentation is the baseline reference for what the engine guarantees. The PostgreSQL ALTER TABLE reference states that an ACCESS EXCLUSIVE lock is acquired unless a subform is explicitly noted otherwise, and that when several subcommands are combined the strictest lock required by any of them applies. That sentence alone explains many incidents: someone bundles a harmless change with a strict one and gets the strict lock for both.

What “zero downtime” can honestly mean

Strictly, almost no migration is literally zero impact. A brief exclusive lock, a replication-lag spike, or a slightly slower write path is normal. In this article, zero downtime means: no sustained period in which reads or writes fail or queue unboundedly, no data loss, and an ability to abort before the point of no return. That is a measurable definition, and it lets us compare tools by what they cost rather than by marketing claims.

Why Migrations Block: Locks, Rewrites and Queues

A migration blocks traffic when it requests a lock that conflicts with running queries and waits in the lock queue; every later query then queues behind it. The danger is the waiting, not the work. A statement that needs one millisecond but waits ten seconds for a lock can stall the whole table for ten seconds.

The lock queue effect

In PostgreSQL, lock requests are served roughly first come, first served per lock object. Suppose a long-running SELECT holds an ACCESS SHARE lock. Your ALTER TABLE requests ACCESS EXCLUSIVE and must wait. Every new query, even a trivial SELECT, requests ACCESS SHARE, which conflicts with the queued exclusive request, so it queues behind it. Within seconds the connection pool is exhausted and the application looks down, although the migration has done nothing yet.

The defence is a short lock_timeout combined with retries. Set lock_timeout to a small value such as two seconds in the migration session, let the statement fail fast if it cannot get the lock, and retry with backoff. A failed attempt costs nothing; a queued one costs an outage. This single habit prevents more incidents than any tool on this page. The lock_timeout parameter is documented in the PostgreSQL runtime configuration reference; the exact value is a judgment call that depends on your longest normal transaction.

Metadata-only versus rewriting changes

PostgreSQL distinguishes changes that only touch the catalog from changes that rewrite the heap. The ALTER TABLE documentation explains that when a column is added with a non-volatile DEFAULT, the default is evaluated at statement time and stored in metadata, so the operation is very fast even on large tables and requires no rewrite. By contrast, adding a column with a volatile default such as clock_timestamp(), a stored generated column, or an identity column rewrites the entire table and its indexes. Changing the type of an existing column will normally rewrite too, with some binary-compatible exceptions.

The practical rule: before running any DDL on a big table, know whether it is metadata-only. If you do not know, test on a copy of production-size data and watch for a rewrite, or consult the lock and rewrite behaviour in the docs. Guessing is how a “quick column add” becomes a two-hour lock.

Validation scans hide inside constraints

Adding a CHECK or FOREIGN KEY constraint normally scans the table to verify existing rows while holding a strong lock. PostgreSQL provides NOT VALID to separate the two steps: add the constraint without checking old rows (new writes are checked immediately), then run VALIDATE CONSTRAINT later, which holds a weaker lock and does not block writes. The documentation notes that ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE lock rather than ACCESS EXCLUSIVE, though it takes that lock on the referenced table too.

A related trick covers SET NOT NULL. The documentation states that the not-null check ordinarily scans the entire table, but the scan is skipped if a valid CHECK constraint already proves that no null can exist. The sequence therefore becomes: add CHECK (col IS NOT NULL) NOT VALID, validate it, run SET NOT NULL (now instant), then drop the redundant check. Four statements replace one, and the blocking one becomes cheap.

MySQL’s different baseline

MySQL took a different path. The InnoDB online DDL reference describes algorithms: INSTANT changes only metadata, INPLACE rebuilds without copying to a new table in many cases while allowing concurrent DML, and COPY is the old table-copy method. Per the MySQL 8.0 manual, INSTANT is the default algorithm for adding a column as of 8.0.12, and before MySQL 8.0.29 the instant algorithm could only add a column as the last column. Some operations, such as changing a column data type, are not supported with in-place or instant methods and require a table rebuild.

Even “online” MySQL operations take brief metadata locks at the start and end, and a long-running transaction can block that metadata lock, producing the same queue effect described above. Online does not mean lock-free; it means the long phase does not block DML.

The Expand-Contract Pattern, Step by Step

The expand-contract pattern splits one breaking schema change into additive, reversible steps: expand the schema with something new, migrate data and code gradually, then contract by removing the old structure only after nothing uses it. Each step is backward compatible with the previous application version, so deploys and rollbacks never need to be synchronized with the database.

Figure 1: The six phases of the expand-contract pattern. Dashed arrows are the rollback and re-verification paths that make each phase safe.

Figure 1 shows the sequence. The pattern is also called parallel change in the refactoring literature, and the principle is old: never make a change that requires two things to happen at the same instant. A schema change and a code deploy can never be atomic together, so you design a state in which both orderings work.

Phase 1 and 2: expand and dual write

Start with a rename as the canonical example, say users.fullname to users.display_name. A direct RENAME COLUMN is a metadata change that is fast in both PostgreSQL and MySQL, but it instantly breaks every running application instance that still references fullname. During a rolling deploy, some instances are old and some are new, so one of them will always be wrong.

Expand means adding display_name as a nullable column, with no default requiring a rewrite. This is additive and old code ignores it. Then deploy application code that writes both columns on every insert and update, either in the application or with a database trigger. Reads still use fullname. At this point the new column is being populated for fresh data and is a safe rollback target: reverting the code loses nothing.

Phase 3 and 4: backfill and verify

Existing rows must be copied. The backfill is where most self-inflicted incidents happen, because a single UPDATE users SET display_name = fullname on a large table is one huge transaction. It generates enormous write-ahead log or binlog volume, holds row locks for a long time, bloats the table in PostgreSQL, and spikes replication lag.

Backfill in small batches keyed on the primary key, commit between batches, sleep between them, and watch replication lag and lock waits as feedback signals. A sample batch loop in SQL looks like this:

-- Run repeatedly from a job runner until 0 rows are updated.
-- Batch size and sleep are tunable; start small and measure.
UPDATE users
SET display_name = fullname
WHERE id IN (
  SELECT id FROM users
  WHERE display_name IS NULL
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
);

Verification is the phase teams skip. Before switching reads, compare the two columns: count rows where display_name IS DISTINCT FROM fullname and confirm the number is zero or explainable. If the dual write has a bug, the mismatch shows up here, and the dashed arrow in Figure 1 sends you back to fix it with no user impact.

Phase 5 and 6: switch reads and contract

Deploy code that reads display_name. Keep dual writing for at least one full release cycle so that rollback remains possible: if the new read path has a bug, the old column is still current. Only when the new path has proved itself do you stop writing the old column in one deploy, wait until no running instance uses it, and finally drop it.

The drop is the only irreversible step. Treat it as its own change with its own review, and consider renaming the old column to something like fullname_deprecated for a period first, since a rename is reversible and will surface any forgotten reader as an error rather than as silent data loss. The contract step is also where a long-lived Postgres lock matters again: DROP COLUMN takes ACCESS EXCLUSIVE, though it is a quick catalog operation, so use the short lock_timeout approach.

The cost nobody puts in the slide

Expand-contract multiplies the number of deploys. One logical change becomes at least four releases spread over days or weeks. It also leaves transitional code that must be cleaned up, and cleanup is the work that gets forgotten. Many production schemas contain columns that are two years into their “temporary” dual-write state. A migration ticket should therefore include the contract step as a tracked, dated follow-up, not an afterthought.

This is also where release tooling helps. Wrapping the new read path in a flag lets you flip back instantly without a deploy; see our guide to feature flag architecture with OpenFeature and progressive delivery for how to evaluate flags safely, and our Argo CD versus Flux decision record for how to sequence the deploys that carry each phase through a GitOps pipeline.

Tool Deep-Dive: Native Online DDL, pgroll and gh-ost

Expand-contract is a procedure; tools automate parts of it. This section examines three approaches in depth, because they make different bets about where the complexity should live: in the engine, in a versioned view layer, or in an external copy process.

Native online DDL in PostgreSQL

PostgreSQL’s native toolbox is larger than many teams assume. The core primitives are CREATE INDEX CONCURRENTLY, NOT VALID plus VALIDATE CONSTRAINT, metadata-only ADD COLUMN with a constant default, and ALTER TABLE ... SET NOT NULL backed by a validated check. Combined with lock_timeout and retries, these cover a large share of everyday changes without any extra software.

CREATE INDEX CONCURRENTLY builds an index without blocking writes, at the cost of two table scans and waiting for existing transactions to finish. It cannot run inside a transaction block, and if it fails it leaves an invalid index that you must drop and recreate. Check pg_index.indisvalid after any failed concurrent build, because an invalid index still slows writes while helping nobody. These behaviours are documented in the PostgreSQL CREATE INDEX reference.

A safe sequence for adding a foreign key and a required column to a large table looks like this:

-- Session settings for every migration step
SET lock_timeout = '2s';
SET statement_timeout = '15min';

-- 1. Additive, metadata-only on modern Postgres (constant default)
ALTER TABLE orders ADD COLUMN currency text DEFAULT 'USD';

-- 2. Foreign key without a full-table validation under strong lock
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

-- 3. Validate later; this scans but does not block writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

-- 4. Index without blocking writes (cannot run in a transaction)
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

Native DDL’s strength is that there is nothing extra to operate. Its weakness is that it only helps for changes the engine can do cheaply. Changing a column type from integer to bigint, splitting a table, or changing a primary key all require a rewrite or a multi-step data migration, and for those the engine offers nothing. That gap is exactly what pgroll targets. The phrase “online DDL Postgres” in search results often blurs these cases; the honest answer is that Postgres online DDL covers additive changes well and breaking changes poorly.

pgroll: versioned schemas through views

According to its README, pgroll is an open-source command-line tool, licensed under Apache 2.0 and written in Go, that provides zero-downtime, reversible schema migrations for PostgreSQL by serving multiple schema versions simultaneously. The documentation states it works with Postgres 14.0 or later and with any Postgres service including RDS and Aurora. It ships as a single binary with no external dependencies.

Figure 2: The pgroll lifecycle. Start creates the new version and keeps old and new in sync; complete removes the old structure after clients have moved.

The mechanism is the interesting part. pgroll creates virtual schemas using views on top of the physical tables. On start, it performs additive changes in the physical schema. When a change is breaking, for example altering a column in an incompatible way, pgroll creates a new column in the physical table, backfills it from the old column, and installs triggers so that writes to either column propagate to the other for the whole migration period. It then exposes the new column through a new view-based schema version.

Clients choose a version by setting their search_path to the version schema, for instance SET search_path TO 'public_initial_migration' in the README’s example. Old and new application versions therefore run side by side against the same physical tables, each seeing the shape it expects. pgroll complete removes the old schema, drops no-longer-needed columns and triggers, and renames the new columns to their final names; the views keep clients working during that phase. pgroll rollback at any point before completion removes the new version and leaves the old one untouched.

Put differently, pgroll automates phases 1 through 3 and 6 of Figure 1 and provides a clean way to do phase 5, since the application cuts over by changing its schema search path rather than by changing which column it reads. A migration is declared as a JSON file of typed operations. The README gives a create_table example; the project supports many operation types, and you should consult the current documentation for the list rather than rely on a copy here.

The README also describes a baseline command to import an existing populated database, so adoption does not require starting from scratch. It documents benchmarks that run on every commit across multiple Postgres versions, covering backfill rate in rows per second, write amplification with and without the sync trigger, and schema-read function throughput. I will not quote numbers; the project publishes them and they change with each commit. What matters for planning is the direction: the trigger adds write cost during a migration, and the backfill competes with production traffic for I/O.

What pgroll costs you: your application must be able to select a schema version (usually a search_path set per connection or per session), which interacts awkwardly with connection poolers in transaction mode. Views add a layer to query planning, and some features such as certain DDL or tooling that introspects the physical tables will not see what the application sees. These are architectural properties of any view-based approach rather than defects, but they deserve a pilot before rollout.

gh-ost: triggerless shadow tables for MySQL

GitHub’s gh-ost addresses a different environment: very large MySQL tables under heavy write load. Its triggerless-design document explains the contrast with older tools. pt-online-schema-change adds AFTER INSERT, AFTER UPDATE and AFTER DELETE triggers to the original table so each change is replayed synchronously onto the ghost table in the same transaction. Facebook’s OSC uses triggers too, writing to a changelog table that a background process applies asynchronously.

gh-ost removes the triggers. It reads the changes from the binary log, pretending to be a MySQL replica, filters events for the original table, and applies them to the ghost table. According to the design document it works with row-based replication entries, and it can also be used with statement-based replication via a separate procedure. It prefers to connect to a replica (which needs log-slave-updates and binlog-format=ROW) and write to the primary, although it can connect directly to the primary.

Figure 3: gh-ost copies rows in chunks while tailing the binlog, throttles on lag or load, and finally swaps tables after a brief write block.

The workflow in the design document has four parts. First, setup and validation: it creates the changelog and ghost tables, applies the ALTER to the ghost table, and picks a shared unique key for chunking. Second, a copy flow that alternates between row-copy chunks and the binlog backlog, throttling on replication lag or load thresholds. Third, an optional postponement of cut-over, controlled by a flag file, so you can pick the moment. Fourth, cut-over: gh-ost locks the original table for writes, drains the remaining backlog, and swaps the tables.

The cut-over deserves attention. Because the design is asynchronous, a naive atomic rename would be incorrect when events remain unapplied. The design document says Facebook’s solution uses a two-step rename with a brief interval in which the table does not exist, a “table outage”. gh-ost instead blocks writes and then issues an atomic swap, using safety latches so the operation either succeeds atomically or fails and returns to the pre-cut-over state.

Decoupling is the headline benefit. The primary sees the ghost-table writes as unrelated to the original table’s workload; a single writer thread alternates between copy and backlog, so no high-concurrency contention occurs on the ghost table; and pausing the migration stops writes to the ghost table entirely, aside from negligible heartbeat writes. The document also notes testability: you can run a migration on a replica, stop replication at the end, perform the cut-over and roll it back, then compare checksums of the original and ghost tables. It states that GitHub continuously validates the tool this way on production replicas.

The document is candid about costs under “No free meals”. Traffic increases because gh-ost reads the data and writes it back, and the client library at the time did not support compression, so the full table volume crosses the wire. The tool is also more complex than a trigger-based design, since it must manage replica connections, heartbeats, lag and failures. Its design assumes a table with a suitable shared unique key and has restrictions around foreign keys; consult the project’s requirements document before relying on it for a specific schema.

Native MySQL online DDL as the first resort

Before reaching for gh-ost, check whether the operation is already online. The MySQL 8.0 manual’s online DDL table lists, per operation, whether in-place and instant algorithms apply and whether concurrent DML is permitted. Adding a column, dropping a column, renaming a column, and setting or dropping a default are instant or in-place with concurrent DML in the documented table, subject to the footnotes. Changing a column data type is marked as not permitted in place or instant, which means a table copy. That is the classic gh-ost use case.

You can protect yourself by stating the algorithm explicitly: ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT; fails immediately if the operation cannot be done instantly, instead of silently falling back to a slower copy. Explicit algorithm and lock clauses turn a surprise into an error, which is what you want from a migration.

Where each tool sits in the expand-contract flow

It helps to map the tools back to the phases. Native DDL performs the expand and contract steps that are cheap. A shadow-table tool performs a single large rebuild that replaces the backfill and the dual-write phases with one mechanism, at the price of needing double disk space and a cut-over moment. pgroll performs the whole six-phase cycle but pushes the compatibility problem to the view layer. None of them removes the need for an application that tolerates two schema shapes; gh-ost and native DDL leave that entirely to you.

Decision Record: Choosing an Approach

Choose by engine, by whether the change is breaking, and by how many application versions run concurrently. Use native online DDL for additive changes, a shadow-table tool for large MySQL rebuilds, and pgroll or manual expand-contract for breaking Postgres changes. The sections below record the context, options, decision, and consequences, in the style of an architecture decision record.

Figure 4: A decision path for choosing a migration approach. The first split is the engine; the second is whether the engine can do the change cheaply.

Context

A team operates a production relational database with tables from millions to billions of rows, deploys application changes several times per day via rolling updates, and requires that no schema change cause a sustained outage. Migrations are authored by application engineers, not a dedicated DBA. Rollback must be possible without restoring from backup.

Options considered

Dimension	Native online DDL	Manual expand-contract	pgroll	gh-ost	pt-online-schema-change
Engine	Postgres, MySQL	Any	Postgres 14+	MySQL	MySQL
Mechanism	Engine algorithms	Additive steps over several releases	Views plus sync triggers	Binlog tail plus ghost table	Triggers plus copy table
Handles breaking changes	Rarely	Yes	Yes	Rebuild-type changes	Rebuild-type changes
Old and new clients together	You design it	You design it	Built in via schema versions	You design it	You design it
Rollback before completion	Often not	Yes, by design	Yes, `rollback`	Abort before cut-over	Abort before swap
Extra write load	None	Dual writes	Sync trigger	Re-reads data, single writer	Triggers on every write
Extra disk	Varies	Extra column	Extra column	Full table copy	Full table copy
Operational footprint	None	Process discipline	One CLI plus state table	Replica access, binlog format	Triggers, privileges
Main risk	Hidden lock or rewrite	Forgotten contract step	View and search_path integration	Cut-over lock, lag, key choice	Trigger overhead and contention

Treat this matrix as qualitative. The properties come from the projects’ own documentation, but the relative cost on your workload can only be measured on your workload.

Decision rules

For PostgreSQL, apply these rules in order. If the change is additive or metadata-only per the ALTER TABLE documentation, use native DDL with a short lock_timeout. If it adds a constraint or index, use NOT VALID and CONCURRENTLY forms. If it is breaking, such as a type change, a split, or a rename with live consumers, use expand-contract; adopt pgroll if you want the versioning and backfill automated and can accept its integration requirements, otherwise write the phases yourself.

For MySQL, first check the online DDL table for the specific operation and state ALGORITHM=INSTANT or INPLACE explicitly. If the operation requires a table copy and the table is large and busy, use gh-ost when you can run it against a replica with row-based binlogs and the table has a suitable key; fall back to pt-online-schema-change otherwise, accepting trigger overhead. In sharded MySQL, check whether your platform already offers managed online schema changes before adopting a standalone tool; that is a platform question beyond what I verified for this article.

Consequences

Choosing expand-contract means more releases and a policy that every migration ships with a dated contract-step ticket. Choosing a tool adds a dependency that must be version-pinned and rehearsed. Whatever you choose, the application must tolerate two schema shapes for a period, and that is a code review rule, not a database feature.

Walk-through: Changing a Column Type Without an Outage

Abstract rules are easier to trust once applied. Consider converting events.id from integer to bigint before the sequence reaches the 32-bit limit. A direct ALTER COLUMN TYPE rewrites the table, per the ALTER TABLE documentation’s note that type changes normally rewrite the table and its indexes, and holds a strong lock for the duration. On a multi-billion-row table that is unacceptable.

The manual Postgres route

Expand: add id_new bigint with no default. Install a trigger, or application dual-write, that copies id into id_new on insert and update. Backfill in batches with the loop shown earlier, committing between batches. Build a unique index on id_new using CREATE INDEX CONCURRENTLY, then attach it as the primary key with ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY USING INDEX, which is a documented form that adopts an existing index. Update foreign keys in referencing tables using the NOT VALID pattern. Swap column names in one short transaction with a tight lock_timeout, and drop the old column in a later release.

This works, but each step is something an engineer must remember, and the sequence is easy to get wrong when sequences and defaults are involved. Treat the above as an outline and rehearse it on a production-sized copy. Primary key changes are the single most error-prone migration category I know of, which is an opinion rather than a statistic.

The pgroll route

With pgroll, the same intent is expressed as a typed operation in a JSON migration file, and the tool creates the new column, installs the sync triggers, backfills, and exposes both versions through views. The application that expects bigint switches its search_path to the new version schema while the old application instances keep reading the old view. The check that remains yours is whether your ORM, connection pooler, and monitoring work against view-backed schemas. Run pgroll start, deploy the new application, observe, then run complete; if the new version misbehaves, rollback restores the previous state.

The gh-ost route on MySQL

For the equivalent MySQL change, MODIFY COLUMN id BIGINT is documented as not supported in place or instant, so it requires a table rebuild. gh-ost builds the ghost table with the new type, copies in chunks, applies binlog events, throttles by lag, and swaps at a moment you choose using the postponement flag file. You need roughly the table’s size in extra disk, plus headroom for the binlog.

Rehearsal is the real control

In all three routes the strongest safeguard is the same: rehearse on a restored copy with realistic data volume and a replayed or synthetic write load. A rehearsal reveals the duration of the backfill, the lock waits, replication lag, and the edge cases that data contains and schemas do not mention, such as null values, duplicates, and values that fail a new constraint. Record the rehearsal timings in the change ticket; they become your abort criteria in production. If a rehearsal takes six hours and your abort criterion is “replication lag above thirty seconds”, you will know in advance whether a six-hour production run is realistic.

Trade-offs, Gotchas, and What Goes Wrong

Every approach here has a characteristic failure, and knowing them in advance is most of the value of this article.

Lock queues from long transactions. The most common cause of migration outages in PostgreSQL is not the migration but an idle-in-transaction session that holds a lock while your DDL waits. Before running DDL, query pg_stat_activity for old transactions, set lock_timeout, and retry. In MySQL the equivalent is a long transaction holding a metadata lock that blocks the online DDL’s brief locking phases.

Backfills that hurt production. A backfill is production traffic. Unbatched updates produce replication lag, table bloat in PostgreSQL, and long row-lock chains. Batch, sleep, and tie the batch rate to a lag signal. If you use replicas for reads, lag turns directly into stale reads for users.

Dual-write drift. If the application and a trigger both write the new column, or if some code path (a batch job, an admin script, a different service) bypasses the dual-write, columns diverge silently. The verification phase exists to catch this. Include out-of-band writers in your inventory before you start.

Invalid indexes and half-done constraints. A failed CREATE INDEX CONCURRENTLY leaves an invalid index. A NOT VALID constraint that you forget to validate protects only new rows. Both look finished in a casual schema dump. Add checks to your pipeline that fail the build when invalid indexes or unvalidated constraints exist.

Forgotten contract steps. Expand-contract leaves debris if the final step is not scheduled. Old columns confuse new engineers, consume space, and slow row scans. Track cleanup like any other work item with an owner and a date.

ORMs and caches that assume a shape. Object-relational mappers often cache column lists at startup, and prepared statements can fail after a column change. Some drivers raise “cached plan must not change result type” style errors after a type change in PostgreSQL. Test rolling restarts against the intermediate schema states, not only the final one.

Tool-specific limits. gh-ost needs access to binlogs with a row format, a replica configured to log updates, and a suitable key; it also transfers all data through the tool host. pgroll needs the application to select its schema version and adds sync-trigger overhead during the migration. Trigger-based tools add write latency and can contend with hot rows. None of these is a reason to avoid the tools; each is a reason to rehearse.

Replication and downstream consumers. Logical replication, change data capture pipelines, and analytics consumers see schema changes too. Adding a nullable column is usually harmless to them, but a type change or column rename can break a downstream connector. Coordinate with owners of CDC pipelines before the expand step, not after.

Hot-spot tables and the contract moment. Even when every previous step is gentle, the final swap or drop needs a brief strong lock. On a table receiving thousands of writes per second, that moment must be rehearsed, scheduled for lower traffic, and protected by lock_timeout or the tool’s own latches.

Practical Recommendations

Start by making lock behaviour visible. Put lock_timeout and statement_timeout in every migration session, and fail the migration rather than letting it wait. Add a migration linter or review checklist that flags operations known to rewrite or take strong locks, for example volatile defaults, type changes, plain CREATE INDEX, and constraints added without NOT VALID.

Second, standardize on expand-contract as the default for any breaking change, and write down the six phases so that engineers do not reinvent them under pressure. Tie each phase to a separate deploy and a separate pull request, and require that the contract step has a ticket with an owner before the expand step merges.

Third, choose tools by engine and by cost of rehearsal. On PostgreSQL, rely on native DDL for additive work and evaluate pgroll in a staging environment against your ORM and pooler before you need it in anger. On MySQL, use explicit ALGORITHM clauses, and keep gh-ost ready for table rebuilds, tested regularly on a replica so that the first production use is not the first use at all.

Fourth, rehearse on production-sized data and record timings. Fifth, add observability: dashboards for replication lag, lock waits, and long-running transactions, and a documented abort rule.

A short checklist:

Set lock_timeout and retry with backoff for every DDL statement.
Confirm whether each statement is metadata-only, in-place, or a rewrite, from the documentation.
Split constraints into add-as-NOT VALID and validate-later steps.
Build indexes with CONCURRENTLY and check for invalid indexes afterwards.
Batch backfills, throttle on replication lag, and verify old versus new before switching reads.
Keep dual writes for a full release cycle after switching reads.
Schedule the contract step with an owner and a date; rename before dropping.
Inventory out-of-band writers and downstream consumers before starting.
Rehearse on production-sized data and write down abort criteria.

Frequently Asked Questions

What is a zero downtime schema migration?

A zero downtime schema migration changes a database schema while the application keeps serving reads and writes without sustained errors or queueing. It relies on additive steps, short or avoided locks, and compatibility between old and new application versions. Brief exclusive locks measured in milliseconds are normally accepted; the goal is that no user-visible outage occurs and that you can abort or roll back before the final irreversible step.

How does the expand-contract pattern work?

Expand-contract splits a breaking change into backward-compatible stages. First expand the schema with a new column or table, then dual-write both structures, backfill existing data in batches, verify the two match, switch reads to the new structure, and finally contract by removing the old one. Because each stage works with both the previous and next application versions, deploys and rollbacks never need to be synchronized with the database.

Is pgroll better than writing expand-contract migrations by hand?

pgroll automates much of the expand-contract cycle for PostgreSQL 14 and later: it creates new columns, installs sync triggers, backfills data, exposes versions through views, and offers rollback. That removes error-prone manual steps. The trade-off is integration work, since applications must select a schema version, and extra trigger overhead during the migration. For occasional simple changes, native DDL or hand-written steps may be enough.

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

Use native online DDL first when the operation supports INSTANT or INPLACE, as listed in the MySQL online DDL table. Use gh-ost when the change needs a table rebuild, such as changing a column data type, and the table is large and busy. gh-ost reads the binary log instead of using triggers, throttles on lag, and swaps tables with a brief write block. It needs row-based binlog access.

Does PostgreSQL support online DDL?

Partly. PostgreSQL adds columns with a non-volatile default as a metadata-only change, builds indexes without blocking writes via CREATE INDEX CONCURRENTLY, and lets you add constraints as NOT VALID and validate them later with a weaker lock. Many other changes, such as most column type changes, still rewrite the table under a strong lock. Always use lock_timeout so a blocked migration fails fast instead of queueing traffic.

How do I roll back a failed schema migration?

Design for rollback before you start. In expand-contract, every step before the contract is reversible because the old structure still exists and is still written. pgroll offers a rollback command that removes the new version while the old one is untouched. gh-ost can abort before cut-over and leave the original table intact. After a destructive drop, only a backup restore helps, so rename before dropping and delay the drop.