The migration that never ran

A schema migrator that crashes is a good afternoon. You get a stack trace, a line number, and a database that is still in a state you understand.

The bad afternoon is the one where the migrator prints that the schema is up to date, exits zero, and is wrong. We have had that afternoon more than once, from more than one cause, and the worst of them ended with production code reading a column that did not exist.

This is what we learned, written for anyone running Postgres migrations from a file directory, whichever tool generated the files.

The decision is made on one number

Most file based migrators keep a table of what has been applied. Ours does too: a schema, a table, a row per migration with a hash and a creation timestamp.

The mistake is assuming that table is a set the migrator consults. It is not. Read the code and you find a query that selects exactly one row, the newest by timestamp, and then a single comparison per migration file: run it if the last applied timestamp is lower than this file's timestamp.

Three things follow immediately, and none of them are in anybody's mental model.

The hash plays no part in the decision. It is written when a migration runs and never read again to decide anything. A hash in the table that no longer matches any file on disk usually means that file was edited after it was applied, which is untidy but harmless. It is not the tool noticing anything.

Only the newest row matters. Not the set of applied files, not the count. One row.

Ordering is by timestamp, not by filename. The numbers at the front of your filenames are for humans. If your tool records an ordering value in a manifest, that value is the truth and the number is decoration.

Put those together and you get the property that costs afternoons: a migration whose timestamp is not greater than the newest applied timestamp will never run, on that database, ever. Not on this deploy, not on the next one, not after ten retries. There is no error, because from the tool's point of view nothing is wrong.

How you actually get one

It sounds like a thing that could not happen to a careful team. Here is how it happened to us, twice, in ways that had nothing to do with carelessness.

Two branches, the same number. Two people each add a migration in parallel. Both tools hand out the next free number, both get the same one, both write a timestamp. Whichever branch merges second gets renamed to the next number, which is the obvious fix and not the whole fix: if its timestamp is still lower than the one already applied, the rename changed the filename and not the decision. The migration is skipped. The symptom, in our case, was an enum type that was simply missing a value, on a database where every other object was present and correct.

Hand written timestamps that were not in order. Ours are round numbers set by hand rather than a real clock, which makes them readable and makes them easy to get wrong. One migration got a stamp earlier than two that were already applied. The migrator reported success. In production that would have been a type error on every single integration row, which is not the sort of thing you want to find from a customer.

Both cases share a shape worth naming. The bookkeeping is keyed on an ordering value, not on identity. A ledger keyed on identity ("have I applied this migration?") cannot skip anything, whatever order things arrive in. A ledger keyed on ordering ("what is the furthest I have got?") can, and it is a design most migrators share because it makes the common path a single row read.

The counter is not the check

After the first incident we added the obvious guard: after migrating, look at the number of rows in the migrations table and confirm it went up.

That guard is necessary and it is not sufficient, which we found out by watching it pass while the thing we needed was missing. A migration run took the row count from ninety eight to one hundred and four. Six migrations went in. The one we cared about was not among them. The count went up, everything reported success, and every logged in page in the product returned a 500 on a column that did not exist.

Nothing about a count tells you which migrations ran. If you want to know whether an object exists, the only honest check is to ask the database whether that object exists: list the tables, describe the table, select the enum values. We compare the table list of the environment we just migrated against the one we trust.

There is a variant of this failure that has nothing to do with timestamps and produces the same green output. Our migrator ships inside one of our service images. Somebody rebuilt only the other service, ran the migration from the stale image, and got a cheerful "schema is up to date" from a container that had never heard of the two migrations in question. The tool was not lying. It was answering a question about the files it had, and it had old files.

Everything or nothing, in one transaction

One detail in our migrator's implementation is worth knowing because it changes how you recover: every pending migration runs inside a single transaction, with the bookkeeping insert for each one inside it.

That is good news and a constraint. The good news is that a failure halfway through a batch of seven leaves you with none of the seven applied, rather than three and a half. There is no partially migrated state to reason about, which is exactly what you want at the point when you are least able to reason.

The constraint is that one statement Postgres cannot run inside a transaction block will take the whole batch down with it, and that a very long migration holds locks for the whole batch rather than for its own duration. Neither has bitten us yet. Both are the kind of thing you want to have read before it does.

Repairing one, without making the next one worse

Say you find it: a migration in your directory that this database never ran. The instinct is to fix the manifest, lower or raise the timestamp until the tool picks it up, and re-run.

Do not lower it, and be careful raising it.

The reason is the same rule that caused the problem. If you lower a timestamp on a migration that has already been applied elsewhere, you have not made anything run; you have made a file whose stamp is now below the high water mark on the databases where it did run, and the next migration you write with a stamp between the two will be skipped there. You fixed one environment and armed the next failure in another.

What works is to apply the SQL yourself, in a single transaction, and then make sure the bookkeeping reflects reality. The migrator cannot get it done for you, because from its side there is nothing to do.

What we do now

Five rules, all of them cheap, all of them written down because we did not follow them once each.

1. 01Deploy and migrate are one operation, not two. The gap between them is a window in which new code reads an old schema. Ours cost us an admin page for every administrator and a billing job that failed quietly all afternoon on columns that were not there yet.
2. 02Build everything before migrating, not just the service you changed. The migrator travels in an image, and an image you did not rebuild contains the migrations you did not add.
3. 03Check the ordering values, not the filenames, whenever two people have been working in parallel. Renaming a file does not change what the tool compares.
4. 04Verify the object, not the counter. After a migration that adds a column, ask for the column. This takes one command and is the only check that cannot pass vacuously.
5. 05Never lower an ordering value that has already been applied somewhere. It looks like a local fix and it is a remote landmine.

The uncomfortable thread through all of this is that the tool behaved correctly every time. It never crashed, never corrupted anything, never lied about what it did. It answered a narrower question than the one we thought we were asking, and printed a sentence that was true about that narrower question.

That is the class of failure worth building habits against, in migrations and everywhere else. A tool that fails loudly teaches you what it does. A tool that succeeds narrowly teaches you nothing until the day it matters.