title: pathological spike in postgresql load average after large delete
body: <p>I ran two deletes on a PostgreSQL 9.3.12 database against a fairly large table.  Each one required a table scan and took about ~10 minutes to complete.</p>
  
  <p>While they were running clients weren't impacted.  Disk I/O was high, upwards of 70%, but that's fine.</p>
  
  <p>After the second delete finished Disk I/O went to near zero and Load Average shot through the roof.  Requests were not being completed in a timely manner and since new requests continued to arrive they all stacked up.</p>
  
  <p>My two theories are:</p>
  
  <ol>
  <li><p>Something with the underlying I/O layer that caused all I/O requests to block for some period of time, or</p></li>
  <li><p>Postgres acquired (and held for a non-trivial period of time) a lock needed by clients.  Either a global one or one related to the table from which rows were deleted.  This table is frequently inserted into by clients; if someone were holding a lock that blocked inserts it would definitely explain this behavior.</p></li>
  </ol>
  
  <p>Any ideas?  Load was in excess of 40, which never happens in our environment even during periods of heavy load.  Network I/O was high during/after the deletes but only because they were being streamed to our replication server.</p>
  
  <p><a href="https://i.stack.imgur.com/JL9nb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JL9nb.png" alt="CPU usage"></a></p>
  
  <p><a href="https://i.stack.imgur.com/PKYIY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PKYIY.png" alt="Disk and Network I/O"></a></p>
  
  <p><a href="https://i.stack.imgur.com/SBElc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SBElc.png" alt="Load Average"></a></p>