Zero-Downtime Postgres Migrations Need Two Deploys, Not Heroics

A practical expand-contract plan for changing schema while production traffic keeps writing.

This image was created using an AI image creation program

Your migration ran fine in staging.

In production, one ALTER TABLE waited on the wrong lock and every checkout request started stacking up.

After this, you will be able to split a risky Postgres schema change into phases that keep old code and new code working at the same time. That is the whole payoff. Not magic. Not bravery. A boring release shape that makes the database change survivable.

The mistake starts before SQL

Most migration reviews start too late. Someone opens a pull request with one tidy migration file, the tests pass, and the team talks about the SQL statement as if it is the whole event.

It is not.

A production schema change is a distributed deployment problem wearing a database costume. Old application pods may still be running. New pods may already be writing. Background workers may be delayed. A mobile client might send yesterday’s request shape. Lovely, right?

Proposed disagreeable opinion, only keep it if it is genuinely yours: rollback-first migration planning is often theater. A better plan is pause-first design: every phase should be safe to stop while both schemas still accept real traffic.

Draw the compatibility box

Before writing SQL, draw the versions that must coexist.

Old code must tolerate the expanded schema. New code must tolerate old rows. The backfill must tolerate retries. Reads must tolerate partial migration. Deletion must happen after burn-in, not during the same emotional sprint that created the new column.

This image was created using an AI image creation program

That box changes the review question. Instead of asking, “Does this migration run?”, ask, “Can I deploy, stop, sleep, and continue tomorrow without corrupting data?”

Tiny change. Big difference.

Here is the expand-contract shape:

1. Expand the schema without changing existing meaning.
2. Deploy code that can read and write both paths.
3. Backfill data in small, retryable batches.
4. Switch reads after checks pass.
5. Validate constraints and indexes with production-safe operations.
6. Remove old schema after enough quiet time.

Expand with boring SQL

The first database change should usually be the least dramatic one.

For a new field, that often means adding a nullable column, then setting a default for future inserts. PostgreSQL documents that changing a column default affects future INSERT commands, not existing rows. That distinction matters because a default is not a backfill.

ALTER TABLE orders
  ADD COLUMN fulfillment_state text;

ALTER TABLE orders
  ALTER COLUMN fulfillment_state SET DEFAULT 'pending';

The dry rule: if the migration needs a sentence beginning with “it should only take,” it deserves a smaller first step.

Dual-write before you trust reads

Once the schema can hold the new data, ship application code that writes both the old and new representations. Keep reads on the old path at first.

That feels wasteful. It is also the part that buys you a calm rollback.

await saveOrderStatus(orderId, status);
await saveFulfillmentState(orderId, mapStatus(status));

The real version should include idempotency and metrics. Count rows where old and new values disagree. Alert on a sudden mismatch. Log enough context to debug one bad row without dumping private data into your logs.

Do not switch reads just because dual-write code deployed. Switch reads when the database shows that writes are landing where you think they are landing.

Backfill like it can fail

Backfills are where tidy migration plans get noisy.

Rows are not evenly distributed. One customer can own a strange chunk of the table. A queue can fall behind. A replica can get hot. The job that looked harmless on staging’s toy data can turn into the loudest thing in the cluster.

GitLab’s database docs recommend batched background migrations for data changes on high-traffic tables when a normal migration would exceed timing limits. The general lesson travels well: move data in chunks, make the job retryable, and give operators a throttle.

A backfill should have three buttons: start, pause, resume. Delete is not a button. Delete is a later release.

Validate with the database, carefully

Postgres has useful tools for this phase, but they come with footnotes.

CREATE INDEX CONCURRENTLY can build an index without blocking inserts, updates, and deletes, but PostgreSQL also documents that it does more work, waits on existing transactions, and cannot run inside a transaction block. If your migration runner wraps every file in one transaction, split this step out.

CREATE INDEX CONCURRENTLY idx_orders_fulfillment_state
  ON orders (fulfillment_state);

ALTER TABLE orders
  ADD CONSTRAINT fulfillment_state_present
  CHECK (fulfillment_state IS NOT NULL) NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT fulfillment_state_present;

For constraints, NOT VALID lets Postgres enforce the rule for new or changed rows while delaying the scan of existing rows. VALIDATE CONSTRAINT does that later scan with a less restrictive lock than a naive add-and-scan approach.

Small print. Production-sized consequences.

Contract after the quiet week

The last phase is where impatience sneaks back in.

After reads switch to the new path, leave the old path in place for a burn-in window. Watch mismatches. Check slow queries. Confirm that workers, scheduled jobs, and admin scripts moved too.

Then contract:

ALTER TABLE orders DROP COLUMN status;

Dropping the old path should feel dull. If it feels exciting, you skipped a check.

The reusable takeaway is simple: a safe schema migration is not one operation. It is a short release plan where every step keeps production compatible with the step before it.

I write one hard-won software engineering lesson like this twice a week.