Zero-Downtime Database Schema Migrations¶

A schema migration is not just a database command. During a rolling deployment, old and new application versions can run at the same time. Queue workers may lag behind web instances, scheduled jobs may still use an older binary, and external consumers may depend on the old schema. A change that works after every process has restarted can still break the system halfway through deployment.

The goal is backward-compatible evolution: every intermediate combination of application code and database schema must remain safe while traffic continues.

This chapter focuses on PostgreSQL-backed services and the expand-and-contract approach. It covers column changes, large-table backfills, constraint validation, migration orchestration, and recovery. It does not repeat the handbook's separate guide to index selection or transaction isolation; those topics are linked where relevant.

1. Treat a schema change as a multi-version protocol¶

A database is shared by more than the process receiving HTTP requests. Inventory every reader and writer:

API instances during a rolling deployment.
Queue consumers, scheduled jobs, and one-off workers.
Administrative scripts and support tools.
Reporting, analytics, ETL, and change-data-capture consumers.
Older binaries that may be restarted during rollback.

A migration is safe only when those consumers can coexist with the schema at each deployment stage.

For a rename from customers.name to customers.display_name, a direct rename is risky. An old binary that still queries name fails as soon as the column disappears. Releasing the application first is not enough: a new binary may start before every old worker has stopped.

Use an expand-and-contract sequence:

Expand: add the new representation without removing the old one.
Deploy compatibility code: understand both representations and write both where necessary.
Backfill: copy existing data in bounded, restartable batches.
Verify and constrain: prove the new representation is complete, then enforce its invariant.
Switch reads: make the application use the new representation.
Contract: stop using the old representation, wait through the rollback window, and remove it in a later migration.

The important property is not the number of releases. It is that no step requires every application process to switch at exactly the same instant.

2. Write a compatibility matrix first¶

For the rename example, make the expected combinations explicit:

Application version	Expanded schema: old and new columns	Contracted schema: new column only
Old version reads/writes `name`	Works	Fails
Compatibility version reads old, writes both	Works	Fails if it still references `name`
New version reads/writes `display_name`	Works after backfill	Works

The old version must be gone before the old column is removed. The compatibility version must stop referencing the old column before contraction. If rollback may restore an older binary, keep the old column until that rollback window has closed.

Apply the same reasoning to required fields, type changes, table splits, enum replacements, foreign keys, and serialized JSON structures. A database migration and an application release are separate deployable changes; design their order explicitly.

3. Expand with changes old code can tolerate¶

For a new derived field, start with a nullable column:

ALTER TABLE public.customers
    ADD COLUMN display_name text;

Old inserts that omit the column continue to work because it initially accepts NULL. New code can begin populating it while the old field remains available.

PostgreSQL lock behavior still matters. Most forms of ALTER TABLE acquire an ACCESS EXCLUSIVE lock unless a subcommand documents a weaker mode. The operation above is usually a short catalog change, but it can wait behind a long-running transaction. A waiting DDL request can itself create queueing for later work.

Use a short, migration-scoped lock_timeout so the operation fails and can be retried rather than waiting indefinitely:

SET lock_timeout = '2s';
ALTER TABLE public.customers
    ADD COLUMN display_name text;
RESET lock_timeout;

The value is illustrative, not a universal production setting. Keep statement_timeout greater than lock_timeout when both are set. PostgreSQL documents that lock_timeout applies only while waiting to acquire a lock; it does not limit statement execution time.

A constant, non-volatile default can be fast on supported PostgreSQL versions because its value is stored in metadata for existing rows. A volatile default such as clock_timestamp() can require a table rewrite. Changing an existing column's type can also rewrite the table and its indexes. Check the exact PostgreSQL version and documented behavior rather than inferring cost from the SQL syntax.

For a large table, adding a nullable column and populating it later is often easier to reason about than combining a new column, a default, a constraint, and a full-table update in one migration.

4. Deploy compatibility code before the backfill¶

The first application release should preserve the old contract while preparing the new one:

Continue reading name so rows not yet backfilled still work.
On every write, populate both name and display_name.
Derive both values from the same validated business input.
Ensure every writer follows the same rule.
Keep the new field nullable until all writers are compatible and existing rows are repaired.

Deploy this version to every process that can write the table: workers, scheduled tasks, admin jobs, and maintenance scripts as well as web instances. Wait until old processes have drained or been deliberately stopped.

Dual writes introduce a temporary consistency risk: the two fields can disagree. Keep the transformation in one application function, test every write path, and measure mismatches if the values should be equivalent. If the transformation is not reversible, define which field is authoritative during the transition.

Do not switch reads merely because the new column exists. Existence is a schema fact; completeness and correctness are data facts.

5. Backfill large tables in bounded batches¶

A single update over millions of rows can generate substantial WAL, hold locks for a long time, create dead tuples, increase replica lag, and compete with live queries for I/O. It may also exceed the deployment window or make cancellation difficult.

Use a restartable batch process. This PostgreSQL statement claims up to 1,000 rows whose new value is missing, updates them, and returns the processed identifiers:

WITH batch AS (
    SELECT id
    FROM public.customers
    WHERE display_name IS NULL
    ORDER BY id
    LIMIT 1000
    FOR UPDATE SKIP LOCKED
)
UPDATE public.customers AS customer
SET display_name = customer.name
FROM batch
WHERE customer.id = batch.id
  AND customer.display_name IS NULL
RETURNING customer.id;

Run each batch in its own short transaction and repeat. The final IS NULL predicate makes the update safe to retry for rows already populated. SKIP LOCKED lets multiple workers claim disjoint batches, but it is not proof that the table is complete: an empty batch can mean remaining rows are temporarily locked. Run an independent completion check after workers have had a chance to finish.

For a single worker, keyset pagination over a stable primary key is another option. Avoid large OFFSET scans; their work grows as the cursor moves, and concurrent inserts or deletes make page boundaries awkward. Choose a strategy that can resume from durable progress without skipping rows.

A production backfill should:

Store progress outside process memory.
Use deterministic transformations and idempotent batches.
Limit batch size and concurrency; increase them only when the database has headroom.
Pause or slow down when replica lag, write latency, WAL volume, or dead tuples rise.
Avoid one transaction spanning the entire table.
Record rows attempted, changed, skipped, failed, and remaining.
Reconcile the result with an independent count or consistency query.

If the backfill takes hours or days, run it as a controlled background job, not inside application startup or a request handler. Make pause, resume, and retry explicit.

6. Verify data before enforcing the new invariant¶

Before making display_name required, prove that no existing row is missing it and that the transformation is correct. A null count is necessary but may not be sufficient: compare old and new values, check domain rules, and inspect conversion exceptions.

After old writers have stopped and the backfill is complete, PostgreSQL can add a check constraint as NOT VALID:

ALTER TABLE public.customers
    ADD CONSTRAINT customers_display_name_present
    CHECK (display_name IS NOT NULL) NOT VALID;

The constraint is enforced for subsequent inserts and updates even though existing rows have not yet been scanned. An old writer that omits display_name would now fail, which is why compatibility and backfill must come first.

Validate existing rows separately:

ALTER TABLE public.customers
    VALIDATE CONSTRAINT customers_display_name_present;

Validation scans existing data and acquires a SHARE UPDATE EXCLUSIVE lock, which permits ordinary concurrent reads and writes. The initial ADD CONSTRAINT still needs a DDL lock; NOT VALID avoids the full-table validation scan during that short operation, not every lock.

Once the check is valid, PostgreSQL can use it as proof when setting the column to NOT NULL:

ALTER TABLE public.customers
    ALTER COLUMN display_name SET NOT NULL;

ALTER TABLE public.customers
    DROP CONSTRAINT customers_display_name_present;

Keep the valid check in place until SET NOT NULL has completed. PostgreSQL documents that a valid check proving the column has no nulls can let it skip the usual full-table scan for SET NOT NULL. Test the sequence on the PostgreSQL major version you operate.

7. Treat indexes and foreign keys as separate rollout steps¶

An index can be part of a schema migration, but its operational cost differs from adding a nullable column. A normal PostgreSQL index build blocks writes while it runs. CREATE INDEX CONCURRENTLY permits ordinary inserts, updates, and deletes, but performs more work, waits for relevant transactions, and cannot run inside a transaction block.

A concurrent build can fail and leave an invalid index that is ignored by query planning but still adds write overhead. Inspect its validity before retrying or assuming the migration succeeded. The handbook's SQL indexes and safe index deployment guide covers index selection and query-plan validation in detail.

Foreign keys and uniqueness rules also need a deliberate plan. PostgreSQL supports adding a foreign key as NOT VALID and validating it later. For uniqueness, a common pattern is to create a unique index concurrently and then attach it as a constraint where the table and index shape meet PostgreSQL's requirements. Check the relevant restrictions, especially with partitioned tables.

Do not put a long index build, a large backfill, and unrelated table changes into one all-or-nothing deployment step. Separate operations so each has a clear timeout, progress signal, and recovery procedure.

8. Coordinate migrations independently of application startup¶

Do not let every web process attempt schema migration when it starts. During autoscaling or a rolling deployment, several instances may start together. They can race, contend for DDL locks, or fail because another instance is midway through a change.

Use one migration job in the deployment pipeline or a migration system that provides reliable serialization. The runner should:

Target the intended database and schema version.
Serialize execution across deployment workers.
Record which migration completed, not merely which process started it.
Use bounded lock waits and statement timeouts.
Separate transactional DDL from operations PostgreSQL forbids inside a transaction, such as concurrent index creation.
Emit enough diagnostics to identify the exact failed step.

A database advisory lock can help coordinate a custom runner, but it does not replace deployment orchestration or a migration history table. Every process that can run migrations must use the same coordination rule.

9. Plan for partial completion and rollback¶

A migration can fail after the column is added, halfway through a backfill, during constraint validation, or after an index build leaves an invalid artifact. Recovery should be designed, not improvised.

Failure	Safe response
DDL cannot acquire its lock before timeout	Let it fail; identify the blocker and retry in a quieter window
Backfill worker stops	Resume from durable progress; repeat idempotent batches
Rows fail transformation	Quarantine or repair exceptional records; do not silently substitute incorrect values
Constraint validation fails	Repair violating rows, then validate again
Concurrent index build fails	Inspect index validity, remove or rebuild the invalid artifact, then retry
New application version has a defect	Roll back application code while the old schema is present; preserve data written to the new field
Contract migration removed the old field	Prefer a forward fix or planned recovery; do not assume an application rollback can recreate lost data

A destructive contract step is not automatically reversible. Dropping a column can make a later application rollback impossible even if the old application artifact is available. Delay contraction until the new path is stable and the rollback window has closed. For high-risk transformations, retain a recovery copy or reversible mapping until the business owner accepts the loss of the old representation.

Make migration steps observable and restartable. A migration marked complete before its data work is complete is worse than one that fails visibly.

10. Monitor the database and application during the change¶

A migration can succeed syntactically and still harm the service. Watch both the database and the user-facing system:

DDL lock waits, blocked sessions, and long-running transactions.
Request latency, error rate, connection-pool wait time, and write throughput.
Backfill rows per second, batch duration, retries, and remaining rows.
WAL generation, replication lag, disk headroom, and dead-tuple growth.
Index build progress and index validity.
Constraint validation duration and failures.
Mismatches between the old and new representations.

PostgreSQL exposes current activity and locks through pg_stat_activity and pg_locks. It reports index-build progress through pg_stat_progress_create_index. Use those views to understand whether a migration is waiting, scanning, or blocked; do not rely only on a CI job's final exit code.

Define abort thresholds before starting. If user-visible latency or replica lag crosses the agreed limit, pause the backfill or stop the rollout. A migration is not successful if it completes by exhausting the database.

11. Interview questions with reasoned answers¶

Q1. Why is a direct column rename unsafe during a rolling deployment?¶

Old application instances can continue querying the old column after the DDL commits. Add the new column, deploy compatibility code, backfill, switch readers, and remove the old column only after every consumer has moved.

Q2. Why add a nullable column before adding NOT NULL?¶

The old application may omit the new field. A nullable expansion keeps those writes valid while new code is deployed. Backfill existing rows, stop old writers, verify completeness, and enforce the constraint later.

Q3. What does NOT VALID guarantee?¶

For supported check and foreign-key constraints, PostgreSQL skips the initial scan of existing rows but enforces the constraint for subsequent writes. Existing rows are not yet proven valid. Run VALIDATE CONSTRAINT after repairing old data.

Q4. Why backfill in batches instead of one update?¶

Small committed batches bound transaction duration, lock lifetime, WAL bursts, and rollback work. They let the operator pause or throttle the job and make progress restartable. The trade-off is more orchestration and an explicit completion check.

Q5. Can CREATE INDEX CONCURRENTLY run in a normal transactional migration?¶

No. PostgreSQL does not allow it inside a transaction block. The migration runner must support a non-transactional step. A failed concurrent build can leave an invalid index, so inspect catalog state before retrying.

Q6. How do you change a column's type safely on a large table?¶

Check whether PostgreSQL can perform the cast without rewriting the table; most substantive type changes require a rewrite. For a large table, add a new column of the target type, dual-write, backfill, validate converted values, switch readers, stop old writes, and remove the old column later.

Q7. Where should migrations run in a multi-instance service?¶

Prefer one serialized deployment job, not every application instance at startup. Keep migration history, lock coordination, bounded timeouts, and clear failure reporting.

Q8. When is rollback no longer safe?¶

Once the contract step removes data or a schema element required by the old application, restoring the old binary may fail or lose information. Delay destructive cleanup until the rollback window closes; after that, plan a forward fix or explicit data recovery.

12. Migration review checklist¶

Before approving a production schema change, verify:

[ ] All readers and writers are inventoried, including workers and external consumers.
[ ] Old and new application versions are compatible with the expanded schema.
[ ] DDL lock behavior and table-rewrite risk are understood for the PostgreSQL version in use.
[ ] Lock and statement timeouts are scoped to the migration.
[ ] Large data changes are batched, restartable, observable, and throttled.
[ ] New writes cannot reintroduce rows that the backfill has repaired.
[ ] Data completeness and semantic correctness are checked independently.
[ ] Constraints are enforced and validated in a safe sequence.
[ ] Non-transactional operations have explicit failure and retry handling.
[ ] Rollback remains possible until the planned contract point.
[ ] The rollout has pause thresholds, an owner, and a recovery runbook.

The safest migration is not the one with the fewest SQL statements. It is the one whose intermediate states are compatible, whose workload is bounded, and whose failure can be understood and resumed without improvisation.