skip to content

For a service that deploys many identical application instances, where in the delivery pipeline should database migrations actually execute, and what stops two instances starting at once from corrupting the migration history?

level: seniorimportance: should knowfreq 48%

answer

  1. dedicated job/init container vs boot
  2. lock table vs session advisory lock
  3. stale lock after a killed pod
  4. separate DDL credential from runtime
  5. CI: empty build + restored prod snapshot

basics

~20 s

Run migrations once per deploy as a dedicated step — a pipeline job or init container — before the new instances take traffic, using a credential with DDL rights the app itself lacks. If instances do migrate at boot, the tool's lock table or an advisory lock serialises them so exactly one applies and the rest wait.

solid answer

~60 s

Two workable shapes. **A dedicated migration step** — a pipeline stage, a Kubernetes Job, or an init container — runs the tool once with a DDL-capable credential, and application instances start only after it succeeds. This makes the deploy fail fast on a bad migration, keeps the runtime credential free of DDL rights, and gives one clear place to look at timing. **Migrate at application boot** is common with frameworks and acceptable for small services, but then every replica in a rolling deploy races. The safeguard is the tool's own mutual exclusion: Liquibase's `DATABASECHANGELOGLOCK` row, Flyway's session-level lock (a PostgreSQL advisory lock, a `SELECT … FOR UPDATE` on the history table elsewhere). One instance migrates; the others block, then find nothing pending. The residual risk is a long migration blowing the container's startup probe and leaving a stale lock behind. Either way, migrations run **before** the new code takes traffic, and CI must have already replayed them against a production-shaped snapshot so you know how long they take and whether real data violates the new constraints.

code

text · 6 lines
text
1. build image (contains app + migration scripts)
2. run migration job   <- DDL credential, exactly one execution
   |- fails? stop the deploy, no new instances start
3. roll out new instances gradually
   |- old instances still running against the new schema
4. (later, separate deploy) destructive cleanup migration

go deeper

for a junior

Say migrations run as part of the deploy, before the new code takes traffic, and that the tool locks so only one process applies them.

for a middle

Compare a dedicated migration job against boot-time migration, and name the lock mechanisms plus the startup-timeout hazard.

for a senior

Add credential separation, stale-lock recovery, replaying migrations onto a restored production snapshot in CI, and alerting on migration duration.

for a principal

Frame it as deploy-pipeline design: exactly-once execution, least privilege, drift detection against production, decoupling long schema operations from the deploy path, and who is allowed to run migrations by hand.

## The shape of the problem Modern deployments run N identical instances and replace them gradually. Migrations are the one part of the deploy that must happen **exactly once**, in a specific order, against shared state. Everything below follows from that mismatch. ## Option A: a dedicated migration step A separate execution of the migration tool — a pipeline stage, a Kubernetes `Job`, an ECS one-off task, an init container that runs before the app container — with the deploy gated on its success. Strengths: - **Exactly-once by construction.** No race to design around. - **Least privilege.** The migration credential owns DDL; the runtime credential holds only DML on its tables. If the application is compromised it cannot drop a table. - **Fail fast and visibly.** A failing migration fails the deploy before any new instance takes traffic, with its own logs and duration. - **Operable.** You can run it manually against a maintenance window for an expensive change. Costs: more pipeline machinery, and the step must be idempotent and retry-safe, because pipelines retry. ## Option B: migrate at application startup The framework runs the tool during context initialisation. Simple, keeps schema and code atomically versioned in one artefact, and is genuinely fine for a single-instance service or a small team. What you must then handle: - **Concurrency.** Several replicas boot simultaneously. Both tools take a lock: Liquibase inserts a row in `DATABASECHANGELOGLOCK`; Flyway takes a session-level lock (advisory lock on PostgreSQL, row lock on the history table elsewhere). The first instance migrates while the others wait and then find nothing pending. A session-level lock is preferable because it dies with the connection; a *row-in-a-table* lock survives a hard kill and must be cleared manually (`releaseLocks`), which is the classic 3 a.m. Liquibase incident. - **Startup timeouts.** A migration that takes eight minutes on a large table will exceed a container liveness/startup probe. The orchestrator kills the pod mid-migration, possibly leaving a lock or, on a non-transactional-DDL engine, a half-applied change — and then does it again on the restart. Either raise the startup probe budget generously or move long migrations out of boot entirely. - **Privilege.** The runtime credential now needs DDL permanently, which is a real weakening of the blast radius. ## Ordering with respect to traffic Whichever option, migrations complete **before** the new version serves requests. During a rolling deploy that means the schema is momentarily ahead of some still-running old instances, which is fine as long as each change is compatible with the previous code version — additive changes, no renames in the same deploy that ships the code using them. ## What CI must do before any of this A migration that has only ever run against an empty test database is untested. Two pipelines earn their keep: 1. **From empty**, on every pull request: replay the whole history into a fresh database (a throwaway container). Catches broken SQL, duplicate versions and ordering bugs. 2. **Against a snapshot**: restore a recent production-shaped dump — real row counts, sanitised values — and apply only the pending scripts. This is the only way to learn that adding `NOT NULL` fails because 12,000 legacy rows are null, that a unique index is violated by real duplicates, or that the `ALTER TABLE` needs 40 minutes and a lock nobody can afford. Record the duration from run 2 and treat a large number as a design signal, not a scheduling problem. ## Secrets, environments, drift - The migration credential is a deploy-time secret from the pipeline's secret store, never baked into the image or into a migration file. - Migrations must be environment-agnostic. The moment a script contains a production hostname or an environment-specific seed row, someone will edit it per environment and break immutability. Keep environment data out; use configuration or separate seed changelogs. - Add a **drift check**: a scheduled job that runs the tool in validate/status mode against production and alerts if the history table disagrees with the shipped scripts. That catches the manual `ALTER` someone ran during an incident, before it detonates in the next deploy. ## Practical rules of thumb - One logical change per migration; small migrations fail cheaply and can retry. - Never make a deploy depend on a migration that takes longer than your deploy timeout — run those separately and deliberately. - Alert on the migration step's duration, not only its exit code. - Give the pipeline a way to run migrations *without* deploying code, so schema and code can be sequenced independently when an incident demands it.

  • A pod running migrations at startup is killed by its liveness probe halfway through. What is the state and how do you recover?
    On an engine with transactional DDL the migration itself rolls back, but a lock held as a row in a lock table can survive the killed session and block every later attempt until it is released manually. On MySQL or older Oracle the DDL may be half applied, leaving a failed history row that must be repaired against the schema's real state. The prevention is to move long migrations out of the boot path and to prefer session-scoped locks that die with the connection.
  • Why give the migration step a different database credential from the running application?
    Because DDL rights are the blast radius that matters: an application compromised through an injection flaw or a dependency should not be able to drop or alter tables. Splitting the credentials means the destructive capability exists only for the seconds the migration job runs, under pipeline control, and the long-lived runtime credential holds only the DML it needs.

saying these in an interview costs you the question

  • Letting every replica migrate at boot with no consideration of locking or startup timeouts
  • Using the same all-powerful database credential for migrations and for application runtime
  • Claiming CI validating migrations on an empty database proves they are safe against production data
  • Putting a multi-hour ALTER TABLE inside the deploy path and being surprised when the rollout times out
  • Embedding environment-specific values in migration scripts, which invites per-environment edits

context

resolving links…