Once the I/O system has been properly inspected, it makes sense to stop for a minute and see whether there are major conflicts going on in the system:

test=# \d pg_stat_database_conflicts
View "pg_catalog.pg_stat_database_conflicts"
      Column      |  Type  | Modifiers 
------------------+--------+-----------
 datid            | oid    | 
 datname          | name   | 
 confl_tablespace | bigint | 
 confl_lock       | bigint | 
 confl_snapshot   | bigint | 
 confl_bufferpin  | bigint | 
 confl_deadlock   | bigint | 

The pg_stat_database_conflicts view informs us about conflicts happening in the system. The view actually lists quite a number of different conflicts. However, not all of them are actually relevant. The most important of them are locking conflicts, snapshot conflicts, and deadlocks. A locking conflict happens if one transaction is waiting for another transaction to complete. A snapshot conflict can happen when a transaction is relying on a snapshot that...