Zero-downtime database migrations for continuously deployed services

In a rolling deployment, old and new application instances can serve traffic at the same time. A schema change that is valid for the new code but invalid for the old code can turn a normal rollout into an outage. Treat every migration as a compatibility problem. Ask which application versions can read and write the table while the migration is running, which background jobs still use the old shape, and whether a rollback can safely return to the previous code.

The safest default is expand and contract. Expand the schema in a backward-compatible way, deploy code that can use both forms, backfill or dual-write, switch reads, and only then remove the old form. The process takes longer than a single destructive ALTER statement, but it gives you observation and rollback points. A migration is complete when old dependencies are drained, not when the first DDL command succeeds.

Adding a nullable column is usually compatible, but adding a non-null column without a default can fail existing inserts. Adding a default can rewrite a large table or create lock pressure depending on the database version and expression. Renaming a column breaks every old query immediately. Adding a new table is safer only if foreign keys, policies, triggers, and replication behavior are considered. Inspect the actual database and client code before labeling a change additive.

Indexes deserve an operational plan as well. Building a large index in a blocking mode can prevent writes. A concurrent index build may take longer, fail if duplicate data exists, or require a cleanup step. Verify the platform's supported command, lock behavior, and migration timeout. A migration runner should report progress and retain enough state to resume or explain a partial operation.

When a field changes shape, deploy code that can read the new value and fall back to the old value. Write the new representation first or write both representations in a controlled order. A background backfill can populate existing rows in batches. Include a version or checksum so the backfill can be resumed and verified. Do not let a best-effort dual-write silently hide a failure; record the mismatch and alert when it exceeds a safe threshold.

Once new reads are stable, stop writing the old form and monitor that it is no longer changing. Only then remove the fallback read and archive the old column. Keep a reconciliation query that compares both representations until the contract is retired. The application code should make the transition explicit instead of sprinkling null coalescing across many handlers where no one can tell which side is authoritative.

A backfill competes with user traffic for CPU, disk, locks, connections, and cache. Process bounded batches ordered by a stable key, commit frequently, and pause when the database shows pressure. Avoid a single transaction that locks millions of rows or creates a rollback larger than the available disk. Record the last processed key and the code version so an operator can resume without guessing.

Backfills should be idempotent and safe to run more than once. Recompute from authoritative source data rather than incrementing a derived value each time. If the new value depends on external state, snapshot that state or record the version used. Measure rows processed, rows failed, lag, lock waits, and estimated completion. A migration with no progress signal is an incident waiting for an operator to interrupt it blindly.

Understand which locks the DDL acquires and how long they can wait. Set a lock timeout so a migration fails clearly instead of blocking production writes indefinitely. Run the statement in a maintenance window only when the compatibility design cannot avoid it. Inspect active transactions and idle-in-transaction sessions before starting. A small forgotten transaction can prevent a metadata change and make the migration appear hung.

For multi-step changes, decide which steps are atomic and which are independently recoverable. A migration runner should record each step and stop on an unsafe partial state. Do not automatically roll back a long backfill because the rollback itself may be more disruptive than pausing and investigating. Separate schema intent from operational execution so the release record explains what happened.

Search for every consumer of a changed table: API routes, cron jobs, workers, analytics queries, database functions, RLS policies, views, and reporting exports. A background job that runs once a day can keep an old column alive long after the web deployment appears complete. Supabase policies and generated types can also encode column names or relationships. Include these dependencies in the compatibility window and drain plan.

If a migration changes authorization data, treat it as a security release. Verify policies in every role and test a rollback without accidentally widening access. If it changes billing or webhook state, preserve idempotency keys and event history. A schema migration is not isolated from product behavior. Its acceptance criteria should include the user-facing action that depends on the new shape.

Before deployment, rehearse the migration against a production-sized copy and capture duration, locks, writes, and index usage. During rollout, compare error rate, query latency, pool waits, replication lag, and backfill progress with the baseline. Keep the previous revision available until the new code and schema have survived a normal traffic cycle. A successful migration command is not proof that the contract is healthy.

Schedule the contract phase as a separate change with a named owner. Remove old columns, fallback reads, dual-writes, and compatibility flags only after evidence shows they are unused. Document the final schema and the rollback limits. Zero downtime is achieved through a sequence of compatible states, not by hoping one large DDL operation happens quickly.

During an expand-and-contract migration, old and new application revisions may run at the same time. Keep reads and writes compatible across that overlap, observe backfill progress, and make the backfill resumable. Gate the contract step on evidence that no old revision or job still uses the removed shape. A migration is safe when deploy, rollback, retry, and partial progress are all ordinary states rather than emergencies.

Use expand, migrate, contract as separate observable releases. Add nullable or dual-write fields first, deploy code that reads both forms, backfill in bounded batches, verify parity, switch reads, and remove the old form only after old revisions and jobs are drained. Keep each step resumable and compatible with rollback.

Run old and new application revisions against the expanded schema, interrupt the backfill, retry it, and exercise a rollback at every phase. Monitor lock duration, replication lag, batch age, error rate, and old-column/new-column parity. A migration that succeeds once but cannot resume after a worker crash is not production-safe.

Set a maintenance owner, abort threshold, batch size, and cleanup date. Avoid unbounded DDL locks and coordinate long-running backfills with database capacity. If parity fails, stop the contract step, keep the old representation, and repair from the source of truth. Record which revisions are compatible before removing a fallback.

Use PostgreSQL locking and ALTER TABLE documentation, expand-and-contract migration patterns, and the deployment platform's revision overlap behavior. Treat schema compatibility as an application contract.