How to debug ShareLock in Postgres : <p>I am seeing quite a few occurrences of the following in my Postgres server log:</p>

<pre><code>LOG:  process x still waiting for ShareLock on transaction y after 1000.109 ms 
DETAIL:  Process holding the lock: z. Wait queue: x.
CONTEXT:  while inserting index tuple (a,b) in relation "my_test_table"
    SQL function "my_test_function" statement 1 
...
LOG:  process x acquired ShareLock on transaction y after 1013.664 ms
CONTEXT:  while inserting index tuple (a,b) in relation "my_test_table"
</code></pre>

<p>I am running Postgres <strong>9.5.3</strong>. In addition I am running on Heroku so I don't have access to the fine grained superuser-only debugging tools.</p>

<p>I am wondering how best to debug such an issue given these constraints and the fact each individual lock is relatively transient (generally 1000-2000ms).</p>

<p>Things I have tried:</p>

<ul>
<li>Monitoring <a href="https://www.postgresql.org/docs/9.5/static/view-pg-locks.html" rel="noreferrer"><code>pg_locks</code></a> (and joining to <a href="https://www.postgresql.org/docs/9.5/static/catalog-pg-class.html" rel="noreferrer"><code>pg_class</code></a> for context).</li>
<li>Investigating <a href="https://www.postgresql.org/docs/9.5/static/pageinspect.html" rel="noreferrer"><code>pageinspect</code></a>.</li>
<li>Replicating locally both by hand and with <a href="https://www.postgresql.org/docs/9.5/static/pgbench.html" rel="noreferrer"><code>pgbench</code></a> where I do have superuser perms. I have so far been unable to replicate the issue locally (I <em>suspect</em> due to having a much smaller data set but I can't be sure).</li>
</ul>

<p>It is worth noting that CPU utilisation appears high (load average of >1) when I see these issues so it's possible there is nothing wrong with the above per se and that I'm seeing it as a consequence of insufficient system resources being available. I would still like to understand how best to debug it though so I can understand what exactly is happening.</p>
