Advisory locks, migration tools, and deploy coordination

A Kubernetes deployment scales from zero to three replicas simultaneously. All three pods boot and each tries to run the pending migration. Without coordination, all three apply it in parallel — one succeeds, two fail with duplicate-constraint errors, the app half-starts in an unknown state.

Postgres DDL transaction semantics

Most DDL in Postgres is transactional: BEGIN; CREATE TABLE; ALTER TABLE; COMMIT; either all succeeds or all rolls back atomically. Concurrent sessions see the change only after COMMIT (MVCC snapshot isolation).

Exceptions that cannot run inside a transaction block:

• CREATE INDEX CONCURRENTLY
• REINDEX CONCURRENTLY
• ALTER TYPE ... ADD VALUE (in PG 11 and earlier; transactional from PG 12+)

Migration tools wrap each migration in a transaction by default. Any migration containing a CONCURRENT operation needs a non-transactional mode annotation. Most tools support this:

• Atlas: -- atlas:txmode none
• golang-migrate: -- migrate:notransaction
• Prisma: not natively supported; use prisma db execute with a raw SQL file

If you mix a CONCURRENT operation with other DDL in one migration file, split them into two files.

The advisory lock pattern for serialised migration runners

Multiple application servers booting simultaneously can each try to run pending migrations. Without coordination, two processes might apply the same migration twice or conflict on the schema_migrations table.

The standard fix is a Postgres advisory lock at migration start:

-- Acquired before any migrations run:
SELECT pg_advisory_lock(12345);  -- 12345 = stable integer key for this app

-- Migrations run here ...

-- Released after all migrations complete:
SELECT pg_advisory_unlock(12345);

pg_advisory_lock blocks until it acquires the lock. The first server gets it and runs migrations; subsequent servers wait. When the first finishes and releases, the next server acquires — but finds all migrations already applied and does nothing.

Most migration tools implement this automatically:

• golang-migrate: acquires pg_advisory_lock(hashInt64(databaseName)) at start.
• Prisma: uses a _prisma_migrations table with row-level locking.
• Atlas: uses pg_advisory_lock with a configurable key.

For non-blocking startup behaviour, use pg_try_advisory_lock with a retry loop:

-- Returns true if acquired, false if already held
SELECT pg_try_advisory_lock(12345);

Deploy coordination: migration before pod rollout

The three common patterns:

1. Pre-deploy job (recommended): a k8s Job or CI step runs migrate up against production before the rolling deploy. Code deploy is gated on migration success. Old pods handle traffic during migration; schema is additive (expand-only). This is the cleanest pattern — clear separation of concerns.

2. Init container on each pod: each new pod runs migrate up on boot; advisory lock serialises them. Simple but slow — every pod waits for the migration check, even if nothing is pending.

3. Separate migration pipeline: migration is run manually or on its own cadence, fully decoupled from code deploy. Requires human coordination; error-prone at scale.

2026 senior default: separate pre-deploy job (pattern 1), gated on migration success. The migration job emits metrics; any failure pages on-call and blocks the code rollout.