Your migration tool does not know about locks

The migration that takes a production system down is almost never the big one. Adding a column with a default to a table of a hundred rows can stop every request for a minute, and rewriting a table of a hundred million rows can pass unnoticed, because the thing that decides is not the size of the change. It is the lock the statement takes and the queue that forms behind it while it waits. SocialSure's backend runs PostgreSQL through Prisma and Drizzle, and the lesson I keep relearning is that the migration tools are honest about what they do: they run the SQL you give them. What they do not do is know anything about locks.

The queue is the danger

Most schema changes in Postgres take an ACCESS EXCLUSIVE lock on the table they touch, the strongest lock there is, and the locking documentation lists which statements take which level. ACCESS EXCLUSIVE conflicts with everything, including the ACCESS SHARE lock that a plain SELECT holds. The statement cannot proceed until every existing reader has finished.

That alone would be fine, because most reads are short. The problem is the queue. Postgres grants locks in order, so once an ALTER TABLE is waiting for its exclusive lock, every new query that wants any conflicting lock, which is every query on that table, queues behind it. A single long-running report that started before the migration holds the ALTER off for as long as it runs, and for that whole time the table is effectively offline to everyone else, not because anything is being altered but because everyone is waiting behind something that is waiting.

What the tools do and do not set

Prisma Migrate takes an advisory lock so that two migration runs cannot overlap, and its documentation describes the workflow around that. Ten seconds of waiting for that advisory lock and the run fails. That lock is about migrations racing each other. It has nothing to do with the table locks the migration's SQL will take, and Prisma does not set a lock_timeout for those. Drizzle Kit runs the generated SQL the same way. In both cases the statement waits for its table lock as long as Postgres lets it, which by default is forever, and the queue grows the whole time.

The fix is one setting, and the tools let you write it because it is just SQL: set lock_timeout at the top of the migration, to a value shorter than the time you are willing to block the table. If the exclusive lock cannot be obtained within that window, the statement fails instead of waiting, the queue drains, and the migration retries later or under a maintenance window. The failure is the safe outcome. The wait was the dangerous one.

SET lock_timeout = '3s';
ALTER TABLE deals ADD COLUMN source text;

Which statements need the timeout is the part the tools cannot tell you, and it is where the size intuition goes wrong. Adding a nullable column with no default takes the exclusive lock for a moment and is cheap once obtained; its danger is entirely the queue. Adding a column with a volatile default rewrites the table and holds the lock for the whole rewrite. Creating an index the ordinary way takes a SHARE lock that blocks writes for the duration; creating it concurrently takes a weaker lock and runs alongside traffic. The right question about every migration is which lock, for how long, behind what.

Postgres has been making this cheaper for twenty years

Most of the expensive operations have a cheap form now, and the history of when each arrived is a useful map of what to reach for.

Each entry on that line replaces an operation that held a strong lock for a long time with one that holds a weak lock, or a strong lock briefly. Since version 11, adding a column with a constant default is a catalogue change rather than a rewrite. Since 9.1, a foreign key or check constraint can be added as NOT VALID, which takes the exclusive lock only for an instant, and validated later under a lock that allows reads and writes. Since 8.2, an index can be built without blocking writes. The tools generate the plain forms by default, because the plain forms are portable and simple, and the migration author has to know to write the cheap ones.

The lock receipt

The habit I use to make that knowledge visible is a five-line header committed with every migration file. It states the lock level taken, the tables affected, the worst-case hold time and what it depends on, the lock timeout set, and the retry plan if the timeout fires. A migration without a receipt does not merge. The header is not enforced by any tool. It is enforced by the reviewer, who can now see in five lines what the SQL below is going to do to the table at three in the afternoon.

-- lock: ACCESS EXCLUSIVE on deals (instant, catalogue only)
-- tables: deals
-- worst case: held for the duration of any SELECT already running on deals
-- lock_timeout: 3s
-- retry: rerun; safe to repeat, the column add is idempotent via IF NOT EXISTS

Writing the receipt is what forces the right questions. Filling in the worst-case line for an ADD COLUMN with a default on version 10 makes you look up whether it rewrites, and it did. Filling it in for a CREATE INDEX makes you write CONCURRENTLY. Filling in the retry line for a rewrite that cannot be repeated makes you split it into a backfill in batches and a final constraint. The receipt is small because the thinking it forces is where the value is.

The retry that is not a retry

One line of the receipt deserves more than a line, because it is where migrations go from risky to safe: the retry plan. A statement that fails on lock_timeout has done nothing, and rerunning it is fine. A statement that was halfway through a table rewrite when the connection dropped may have left the table in a state the tool does not recognise, and the tool's own record of which migrations have run may disagree with the schema. The receipt forces the author to say which kind of statement this is before it runs.

For the dangerous kind, the plan is almost always to split. A backfill runs in batches of a few thousand rows, each in its own short transaction, so that a failure loses one batch and a rerun resumes. The constraint that depends on the backfill is added NOT VALID at the start, so that new rows obey it while old rows catch up, and validated at the end under a lock that lets traffic through. The migration that would have held the table for ten minutes becomes a hundred migrations that each hold it for none, and the receipt for each one says "safe to repeat".

What the tools could do

It is worth saying that none of this is a complaint about Prisma or Drizzle. Generating portable SQL from a schema diff is the right job for a tool, and knowing your production traffic pattern is not something a tool can do. A lock timeout is a value only the team can choose, because it encodes how long they are willing to block a table, and that number is different for a reporting database and a ticketing gate. The tools could make the setting more visible, and some of the safer-migration libraries do exactly that, but the decision was always going to be the team's.

So the rule is the team's too. Every migration says what lock it takes, for how long, behind what, with a timeout that makes waiting fail instead of spreading. The size of the change never appears in that sentence, because it was never the thing that mattered.