Postgres migrations deadlock because the app starts before the migration job finishes
Postgres migrations deadlock because the app starts before the migration job finishes
The deploy starts. A migration job runs ALTER TABLE accounts ADD COLUMN .... Meanwhile the new app pods pass their TCP check and start serving requests. One request opens a transaction, updates a row, and holds a lock the migration needs. The migration waits. New requests queue behind the migration’s pending ACCESS EXCLUSIVE lock. Now the app waits on Postgres, Postgres waits on the app, readiness checks fail, and the rollout stalls with connections piling up. Often this is a lock queue rather than a true deadlock, but the operational result is the same: nothing moves and rollback gets riskier.
Why the app starts before migrations deadlock
PostgreSQL takes an ACCESS EXCLUSIVE lock for many ALTER TABLE operations [link needed]. While that lock is pending, new queries can queue behind it [link needed]. If the app is already running, it can hold the very locks the migration needs while also waiting for the migration’s lock. A common trigger is an app transaction that touches a table, then calls code that expects a new column while the migration is still blocked.
The fix is not “make migrations faster.” It is ordering: migration completion must be a startup condition, not a race.
Procedure: gate readiness on the migration job
1. Run migrations as one job with one replica. Do not run them from every app container entrypoint.
2. At job start, take a fixed advisory lock, for example SELECT pg_advisory_lock(814729); [link needed]. Release it only after the migration commits.
3. Set lock_timeout on the migration session so it fails fast instead of holding the lock queue forever [link needed].
4. Write a row to schema_migrations with the target version and finished_at only after all DDL commits.
5. Add a readiness endpoint that queries that row and compares it with the app’s expected version. Return 503 until they match.
6. Add a startup probe so liveness does not restart the pod while it waits for the migration [link needed]. Readiness keeps traffic out; the startup probe keeps the process alive.
7. In CI/CD, run the job, wait for success, then roll the app.
The procedure below prevents the app starts before migrations deadlock by making migration completion a readiness condition.
Lock hygiene that prevents repeats
Keep app transactions short around tables being migrated. If the migration needs a long backfill, split it from the DDL and use an expand/contract sequence: add nullable columns, deploy compatible code, backfill, then add constraints later [link needed]. Set statement_timeout for app queries so one stuck transaction cannot block the migration indefinitely [link needed]. For multi-table migrations, acquire locks in a consistent order to reduce true deadlock risk [link needed].
When NOT to do this
Do not gate every app start on migrations when the schema change is already backward-compatible and online. If old and new app versions can both run safely, expand/contract plus feature flags usually gives faster autoscaling and less deploy coupling. Also skip a global readiness gate when multiple services share the database and each has its own migration timeline; use a per-service version table instead.
Checklist
- One migration job, one migrator
- Advisory lock around the job
lock_timeoutset on migrations- Version marker written only after commit
- Readiness checks schema version
- Startup probe protects the waiting app
- CI runs migration before rollout
- Rollback path tested for app and schema