Transaction lock waits

Locking performance issues will often be evident by an excess of clients that are waiting for a lock to be granted. If you join two pg_locks entries together with a matching pair of pg_stat_activity ones, it's possible to find out information about both the locker process that currently holds the lock, and the locked one stuck waiting for it:

    SELECT 
      locked.pid AS locked_pid, 
      locker.pid AS locker_pid, 
      locked_act.usename AS locked_user, 
      locker_act.usename AS locker_user, 
      locked.virtualtransaction, 
      locked.transactionid, 
      locked.locktype 
    FROM 
      pg_locks locked, 
      pg_locks locker, 
      pg_stat_activity locked_act, 
      pg_stat_activity locker_act 
    WHERE 
      locker.granted=true AND 
      locked.granted=false AND 
      locked.pid=locked_act.pid AND 
      locker.pid=locker_act.pid AND 
      (locked.virtualtransaction=locker.virtualtransaction OR 
      locked.transactionid=locker.transactionid); 

This variation looks for and provides additional information about transaction ID lock waits like this:

    locked_pid         | 11578 
    locker_pid         | 11578 
    locked_user        | postgres 
    locker_user        | postgres 
    virtualtransaction | 2/2580206 
    transactionid      | 534343 
    locktype           | transactionid 

These will also show up for virtual transactions, as mentioned before:

    locked_pid         | 11580 
    locker_pid         | 11580 
    locked_user        | postgres 
    locker_user        | postgres 
    virtualtransaction | 4/2562729 
    transactionid      |  
    locktype           | tuple 

The preceding examples aren't necessarily representative of common situations; note that the pid is actually the same in each case, and this lock sequence is due to how the pgbench transactions execute. You will likely want to display more of the information available when analyzing locks like this, such as showing enough of the query text for each activity row to see what is happening.