Keeping Schema Migrations in Sync Across Microservices

Two services deploy in the same release train, both carry migrations, and one of them fails at startup with relation "billing_accounts" does not exist — even though the table is right there in the schema history of the other service. Or worse: both services own migration files that touch the sharded orders database, their pipelines run in parallel, and the deploy that lands second overwrites a column the first one just added, leaving flyway_schema_history describing a schema no single service actually produced. When several services share or depend on one database, migration correctness stops being a property of any one repository and becomes a property of the order they run in and who is allowed to run them. This page is the runbook for diagnosing cross-service ordering failures, mitigating them mid-incident, and replacing the race with an ownership contract. It assumes you already work within the broader environment parity strategies this section covers and the migration fundamentals that define a correct, backward-compatible change.

Symptom / Error Signatures

The tell-tale signature is a migration or an application that references a database object owned or created by a different service, at a moment when that object does not yet exist. It surfaces at deploy time, at startup, or as a lock fight on the shared history table:

• PostgreSQL ERROR: relation "billing_accounts" does not exist at startup, when the table is created by a sibling service’s migration that has not run yet
• MySQL ERROR 1146 (42S02): Table 'app.billing_accounts' doesn't exist during a foreign-key add that references another service’s table
• PostgreSQL ERROR: constraint "fk_orders_account" for relation "orders" already exists when two services both try to own the same cross-table constraint
• FlywayValidateException: Validate failed: Detected resolved migration not applied to database: 20260724_02 — one service’s history table is missing a version another service expects
• Liquibase Validation Failed: change sets check sum ... but the change set has already been applied when two repositories share a changelog table but not the changelog file
• Prolonged ACCESS EXCLUSIVE (Postgres) or metadata lock waits on flyway_schema_history / databasechangelog while two pipelines migrate the same database concurrently

Confirm which service is ahead and which is behind with read-only queries before touching anything:

-- PostgreSQL · read-only · safe against production with a SELECT-only role
-- Show every service's recorded migration state in the SHARED database.
-- Each service should write to its own history table; a single shared
-- flyway_schema_history is itself a warning sign.
SELECT 'orders_svc'  AS service, version, description, installed_on, success
FROM   orders_svc.flyway_schema_history
UNION ALL
SELECT 'billing_svc' AS service, version, description, installed_on, success
FROM   billing_svc.flyway_schema_history
ORDER  BY installed_on DESC
LIMIT  20;

-- PostgreSQL · read-only · run during the incident to catch the concurrent-migration race
-- Any two ALTER/CREATE statements waiting on the same relation = a cross-service collision.
SELECT pid, usename, state, wait_event_type, LEFT(query, 80) AS query
FROM   pg_stat_activity
WHERE  wait_event_type = 'Lock'
  AND  query ~* '(ALTER|CREATE|DROP)';

Root Cause Analysis

A migration tool computes the next action by diffing its recorded history against its migration files, and it reasons about exactly one lineage of versions. That model breaks the instant a second service can write to the same physical schema, because now the true schema state is the interleaving of two independent version lines that neither tool can see. Two distinct failure classes follow. The first is an ordering violation: service B’s migration (or its application code) references an object that service A owns, but B’s pipeline reached the database first. Nothing in B’s history table is wrong — the object simply is not there yet, because the cross-service dependency edge lives outside any single tool’s dependency graph. The second is an ownership collision: two services both believe they own the same table, index, or constraint, so their migrations race, and whichever lands second either duplicates the object or overwrites the first service’s change — the same drift that breaks the additive, backward-compatible contract described in idempotent script design.

The correct mental model is that a shared database has exactly one physical schema but potentially many writers, so someone must own each object and something must sequence changes that cross an ownership boundary. The way the two dominant tools behave when pointed at a shared schema diverges enough to matter:

Neither tool sequences across repositories on its own. Ordering has to be imposed above them — by the pipeline, by an explicit precondition guard, or by removing the dependency edge entirely — which is what the mitigation and the permanent fix below do. For the deeper commit-model reasons a half-applied cross-service ALTER cannot self-heal on MySQL, see transactional vs non-transactional databases.

Immediate Mitigation

Apply every corrective step against a staging clone with the same two-service topology first, and take a verified snapshot before editing any shared history table. The goal is to get the dependency ordered correctly and stop the concurrent race — not to redesign ownership under fire.

1. Freeze both pipelines. Stop the CI/CD runners for every service that writes to the shared database so no second migration races the reconciliation.

# bash · run from the CI control host · read-write to the CI system, not the DB
# Pause the deploy jobs for every service that owns migrations on this database.
for svc in orders_svc billing_svc; do
  ci pipeline pause --project "$svc" --reason "cross-service migration reconcile"
done

2. Establish who is ahead. Compare each service’s recorded head against what the failing service expects. If the dependency (service A) simply has not run, the fix is ordering, not repair — run A’s migration to the required version first.

# bash · run with a migration-capable role · applies real DDL, use a low-write window
# Bring the DEPENDENCY service to the version the dependent service requires, first.
flyway -configFiles=orders_svc.conf -target=20260724_02 migrate

3. Guard the dependent change so it cannot run early. Add a precondition that halts (rather than errors) when the dependency is absent, so a re-ordered pipeline converges instead of failing. In raw SQL, wrap the dependent step in an existence check:

-- PostgreSQL · run as the dependent service's migration role · must run AFTER the dependency
-- Halts cleanly if the owning service has not created the table yet, instead of a hard error.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.tables
    WHERE table_schema = 'billing_svc' AND table_name = 'billing_accounts'
  ) THEN
    RAISE EXCEPTION 'dependency billing_accounts absent — run billing_svc migration first';
  END IF;
  ALTER TABLE orders_svc.orders
    ADD CONSTRAINT fk_orders_account
    FOREIGN KEY (account_id) REFERENCES billing_svc.billing_accounts (id) NOT VALID;
END $$;

4. Serialize the shared writer. If the collision was two pipelines migrating at once, take a single advisory lock so only one migration touches the database at a time; the second waits rather than corrupting state.

-- PostgreSQL · run at the start of EVERY service's migration job · released on session end
-- One well-known key means concurrent migrations queue instead of racing.
SELECT pg_advisory_lock(hashtext('shared-db-migration'));
-- ... run the service's migration here ...
SELECT pg_advisory_unlock(hashtext('shared-db-migration'));

5. Resume in dependency order. Un-pause the owning service first, let it reach its target version, then un-pause dependents. Verify application error rate and connection-pool metrics returned to baseline before the next release train.

Permanent Fix / Long-Term Pattern

The durable fix is to stop treating a shared database as a place where any service may create anything. Assign every object a single owning service and make the ownership boundary explicit — ideally one schema (or database) per service, so orders_svc physically cannot write billing_svc’s tables and each keeps its own history table. Where services genuinely must share a table, publish it as a schema contract: the owning service versions the table’s shape, and consumers depend on the contract rather than reaching across to alter it. Then remove the tight ordering edge instead of orchestrating it. A cross-service foreign key is the tightest coupling you can add; prefer adding it NOT VALID and validating later, or dropping the hard constraint in favor of application-level referential checks, so a dependent deploy no longer requires the dependency to have migrated first. When a column must move from one service’s ownership to another, run it as a phased expand-and-contract change coordinated with a dual-write window, so both old and new shapes are valid during the transition and neither service is forced to deploy in lockstep.

Sequencing that does have to cross a boundary belongs in the pipeline, not in a tool’s version numbers. Encode the dependency-first order as an explicit stage in migration pipeline gating — the owning service’s migration is a gate the dependent service’s deploy waits on — and keep every migration a guarded, idempotent script so a re-run after a re-ordering converges rather than double-applies. Give each service its own history table and its own logical file path, verified as part of schema version control basics, so no two lineages ever fight over one row. This is the same parity discipline the environment parity strategies overview applies to configuration and data, extended to the one thing multiple services genuinely share: the ordering contract between them.

Verification Checklist

• Every table, index, and constraint in the shared database maps to exactly one owning service, recorded in a checked-in ownership map
• Each service writes to its own migration history table (distinct flyway_schema_history / logicalFilePath), never a single shared one
• Cross-service dependencies are enforced as pipeline gates so the owning service migrates before any dependent deploy
• Every migration takes the shared-database advisory lock (or an equivalent serializer) so concurrent runs queue instead of racing
• Dependent DDL uses a precondition/existence guard that halts cleanly when its dependency is absent, and remains idempotent on re-run
• A staging clone reproduces the full multi-service topology and passes a dry-run in dependency order before the release train ships

Return to the environment parity strategies overview for how this ordering contract fits the wider prepare-phase parity discipline.

Related

• Ensuring Environment Parity Between Dev and Prod Databases
• Seeding Anonymized Production Data into Staging
• Environment Parity Strategies

Frequently Asked Questions

Should each microservice have its own database, or is a shared one ever acceptable? A schema (or database) per service is the design that eliminates ownership collisions outright, because no service can physically alter another’s objects and each keeps an independent migration history. A shared database is acceptable when services are tightly coupled around one dataset and the operational overhead of splitting outweighs the coupling — but only if you impose explicit ownership: one owning service per object, per-service history tables, and pipeline-level sequencing. The shared database is not the problem; unowned objects and unordered writers are.

How do I order a migration in service B that depends on a table owned by service A? Do not try to express it in either service’s version numbers — a migration tool only understands its own lineage. Make the ordering a pipeline gate: service A’s migration to the required version becomes a prerequisite stage that service B’s deploy waits on. As a safety net, guard B’s dependent DDL with an existence precondition that raises a clear error (or halts) when A’s object is absent, so a mis-ordered run fails loudly and re-runs cleanly once A has migrated. For a change that moves ownership between services, sequence it as an expand-and-contract transition with a dual-write window rather than a single hard cutover.

Two services share one flyway_schema_history table and their checksums now conflict — what do I do? That shared history table is the root cause: two independent version lineages cannot safely write one history row set. Immediately, snapshot the database, pause both pipelines, and split the history so each service has its own table (Flyway’s table config) or its own logicalFilePath (Liquibase). Reconcile by confirming the physical schema is correct, then let each service re-baseline against its own history rather than hand-editing rows. Long term, give every service a distinct history target and enforce object ownership so the two lineages never touch the same row again.