Will the pg_stat_statements view show old data if there is no transaction going on in the database?

Question

I am trying to write a query using pg_stat_statements to find the elapsed time of queries running in the database. I want to know if there are no transactions in the database will the view show only the old data ? When will the data get purged from the view ? I want only those queries which was running in the past few minutes(suggest me an alternate view or any method to achieve this)....

Thanks in advance....

Answer 1

The statistics collected by and shown in pg_stat_statements are cumulative, that is, you see the sum of everything that happened since you installed it or since you last called pg_stat_statements_reset() to reset the statistics.

To get meaningful data from pg_stat_statements, either regularly reset the statistics, or (better) use a monitoring program like pgWatch2 that regularly takes a snapshot of the data. By calculating the difference between such snapshots you can get data about the workload in that time interval.