Schema changes on a system people depend on

An ALTER TABLE that executes in three milliseconds can take your API down for ninety seconds. The statement is not slow. It is waiting, and everything else is waiting behind it. That mechanism is the single most useful thing to understand about PostgreSQL migrations, and it is not the one most migration guides lead with.

This is the practical set of habits we use on Tumbuhku's PostgreSQL database, which holds growth measurements, milestones, and immunization records for real families and cannot be down while we tidy a column.

Expand and contract, in the order that matters

The pattern is well known and usually described too loosely to follow. The step order is the whole thing, and several of the steps are application deploys rather than migrations.

1. Add the new column, nullable, no constraint, no default that forces work. Migration.
2. Deploy code that writes both the old and the new column on every write path. Deploy.
3. Backfill existing rows in batches, as a job, not inside the migration.
4. Add the constraint once the data satisfies it.
5. Deploy code that reads the new column. Deploy.
6. Deploy code that stops writing the old column. Deploy.
7. Drop the old column. Migration.

Seven steps and four deploys to move one column. That is a genuine cost and it is why the honest advice includes a threshold: on a table with a few thousand rows and a maintenance window you are allowed to use, take the lock and do it in one statement. The pattern earns its complexity on tables that are large, hot, or both.

A real example makes the write-both step less abstract. Changing height_cm numeric to height_mm integer on the measurements table is not a rename. It is a unit change and a type change at once, so the backfill computes round(height_cm * 10) and the application has to know both units for the duration. If you skip step two, every row written between the backfill and the read switch is missing from the new column, and you will find them one at a time, in production, as null pointer complaints.

The NOT NULL default story, and what changed

Adding a column with a non-null default used to rewrite the entire table while holding an ACCESS EXCLUSIVE lock, which on a large table meant an outage measured in minutes. Since PostgreSQL 11 that is no longer true for a non-volatile default: the value is recorded in the catalog and applied to existing rows on read, so the operation is a catalog update and returns immediately.

The dangerous cases that remain are worth memorising. A volatile default such as a random UUID or a timestamp function still rewrites the table, because every row needs a distinct value. A type change that alters the on-disk representation still rewrites. And setting NOT NULL on an existing column still requires a full table scan under a strong lock.

That last one has a workaround worth using. Add a check constraint asserting the column is not null with NOT VALID, which takes a brief strong lock but performs no scan. Then run VALIDATE CONSTRAINT, which scans the table under SHARE UPDATE EXCLUSIVE and does not block reads or writes. Then SET NOT NULL, which PostgreSQL can now satisfy from the validated constraint without scanning again. Drop the check afterwards. Three statements instead of one, and no interruption.

CREATE INDEX CONCURRENTLY and the invalid index it leaves behind

Building an index concurrently avoids blocking writes, at the price of two table scans and a wait for in-flight transactions to finish. On any table people are actively using, it is the default choice.

The failure mode is specific and quiet. If the build fails, because of a deadlock, a statement timeout, a cancelled deploy, or a unique violation, PostgreSQL leaves behind an index marked invalid. That index is not used by the planner, so nothing gets faster. It is still maintained on every insert and update, so writes get slower. You are paying the cost of an index and receiving none of the benefit, and nothing in your application will tell you.

Detect it with a query against pg_index filtering on indisvalid being false, and run that as a post-deploy check rather than as something you remember to do. Recovery is DROP INDEX CONCURRENTLY followed by another attempt, ideally after fixing whatever caused the first failure.

Two operational notes. A concurrent build cannot run inside a transaction block, and most migration tools wrap each file in a transaction by default, so you need your tool's escape hatch. Check what yours does before assuming, because the behaviour differs between tools and versions. And put a concurrent index build alone in its own migration file, with nothing else in it, so a retry is idempotent.

Lock queues turn a fast statement into an outage

Here is the mechanism from the opening. Your ALTER TABLE needs ACCESS EXCLUSIVE, which conflicts with everything. A long-running report is holding ACCESS SHARE on the same table. The ALTER cannot proceed, so it waits. Lock requests are queued in order, so every query that arrives after the ALTER waits behind it, including the ordinary reads that were perfectly compatible with the report.

The result is that a ninety second analytics query plus a three millisecond schema change equals ninety seconds of unavailability for that table. Neither statement is at fault individually. On Tumbuhku the shape of this is a prevalence report scanning the measurements table, which is exactly the kind of query that runs long and nobody thinks about during a deploy.

The seatbelt is one line at the top of any migration taking a strong lock: set lock_timeout to a few seconds. The migration then fails fast instead of queueing, the deploy goes red, and you retry. A failed deploy is a minor inconvenience. A queued lock is an incident. Set it as a session parameter in the migration itself rather than relying on a server default, because the server default is zero, which means wait forever.

Pair that with a retry loop with backoff for the migrations you expect to contend, and a habit of not shipping schema changes during whatever window your heavy scheduled work runs. When something does block, pg_locks joined against pg_stat_activity tells you which process is holding what, which is the first query to run and the one people look up under pressure.

Backfilling without holding the database hostage

A single UPDATE touching every row is one long transaction. It holds row locks for the duration, generates write-ahead log volume faster than replicas can apply it, prevents autovacuum from cleaning the dead tuples it is producing, and cannot be interrupted without losing all of the work.

Batch by primary key range, never by OFFSET, because offset re-scans everything it skips and gets quadratically slower as it goes. Carry the last processed key forward, select the next few thousand rows above it, update, commit, and pause briefly between batches. The pause is not superstition: it gives autovacuum and any read replicas room to keep up.

Use replication lag as the throttle signal rather than a fixed sleep. If lag is growing, slow down. And run the backfill as a job outside the migration system, because a migration that takes forty minutes will eventually be killed by a deployment timeout at minute thirty-nine, and your migration tool will be left recording a state that never finished.

Reversibility is a plan, not an assumption

A down migration that drops a column is not a rollback. It is data loss with a reassuring name. If the forward migration destroyed information, no down migration can restore it, and writing one that pretends otherwise is worse than writing none, because it invites somebody to run it at two in the morning.

So every migration pull request carries a written line answering the question: if this turns out to be wrong tonight, what do we do? There are only a few acceptable answers. Revert the application deploy, which works precisely because expand and contract kept the old code functional. Run this specific down migration, which is only valid when it is genuinely the inverse. Or restore to a point in time, in which case the description names the amount of data loss that implies.

The check before merging

Four questions, and a migration does not merge until all four have an answer in the description.

• Does this take an ACCESS EXCLUSIVE lock? If so, it has a lock_timeout.
• Does it rewrite or scan the table? If so, it is split into expand, backfill, and contract.
• Does it work against the application code that is currently deployed?
• What is the rollback, in one sentence?

The third question is the one that gets missed, and it is the one that causes most self-inflicted incidents. A migration runs before the deploy completes, and during a rolling deploy both the old and the new version of your code are serving traffic at the same time. Dropping a column that the currently running process still selects produces errors for however long the rollout takes, and the pull request that did it looked correct in review because reviewers read the new code.

Write the migration so the code already running keeps working, and the rollback becomes a deploy revert instead of a database operation. That is the whole discipline. Everything else in this article is a technique for making that possible on a table you cannot lock.