Here is a migration that is completely correct and takes production down anyway.
ALTER TABLE users DROP COLUMN legacy_ref;
The column is unused. The code that read it was deleted in the same pull request. The tests pass. And for the thirty to ninety seconds between the migration running and the last old pod being replaced, the previous version of the application is still running, still has legacy_ref in its model, and every query it makes that mentions the column fails with column "legacy_ref" does not exist. If the ORM selects every column by name, which most do, that is every query on the table.
The migration was not wrong. The timing was. The schema moved before the code did, and there was a window where they disagreed.
The deploy window
Every rolling deploy has one. Migrations usually run first, as a job before the new version starts, and the old version keeps serving until the rollout completes. During that window the database is at version N+1 and some of the application is still at version N. Anything the old code needs that the new schema removed is a live error for as long as the window lasts, and rollbacks make it worse: rolling back the code without rolling back the schema puts the whole fleet on version N against a schema it does not understand.
Adding things is safe across the window, because old code ignores columns it does not know about. Removing or renaming things is not, because old code can not ignore a column it expects. That asymmetry is the whole pattern.
Expand, migrate, contract
The fix is to never remove anything in the same release that stops using it. Three phases, at least two releases.
- Expand. Add the new column, table or constraint. Old code ignores it, new code can start writing to it. If it replaces something, write to both for now.
- Migrate. Backfill the new thing from the old, in batches. Switch reads to the new thing once the backfill is complete. Ship this and let it soak: every pod is now on code that no longer needs the old column.
- Contract. In a later release, drop the old column. Nothing running reads it anymore, so the window is harmless.
A rename is the same problem wearing a different hat. RENAME COLUMN name TO full_name removes name from the point of view of old code, instantly. The safe version is add full_name, copy, switch, drop name later. It takes two releases instead of one statement, and it is the only version that does not depend on the deploy being fast.
The same change, both ways
-- wrong: one release, and old pods fail on every query that names the column
ALTER TABLE users RENAME COLUMN name TO full_name;
-- right, release 1 (expand): add the new column; nothing reads it yet
ALTER TABLE users ADD COLUMN full_name text;
-- release 1 (migrate): backfill in batches, then deploy code that writes both and reads full_name
UPDATE users SET full_name = name WHERE full_name IS NULL AND id BETWEEN 1 AND 50000;
-- release 2 (contract): only once no running version reads name
ALTER TABLE users DROP COLUMN name;
The drop in the first paragraph of this post follows the same shape: it is a contract step, and the question is only whether the migrate step, the deploy that stopped reading legacy_ref, has fully rolled out. If it has, the drop is safe. If you are not sure, it is not.
What "later" means
Later means after you are sure no version of the code that references the old column can be running or rolled back to. For most teams that is the next release, or the one after. The contract step is also the one that gets forgotten, wich is how tables grow a graveyard of columns named legacy_ something. i put it in the same ticket as the expand step, with a date on it.
What a checker can and cannot know here
BV030 fires on a DROP COLUMN and BV005 on a rename or drop, and both are warnings rather than criticals for an honest reason: the statement itself is fine. What makes it dangerous is whether code that still reads the column can be running when it executes, and that is a property of your deploy, not of the SQL. A deterministic rule can tell you "this is a contract step, make sure the migrate step already shipped". It can not tell you whether it did. That is the review question the rule is there to force, and it is a much better use of a reviewer's attention than checking syntax.
Where live-schema context helps is the other direction: with a read-only connection the checker can see that the column you are about to drop is still the target of an index, a view or a foreign key, which is the class of contract-step mistake that shows up as an error rather than a window.
Originally published at bolvrk.com. Bolvrk is a deterministic checker for database migrations and committed credentials: free CLI, GitHub App and Action.
