In the previous post I argued that migrations don’t earn Git’s protections by being made of files: two incompatible schema changes live in two separate migration files, so there is nothing to textually conflict, and both branches merge clean. The repository accepts the pair without comment.

That post ended with a claim: the collision is not resolved in the repo, so it must be resolved somewhere else — at apply time, per environment, inside the database. This post is that claim, run for real. What actually happens when the pair lands?

I ran the collisions on PostgreSQL 16 to find out. The answer comes in two flavors — one loud, one silent — and the silent one is worse.

None of what follows is an argument against migrations, or against the defensive idioms people write in them. Every tool below does exactly what it promises. The point is what those promises do and don’t cover once two branches meet.

The loud collision: order decides which environment breaks

Branch A adds a column to users. Branch B renames users to accounts. Semantically incompatible; textually invisible to Git; both merged.

Each environment applies the migrations in whatever order they arrive — and the order can differ between environments, because branch merge order and migration timestamp order are two different clocks. So I built two databases and applied the pair in both orders:

-- env_one: add column, then rename ALTER TABLE ✓ ALTER TABLE ✓  -- env_two: rename, then add column ALTER TABLE ✓ ERROR: relation "users" does not exist

env_one is fine. env_two is broken — and its migration run has now halted partway, with everything after the failed file left unapplied.

Note what this means operationally: the same pair of migrations produces a healthy database in one environment and a failed deploy in another, decided by application order alone. If your staging environment happened to apply them in the survivable order, staging is green and the failure is waiting in production. The check that was supposed to catch this — running migrations in staging first — passed honestly and told you nothing.

Still, this is the good outcome. The error is loud, it names a relation, someone gets paged, the incident gets fixed. Postgres refused to do something incoherent. The failure mode below refuses nothing.

The silent collision: IF NOT EXISTS and a shared name

Defensive migrations are common: CREATE INDEX IF NOT EXISTS, ADD COLUMN IF NOT EXISTS. They make migrations re-runnable, and they feel like safety.

Now let two branches each add an index on users(email) — same obvious name, different definitions. Branch A indexes the raw column; branch B, written with case-insensitive lookup in mind, indexes lower(email):

-- migration A CREATE INDEX IF NOT EXISTS idx_users_email ON users (email); -- migration B CREATE INDEX IF NOT EXISTS idx_users_email ON users (lower(email));

Apply both, in both orders, on two databases:

-- silent_one: A then B -- silent_two: B then A CREATE INDEX CREATE INDEX NOTICE: relation NOTICE: relation  "idx_users_email" "idx_users_email"  already exists, skipping already exists, skipping CREATE INDEX CREATE INDEX

Every statement reports success. Exit code zero, four times. The migration history table in both databases now records both migrations as applied.

Here is what the two databases actually contain:

silent_one: CREATE INDEX idx_users_email ON users USING btree (email) silent_two: CREATE INDEX idx_users_email ON users USING btree (lower(email))

Two environments, same applied-migration set, different schemas. No error, no failed deploy, no page. One environment will use the index for WHERE lower(email) = ... and the other will sequential-scan, which surfaces months later as “this query is slow only in production” — a symptom nobody will trace back to a merge.

IF NOT EXISTS checks the name. It knows nothing about the definition. Every idempotency guard of this shape trades a loud, immediate, fixable error for a silent, persistent divergence. That is drift, manufactured by the workflow that was supposed to prevent it — and nothing in the workflow will ever report it. Only comparing the actual schemas surfaces it.

What transactional DDL saves you from

The loud collision left env_two halted mid-migration-run. How bad that is depends on a Postgres property that people coming from other databases consistently underestimate: DDL is transactional.

A three-statement migration that fails on the last statement, wrapped in a transaction:

BEGIN ALTER TABLE ✓ (add plan_id) CREATE INDEX ✓ (index on plan_id) ERROR: relation "nonexistent_table" does not exist ROLLBACK

Inspect users afterward: no plan_id, no index. The database is exactly what it was before the migration started. The failed migration is a re-runnable no-op, not an archaeology project.

The same three statements without a transaction:

ALTER TABLE ✓ CREATE INDEX ✓ ERROR: relation "nonexistent_table" does not exist

Now users has plan_id and the index — a state that matches neither the pre-migration schema nor the post-migration schema. It corresponds to no version of your repository. To retry, someone first has to manually determine how far the migration got and hand-roll the difference.

On MySQL, the second scenario is the only scenario. MySQL 8.0 did introduce atomic DDL, and it is a real improvement — a single ALTER TABLE either happens or doesn’t, rather than leaving the data dictionary and the storage engine disagreeing after a crash. But atomicity stops at the statement boundary: DDL still performs an implicit commit, so a migration containing three statements cannot be one unit, no matter the version. Half-applied migrations aren’t a failure mode there; they’re the baseline. If you’ve mostly worked on Postgres, this is a bigger part of your migration tool’s apparent reliability than the tool itself — Flyway and most peers wrap each migration in a transaction where the database allows it, and on Postgres it does.

Two honest caveats. First, this safety has a hole: CREATE INDEX CONCURRENTLY — the thing you actually run on large production tables — cannot execute inside a transaction, so the migrations most likely to fail midway (long-running ones on big tables) are exactly the ones that opt out of the protection. Second, rollback restores schema, not time: a migration that succeeded and was later found wrong still needs a forward fix. Transactional DDL narrows the damage of failing migrations; it says nothing about wrong ones.

The cheap prevention: a database per branch

Both collisions above needed a shared database to matter. The loud one needed environments applying in different orders; the silent one needed two branches’ migrations meeting in one place before anyone watched them interact. A prevention that attacks the root: stop sharing. Give every branch its own database, built from the branch’s own migration history.

The standard objection is cost — who wants to rebuild a database per branch? On Postgres, the objection mostly dissolves:

CREATE DATABASE feature_x TEMPLATE dev_main;

TEMPLATE copies at the file level, not by replaying SQL. I timed it on a 64 MB database — 60 tables, indexes, 300k rows: 1.3 seconds. Branch off dev_main, apply only your branch’s new migrations, and your local database now corresponds to your branch — which, as the Git post argued, is a correspondence the default workflow never gives you. Switch branches, drop the copy, template a fresh one. The “my local database matches no branch at all” accumulation stops accumulating.

One practical catch: Postgres refuses to copy a template that anyone is connected to (source database "dev_main" is being accessed by other users). If dev_main is the database your editor, your app and your open psql session are all pointed at, the very first CREATE DATABASE will fail. The usual fix is to keep dev_main as a pristine base nobody works in directly — you template from it and work in the copy — rather than trying to evict connections each time.

(The copy is Postgres-specific in its cheapness. MySQL has no equivalent of template databases; you’re back to dump-and-restore, which is why this workflow is rare outside Postgres shops.)

This doesn’t detect cross-branch incompatibility — the two index migrations will still meet for the first time after merge. What it buys is isolation before the merge and a reproducible state after checkout, which removes the third, dumbest collision source: migrations from abandoned experiments still applied to a long-lived shared dev database.

What to take from this

  • A collision that merges clean in Git resolves at apply time, per environment, by order of application. Loud failures pick their victim environment by that order — a green staging run is evidence about staging’s order, not about yours.
  • IF NOT EXISTS converts loud collisions into silent schema divergence that no migration tool records or reports. The history table says the environments match; the catalogs disagree.
  • Transactional DDL makes failed migrations clean no-ops instead of half-states — a Postgres property, with a CONCURRENTLY-shaped hole exactly where failures are most likely.
  • Template databases make branch-isolated schemas cheap enough to be a habit rather than an aspiration.

The pattern across all four: the repository’s view of schema changes and the database’s are reconciled at apply time, separately, in every environment — and the outcomes range from crash to silence. The crashes announce themselves. For the silences, the only instrument is comparing what the databases actually are.