Inspecting pg_stat_activity
Firstly, let’s check the content of pg_stat_statements, and answer the following questions:
- How many concurrent queries are currently being 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 us an idea of what is happening in 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 that actually run on the server. Therefore, a good overview of the system, as 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 I find useful for spotting...
