Lorixen.
EN

Postgres queries hanging? Find the session blocking them

Short answer: When PostgreSQL queries that are normally instant suddenly hang, and the app shows timeouts or spinning pages, the usual cause is a lock: one session holds it and everything else queues behind. Find the culprit with pg_stat_activity and the pg_blocking_pids() function, which returns the process IDs blocking a given session. Often it's a transaction left open by a script or a stuck job, shown as "idle in transaction". Cancel or terminate that session, then set lock_timeout and idle_in_transaction_session_timeout so it can't freeze the app again.

What blocking looks like

PostgreSQL uses locks to keep data consistent. Most of the time they're held for milliseconds and nobody notices. Problems start when a session holds a lock for a long time:

  • A developer opens a transaction in a database tool, updates a row and goes to lunch without committing.
  • A migration runs ALTER TABLE, which needs a strong lock, and waits behind a long report query, and then every normal query queues behind the migration.
  • A background job crashes mid-transaction but its connection stays open.
  • A bulk update touches thousands of rows that the website also needs.

Symptoms: page loads hang rather than fail fast, the connection pool fills up, CPU on the database stays low (it's waiting, not working), and then everything recovers the moment one session ends.

Step 1: see what's running and waiting

pg_stat_activity has one row per server process, showing its state, current query and what it's waiting for. Useful columns:

  • state: the PostgreSQL documentation defines values such as active, idle, and idle in transaction, meaning the session is inside a transaction but not currently running a query.
  • wait_event_type and wait_event: what the session is waiting for, if anything. A type of Lock means it's waiting for a lock.
  • xact_start and query_start: when the transaction and the current query began.
  • query: the most recent statement.

Look for sessions waiting on locks, and for any session whose transaction started long ago.

Step 2: find who's blocking whom

The pg_blocking_pids(pid) function returns an array of the process IDs of the sessions blocking the given process. A simple query selects each waiting session's pid, its query, and pg_blocking_pids(pid), filtering to rows where that array isn't empty. Join back to pg_stat_activity to see what the blockers are doing.

Often you'll find a chain: many sessions blocked by one, which is itself blocked by another. Follow it to the session at the head of the queue, the one that isn't waiting on anyone.

Step 3: end the blocker carefully

PostgreSQL provides two administrative functions:

  • pg_cancel_backend(pid) cancels the session's current query. The session stays connected.
  • pg_terminate_backend(pid) terminates the whole session, rolling back its open transaction.

If the blocker is "idle in transaction", there's no running query to cancel, so termination is usually what clears it. Before you terminate, check what it is. Ending a half-finished data fix may leave work undone, though PostgreSQL rolls back the transaction cleanly. Note who and what it was, so you can stop it recurring.

Step 4: prevent the next pile-up

Two settings put limits on lock trouble:

  • lock_timeout aborts any statement that waits longer than the specified time to acquire a lock on a table, index, row or other object. Set it for migrations and admin scripts so they give up rather than queue indefinitely and drag the app with them.
  • idle_in_transaction_session_timeout ends sessions that sit idle inside an open transaction for longer than the limit, which stops forgotten transactions from holding locks for hours.

Set them for specific roles or sessions rather than globally if you're unsure; for example, a short lock_timeout for your migration user, and a sensible idle timeout for the app's role and for people using database tools.

Habits that avoid blocking

  • Keep transactions short. Don't call external APIs, send emails or wait for user input inside a database transaction.
  • Run heavy updates in batches, committing every few thousand rows, rather than one giant transaction.
  • Schedule migrations that need strong locks for quiet periods, with lock_timeout set and a retry plan.
  • Turn on autocommit in database tools used by people, or train the team to commit or roll back promptly.
  • Monitor long transactions: alert when any transaction has been open longer than a threshold you choose.
  • Log lock waits. The log_lock_waits setting records statements that wait longer than the deadlock check interval, giving you evidence after an incident.

Write a runbook entry

Lock incidents tend to happen at awkward times and to whoever is on hand. Save the diagnostic queries from steps 1 and 2 in your team's runbook, with a note on how to decide between cancelling and terminating, who to tell, and which settings to check afterwards. The next person can then clear a pile-up in minutes instead of searching for queries under pressure.

A worked example

Picture a hypothetical Calgary veterinary clinic group whose appointment app froze on a Monday morning. Pages hung, the connection pool maxed out, and the database server's CPU was nearly idle. A query on pg_stat_activity with pg_blocking_pids showed dozens of sessions waiting behind one connection in "idle in transaction" state since the previous Friday: a manual data fix run from a desktop database tool, never committed. The developer terminated the session, the app recovered at once, and the team set idle_in_transaction_session_timeout for staff database accounts and a lock_timeout for migrations.

FAQ

Is this the same as a deadlock?

No. In a deadlock, two sessions wait for each other and PostgreSQL detects it and cancels one. Blocking is a one-way queue that can last indefinitely.

Will terminating a session lose data?

It rolls back that session's uncommitted changes. Committed data is safe.

Can my app detect lock waits itself?

Yes, with statement or lock timeouts it gets an error it can handle instead of hanging.

Do managed databases support these functions?

Most do, though some restrict which roles may terminate other sessions. Check your provider's documentation.

If your database keeps freezing under load or after maintenance, tell us what you're seeing and we'll help find the blocker.

Sources

WhatsAppBook a call