Why are my PostgreSQL migrations locking tables?

PostgreSQL migrations often lock tables because many schema changes need to protect the table from concurrent reads/writes while PostgreSQL rewrites metadata or data.

Common reasons migrations lock tables

1) ALTER TABLE takes an exclusive lock

Many schema changes require at least an ACCESS EXCLUSIVE lock on the table, which blocks:

• reads
• writes
• other DDL

Examples:

• ALTER TABLE ... ADD COLUMN is usually lightweight, but still takes a lock.
• DROP COLUMN, ALTER COLUMN TYPE, SET NOT NULL, adding some constraints, renaming columns/tables, etc. can block heavily.

2) Some changes rewrite the whole table

Operations like changing a column type may force PostgreSQL to rewrite the table, which can hold a lock for the duration of the rewrite.

3) Constraints and indexes can block

• ADD CONSTRAINT may scan the table to validate data.
• CREATE INDEX locks writes depending on the method.
• Regular CREATE INDEX can interfere with writes; CREATE INDEX CONCURRENTLY reduces blocking.
• Foreign keys can take locks on both tables.

4) Migrations run inside a transaction

Many migration tools wrap the whole migration in one transaction. That means:

• locks are held until the transaction commits
• if one statement is slow, all locks stay active longer

5) Long-running queries can make it look worse

A migration may wait for existing transactions to finish before it can acquire its lock. Once it gets the lock, it may then block everyone else.

Typical lock levels

PostgreSQL uses different lock strengths, but many schema changes default to a very strong lock:

• ACCESS SHARE: normal reads
• ROW EXCLUSIVE: inserts/updates/deletes
• ACCESS EXCLUSIVE: blocks almost everything

A lot of migration DDL uses ACCESS EXCLUSIVE.

How to reduce locking

• Use CREATE INDEX CONCURRENTLY for indexes
• Avoid ALTER COLUMN TYPE on large tables if possible
• Break big migrations into smaller steps
• Add nullable columns first, backfill later, then add constraints
• Use NOT VALID for foreign keys/check constraints when possible, then validate later
• Run migrations during low traffic windows
• Keep transactions short

How to see what’s locking

You can inspect locks and blockers:

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
  ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocked_locks.locktype = blocking_locks.locktype
 AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
 AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
 AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
 AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
 AND blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
 AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
 AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
 AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
 AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
 AND blocked_locks.pid <> blocking_locks.pid
JOIN pg_stat_activity blocking
  ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

If you want, I can also explain which specific migration statement is causing the lock and how to rewrite it safely.