Zero-Downtime Database Deployments: A Practical DevOps Guide

Most teams can deploy application code without downtime. Rolling updates, blue/green and canary releases are well understood, and we covered the deployment side in Zero-Downtime Deployments Across Multi-Cloud Topologies. The part that still causes outages is the database.

A rolling deploy means two versions of your application run against the same database at the same time, for minutes or hours. Any schema change that the old version cannot handle, or that locks a busy table, turns a routine release into an incident. Zero-downtime database deployments are about making schema changes that are safe under exactly those conditions.

Why database changes break releases

Three things go wrong, usually together:

1. Incompatible schema. The new schema drops or renames something the old application version still reads or writes.
2. Locks. DDL takes a lock on the table. On a large or busy table, the statement waits behind long transactions and every query queued behind it waits too. The application sees timeouts that look like an outage.
3. No rollback path. Code can be rolled back in seconds. A dropped column cannot. If the migration and the code ship together and the code is reverted, the old code may not work with the new schema.

The fix for all three is the same discipline: never make a change that requires the old and new application versions to disagree about the schema.

The expand and contract pattern

Expand and contract (also called parallel change) splits every breaking change into backward-compatible steps, each shipped and verified on its own.

Example: renaming a column

Renaming users.fullname to users.display_name with a single ALTER TABLE ... RENAME COLUMN breaks every running instance of the old code at the moment it executes. The expand and contract version:

1. Release 1: ALTER TABLE users ADD COLUMN display_name text; Deploy code that writes both columns.
2. Backfill: copy fullname into display_name in batches (see below).
3. Release 2: deploy code that reads display_name, still writing both.
4. Release 3: stop writing fullname.
5. Release 4: ALTER TABLE users DROP COLUMN fullname;

That is four releases instead of one. In exchange, every step can be rolled back independently, and nothing ever needs a maintenance window. For teams that deploy daily, this takes days, not weeks.

Lock-safe DDL in PostgreSQL

Knowing which statements are cheap matters as much as the pattern itself. A few PostgreSQL specifics:

• Adding a nullable column is a metadata-only change, and since PostgreSQL 11 so is adding a column with a constant (non-volatile) default. Both still need a brief ACCESS EXCLUSIVE lock, which is why the lock timeout below matters.
• Creating an index blocks writes unless you use CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block. Many migration frameworks wrap migrations in transactions by default, so this needs to be disabled for that migration.
• Adding a foreign key or check constraint scans the table under lock. Add it as NOT VALID first, then validate separately. Validation takes a weaker lock.
• Setting NOT NULL scans the table. On PostgreSQL 12+, a validated CHECK (col IS NOT NULL) constraint lets SET NOT NULL skip the scan.

Always set a lock timeout so a migration that cannot get its lock fails fast instead of queuing every other query behind it:

SET lock_timeout = '3s';
SET statement_timeout = '15min';

-- Foreign key without a long lock
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

-- Must run outside a transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx
  ON orders (customer_id);

If a concurrent index build fails, it leaves an INVALID index behind. Drop it and retry rather than assuming it exists.

Online schema changes in MySQL

MySQL 8.0 supports ALGORITHM=INSTANT for adding columns and some other changes, which avoids copying the table. Specify the algorithm explicitly so the statement fails instead of silently falling back to a table copy:

ALTER TABLE users ADD COLUMN display_name VARCHAR(255) NULL, ALGORITHM=INSTANT;

For changes that cannot be done in place on large tables, use an online schema change tool such as gh-ost or pt-online-schema-change. Both build a shadow copy of the table, keep it in sync and swap it in at the end. Test the cut-over on a production-sized copy first, and watch replica lag during the run.

Backfilling data without hurting production

Backfills are where well-planned migrations still cause incidents. A single UPDATE users SET display_name = fullname; on a large table holds row locks for a long time, generates a burst of write-ahead log and can push replicas far behind.

Backfill in small, resumable batches:

-- Run repeatedly from a job until it updates 0 rows
UPDATE users
SET display_name = fullname
WHERE id IN (
  SELECT id FROM users
  WHERE display_name IS NULL AND fullname IS NOT NULL
  ORDER BY id
  LIMIT 5000
);

Pause between batches, monitor replication lag and database CPU, and make the job safe to stop and restart. Run it as a separate, observable job, not as part of the migration that runs during deploy.

Wiring zero-downtime database deployments into the pipeline

The pattern only works if the pipeline enforces it. A workable setup:

1. Migrations run as their own pipeline step, before the new application version rolls out. Expand migrations are designed to be compatible with the code already running.
2. Destructive changes are separate migrations, released only after the code that stopped using the old structure has been fully deployed.
3. A migration linter runs in CI. Tools such as squawk for PostgreSQL flag statements like non-concurrent index creation, adding constraints without NOT VALID or column type changes. A simple rule that rejects DROP COLUMN, RENAME and ALTER COLUMN ... TYPE in the same release as the code change catches most mistakes.
4. Migrations are tested against production-like data size. A migration that takes 50 ms on a seed database can take 40 minutes on a real one.
5. Every migration has a defined rollback, even if the rollback is "leave the new column in place and redeploy the previous code."

For Kubernetes deployments, run migrations as a Job or a pre-deploy pipeline step rather than an init container on every pod. Init containers race each other when several replicas start at once.

Common mistakes

• Coupling migration and code in one deploy. If a rollback of the code would break against the new schema, the release is not zero-downtime, however fast it is.
• Forgetting the ORM. Some ORMs select every column explicitly and fail when a column disappears, even if your code never uses it. Remove the column from the model in a release before you drop it.
• No lock timeout. One long-running analytics query can turn a 100 ms ALTER TABLE into a site-wide stall.
• Backfilling inside the migration. Data migrations belong in a monitored, batched job.
• Contracting too early. Wait until no running instance, scheduled job or replica consumer uses the old structure. Background workers and reporting queries are easy to forget.

When to bring in help

Zero-downtime database deployments are a process change more than a tooling change. Most teams need a migration standard, a CI linter, a backfill job template and a few rehearsals on production-sized data before it becomes routine. That is typical DevOps & Platform Engineering work, and if you are managing a database-heavy roadmap, a dedicated engineer can own the process end to end.

If schema changes are already causing incidents or forcing maintenance windows, start with a Free Engineering Assessment. We will review how migrations and deployments work today and recommend the next practical step.