Schema Migrations on a Running System

Two facts make live schema changes harder than they look.

For a separate people-operations perspective, the practical overview covers structured time records.

Old and new application code run at the same time during every rolling deploy. For a window measured in minutes, some instances expect the old schema and some expect the new one. Every migration must work for both.

Some DDL statements take locks that block everything on the table. A statement that runs instantly on an empty table can hold an exclusive lock for minutes on a large one, and queued queries behind it turn into a full outage.

Both are solved by the same discipline: never change and use in one step.

Expand and contract

The pattern, in four deploys. It feels laborious and it is the only reliable approach.

1. Expand. Add the new structure, without removing the old. Nullable column, new table, new index. Nothing reads it yet.

2. Migrate and dual-write. Deploy code that writes both old and new. Backfill existing rows in batches. Reads still come from the old.

3. Switch reads. Deploy code that reads from the new structure. Still writing both, so a rollback is safe.

4. Contract. Once you are confident, stop writing the old and drop it — in a separate deploy from step 3.

The gap between steps 3 and 4 is the safety margin. Dropping the old column in the same release that switches reads means a rollback breaks immediately, and rollbacks happen at the worst moment by definition.

Rename a column

The canonical example, because the naive version is guaranteed to break.

ALTER TABLE users RENAME COLUMN email TO email_address is atomic and fast — and old instances still running immediately fail on every query referencing email.

The safe sequence:

1. Add email_address, nullable
2. Deploy code writing to both columns
3. Backfill email_address from email, in batches
4. Deploy code reading email_address
5. Wait. Confirm nothing reads email — check query logs rather than trusting a grep
6. Stop writing email
7. Drop email

Seven steps for a rename. This is why renames get postponed, and why a column name that is merely awkward is often left alone.

The locks that surprise people

Behaviour differs by engine and by version, and the version matters — several of these operations became non-blocking in specific releases. Check your engine's documentation for the version you run rather than trusting general advice, including this.

Adding a nullable column is generally fast and metadata-only in modern engines.

Adding a column with a default used to rewrite the entire table. PostgreSQL made this metadata-only for non-volatile defaults in version 11; older versions rewrite. On a large table on an older version this is an outage.

Adding NOT NULL requires validating every row. PostgreSQL can avoid a long lock by adding a CHECK (col IS NOT NULL) NOT VALID constraint, validating it separately with a weaker lock, then converting.

Creating an index locks writes unless you use the concurrent variant — CREATE INDEX CONCURRENTLY in PostgreSQL, online index creation in others. The concurrent version is slower, uses more resources, cannot run inside a transaction, and can leave an invalid index behind if it fails, which then needs dropping manually. Check for invalid indexes after a failed run.

Changing a column type usually rewrites the table. The safe route is add-new-column, backfill, switch, drop.

Adding a foreign key validates existing rows. Add as NOT VALID, then VALIDATE CONSTRAINT separately.

Dropping a column is generally fast, and the space is not reclaimed until a vacuum or rebuild.

The universal precaution: set a lock timeout. A migration that cannot get its lock within a few seconds should fail rather than queue:

SET lock_timeout = '3s';
ALTER TABLE ...;

Without this, the DDL waits behind a long-running query, and every subsequent query queues behind the DDL. The blocking is not caused by the migration taking long — it is caused by the migration waiting. This single setting prevents the most common migration outage.

Backfilling large tables

UPDATE users SET email_address = email on a hundred million rows is a single transaction holding locks, generating enormous write-ahead log volume, and blocking vacuum.

Batch it:

UPDATE users SET email_address = email
WHERE id BETWEEN $start AND $start + 1000
  AND email_address IS NULL;

Points that matter:

Commit each batch. The transaction should cover one batch, not the loop.

Pause between batches. A short sleep lets replication catch up and lets other work through. Backfills that saturate replication cause replica lag, which causes stale reads, which causes bugs elsewhere.

Make it resumable. Track progress so an interrupted backfill continues rather than restarting.

Make it idempotent — the IS NULL condition above means re-running is harmless.

Watch replica lag while it runs, and stop if it grows.

Rate-limit adaptively if you can: slow down when lag rises rather than running at a fixed rate chosen in advance.

Migrations and deploys are separate

A common structure that causes trouble: the migration runs as part of application startup.

Problems: several instances start simultaneously and race; a failed migration prevents the application starting; and migration timing is coupled to deploy timing, which removes your ability to run the expand step well before the code that needs it.

Better: migrations run as a distinct step, with an advisory lock so only one runs at a time, and the application checks compatibility at startup rather than performing the change.

Both directions must be considered. Not every migration is reversible — a dropped column cannot be restored — which is exactly why the contract step comes late and separately. The recovery path for a bad contract is a backup, so know that before you run it.

Before running one

• [ ] Tested against a copy of production-scale data, not an empty schema
• [ ] Lock behaviour confirmed for your engine version
• [ ] lock_timeout set
• [ ] Backfill batched, resumable, idempotent, rate-limited
• [ ] Old and new code both work against the intermediate schema
• [ ] Rollback path known, and it does not require reversing a destructive step
• [ ] Replica lag monitored during execution
• [ ] Run during a low-traffic window if the change touches a large table

The summary

Expand, migrate, switch, contract — in separate deploys, with the destructive step last and late.

Set a lock timeout on every migration. The outage is caused by waiting, not by working.

Batch every backfill, watch replication, and make it resumable.

Test against production-scale data. Locking and planning behaviour both change with size, and a migration that is instant in staging can be an outage in production for reasons that have nothing to do with correctness.