Git Branching Strategies for Schema Version Control
Two feature branches each add a migration numbered 0142, both merge the same afternoon, and the deploy dies on duplicate key value violates unique constraint "schema_migrations_pkey". Git is built for parallel, divergent history; a relational database demands one strict, linear sequence of schema states. When those two models collide you get version-number duplication, out-of-order DDL, and columns dropped before the application code that still reads them has rolled out. This page shows how to align a Git workflow with linear schema evolution so the collision cannot happen, and is the practical layer over the schema version control basics section of the migration fundamentals guide.
Symptom / Error Signatures
The failure shows up at merge time or during a rolling deploy:
ERROR: duplicate key value violates unique constraint "schema_migrations_pkey"ERROR: column "new_field" of relation "users" does not exist(during a rolling deploy)Lock wait timeout exceeded; try restarting transactionMigration state mismatch: expected version 142, found version 139
These surface in CI/CD pipelines that target a shared staging or production database when multiple branches merge simultaneously or deploy out of order.
Root Cause Analysis
A relational database advances through one linear sequence of schema states; Git history does not. When feature branches each generate migration files independently, they pick overlapping or non-sequential version identifiers. At merge or deploy the database then sees duplicate version inserts, out-of-order DDL, or a backward-incompatible column drop reaching production before the application code adapts. The damage is worse on engines with non-transactional DDL — MySQL forces an implicit commit on ALTER, so a failed step leaves the schema half-applied and the version table out of sync, which is the mechanism covered in handling non-transactional DDL in MySQL migrations. Establishing deterministic tracking via schema version control basics is the precondition for any branching policy to hold.
Immediate Mitigation
Run from a production DBA terminal or a privileged CI runner, sequentially, with all deploy jobs halted.
- Clear active DDL locks to regain schema control:
-- PostgreSQL · run as a privileged role · review pids before terminating
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' AND query ILIKE '%ALTER%';-- MySQL · run as a privileged user · this only generates KILL statements; execute them individually after review
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE info LIKE '%ALTER%' AND command != 'Sleep';- Revert the conflicting migration. Locate the offending script in the logs, then roll back: Flyway Teams/Enterprise
flyway undo; Liquibaseliquibase rollbackCount 1; or manual inverse DDL such asALTER TABLE users DROP COLUMN IF EXISTS new_field;after confirming dependent indexes and constraints. - Reset the version ledger to the last consistent state:
-- PostgreSQL/MySQL · run as migration role · adjust table name per framework (flyway_schema_history, DATABASECHANGELOG)
DELETE FROM schema_migrations WHERE version > '141';- Re-run a single ordered batch after verifying dependency resolution and lock clearance.
Permanent Fix / Long-Term Pattern
Remove the collision at its source by keeping DDL out of divergent branches. Adopt trunk-based schema development: commit schema changes directly to main/trunk so the migration sequence is linear by construction, and let application code reference new structures behind a flag until it is ready — the toggle mechanics are covered in using feature flags to toggle schema changes safely. Sequence every change through expand/contract: add nullable columns, tables, or indexes first; deploy code that reads and writes both shapes; then drop legacy objects in a later release. Enforce it mechanically with a pre-merge gate that rejects duplicate version numbers and non-additive DDL on production-targeted branches, and require IF NOT EXISTS guards plus online-build flags (CREATE INDEX CONCURRENTLY on PostgreSQL, ALGORITHM=INPLACE, LOCK=NONE on MySQL) through idempotent script design. When two branches genuinely produce conflicting versions, resolve them with the procedure in resolving migration version conflicts during merges.
Verification Checklist
The Conflict Git Cannot See
Branching migrations safely means guarding against a conflict that never appears as a Git merge conflict. When two engineers each add a new migration file on separate branches, Git sees two independent additions and merges them cleanly — no conflict markers, no warning. The collision is not in the text; it is in the apply order. If your runner orders migrations by a timestamp baked into the filename, the branch that was authored first but merged second carries a lower version than a migration already applied in production, and the runner is now asked to apply a pending change that sorts behind the current schema. Some runners refuse this out-of-order state and block every subsequent deploy; others apply it against a schema that has already moved past the point the migration assumed, which is how you get an “already exists” or “does not exist” error from a migration that passed every test in isolation.
The branching strategies that avoid this all attack ordering rather than text. Rebase feature branches so migrations are re-stamped in merge order before they land, or assign the sequence number at merge time rather than authoring time, so the applied order always equals the integration order. Keep each migration a single self-contained change, so that even if two independent migrations reorder relative to each other the result is identical. Add a CI gate that fails when a branch introduces a migration whose version is lower than the maximum already present on the target branch, catching the out-of-order case at review instead of at deploy. And treat applied history as immutable: never edit a migration that has run anywhere, because a good runner’s checksum will reject the changed file and halt the pipeline — append a new migration instead. The through-line is that schema version control fails in the seams between branches, so a branching strategy is really a strategy for keeping application order deterministic and history append-only.
Frequently Asked Questions
Why use timestamp-based migration versions instead of sequential integers? Sequential integers collide the instant two branches both grab the next number; timestamp (or UUID) versions almost never collide because they are generated from wall-clock time, not a shared counter. They do not impose ordering by themselves, so still pair them with a linearity check at merge — but they remove the most common duplicate-key failure outright.
Can I keep schema changes inside feature branches at all? You can, but then you must serialize their merges — a merge queue that applies and tests migrations one at a time, rejecting any that no longer apply linearly. Trunk-based DDL is simpler because it avoids divergence entirely; reserve per-branch DDL for teams that genuinely need long-lived schema experiments.
What do I do when two already-merged migrations conflict on production? Halt deploys, reset the version ledger to the last consistent version, and re-run a single ordered batch after reconciling the files into one linear sequence. The full step-by-step for merge-time conflicts lives in the dedicated guide on resolving migration version conflicts during merges.