Setting lock_timeout on a migration keeps a blocked ALTER TABLE from freezing your traffic, but on its own it just converts one outage into a flaky deploy. The complete version is five rules: exactly one migrator at a time (advisory lock), a short lock timeout scoped to the transaction, the blocker logged before every retry, jittered backoff so replicas don't wake in lockstep, and a wall-clock budget after which the deploy fails instead of retrying forever. A reader raised this on an earlier post about migration locks, and it's the part most runners get wrong.
Everything here holds for Postgres 12 and up, as of mid-2026.
Why do several replicas try to run the same migration at once?
Most deploy systems run migrations as a pre-start step in the application container. Roll out four replicas and you have four processes racing to apply the same DDL. Usually the migration table's own row lock hides this, and you never notice.
You notice when the DDL is blocked. All four sit in the lock queue behind the same idle-in-transaction session. Then the blocker commits, all four wake, and whichever loses the race hits a duplicate-object error or, worse, a partially applied migration:
ERROR: column "shipped_at" of relation "orders" already exists
Now the deploy is red for a reason that has nothing to do with the real problem. Worse, if you added deterministic exponential backoff — sleep 1s, 2s, 4s — every replica computes the same delays, so they retry in unison and keep colliding on the same schedule. Backoff without jitter is a synchronized herd.
Takeaway: a blocked migration turns a harmless replica race into a deploy failure, so serialization has to happen before the retry logic, not after.
How do I make sure only one migrator runs at a time?
Postgres advisory locks. They're application-defined locks on an arbitrary bigint key, scoped to the database, with no table involved:
-- session-level: held until unlocked or the connection closes
SELECT pg_try_advisory_lock(8675309); -- true = you are the migrator
-- ...run migrations...
SELECT pg_advisory_unlock(8675309);
Use pg_try_advisory_lock (returns immediately) rather than pg_advisory_lock (waits forever) so a losing replica can decide what to do rather than hanging. Two reasonable choices: exit 0 and let the winner apply the schema, or wait a bounded time if the replica cannot safely serve traffic against the old schema. Pick one deliberately — silently exiting 0 is a real footgun when the new code needs the new column.
One deployment detail decides which lock scope you can use. If your connection goes through PgBouncer in transaction pooling mode, session-level advisory locks are unsafe, because the connection returns to the pool between transactions while the lock is still attached to it. There, use pg_advisory_xact_lock(8675309) inside the migration transaction, which Postgres releases automatically at commit or rollback.
Some runners already do this for you, and it's worth checking before you build your own:
| Runner | Cross-process locking (as of mid-2026) | Watch out for |
|---|---|---|
| Flyway | Yes, database-level lock on Postgres | Verify behavior when running through a transaction pooler |
| Liquibase | Yes, via a DATABASECHANGELOGLOCK row |
A killed run can leave the lock row set; needs manual release |
| Rails Active Record | Yes, advisory lock by default | Can be disabled in config; check it wasn't turned off |
| golang-migrate | Yes, advisory lock on the Postgres driver | Lock is per-database, so shared databases share the lock |
| Alembic | No built-in lock | Serialization is your job — wrap the runner yourself |
If you want serialization without writing a runner, Flyway is the one that takes a database lock for you and fails the second process cleanly instead of letting it race.
Takeaway: check whether your migration tool already serializes runs before adding your own advisory lock — two locking schemes are not safer than one.
Where should lock_timeout actually be set?
Inside the transaction, with SET LOCAL, so it evaporates at commit:
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL application_name = 'migrator:add_shipped_at';
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;
COMMIT;
The reason to prefer SET LOCAL over a plain SET is pooling: a plain SET sticks to the backend, and a pooled connection can hand that 3-second timeout to unrelated application queries later. The distinctive application_name costs nothing and makes the blocker/waiter pair obvious in pg_stat_activity at 2am.
Two exceptions worth knowing. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so SET LOCAL doesn't apply — issue a session-level SET lock_timeout on that connection instead. And don't wrap CONCURRENTLY work in a tight statement_timeout: if the timeout kills it, you're left with an INVALID index that you must drop before retrying.
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
Takeaway: SET LOCAL lock_timeout bounds lock acquisition for this transaction only, which is exactly the scope you want on a pooled connection.
How should the retry loop back off, and what should it log first?
Full jitter — sleep a uniform random amount between zero and the current exponential cap — is what breaks the synchronized herd. And before each sleep, capture who blocked you, because a retry that succeeds tells you nothing about the query that will block tomorrow's migration too.
There's a catch people get wrong: you cannot call pg_blocking_pids(pg_backend_pid()) from the connection that is currently blocked — it's busy waiting. Sampling it live requires a second observer connection. The cheap approximation is to probe right after the lock_timeout fires, from the same connection, for the oldest transactions holding locks on that table:
SELECT a.pid, a.application_name, a.state,
now() - a.xact_start AS xact_age,
left(a.query, 80) AS query
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE l.relation = 'public.orders'::regclass
AND a.pid <> pg_backend_pid()
ORDER BY a.xact_start
LIMIT 5;
Occasionally the blocker has already finished and you log nothing. That's an acceptable trade for not running an observer thread. Here is the whole contract in one runner, using psycopg 3:
import random, time
import psycopg
LOCK_KEY = 8675309 # same constant in every replica
BUDGET_SECONDS = 300 # hard stop for the whole migration step
BASE, CAP = 1.0, 20.0
def run_migration(dsn, ddl, table):
conn = psycopg.connect(dsn, autocommit=True,
application_name="migrator")
with conn.cursor() as cur:
cur.execute("SELECT pg_try_advisory_lock(%s)", (LOCK_KEY,))
if not cur.fetchone()[0]:
print("another migrator holds the lock; nothing to do")
return 0
deadline, attempt = time.monotonic() + BUDGET_SECONDS, 0
try:
while True:
attempt += 1
try:
with conn.transaction(), conn.cursor() as cur:
cur.execute("SET LOCAL lock_timeout = '3s'")
cur.execute(ddl)
print(f"applied on attempt {attempt}")
return 0
except psycopg.errors.LockNotAvailable:
log_blockers(conn, table)
delay = random.uniform(0, min(CAP, BASE * 2 ** (attempt - 1)))
if time.monotonic() + delay > deadline:
print("lock budget exhausted; failing the deploy")
return 1
print(f"blocked; retry {attempt} in {delay:.1f}s")
time.sleep(delay)
finally:
with conn.cursor() as cur:
cur.execute("SELECT pg_advisory_unlock(%s)", (LOCK_KEY,))
conn.close()
Note that only LockNotAvailable (SQLSTATE 55P03) is retried. A syntax error or a constraint violation is not a transient condition, and retrying it just burns the budget.
Takeaway: retry only the lock-timeout error, sleep a random interval rather than a computed one, and log the blocker before every sleep.
When should the deploy just fail?
When the budget runs out. A migration that has been blocked for five minutes is not waiting on a slow query; it's waiting on something structural — an idle-in-transaction connection from a pool with no idle_in_transaction_session_timeout, a BI tool holding a long read, a pg_dump that overlaps your deploy window. Retrying past that point hides the diagnosis you actually need.
| Symptom in the retry log | Likely cause | Fix, not a retry |
|---|---|---|
Blocker state = idle in transaction, age growing |
App or pool leaking an open transaction | Set idle_in_transaction_session_timeout
|
Blocker is a long analytics SELECT
|
Reporting traffic on the primary | Move reads to a replica, or migrate off-peak |
Blocker is another migrator:*
|
Advisory lock missing or disabled | Fix serialization first |
| No blocker found, still timing out | Blocker is short but constant | Raise lock_timeout slightly, or use a quieter window |
Takeaway: the budget's job is to convert an invisible hang into a red deploy with the blocker's identity attached.
FAQ
Why does my Postgres migration fail with "canceling statement due to lock timeout"?
Because lock_timeout is doing its job: your DDL waited longer than that limit for a lock another session held. That error means your app stayed up. Look at what held the lock — usually an idle-in-transaction connection or a long-running read — rather than raising the timeout.
Can two application replicas run database migrations at the same time?
Yes, unless something stops them. Serialize the runner with pg_try_advisory_lock on a fixed key, or confirm your migration tool takes its own lock. Behind PgBouncer in transaction pooling mode, use pg_advisory_xact_lock instead, since session-level locks outlive the transaction that took them.
Should migration retries use exponential backoff?
Use exponential backoff with full jitter — a random sleep between zero and the current cap. Deterministic delays make every replica wake at the same instant and contend again. Cap total retry time with a wall-clock budget and fail the deploy when it's exhausted.
Bottom line
If your migrations already use lock_timeout and a non-blocking two-step for rewrites, the missing piece is the runner around them. Add one migrator via advisory lock (or verify your tool has one), scope the timeout with SET LOCAL, tag the session with a distinctive application_name, log blockers before each jittered retry, and stop hard at a fixed budget. Teams on Flyway, Liquibase, Rails, or golang-migrate mostly need to verify the lock is on and add the budget; teams on Alembic or a hand-rolled script need the whole contract. The point isn't to make blocked migrations succeed — it's to make them fail fast, once, with the blocker named.
Top comments (0)