Checking for memory and I/O

Once you are done finding missing indexes, you can inspect memory and I/O. To figure out what is going on, it makes sense to activate track_io_timing. If it is on, PostgreSQL will collect information about disk wait and present it to you.

Often the main question asked by a customer is: if we add more disks, is it going to be faster? It is possible to guess what will happen, but in general, measuring is the better and more useful strategy. Enabling track_io_timing will help you to gather the data to really figure it out.

PostgreSQL exposes disk wait in various ways. One way to inspect things is to take a look at pg_stat_database:

test=# d pg_stat_database 
          View "pg_catalog.pg_stat_database" 
     Column     |           Type           | Modifiers  
----------------+--------------------------+----------- 
 datid          | oid                      |  
 datname        | name...