How Do You Run Zero-Downtime Database Migrations in Production?

Zero-downtime database migrations are non-negotiable for any SaaS product running under a service-level agreement. Yet schema changes remain one of the highest-risk operations an engineering team executes in production — a single ALTER TABLE on a 200-million-row table can hold an ACCESS EXCLUSIVE lock for minutes, queuing every API call behind it and converting a routine deploy into a P1 incident. The failure mode is entirely avoidable. Teams that run zero-downtime database migrations reliably have internalized a small set of patterns and the discipline to apply them every time, regardless of how minor a change appears on the surface.

The root problem is a mismatch between how developers think about schema changes and how databases execute them. A developer sees 'add a column'; PostgreSQL sees 'take an exclusive lock on every row in this table, potentially rewrite the heap, then release the lock.' At low data volumes the lock is held for milliseconds and nobody notices. At 50 million rows it is held for 20 to 60 seconds. At 500 million rows it can exceed your SLA multiple times over. Understanding what each DDL statement actually does under the hood — and which patterns avoid the dangerous operations entirely — is the prerequisite for shipping schema changes safely.

This guide covers the four phases of the expand-contract pattern that underpin every zero-downtime migration, the specific locking behaviors of common PostgreSQL DDL statements and how to work around each one, backfill strategies for large tables that do not stall production traffic, the tooling landscape (pgroll, Flyway, Liquibase, pg_osc), and the safety nets that keep a migration from becoming an outage. If your team is building production infrastructure as part of a SaaS product, these patterns should be standard operating procedure from the first schema change, not something discovered after the first production incident.

Why Database Migrations Are Your Riskiest Deploy

Application code deployments are reversible in seconds — roll back a container image, traffic shifts, the old version is live again. Database schema changes are fundamentally different because they modify shared state. Once a migration runs, it affects every application instance pointing at that database, regardless of which code version they are running. This creates the forward-and-backward compatibility constraint that makes migration safety hard: during a rolling deployment you may have version N+1 and version N of your application running simultaneously against the same database schema. Any schema change that breaks version N while version N+1 is still deploying will take down the instances that have not yet updated.

The second risk factor is lock contention. Relational databases use locks to maintain consistency, and many schema changes take locks that conflict with normal read/write traffic. A migration that takes 10 seconds to run is not holding a lock for 10 seconds — it is holding a lock until it completes, and every query that arrives during that window queues behind it. Connection pools fill up, timeouts cascade, and the error rate spikes across the entire application. This failure mode is the leading cause of migration-related incidents in production SaaS. Combining it with the deployment race described above is how a simple column rename becomes an outage. For a broader view of the structural decisions that make this worse when they are wrong, the post on common SaaS architecture mistakes covers related patterns that compound migration risk.

The Expand-Contract Pattern: Zero-Downtime Schema Changes in Four Phases

The expand-contract pattern, also called parallel change, is the foundational approach for any schema change that must not break running application code. It separates the change into a sequence of small, backward-compatible steps — each one individually safe to deploy — that together achieve the migration goal without ever creating a moment where old and new application code cannot both function against the same schema state.

• →Phase 1 — Expand: add the new column, table, or index to the schema without removing or renaming anything the current application code depends on. At this point old and new application code can both work: old code ignores the new column, new code writes to it. If the new column requires a NOT NULL constraint, add it as nullable first — a NOT NULL column without a default forces PostgreSQL to rewrite every existing row under an exclusive lock. Add nullable, backfill separately, then add the constraint in a later phase
• →Phase 2 — Dual-write: deploy application code that writes to both the old and new schema locations simultaneously. For a column rename (old_name to new_name), phase 2 writes to both columns on every insert and update. For a data-type change, phase 2 writes the converted value to the new column while keeping the old one populated. This ensures any new data is in both places during the transition window
• →Phase 3 — Backfill and switch reads: backfill historical data in the new column for rows that existed before dual-write started. Once the backfill is complete and you have verified data consistency, deploy code that reads from the new location only. By this point the old version is no longer running anywhere in the fleet, so reads from the new column are safe across all instances
• →Phase 4 — Contract: remove the old column, constraint, or index that no longer serves any purpose. No running application code references the old schema element, so the DROP is safe. The full migration required four deployments and produced zero downtime because at every moment exactly one version of application code was compatible with the current schema state

What PostgreSQL Locks During DDL — and How to Work Around It

PostgreSQL has eight lock levels, and most DDL statements take ACCESS EXCLUSIVE — the most restrictive lock, which conflicts with every other lock type including plain SELECT. A running DDL holding ACCESS EXCLUSIVE blocks every read and write to that table until the DDL completes. Knowing which statements trigger which locks tells you precisely where to focus your safety effort.

• →ADD COLUMN with a volatile default: in PostgreSQL 10 and earlier, adding any column with a default value required a full table rewrite under ACCESS EXCLUSIVE. PostgreSQL 11+ changed this: non-volatile constant defaults (string literals, integers, NULL) are added without a rewrite and hold the lock only briefly. Volatile defaults (now(), random(), uuid_generate_v4()) still trigger a full rewrite. Always add nullable columns with no default first, backfill values explicitly, then add the constraint
• →CREATE INDEX vs CREATE INDEX CONCURRENTLY: standard CREATE INDEX takes a SHARE lock that blocks all writes for the duration of the index build — on a large table this can be minutes. CREATE INDEX CONCURRENTLY builds the index in multiple phases while allowing concurrent reads and writes, at the cost of taking longer and being non-transactional. If a concurrent index build fails it leaves an INVALID index entry in pg_indexes that must be dropped and rebuilt before the index can be used. Always prefer CONCURRENTLY in production; always verify the index is valid after the build completes
• →ADD FOREIGN KEY: PostgreSQL validates the constraint by scanning the referencing table under ACCESS EXCLUSIVE. The safe alternative is a two-step approach: ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES other(id) NOT VALID adds the constraint metadata under a brief lock without validating existing rows. Then VALIDATE CONSTRAINT in a separate transaction validates existing rows under SHARE UPDATE EXCLUSIVE, which allows reads and writes and blocks only other DDL. Split these into two separate deploys
• →DROP COLUMN: PostgreSQL marks the column invisible in the system catalog rather than rewriting the table, so the operation is fast and holds ACCESS EXCLUSIVE only briefly. The real risk is not the DDL speed but the exposure if any running application code still references the column being dropped. Always confirm via code search and observability that zero application code reads or writes the column before running the DROP
• →RENAME TABLE or RENAME COLUMN: both take ACCESS EXCLUSIVE and immediately break any code referencing the old name. These are almost always the wrong approach for a live production system. Use the expand-contract pattern to add the new name alongside the old, migrate all reads and writes, then drop the old name — never rename directly with active traffic

Backfilling Large Tables Without Halting Production Traffic

A naive UPDATE across an entire large table is functionally equivalent to a full table rewrite under lock. The safe approach processes rows in small batches with explicit sleep intervals between commits, keeping each transaction short and giving autovacuum and normal write traffic time to proceed between iterations.

• →Use cursor-based batching: select the next batch using WHERE id > last_processed_id ORDER BY id LIMIT batch_size with an index on the ordering column. Never use OFFSET-based pagination — it performs a full sequential scan up to the offset on every iteration, making the operation O(n^2) on large tables. Commit each batch in its own transaction so the lock window per transaction is milliseconds regardless of batch size
• →Sleep between batches: add a sleep of 50 to 200 milliseconds between batches. This gives autovacuum time to reclaim dead tuples generated by the updates, prevents write-ahead log from accumulating faster than replicas can apply it, and keeps your primary within its normal SLA envelope. A backfill that runs at full speed and causes replication lag is a hidden risk even if it holds no long-running locks on the primary
• →Monitor replication lag throughout: use pg_stat_replication to watch standby lag during the backfill run. If lag exceeds your acceptable threshold, pause the backfill and let it drain before resuming. Watch pg_stat_user_tables for n_dead_tup on the target table — a high dead tuple count with autovacuum not running means you need to reduce batch size or trigger a manual VACUUM. These monitoring requirements belong in your cloud infrastructure runbook, not just in the migration script itself
• →Batch size guidance: 5,000 to 10,000 rows per transaction is the practical starting range for most workloads. Larger batches reduce total round trips but increase write amplification per transaction; smaller batches are safer for tables with large row widths or heavy concurrent write traffic. At 5,000 rows per batch with 100ms sleep intervals, backfilling 100 million rows takes 33 to 40 minutes of wall-clock time with negligible impact on production latency

Tooling: pgroll, Flyway, Liquibase, and pg_osc

Migration tooling divides into two categories: migration management tools that track which migrations have run and in what order, and lock-safe DDL execution tools that rewrite dangerous schema operations to run without extended locks. Most production systems need both, and they compose well.

• →pgroll: an open-source tool from Xata that implements the expand-contract pattern as a built-in primitive. You declare a schema transformation in its JSON format; pgroll handles the multi-phase execution — creating the new column, building a backfill view, running the background data migration, cutting over reads, and dropping the old column — without taking long-running locks at any phase. As of 2026, pgroll is the cleanest available implementation of zero-downtime DDL for PostgreSQL and the recommended starting point for teams that do not yet have a migration framework in place
• →pg_osc (pg_online_schema_change): implements safe DDL via a shadow-table approach — it creates a copy of the target table, syncs data through triggers, runs the schema change on the copy, and does a fast rename cutover. The trigger-based sync adds write amplification during the sync window and is therefore most appropriate for large table transformations where pgroll's expand-contract approach is not sufficient, such as changing a column data type in a way that cannot be expressed as a dual-write
• →Flyway and Liquibase: both are migration management tools — they track which migration files have been applied in which order and handle ordering and idempotency across environments. Neither makes your DDL lock-safe by itself; you must write SQL using the safe patterns above, or call pgroll or pg_osc for operations that require shadow-table rebuilds. Flyway is dominant in the Java and .NET ecosystems; Liquibase supports multiple database backends (PostgreSQL, MySQL, Oracle, SQL Server) and declarative rollback specifications. For teams already using either tool, the correct approach is to layer pgroll on top for the lock-sensitive operations
• →lock_timeout and statement_timeout: regardless of tooling, always set these two session-level parameters before any production DDL. SET lock_timeout = '2s' means the migration will abort rather than wait indefinitely behind a long-running transaction holding a conflicting lock. SET statement_timeout = '15s' prevents a runaway migration from holding a lock indefinitely. A migration that fails with lock timeout is recoverable — retry it during a low-traffic window. A migration that holds ACCESS EXCLUSIVE for three minutes while thousands of requests queue behind it is a multi-team incident

Safety Nets: Database Branching, Rollback Scripts, and Blue-Green Compatibility

Even well-designed migrations can behave unexpectedly in production due to differences in data distribution, query load patterns, or autovacuum state. Safety nets convert an unexpected behavior into a recoverable situation rather than a prolonged outage. Building these into your deployment process is part of what a mature internal developer platform should enforce automatically, not leave to individual engineers to remember.

• →Database branching for pre-production validation: cloud PostgreSQL providers including Neon, Xata, and AlloyDB offer copy-on-write database branches that materialize in seconds from production data. Run your migration against a production data branch before the real deploy. This surfaces lock time issues with actual data distribution, backfill errors on unexpected values, and constraint violations that do not appear with synthetic test fixtures. This is one of the highest-ROI safety steps available and adds almost zero cost to the release process
• →Explicit down-migration scripts: every migration file should have a companion script that reverses it. For an expand-phase migration adding a column, the down-migration is DROP COLUMN. For a contract-phase migration removing a column, the down-migration requires either a point-in-time restore or reconstruction from application logs — which is why contract migrations require the most careful planning and should never run in the same deploy window as the expand phase
• →Pre-migration VACUUM ANALYZE: run VACUUM ANALYZE on the target table before a large DDL operation. This reclaims dead tuples that would otherwise extend lock scope, updates planner statistics so post-migration queries use efficient plans, and reduces the autovacuum work queued during the migration window. On large tables this step can measurably reduce the effective lock duration
• →Blue-green schema compatibility: for teams using blue-green application deployments, schema migrations must complete while both environments can function with the current schema. This means the migration must be backward-compatible with the application code currently running on the live blue environment — exactly what the expand phase of expand-contract guarantees. Schedule the contract phase (dropping old schema elements) only after the green environment has replaced blue and blue instances are fully drained

Frequently Asked Questions

What is the expand-contract pattern for database migrations?

The expand-contract pattern breaks a schema change into backward-compatible phases executed across multiple deploys. First, expand the schema by adding the new column, index, or table without removing anything the current code depends on. Second, deploy code that dual-writes to both old and new schema locations. Third, backfill historical data and switch reads to the new location. Fourth, contract by removing the now-unused old schema element. At every phase, exactly one version of application code is compatible with the current schema state, eliminating the window where a schema change and a code deploy could race.

What is the safest way to add a column to a large PostgreSQL table in production?

Add it as nullable with no default. A nullable column with no default is added in milliseconds with only a brief catalog lock even on a billion-row table, because PostgreSQL does not rewrite existing rows. Once the column exists, populate it with a batched backfill script running 5,000 to 10,000 rows per transaction with sleep intervals between batches. Add the NOT NULL constraint only after every row is populated: use ALTER TABLE t ALTER COLUMN c SET NOT NULL on PostgreSQL 12+, which performs a fast validation scan under SHARE UPDATE EXCLUSIVE rather than rewriting the table, confirming no nulls exist without blocking reads or writes.

How do you roll back a failed database migration in production?

Rollback strategy depends on which phase failed. An expand-phase failure (adding a column that has not yet been written to by any application code) rolls back cleanly by dropping the new column — no data has been migrated and no production code depends on it. A contract-phase failure (an old column was dropped prematurely) is much harder: recovery requires either restoring from a point-in-time backup (accepting data loss for the interval since the backup) or reconstructing values from application logs or CDC event streams if available. This asymmetry is why contract migrations require the most careful pre-validation and should always have explicit recovery runbooks written before the migration runs.

What is the difference between pgroll and Flyway?

They solve different problems and compose well together. Flyway is a migration management tool: it tracks which migration files have been applied in which environments, handles versioning and ordering, and provides a schema history table. Flyway does not make your DDL lock-safe — you write the SQL and are responsible for using safe patterns. pgroll is a lock-safe DDL execution tool that implements expand-contract as a primitive: you declare a transformation in its schema, and pgroll orchestrates the multi-phase execution without long-running locks. Many production teams use both: Flyway manages migration history and ordering while pgroll or pg_osc handles the execution of individual changes that would otherwise take dangerous locks.

How do you backfill 100 million rows without locking the table?

Process rows in batches of 5,000 to 10,000 using cursor-based iteration (WHERE id > last_processed_id LIMIT batch_size) rather than OFFSET, commit each batch in its own short transaction so the lock window per commit is milliseconds, and sleep 50 to 200 milliseconds between batches to allow autovacuum to reclaim dead tuples and prevent replication lag from accumulating. Monitor pg_stat_replication for standby lag during the run and pause if lag exceeds your threshold. At 5,000 rows per batch with 100ms between batches, 100 million rows completes in roughly 33 to 40 minutes with no table lock and no measurable latency impact on production traffic.

How Belsoft Helps Engineering Teams Ship Schema Changes Safely

Database migration safety is an engineering discipline that compounds over time: teams that establish the right patterns early ship schema changes as routine events; teams that do not accumulate migration fear and incident debt. Belsoft works with SaaS engineering teams to establish migration tooling, expand-contract workflows, backfill automation, and the safety net practices — lock timeouts, database branching, rollback runbooks — that make schema changes reliable at every stage of product scale. Our cloud infrastructure practice covers the full database operations lifecycle, from initial schema design through PostgreSQL major-version upgrades and cross-cloud provider migrations.

If you are planning a high-risk schema change, a database engine migration, or want an independent review of your current migration process before the next incident reveals its gaps, book a technical conversation with the Belsoft engineering team. A focused review of your migration plan is a small investment against the cost of a multi-hour production outage.

“A schema change that requires downtime is not a database limitation — it is a planning failure.”