Checking for memory and I/O

Once we are done finding missing indexes, we 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 time 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 gather the data to really figure this out.

PostgreSQL exposes disk wait time 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           ...