Skip to main content

Locks & Blocking

Locking is how Postgres ensures data consistency, but too much locking (contention) leads to blocking, where queries wait indefinitely for others to finish.

How blocking leads to timeouts​

When a "heavy" query (like a migration or a massive update) holds a lock, other queries trying to access the same data will queue up. Eventually, your app will hit a timeout, and users will see errors.

What to check​

  • Long Transactions: A transaction that stays open for minutes while the app does something else (like calling an external API).
  • Schema Migrations: Operations like ALTER TABLE often require exclusive locks.
  • Background Jobs: Heavy reports or batch updates running during peak traffic.

What to do​

  1. Identify the Blocker: Use pgpulse to see which query is currently holding the lock that others are waiting for.
  2. Terminate Cautiously: In an emergency, you can terminate the blocking backend, but do so with caution to avoid data inconsistency.
  3. Reduce Transaction Time: Keep transactions as short as possible. Never perform network calls or heavy logic inside a database transaction.
  4. Use CONCURRENTLY: When creating indexes, always use CREATE INDEX CONCURRENTLY to avoid locking the table for writes.