Postgres Migration Froze Your App? ALTER TABLE Lock Fix

A simple ALTER TABLE during a deploy can freeze every query on that table. Learn why Postgres lock queues cause it, how to unblock a stuck migration, and how to run migrations safely.

A Postgres migration freezes your app because ALTER TABLE needs an ACCESS EXCLUSIVE lock. If one long query or open transaction already holds a lock on that table, the migration waits, and every new query queues up behind it. Fix it by finding the blocker with pg_stat_activity, ending it or cancelling the migration, and then setting a short lock_timeout so future migrations fail fast instead of freezing the app.

The scary part is that the migration itself is tiny. Adding one column is usually instant. The outage comes from the waiting, not the work. Here is how to find it, stop it, and prevent it.

Step 1: Understand the Lock Queue

Postgres grants locks in order. Your ALTER TABLE asks for the strongest lock on the table. It cannot get it while any other query, even a plain SELECT, holds a lock on that table. So it waits.

Now the key detail. While the ALTER waits, new queries on that table line up behind it. They do not slip past. This is described well in Schema changes and the Postgres lock queue and in GoCardless's write-up on zero downtime migrations.

The chain looks like this:

  1. A slow report query or an idle open transaction is touching orders.
  2. Your deploy runs ALTER TABLE orders ADD COLUMN ... and waits.
  3. Every new SELECT and INSERT on orders waits behind the migration.
  4. Connections fill up. The app returns timeouts and 502 errors.

From the outside it looks like a database outage. From the inside it is one blocked statement.

Step 2: Find the Blocker

Connect with psql from a fresh session and list who is waiting and who is blocking:

SELECT
  pid,
  pg_blocking_pids(pid) AS blocked_by,
  state,
  wait_event_type,
  now() - xact_start AS xact_age,
  left(query, 80) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND (cardinality(pg_blocking_pids(pid)) > 0 OR state = 'idle in transaction')
ORDER BY xact_start;

Read the output like this:

  • The ALTER TABLE row has a blocked_by list. Those pids are your blockers.
  • Rows with state idle in transaction are the usual culprits. A code path opened a transaction and never finished it.
  • Rows with a large xact_age are long queries or forgotten sessions.

The pganalyze guide to finding the cause of locking problems walks through the same idea in more depth.

Step 3: Unblock the System

You have two safe options. Pick the least harmful one first.

Option A: Cancel the migration. This is the safest. Nothing was changed yet, so you lose nothing:

SELECT pg_cancel_backend(<migration_pid>);

Traffic starts flowing again within moments, because the queue behind the ALTER is no longer waiting on it.

Option B: End the blocker. If the blocker is an abandoned idle transaction, you can end that session:

SELECT pg_terminate_backend(<blocker_pid>);

Only do this when you know what the session is. Ending a session rolls back its transaction. If it is a real long-running job, cancel the migration and run it later.

Managed databases such as Supabase, RDS and Cloud SQL let you run these functions on your own sessions and usually on others in the same role. If the call is refused, use the provider's dashboard to cancel the query.

Step 4: Set a Short lock_timeout for Migrations

The fix is to make the migration give up quickly. If it cannot get the lock in a couple of seconds, it should fail, and the app should stay up.

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;

The Postgres documentation describes lock_timeout as the time a statement may wait to acquire a lock before it is aborted. Sources such as Xata mention values of around two seconds or less as common for migrations.

You can set it for the whole migration role so no script can forget it:

ALTER ROLE migrator SET lock_timeout = '2s';

A failed migration with a clear error is a much better outcome than a frozen app. Retry the migration at a quieter moment, or let your tool retry it.

Step 5: Use Safe Patterns for Common Changes

Most freezes come from a few habits. Swap them for safer versions.

Add an index. A normal CREATE INDEX blocks writes. Use the concurrent form, outside a transaction block:

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Add a foreign key or check constraint. Add it without validating old rows, then validate in a second step that takes a weaker lock:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

Add a column. Adding a nullable column, or one with a constant default, is fast on modern Postgres. A volatile default, such as random(), or an older Postgres version (before 11), rewrites the whole table while holding the lock. Check the ALTER TABLE notes in the Postgres docs for your version.

Rename or drop a column. The old app code still uses the old name during a rolling deploy. Add the new column, deploy code that writes both, backfill in small batches, switch reads, then drop the old column in a later release.

Make a column NOT NULL. On a big table, add a CHECK (col IS NOT NULL) NOT VALID constraint, validate it, then set NOT NULL. Newer Postgres versions can skip the scan when a valid check constraint already proves it. Test this on a copy first.

Step 6: Retry Instead of Waiting

A short timeout means the migration will sometimes fail. Wrap it in a small retry loop with a pause, so a busy second does not fail your whole deploy. Postgres.ai describes this lock timeout and retry pattern. A bash version is enough for a small team:

for i in 1 2 3 4 5; do
  psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \
    -c "SET lock_timeout='2s'; ALTER TABLE orders ADD COLUMN note text;" \
    && break
  echo "lock busy, retry $i"
  sleep 5
done

Also fix what made the blocker possible. If you keep finding idle in transaction sessions, set idle_in_transaction_session_timeout so Postgres ends them on its own, and review code paths that open a transaction around slow work such as HTTP calls. For related connection problems, see Postgres "Remaining Connection Slots Are Reserved" error.

Step 7: Know When It Happens Next Time

Run migrations at a low traffic time when you can, and watch the app while they run. If response times jump and connections climb right after a deploy, suspect a lock first. A guide on why an endpoint gets slower as data grows covers the slow-query side of the same family of problems.

How Relia Helps

Relia watches the API, the database and the deploys of a live product, and joins them into one incident explained in plain words. For a frozen migration, that means you see the deploy, the spike in slow requests and the users who hit errors in one place, not in three dashboards. Uptime monitors and checks on key pages also tell you when users are affected. Relia does not run SQL or change your database for you. You can try the live demo to see how an incident reads.

FAQ

Why does ALTER TABLE block SELECT queries in Postgres?

ALTER TABLE often takes an ACCESS EXCLUSIVE lock, which conflicts with every other lock, including the one SELECT takes. It also makes later queries wait in line behind it, even before it has the lock.

What is a good lock_timeout for migrations?

A few seconds is a common choice. Two seconds is often cited for production schema changes. Pick a value short enough that a wait does not cause user-visible errors, and retry on failure.

Is it safe to kill a migration in Postgres?

Cancelling a waiting ALTER TABLE with pg_cancel_backend is safe. DDL in Postgres is transactional, so a cancelled statement leaves the table as it was.

Does adding a column lock the table in Postgres?

Briefly, yes. It needs an ACCESS EXCLUSIVE lock, but it holds it for a very short time when no rewrite is needed. The danger is the wait to get the lock, not the change itself.

All guides