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...
