What is the timeframe for pg_stat_statements

Question

I would like to know for "pg_stat_statements" view in postgres. What is the timeframe for the data? Does it shows query executed in last 24 hours or overall queries executed? As the table doesn't contain any timestamp.

Answer 1

The timeframe of the view provided by pg_stat_statements is from either the last reset (pg_stat_statements_reset) or the time the extension was created, which may be a very long time.

There is logic to expire infrequent statements if the max threshold is reached (5000 on recent Postgres versions), which means that you may not see the full activity if you query the view infrequently.

To work better with this data, you essentially have two options:

1) Call pg_stat_statements_reset() every 24 hours, which means that the query data will only reflect recent activity (ideally you'd keep track of when the reset happened, so you can figure out number of calls per minute, etc)

2) Use a separate monitoring tool that takes snapshots and can keep historic pg_stat_statements statistics

Which one you go with depends on your requirements, though I would usually go with (2) for production systems.

Disclaimer: I'm the author of pganalyze (https://pganalyze.com), a hosted Postgres monitoring tool that also provides historic pg_stat_statement statistics.

Answer 2

There is no timeframe only a maximum number of statements tracked.

F.28.3. Configuration Parameters

pg_stat_statements.max (integer)

pg_stat_statements.max is the maximum number of statements tracked by the module (i.e., the maximum number of rows in the pg_stat_statements view). If more distinct statements than that are observed, information about the least-executed statements is discarded. The default value is 1000. This parameter can only be set at server start.