PostgreSQLBackendsWaiting¶

What it means¶

More than 300 connections (backends) on a database instance wait for a lock. Another connection holds the lock, and the waiting connections cannot continue until it is released.

What happens if you do nothing¶

Queries become slow or hang. Your applications can run into timeouts. The waiting connections also count against max_connections, so the database can run out of connections. See PostgreSQLConnectionsCritical.

What to do¶

1. Find the connections that wait, and the connections that block them:

   SELECT pid, pg_blocking_pids(pid) AS blocked_by,
          now() - query_start AS waiting_for, query
   FROM pg_stat_activity
   WHERE cardinality(pg_blocking_pids(pid)) > 0
   ORDER BY query_start;
   

2. Look at the blocking connections (the numbers in blocked_by):

   SELECT pid, usename, application_name, state,
          now() - xact_start AS transaction_duration, query
   FROM pg_stat_activity
   WHERE pid IN (<blocking_pids>);
   

   Often the blocker is a long running transaction, or a connection in state idle in transaction. See PostgreSQLLongRunningTransaction.

3. If the blocker is not expected, stop it with pg_cancel_backend(<pid>) or pg_terminate_backend(<pid>). The waiting connections then continue.

Prevent lock waits¶

• Keep transactions short.
• Schema changes such as ALTER TABLE take a strong lock on the table. All other queries on this table wait until the change is done. Run schema changes when the load is low.

• Set a lock timeout for schema changes, so that they fail instead of blocking other queries for a long time:

  SET lock_timeout = '5s';
  ALTER TABLE ...;