Table lock waits

Clients waiting to acquire a lock on an entire table is something you should aim to avoid. The following will show you when this is happening:

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,
  relname
FROM
  pg_locks locked
    LEFT OUTER JOIN pg_class ON (locked.relation = pg_class.oid),
  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.procpid AND
  locker.pid=locker_act.procpid AND
  locked.relation=locker.relation;

Output from this query looks similar to the previous transaction id examples:

locked_pid         | 12474
locker_pid         | 12247
locked_user        | postgres
locker_user        | postgres
virtualtransaction | 2/2588881
transactionid      | 
relname            | pgbench_accounts

You really shouldn...