PostgreSQL – How to Ensure pg_stat_activity Shows All Queries

postgresqlstatistics

I am working with pg_stat_activity in postgres. It is working and I can see queries in this view after clicking the play button in datagrip. However, some queries I definitely executed (see screenshot, and look at the first query) are not shown at all. I need statistics of them (so really all my queries). How to ensure pg_stat_activity shows all my queries? Do I need to add additional settings?

Best Answer

pg_stat_activity is not an auditing facility. It only shows one query per connected session. Either the query which is currently executing, or if the connection is idle than the immediately prior query.

If you want to see historical queries, use a different tool. Maybe pg_stat_statements, or log_statement=all. Or any of a large number of third-party tools.

Related Solutions

Postgresql – Connect to Postgresql 9.2 from Eclipse Data Tools Platform

The (initial) database name has been taken from the JDBC URL: "jdbc:postgresql://localhost:5432/database"

Try taking off the "database" suffix from that URL

PostgreSQL – Understanding pg_stat_activity Showing COMMIT

After further diagnostics and some Googling, we came across this article that described many of the same symtoms we were experiencing. The root cause of their problem (and from what we can tell, ours too) was related to the Transparent Huge Pages implementation.

After disabling Transparent Huge Pages with this command:

echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled

The problem appears to have been resolved. We've been running under an increased workload for the last two weeks and the issue has not resurfaced. The system's contexts and interrupts are consistently 1/10th of what they had been and average system time has decreased as well.

Not sure if it is the solution for everyone, but I post it here as a possible cause in case it can help anyone else resolve a similar issue.

Best Answer

Related Solutions

Postgresql – Connect to Postgresql 9.2 from Eclipse Data Tools Platform

PostgreSQL – Understanding pg_stat_activity Showing COMMIT

Related Question