Zero-Downtime Database Migrations: Expand, Backfill, Contract

Every database migration horror story I've heard — including a couple of my own — comes down to one of two mistakes. Either a schema change took a lock that blocked production traffic, or a deploy assumed old code and new schema would never coexist. Both are completely avoidable, and neither requires exotic tooling. What they require is treating migrations as a sequence of small, individually-safe steps instead of one big atomic change.

This guide is Postgres-flavored because that's what I run, but the patterns apply to MySQL too — only the specific lock behaviors differ.

The two constraints that shape everything

Constraint 1: rolling deploys mean two code versions run at once. During any deploy, some replicas run the old code and some run the new. Both talk to the *same* database. So every schema state must be compatible with the code version before it *and* after it. The moment you internalize this, "rename a column" stops being one migration and becomes five steps.

Constraint 2: DDL takes locks. In Postgres, most ALTER TABLE operations take an ACCESS EXCLUSIVE lock — nothing else can read or write the table while it's held. That's fine if the operation is instant. It's an outage if the operation takes 40 seconds on a hot table, because every query behind it queues, connections pile up, and your app tips over.

Everything below is a technique for satisfying these two constraints.

Expand/contract: the master pattern

The idea: never make a breaking change in one step. First expand the schema so old and new code both work, then migrate the code and the data, then contract by removing the old shape.

Concrete example — renaming users.email to users.email_address. The naive version:

-- DO NOT do this in production
ALTER TABLE users RENAME COLUMN email TO email_address;

The rename itself is fast, but the instant it commits, every replica still running old code starts throwing column "email" does not exist. The expand/contract version:

1. 1Expand: add the new column.

`sql

ALTER TABLE users ADD COLUMN email_address text;

`

1. 1Dual-write: deploy code that writes to *both* columns but still reads from the old one. (An ORM hook or a database trigger both work.)
2. 2Backfill: copy existing data in batches (details below).
3. 3Switch reads: deploy code that reads from the new column. Old column is now write-only legacy.
4. 4Contract: once you're confident — days later, not minutes — stop the dual writes and drop the old column.

`sql

ALTER TABLE users DROP COLUMN email;

`

Five steps instead of one, and every step is independently deployable and independently reversible. That last property is the real prize: at no point are you one bad deploy away from an incident you can't roll back from.

The same decomposition applies to changing a column's type, moving a column to another table, or splitting a table. Expand, dual-write, backfill, switch, contract.

Know your locks: what's fast and what isn't

Some Postgres operations are metadata-only and effectively instant regardless of table size. Others rewrite the table or scan it while holding a lock. The dividing line moved over the years, so it's worth knowing where it is now:

Safe (fast, even on big tables):

• ADD COLUMN without a default, or with a constant default on Postgres 11+ — this became metadata-only in PG 11 and is one of the best quality-of-life changes Postgres ever shipped.
• DROP COLUMN (metadata-only; space reclaimed later by vacuum).
• CREATE INDEX CONCURRENTLY (see below).

Dangerous (long lock or full rewrite):

• ALTER COLUMN TYPE in most cases — full table rewrite under ACCESS EXCLUSIVE.
• ADD COLUMN ... DEFAULT (e.g. now(), gen_random_uuid()) — still a rewrite.
• Plain CREATE INDEX — blocks writes for the whole build.
• ADD CONSTRAINT ... NOT NULL / CHECK / FOREIGN KEY in their naive forms — full table scan under lock.

Indexes: always CONCURRENTLY

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

CONCURRENTLY builds the index without blocking writes. Two catches: it can't run inside a transaction (most migration frameworks need a flag to opt out of their transaction wrapper), and if it fails it leaves an INVALID index behind that you must drop before retrying.

Constraints: validate separately

Adding a constraint normally scans the whole table under lock. Split it:

ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;

-- later, takes only a light lock while it scans:
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

NOT VALID enforces the constraint for new writes immediately; VALIDATE checks existing rows without blocking traffic.

Set a lock timeout, always

Even an "instant" DDL statement has a failure mode: it has to *acquire* the lock first, and while it waits, it blocks every query that arrives after it. One long-running SELECT ahead of your migration turns a 5ms ALTER TABLE into a site-wide queue. Cap the damage:

SET lock_timeout = '3s';
ALTER TABLE users ADD COLUMN email_address text;

If it can't get the lock in 3 seconds, it fails — and a failed migration you retry at a quieter moment is infinitely better than a stalled production database. Put this at the top of every migration and make retries part of the process.

Backfills: batch, sleep, repeat

Never backfill with one giant UPDATE. A single statement that touches 50 million rows holds locks for the duration, bloats the table with dead tuples, and can stall replication. Batch it:

UPDATE users
SET email_address = email
WHERE id IN (
  SELECT id FROM users
  WHERE email_address IS NULL
  LIMIT 5000
);

Run that in a loop — from a script or a scheduled job, not a migration file — with a short sleep between batches, until it affects zero rows. Rules of thumb that have served me well:

• Keep batches small enough that each UPDATE finishes in well under a second.
• Sleep between batches so autovacuum and replication keep up.
• Make the backfill idempotent (the WHERE email_address IS NULL predicate does that here) so it can crash and resume.
• Backfills are jobs, not migrations. Migration files should be fast and deterministic; a multi-hour data copy belongs in a supervised script.

Where migrations run in the deploy pipeline

Ordering matters as much as SQL. The rules I hold to:

1. 1Migrations run before the new code takes traffic, as a discrete release step — never inside app startup, where N replicas race each other on the migration table.
2. 2Forward-only. Down-migrations lie; they're rarely tested and often impossible (you can't un-drop data). Rolling back means deploying a new forward migration.
3. 3Schema changes and code changes ship in separate deploys whenever the change is breaking. The expand/contract steps above force this naturally.
4. 4Take a backup before the contract phase. Expand steps are cheap to revert; DROP COLUMN is not. This is where managed-database backup retention earns its keep — on PandaStack, daily backups are kept 7 to 30 days depending on plan, which comfortably covers the "wait a few days before contracting" window.

On the platform side this workflow needs nothing exotic: the app gets its DATABASE_URL injected, migrations run as a release step or one-off command with the same env, and you watch the output in the live logs.

The checklist

Before any production migration, I ask:

• Can old code and new code both live with this schema? If not, decompose it.
• Does any statement rewrite or scan a large table under lock? Split with NOT VALID/VALIDATE, CONCURRENTLY, or expand/contract.
• Is lock_timeout set?
• Is the backfill batched, idempotent, and outside the migration file?
• Is there a recent backup, and is the contract step scheduled for *after* the change has soaked?

None of this is clever. It's the same five moves applied over and over — and the payoff is that "database migration" stops being a scheduled-maintenance event and becomes just another deploy. If you want a managed Postgres with the backups and wiring handled while you practice the pattern, there's one at [pandastack.io](https://pandastack.io).