Zero-Downtime Database Migrations: Safe Schema Changes in Production

A database schema change that requires taking the application offline is a choice, not a necessity. With the right pattern, you can rename columns, add constraints, split tables, and change data types while the application serves live traffic throughout. The technique requires more discipline than a simple ALTER TABLE with a maintenance window, but it makes continuous deployment possible for data-heavy changes that would otherwise require scheduled outages. If schema changes are part of a larger site overhaul, our guide to migrating a small business website without losing SEO covers the URL and content preservation side of that work.

The root problem with naive database migrations is the assumption that code and schema change simultaneously. In a rolling deployment, the old version of the code is still running when the new schema is first applied. In a blue-green deployment, you may need to roll back to old code but can't roll back a destructive schema change. The solution is to design migrations to be compatible with both the old and new versions of the application code simultaneously, the expand-contract pattern.

The Expand-Contract Pattern

Expand-contract (also called parallel change) breaks every migration into three phases, each deployed independently:

Phase 1: Expand: Add new structures without removing old ones. The new schema exists alongside the old. Old code continues working; new code can start using the new structures.

Phase 2: Migrate: Update application code to use the new structures. Deploy this code while old structures still exist. Backfill any data required for the new structures.

Phase 3: Contract: Remove the old structures. Both old and new code are now running the new version; old structures are no longer accessed.

Each phase is a separate deployment. The process takes longer than a maintenance window migration but eliminates downtime entirely.

Renaming a Column

Renaming a column is the canonical expand-contract example. The goal: rename users.full_name to users.display_name.

The naive approach, ALTER TABLE users RENAME COLUMN full_name TO display_name;, breaks old code immediately because full_name no longer exists. The expand-contract approach instead goes in phases.

Phase 1: Expand (Add the new column)

Add the new column with the same default, copy existing data into it, and install a trigger that keeps both columns synchronized. Old code continues reading and writing full_name; new code can start using display_name; the trigger guarantees the two never diverge. The trigger body checks both directions with IS DISTINCT FROM so a write to either column propagates to the other, and it runs BEFORE INSERT OR UPDATE on the users table as a standard PL/pgSQL function.

Phase 2: Migrate (Deploy code that uses new column)

Update all application code to read and write display_name instead of full_name. Deploy this code. Both columns continue to exist; the trigger keeps them synchronized.

Phase 3: Contract (Remove the old column)

Once the new code is fully deployed and the old code is no longer running, drop the sync trigger and function, then drop the old column with ALTER TABLE users DROP COLUMN full_name;.

Rollback is available at every phase. Phase 1 is fully reversible: drop display_name and the trigger. Phase 2: deploy the old code and it still works because the sync trigger is still in place. Phase 3 has no rollback available since the column is dropped, but old code is no longer deployed at that point, so this is acceptable.

Adding a NOT NULL Constraint

Adding NOT NULL to an existing column is a common migration that breaks under load without careful sequencing. The naive version, ALTER TABLE orders ADD COLUMN processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(); on a large table, acquires a full table lock and triggers a rewrite, which translates to minutes of downtime.

On PostgreSQL 11 and later, adding a column with a DEFAULT is fast because there is no table rewrite. The safe sequence is: add the column as nullable with the default, backfill any existing NULL values in small batches (chunks of 10,000 rows with a pg_sleep(0.1) pause between batches so you don't saturate the write workload), then set NOT NULL. On PostgreSQL 12+, ALTER COLUMN ... SET NOT NULL can validate without a full table scan.

For older versions or very large tables, add the constraint as NOT VALID first with ALTER TABLE orders ADD CONSTRAINT orders_processed_at_not_null CHECK (processed_at IS NOT NULL) NOT VALID;, which skips the table scan, then run ALTER TABLE orders VALIDATE CONSTRAINT orders_processed_at_not_null; separately. Validation acquires a ShareUpdateExclusiveLock that doesn't block reads or writes. The official PostgreSQL documentation on constraint validation covers the lock behavior in detail.

Adding an Index Without Locking

Regular CREATE INDEX locks the table for writes during the build. For large tables, this means minutes or hours of blocked writes. The fix is CREATE INDEX CONCURRENTLY, which builds the index without blocking writes at the cost of taking two to three times longer:

-- GOOD: CONCURRENTLY builds without blocking writes
-- Takes 2-3x longer but never locks
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

CREATE INDEX CONCURRENTLY has limitations. It cannot run inside a transaction block, it takes longer than regular index creation, and if it fails it leaves behind an INVALID index that must be dropped and recreated. You can find invalid indexes by querying pg_indexes joined against pg_index where indisvalid is false. Drop the invalid index with DROP INDEX CONCURRENTLY and rebuild. The PostgreSQL CREATE INDEX reference lists every caveat.

Splitting a Table (Adding a Foreign Key)

Extracting columns into a separate table requires careful sequencing to avoid constraint violations. The goal: extract the users.billing_address_* columns into a separate billing_addresses table.

In Phase 1, create the new table with columns for street, city, state, zip code, country, and timestamps. Add a nullable foreign key column billing_address_id BIGINT REFERENCES billing_addresses(id) to users; it must stay nullable so old code that doesn't set it still works. Then backfill: insert distinct existing billing address combinations into billing_addresses, and update users to link each row to its new billing address record by matching on street and city.

Phase 2 updates application code to write new addresses to billing_addresses, read all addresses from it, and update the billing record when a user edits their address. Phase 3 contracts: after all code uses the new structure, drop the five old billing_address_* columns from users in a single ALTER TABLE ... DROP COLUMN statement.

Changing a Column Type

Changing VARCHAR(255) to TEXT, or INT to BIGINT, requires handling the migration without locking. The common case is a table whose integer primary key is nearing the 2.1 billion row limit and needs a bigint.

In Phase 1, add a new bigint column alongside the existing int column with ALTER TABLE events ADD COLUMN id_new BIGSERIAL;, then create a unique index on it with CREATE UNIQUE INDEX CONCURRENTLY idx_events_id_new ON events(id_new);. In Phase 2, update the application to use id_new for new inserts while keeping id for lookups during the transition. Backfill so every existing row has id_new set; note that BIGSERIAL starts its sequence at 1, so you may need to advance the sequence past the current max.

Phase 3 renames columns atomically inside a transaction: ALTER TABLE events RENAME COLUMN id TO id_old;, then ALTER TABLE events RENAME COLUMN id_new TO id;, then ALTER TABLE events ALTER COLUMN id SET DEFAULT nextval('events_id_new_seq');. Phase 4 contracts by dropping id_old after the code is fully updated.

Migration Tool Setup

Flyway

Flyway is the standard SQL-first migration tool. Migrations are numbered SQL files in a db/migrations/ directory, named like V1__create_users_table.sql, V2__add_user_roles.sql, V3__create_orders_table.sql, and V4__add_orders_billing_address_id.sql. A V4 migration that adds the nullable column and builds its index concurrently looks like this: ALTER TABLE orders ADD COLUMN billing_address_id BIGINT; followed by CREATE INDEX CONCURRENTLY idx_orders_billing_address_id ON orders (billing_address_id);. Configuration lives in flyway.conf with the JDBC URL, credentials, flyway.locations=filesystem:db/migrations, flyway.outOfOrder=false, and flyway.validateOnMigrate=true.

Prisma Migrate

For TypeScript applications, Prisma Migrate generates migrations from schema changes. During an expand-contract rename, the schema keeps both fields nullable during the transition: displayName String? alongside fullName String?. Run npx prisma migrate dev --name add_display_name locally to create the migration, and npx prisma migrate deploy in production, which applies pending migrations without interactive prompts.

After the deploy, run a backfill script that pages through users where displayName is null and updates them in batches of 1,000 inside a transaction per batch, copying fullName into displayName.

Common Anti-Patterns That Cause Outages

1. Running migrations and code deployments simultaneously. The migration must complete before the new code goes live. Always: migrate first, deploy second. Never run them in parallel.

2. Adding NOT NULL without a default or backfill. ALTER TABLE orders ADD COLUMN customer_notes TEXT NOT NULL; breaks immediately because all existing rows have NULL for the new column, which violates the constraint.

3. Unique constraint on a column with duplicates. ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); fails if any duplicate values exist. Find them first with SELECT email, count(*) FROM users GROUP BY email HAVING count(*) > 1; and clean them up before adding the constraint.

4. Dropping a column before removing all references from code. Deploy code that removes all references to the column before dropping the column, not the reverse.

5. Long-running data migrations in a transaction. Wrapping a multi-million-row UPDATE in BEGIN; ... COMMIT; holds locks for the entire duration. Instead, run batched updates outside a transaction in a loop: update a batch of 5,000 rows where the target condition holds and the row hasn't been processed yet, exit when no rows were updated, and sleep 0.05 seconds between batches.

6. ALTER TABLE on high-write tables without testing lock acquisition time. Run the migration on staging with production-scale data first, with timing enabled, to estimate how long the lock will actually be held.

CI/CD Integration

In your pipeline, run the migration job first and gate the deploy job on it. A GitHub Actions workflow uses two jobs: a migrate job in the production environment that checks out the code, runs npx prisma migrate deploy with the production database URL from secrets, then runs npx prisma migrate status and fails the build unless the output contains "Database schema is up to date"; and a deploy job with needs: migrate that only runs after the migration succeeds. The GitHub Actions documentation covers job dependencies and environment protection rules. If your application runs on the edge rather than a traditional server, the deployment model changes and our guide to edge functions and Cloudflare Workers covers what that means for release pipelines.

The migration-first, deploy-second ordering is absolute. A code deployment that references a schema that doesn't exist yet causes immediate errors for all users. A schema migration that the old code doesn't use yet causes no problems at all.

Zero-downtime migrations require more planning than scheduled outage migrations, each change needs to be sequenced into compatible phases. The payoff is a deployment process that's predictable, reversible at every stage, and invisible to users. The same compatibility thinking applies when you stretch a single application across regions, which is the problem our multi-region architecture guide tackles.

What Most Guides Get Wrong

Most zero-downtime migration guides present expand-contract as a naming convention problem: add the column, dual-write, swap, drop. That framing misses the actual failure mode, which is almost never the SQL. In production, migrations fail because the team treated the database as a shared artifact that must match a single version of the code. The moment you accept that old code and new code run against the same schema for minutes or hours, the migration stops being a DDL problem and becomes a contract problem between deployed versions. Most guides also skip the backfill entirely, as if copying ten million rows were an afterthought rather than the phase most likely to degrade production performance; a backfill without batching and throttling can spike IOPS and replication lag enough to trip your alerting long before the schema change itself causes trouble. Another common mistake is treating the contract phase as optional cleanup. Teams leave the old column and the sync trigger in place for months, and the trigger silently doubles write latency on that table while nobody remembers why it exists. Guides also love CREATE INDEX CONCURRENTLY without mentioning that it cannot run inside a transaction, which breaks every migration tool that wraps migrations in one by default; you need explicit tooling support, not just the right SQL. Finally, most coverage assumes PostgreSQL. On MySQL 5.7, adding a column with a default still rewrites the whole table, and the "safe" recipe from a PostgreSQL guide will lock a busy table for an hour. The pattern is universal; the lock behavior of every statement is not. Verify each statement against your specific version before trusting any recipe, including this one.

Conclusion

Zero-downtime migrations come down to one discipline: never let the schema and the code change in a way that only one version can survive. Break every change into expand, migrate, and contract phases, deploy them separately, and keep the contract phase on your cleanup calendar so sync triggers and old columns don't linger. Add indexes concurrently, backfill in batches, and always run the migration before the deploy in your pipeline. Start with your next column addition: practice the full three-phase sequence on a low-risk table before you need it under pressure. Once the pattern is habit, maintenance windows become a choice rather than a requirement.