← Back to the library
BackendApplied · 8 min

A migration locks a table and requests pile up

“A deploy runs an ALTER TABLE or CREATE INDEX, and every query touching that table stalls until the migration finishes or is killed.”

ERROR CODES YOU MAY SEE

A code is a clue. Use its meaning and surrounding evidence to narrow the cause.

55P03PostgreSQL SQLSTATE · standard

A lock could not be acquired, for example when lock_timeout expires or NOWAIT is used (lock_not_available).

For a migration this is the safe failure: the DDL gave up instead of queueing traffic. Find the blocker with pg_blocking_pids() and retry.

Official reference for 55P03 (opens in new tab)Code definition reviewed
57014PostgreSQL SQLSTATE · platform

The query was canceled (query_canceled).

Read the accompanying message to identify the cancellation cause. Check timeouts and query traces; cancellation alone does not establish N+1 access.

Official reference for 57014 (opens in new tab)Code definition reviewed

THE PRINCIPLE

Make DDL fail fast rather than wait, and split risky changes into steps that each take only brief or weak locks.

FIRST MOVES

  1. Set a short lock_timeout (and statement_timeout) in the migration session; retry on 55P03.
  2. Before deploying, check for long-running or idle-in-transaction sessions on the table.
  3. Build indexes with CREATE INDEX CONCURRENTLY outside a transaction; drop and retry if it leaves an INVALID index.
  4. Add constraints NOT VALID, then VALIDATE CONSTRAINT separately under a weaker lock.
  5. Use expand/contract: add nullable column, backfill in batches, switch reads, drop old column later.

TOOLS: THEN → NOW

One migration file that rewrites the tableExpand/contract steps released over several deploys
Watching the deploy and hopinglock_timeout plus retry, pg_blocking_pids() to find the blocker

PATTERN SNAPSHOT

-- Session-level, migration connection only.
SET lock_timeout = '3s';
SET statement_timeout = '15min';

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_user_id_idx
  ON orders (user_id);

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;

CLOSE THE AI. EXPLAIN THIS.

Why can an ALTER TABLE that finishes in milliseconds still block reads for minutes?

HOW IT WORKS UNDERNEATH

SOURCES

Guide reviewed