Why not just take a five-minute maintenance window?

Sometimes that is the right answer, and for a small table it usually is. The problem is that five minutes is an estimate made against a table as it is today, and the migration that caused the incident is nearly always one that behaved fine in staging and took forty minutes in production because the table had grown. Expand and contract removes the estimate from the critical path.

What actually causes the lock that takes a site down?

An operation that has to rewrite the table or validate every row while holding a lock that blocks reads or writes. Adding a column with a volatile default, changing a column type, and adding a constraint that is validated immediately are the common ones. The same logical change expressed differently, often by adding the constraint as not valid and validating it separately, takes a far weaker lock.

How big should a backfill batch be?

Small enough that each batch commits quickly and leaves room for production traffic, which usually means thousands of rows rather than millions, with a pause between batches. The batch size that matters is the one that keeps replication lag flat; if lag starts climbing, the batch is too big regardless of how fast the primary is handling it.

When is it safe to drop the old column?

When no deployed version of the application references it and you can prove that rather than believe it. That usually means a release has been live long enough that rollback to a version using the old column is no longer plausible, and a check of query logs shows nothing touching it.

Schema migrations that do not take the site down

Schema migrations cause more avoidable outages than almost anything else in a deployment pipeline, and they do it in a characteristic way: the change is tested, it is small, it passes in staging in under a second, and in production it holds a lock on a table with forty million rows while every request behind it queues.

The pattern that removes this is old and well documented. What is usually missing is the discipline to apply it to changes that look too small to need it.

Expand, migrate, contract

The idea is that the schema and the code change in separate deployments, and that between them both the old and the new shape work.

One column rename, as four deployments

Reversible: at every step, without data loss
Lock held: briefly, and never while rewriting
Gate on: replication lag, not primary throughput

This diagram as text

1 · Expand — add the new column: nullable, no default — brief lock, no rewrite
2 · Write both — code writes old and new, reads old
3 · Backfill — batches, with a pause, watching replication lag
4 · Contract — read the new column, then drop the old one

Relationships

1 · Expand → 2 · Write both — ships alone
2 · Write both → 3 · Backfill — ships alone
3 · Backfill → 4 · Contract — ships alone

Open full size

Expand. Add the new column as nullable with no default. On a modern PostgreSQL this is a metadata change and takes a brief lock. Do not add a constraint yet and do not add a default that has to be written to every row.

Write both. Deploy code that writes to both columns and still reads the old one. The application is now compatible with both shapes, which is what makes everything after this reversible.

Backfill. Copy the data in batches, each committing quickly, with a pause between them. Watch replication lag rather than primary throughput: the primary will usually keep up long after the replicas have fallen behind, and a replica that is minutes behind is a failover you cannot take.

Switch the read. Deploy code that reads the new column. Still writing both. This is the step you roll back if anything is wrong, and rolling it back costs nothing because both columns are current.

Contract. Stop writing the old column, then drop it, once no deployed version references it.

Five deployments for one rename. It feels absurd until the first time a migration holds a lock for forty minutes.

The locks worth knowing

The specifics vary by engine and version, and the shape of the problem does not. These are the operations that routinely surprise people:

Change	Why it hurts	Safer form
Add column with a volatile default	Rewrites every row under a strong lock	Add nullable, backfill in batches, then set the default
Change a column type	Full rewrite, lock held throughout	New column, dual write, backfill, swap
Add a foreign key or check constraint	Validates every existing row while holding a lock	Add as not valid, then validate separately
Add an index	Blocks writes for the duration	Build concurrently
Rename a column	Instant in the database, breaks every deployed version at once	Expand and contract

The last row is the one that catches careful teams. The database operation really is instant. The outage comes from the application, because the previous version is still running on half the instances and it is now querying a column that no longer exists.

Make it rehearsable

The reason migrations are dangerous is that they are tested against data that does not resemble production. A table with ten thousand rows in staging tells you the syntax is right and nothing about the lock.

Two things fix this, and they are the same two things that make ephemeral environments worth building:

Production-shaped data in a lower environment, obfuscated inside the production boundary before it is exported. Same row counts, same distributions, same index bloat.
The migration runs in the pipeline, against that data, on every pull request. A migration that takes four minutes in the rehearsal is a conversation before the release rather than an incident during it.

Things that are not optional

A timeout on the migration itself. Set a lock timeout and a statement timeout so a migration that cannot acquire its lock fails fast instead of queueing every request behind it. A failed migration is a deployment you retry. A queued one is an outage.

A plan for the backfill that is already running. Backfills get interrupted. The job has to be restartable from where it stopped, which means tracking progress somewhere durable rather than in a loop variable.

Knowing which version reads what. The contract step is only safe when no running instance references the old column. If you cannot answer that from deployment records, you are guessing.

How STP approaches this

We treat a migration as a sequence of independently reversible deployments rather than as one change, run it in the pipeline against obfuscated production-shaped data so the lock behaviour is known before the release, and gate the backfill on replication lag rather than on how fast the primary is coping. Where an estate has taken an outage for a migration, the rehearsal environment is usually the missing piece rather than the technique.

More on DevOps and platform automation and secure SDLC, or start a conversation.