How do I identify what blocked a query on my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instance?

Resolution

Uncommitted transactions can block new queries, make them sleep, or cause them to fail when they exceed the lock wait timeout or the statement timeout. To resolve this issue, identify and stop the uncommitted transactions.

Identify the blocked transactions

To identify blocked transactions, turn on Performance Insights and use Database Insights. Use the Database load chart to view the database load dimensions for Waits, Host, SQL, or User. For more information, see Monitoring DB load with Performance Insights on Amazon RDS.

To identify the current state of the blocked transaction, run the following statement:

SELECT * FROM pg_stat_activity WHERE query iLIKE '%TABLE NAME%' ORDER BY state;

Note: Replace TABLE NAME with your table name or condition.

If the value in the wait_event_type column isn't Lock, then there's a performance bottleneck with resources such as CPU, storage, or network capacity. To resolve performance bottlenecks, tune the performance of your database. For example, you can add indexes, rewrite queries, or run vacuum and analyze commands. For more information, see Best practices for working with PostgreSQL.

If the value of the wait_event_type column is Lock, then other transactions or queries block the query. to identify the cause of the blocked transaction, run the following statement:

SELECT blocked_locks.pid     AS blocked_pid,       blocked_activity.usename  AS blocked_user,  
       blocked_activity.client_addr as blocked_client_addr,  
       blocked_activity.client_hostname as blocked_client_hostname,  
       blocked_activity.client_port as blocked_client_port,  
       blocked_activity.application_name as blocked_application_name,  
       blocked_activity.wait_event_type as blocked_wait_event_type,  
       blocked_activity.wait_event as blocked_wait_event,  
       blocked_activity.query    AS blocked_statement,  
       blocking_locks.pid     AS blocking_pid,  
       blocking_activity.usename AS blocking_user,  
       blocking_activity.client_addr as blocking_user_addr,  
       blocking_activity.client_hostname as blocking_client_hostname,  
       blocking_activity.client_port as blocking_client_port,  
       blocking_activity.application_name as blocking_application_name,  
       blocking_activity.wait_event_type as blocking_wait_event_type,  
       blocking_activity.wait_event as blocking_wait_event,  
       blocking_activity.query   AS current_statement_in_blocking_process  
 FROM  pg_catalog.pg_locks         blocked_locks  
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid  
    JOIN pg_catalog.pg_locks         blocking_locks   
        ON blocking_locks.locktype = blocked_locks.locktype  
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE  
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation  
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page  
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple  
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid  
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid  
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid  
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid  
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid  
        AND blocking_locks.pid != blocked_locks.pid  
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid  
   WHERE NOT blocked_locks.granted ORDER BY blocked_activity.pid;

To identify which sessions block transactions, review the blocking_user, blocking_user_addr, and blocking_client_port. In the following example output, the master user uses psql to block the transaction running on the 27.0.3.146 host. The blocking_pid is 8740.

blocked_pid                           | 9069blocked_user                          | master  
blocked_client_addr                   | 27.0.3.146  
blocked_client_hostname               |  
blocked_client_port                   | 50035  
blocked_application_name              | psql  
blocked_wait_event_type               | Lock  
blocked_wait_event                    | transactionid  
blocked_statement                     | UPDATE test_tbl SET name = 'Jane Doe' WHERE id = 1;  
blocking_pid                          | 8740  
blocking_user                         | master  
blocking_user_addr                    | 27.0.3.146  
blocking_client_hostname              |  
blocking_client_port                  | 26259  
blocking_application_name             | psql  
blocking_wait_event_type              | Client  
blocking_wait_event                   | ClientRead  
current_statement_in_blocking_process | UPDATE tset_tbl SET name = 'John Doe' WHERE id = 1;

Stop the transactions

Before you terminate transactions, evaluate the potential effect that each transaction has on the state of your database and your application.

To stop the transactions, run the following statement:

SELECT pg_terminate_backend(PID);

Note: Replace PID with the transactions blocking_pid.

Related information

Amazon Aurora PostgreSQL wait events