Zero-Downtime Database Migrations: Expand, Migrate, Contract

A successful ALTER TABLE does not automatically make a deployment safe. In production, old and new application versions commonly overlap for minutes or longer. A migration that locks a table, renames a column immediately, or backfills millions of rows in one transaction can increase latency, exhaust the connection pool, and make rollback impossible.

The Expand–Migrate–Contract pattern divides a schema change into several backward-compatible releases. The service remains available while code and data gradually move to the new structure.

Why do migrations cause downtime?

Downtime usually comes from locks and version incompatibility rather than SQL syntax:

• Old application instances still read a column that was renamed or removed.
• ALTER TABLE waits behind a long transaction and then blocks incoming queries.
• A regular index build scans a large table while blocking writes.
• A massive backfill creates WAL, replication lag, table bloat, and an I/O spike.
• A new constraint forces the database to scan all existing data during deployment.

The core rule: evolve the database and application in steps where every step works with both the version before it and the version after it.

The three Expand–Migrate–Contract stages

1. Expand: add the new structure while preserving the old one.
2. Migrate: make the application read and write the new structure, backfill old data, and verify it.
3. Contract: remove obsolete code and schema only after every old consumer is gone.

These should be independent deployments. The observation period between them may last hours or days depending on traffic, observability, and the release cycle.

Example: split a customer's full name

The customers table currently stores full_name. We want separate first_name and last_name columns. Renaming or deleting full_name in one migration is unsafe because old application instances still depend on it.

Stage 1: Expand the schema

ALTER TABLE customers
    ADD COLUMN first_name text,
    ADD COLUMN last_name text;

The new columns initially allow NULL because historical rows have not been migrated. This deployment only expands the schema. Set short timeouts so the migration fails early instead of waiting in a lock queue and causing a pile-up:

SET lock_timeout = '2s';
SET statement_timeout = '15s';

ALTER TABLE customers ADD COLUMN first_name text;

If the lock cannot be acquired, stop and retry in a safer window. Do not blindly increase the timeout; identify the long-running transaction first.

Stage 2: Deploy code compatible with both schemas

The transition release can write the old and new representations:

UPDATE customers
SET full_name = :full_name,
    first_name = :first_name,
    last_name = :last_name
WHERE id = :id;

Reads prefer new columns and fall back to the old one:

SELECT
    id,
    COALESCE(first_name || ' ' || last_name, full_name) AS display_name
FROM customers
WHERE id = :id;

Dual-write should be temporary. Track records missing new values and log mismatches between representations. If several services write the table, every writer must be upgraded before proceeding.

Stage 3: Backfill in batches

Do not update the whole table in one transaction. Process small primary-key ranges, commit between batches, and throttle the job:

UPDATE customers
SET first_name = split_part(full_name, ' ', 1),
    last_name = substring(full_name FROM position(' ' IN full_name) + 1)
WHERE id > :last_id
  AND id <= :next_id
  AND first_name IS NULL;

Real name parsing requires better business rules than this simplified SQL. The important property is idempotency: rerunning the job must not corrupt completed rows.

• Batch by primary key instead of OFFSET.
• Measure batch duration, row count, replication lag, CPU, I/O, and WAL.
• Reduce the batch size or pause under database pressure.
• Store checkpoints for recovery.
• Never keep a transaction open while sleeping.

Verify before switching the read path

SELECT count(*)
FROM customers
WHERE first_name IS NULL OR last_name IS NULL;

Beyond counting nulls, compare samples and business invariants. Shadow reads can evaluate both representations, expose the new result to a small traffic percentage, and record differences without affecting every user.

Once verification passes, deploy a version that reads only the new columns while continuing to write the old column during an observation window. A feature flag offers a quick read-path fallback without rolling back the schema.

Add constraints without a long blocking scan

PostgreSQL supports CHECK ... NOT VALID, which enforces the rule for new writes without immediately scanning all historical rows. Validate separately:

ALTER TABLE customers
ADD CONSTRAINT customers_first_name_present
CHECK (first_name IS NOT NULL) NOT VALID;

ALTER TABLE customers
VALIDATE CONSTRAINT customers_first_name_present;

After validation, convert it to NOT NULL according to your PostgreSQL version and tested plan. NOT VALID applies only to selected constraint types such as checks and foreign keys; do not assume universal support.

Build an index on a large table

CREATE INDEX CONCURRENTLY idx_customers_last_name
ON customers (last_name);

CONCURRENTLY reduces write blocking but takes longer, consumes resources, and cannot run inside a regular transaction block. A failure may leave an INVALID index, so deployment automation must detect and resolve it before retrying.

Do not add an index merely because a column is new. Validate real queries with EXPLAIN (ANALYZE, BUFFERS) in a representative environment and monitor after release.

Change a data type safely

A direct type change may rewrite the table or hold a long lock. For large tables, create a new column, dual-write, backfill, and then switch reads:

ALTER TABLE orders ADD COLUMN amount_cents bigint;

UPDATE orders
SET amount_cents = round(amount * 100)
WHERE amount_cents IS NULL
  AND id > :last_id
  AND id <= :next_id;

Only after every reader and writer uses amount_cents should amount enter the Contract stage.

The final Contract stage

Remove old structures only when telemetry proves they are unused. A typical order is:

1. Stop dual-writing the old column.
2. Observe at least one complete deployment cycle and slow background workers.
3. Remove fallback code, temporary dashboards, and migration jobs.
4. Set a lock timeout and remove old constraints, indexes, or columns in a separate deployment.

SET lock_timeout = '2s';
ALTER TABLE customers DROP COLUMN full_name;

In PostgreSQL, dropping a column generally makes it invisible rather than immediately reclaiming all disk space. Do not trigger a table rewrite solely to recover space without assessing I/O, locks, and the maintenance window.

Rollback is not simply running “down”

A code rollback is safe only while the new schema remains compatible with the old application. That is why Expand preserves old columns and the transition release dual-writes them. After Contract, recovery becomes harder because old data may no longer be updated or may have been deleted.

Every plan should state which code version can be restored, what data could be lost, whether the backfill is reversible, and where the point of no return lies. A backup does not replace fast rollback; restoring a large database may exceed the service SLA.

Production checklist

• Test against a copy with production-like size and data distribution.
• Identify the lock mode, lock duration, and whether a table rewrite occurs.
• Set appropriate lock_timeout and statement_timeout.
• Ensure old and new code can overlap during a rolling deployment.
• Make the backfill idempotent, checkpointed, throttled, and observable.
• Monitor errors, latency, connection pools, replication lag, WAL, and disk.
• Use a feature flag for the read path and maintain a stop/resume runbook.
• Contract only after confirming that no old consumer remains.

Conclusion

Zero-downtime database migration is a compatibility and operations problem, not merely a SQL problem. Expand introduces the new path without breaking the old one; Migrate moves code and data with observation; Contract removes legacy structures after evidence says it is safe. Small reversible steps are easier to pause, measure, and roll back than one all-or-nothing migration.

References

• PostgreSQL: ALTER TABLE
• PostgreSQL: Modifying Tables
• PostgreSQL: CREATE INDEX