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.

Branch collision versus trunk-based schema The top shows two feature branches both creating migration 142, colliding at merge. The bottom shows schema migrations committed to trunk in a single linear sequence with no collision. Collision vs Linear Sequence Per-branch DDL — collides branch A: 142 branch B: 142 pkey collision Trunk-based DDL — linear 142 143 144
Generating migration numbers per branch invites collisions; committing DDL to trunk keeps the sequence linear by construction.

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 transaction
  • Migration 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.

  1. 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';
  1. Revert the conflicting migration. Locate the offending script in the logs, then roll back: Flyway Teams/Enterprise flyway undo; Liquibase liquibase rollbackCount 1; or manual inverse DDL such as ALTER TABLE users DROP COLUMN IF EXISTS new_field; after confirming dependent indexes and constraints.
  2. 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';
  1. 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

  • SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1 matches the CI/CD expected version on all nodes
  • column does not exist / relation missing exceptions

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.

Related Articles