Migration numbers are a distributed counter without coordination

Your Migration Numbers Are a Distributed Counter Without Coordination • pgmi

Your Migration Numbers Are a Distributed Counter Without Coordination# What migration-number collisions reveal about identity, ordering, and enforcement. By Alexey Evlampiev Two engineers branch from the same commit on the same Tuesday. Each adds a migration; each picks the next free number — V42. Git merges both files without complaint: two new files, no textual conflict. The migration runner cannot merge them. Two changes now claim the same position. Timestamp versions make that exact collision unlikely, but trade it for a subtler problem: late arrival. A migration stamped earlier can merge after a later one has already run in some environment. Flyway ignores the straggler by default; its outOfOrder setting applies it — “a newly discovered version 2.0 will be executed rather than ignored” — at the cost of an application history that now differs from a freshly built environment. If you have run migrations on a team of more than two people you have lived some version of this, and lived the fixes: timestamp prefixes, outOfOrder, renumbering commits, “announce in Slack before you add a migration.” These are not filename problems. They come from three choices every ordered-change system makes: how changes acquire identity , how order is represented, and where the resulting invariants are enforced. Sequential numbers fuse identity and order into a single uncoordinated allocation — each branch reads the same prefix of history, independently allocates the next slot, and git later unions the allocations without checking uniqueness. pgmi takes a deliberately narrow approach to the third choice. It is not a migration framework : it keeps no applied-migrations ledger by default and does not decide what has already run. What it does is expose the computed source plan as an ordinary PostgreSQL relation: SELECT execution_order, sort_key, path FROM pg_temp.pgmi_plan_view ORDER BY execution_order; That is not diagnostic output rendered from an internal plan object. It is the plan. The same deploy.sql that queries those rows is the code that will execute them — so a project can inspect, reject, transform, or execute the plan before any source file runs, and, against a disposable database in CI, before the branch merges. One scope note. This is the authoring-time problem — two branches allocating the same position. It is distinct from runtime coordination of concurrent migrator processes, which tools handle with locks: Flyway and Liquibase serialize concurrent migrations, and pgmi holds a deploy advisory lock and exits with a dedicated code when another deploy is already running. This article is about the first problem. The problem is visible across ecosystems# Liquibase avoids bare sequence numbers as changeset identity. Its FAQ explains why : on separate branches, two people easily pick the same id, and the version-control system “won’t care that there are two different changesets with the same ‘id’.” The modern design identifies a changeset by the triple id + author + changelog path, and the docs are explicit that id is an identifier, not an ordering value. Redgate’s Flyway documents the same hazard “What if a colleague saves a file with the same version number? Flyway will rightfully protest because the order of execution is now ambiguous.” On a duplicate version Flyway aborts before applying either migration — safe, and still a merge-day fire drill. The guide’s own fixes — timestamp versions, placeholder files, outOfOrder, cherry-picking — it characterizes as workarounds that “compromise the value of control.” Further down the stack, golang-migrate users have requested a duplicate-version lint (issue #720 “it is common that they generate migrations with the same version number”), and Rails’ v3.2 guide records that pre-2.1 sequential numbers made it “easy for these to clash requiring you to rollback migrations and renumber them” — the origin of its 2008 switch to timestamps. Identity, ordering, enforcement# Every migration system answers three questions, and keeping them separate is what makes the landscape legible: Identity — what makes two changes the same change or different ones? Ordering — is order a total ordinal, a timestamp, a declared sequence, or a dependency graph? Enforcement — what invariant is checked, when, and by whom? The first two are properties of the data model. The third is a property of workflow: the same duplicate Flyway version can surface in a local validate, in pre-merge CI, or at deploy time, depending on how the team wires it. SystemChange identityOrder representationEnforcement surfaceFlywayUnique versioned scriptTotal version ordervalidate / migrateLiquibaseid + author + pathChangelog sequenceChangelog validation / updateAlembicRevision identifierDependency DAG, topologically sortedHead / merge...

Migration numbers are a distributed counter without coordination

Related Articles

(no title)

Scientists reverse brain aging, with a nasal spray

NHS apologises and admits Palantir have access to identifiable patient data

AI has torched the market for junior programmers

Al Vigier: Canada's AI strategy shouldn't include secret Palantir bills