Inspecting pg_stat_activity

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 query 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 I find useful to spot problems as quickly as possible.

Querying pg_stat_activity

The following...