The first thing I recommend is checking out pg_stat_statements. Answer the following questions:
How many concurrent queries are currently executed on your system?
- Do you see similar types of queries showing up in the query column all the time?
- Do you see queries that have been running for a long time?
- Are there any locks that have not been granted?
- Do you see connections from suspicious hosts?
The pg_stat_activity view should always be checked first because it will give you an idea of what is happening on the system. Of course, graphical monitoring is supposed to give you a first impression of the system. However, at the end of the day, it really boils down to the queries actually running on the server. Therefore, a good overview of the system provided by pg_stat_activity is more than vital for tracking down issues.
To make it easier for you, I have compiled a couple of queries that...