The migration that failed at step forty
You start a database migration on a Friday afternoon. It runs for a while, then dies on the fortieth of eighty statements because of a lock timeout. Now the schema is half changed. Rerunning from the top fails because the first thirty nine statements already ran. Rerunning from step forty means reading the logs, guessing the state and typing by hand. The weekend is gone.
The fix is not better luck. It is writing migrations that can safely run twice.
What idempotent means for schema changes
An idempotent migration reaches the same end state whether it runs once, twice or after a partial failure. It checks what already exists before it acts, and does nothing when the work is already done.
This is a different mindset from the usual one, where each migration is assumed to run exactly once, in order, against a known state. That assumption holds in a tidy development environment. It fails in production, where a runner can be interrupted, a connection can drop, a deployment can be retried by an impatient colleague, or two processes can start at once.
Make each statement safe to repeat
Most databases give you the tools. In PostgreSQL, for instance, many statements accept guards:
CREATE TABLE IF NOT EXISTS invoices (
id bigint PRIMARY KEY,
total numeric NOT NULL
);
ALTER TABLE invoices
ADD COLUMN IF NOT EXISTS currency text;
CREATE INDEX IF NOT EXISTS idx_invoices_currency
ON invoices (currency);Running that block twice does no harm. Dropping things follows the same pattern with IF EXISTS.
Not every change has a built in guard. Adding a constraint, changing a column type or creating a trigger may need a check against the catalogue first, wrapped in a small procedural block that tests whether the change is already applied. It is more typing, but the typing is cheap compared with a recovery.
Use transactions, within limits
Where the database supports transactional schema changes, wrap each migration in one transaction. If anything fails, everything rolls back and the schema returns to its prior state. PostgreSQL does this well for most statements. Some operations cannot run inside a transaction, such as creating an index concurrently, and these need separate handling.
Do not assume every database behaves the same way. Some commit schema changes implicitly, which means a failure midway leaves you partly migrated, and idempotency matters even more. Know what yours does before you rely on it.
Track what has run
A migration runner keeps a table recording which migrations have been applied. That table is the source of truth, and it should be updated in the same transaction as the change wherever possible, so the record and the reality cannot drift.
Add a lock so that two runners cannot work at once. An advisory lock held for the duration of the run is a simple way to stop a second process from colliding with the first. If you run migrations across many separate schemas, as multi tenant systems sometimes do, track the version per schema, so a failure on one does not hide the state of the others.
Separate the change from the data
Schema changes and data changes behave differently, and mixing them makes failure worse. Adding a column is quick. Back filling ten million rows is not.
Do the structural change first, as a small, fast and idempotent step. Then run the back fill as its own job, in batches, with a way to resume from where it stopped. A common safe pattern is to add the new column as nullable, back fill in the background, and only then add the constraint that requires it. Each stage can fail and be retried without damaging the others.
Roll forward, not back
Teams spend a lot of effort on down migrations. In practice, many are never tested, and a rollback that deletes a column also deletes whatever was written to it since. For many production situations, the safer approach is to prefer rolling forward: write a new migration that fixes the problem.
That works best when changes are made in compatible steps. Add the new thing, deploy code that uses both, move traffic, and only later remove the old thing. At every point, either version of the application can run against the schema. This is sometimes called expand and contract, and it also happens to make each individual migration smaller and easier to repeat.
Test the second run
The simplest test for idempotency is the most telling: run every migration twice against a fresh database and confirm the second run changes nothing. Add it to your continuous integration. Better still, test an interrupted run by killing the runner partway and starting it again.
Also rehearse against a copy of production data when you can. Problems that never appear on a small test database, such as lock waits and slow back fills, appear immediately on a large one.
A habit worth keeping
Before merging a migration, ask one question: if this stopped halfway and someone ran it again, what would happen? If the answer is "I am not sure", the migration is not finished. The goal is not to make failure impossible. It is to make recovery boring, so that the Friday afternoon failure ends with a rerun and not a weekend.