Checking the overall database behavior

The first thing to do when approaching a system is to check the overall health and see what is currently going on in the system. Is the system using too much CPU? Or maybe, too much memory? Is the disk I/O fine?

Checking pg_stat_activity

The best place to start is pg_stat_activity, a system view listing open database connections. Since PostgreSQL 9.2, pg_stat_statement has been extended a little, and a lot more information such as the state of a database connection can be seen:

test=# \d pg_stat_activity
           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers 
------------------+-------------------------------------
 datid            | oid                      | 
 datname          | name                     | 
 pid              | integer                  | 
 usesysid         | oid                      | 
 usename          | name                     | 
 application_name | text                     | 
 client_addr...