Waiting for PostgreSQL 14 – pg_stat_statements: Track time at which all statistics were last reset.

On 18th of December 2020, Fujii Masao committed patch:

pg_stat_statements: Track time at which all statistics were last reset.
This commit adds "stats_reset" column into the pg_stat_statements_info
view. This column indicates the time at which all statistics in the
pg_stat_statements view were last reset.
Per discussion, this commit also changes pg_stat_statements_info code
so that "dealloc" column is reset at the same time as "stats_reset" is reset,
i.e., whenever all pg_stat_statements entries are removed, for the sake
of consistency. Previously "dealloc" was reset only when
pg_stat_statements_reset(0, 0, 0) is called and was not reset when
pg_stat_statements_reset() with non-zero value argument discards all
entries. This was confusing.
Author: Naoki Nakamichi, Yuki Seino
Reviewed-by: Yuki Seino, Kyotaro Horiguchi, Li Japin, Fujii Masao
Discussion: https://postgr.es/m/c102cf3180d0ee73c1c5a0f7f8558322@oss.nttdata.com

This is something that I've been personally waiting since pg_stat_statements appeared

You see, the great thing about PSS is that it very inexpensive, and provides lots of info. The drawback is that we don't know when given queries happened.

So, selecting from pg_stat_statements view will show you both queries that ran in the last hour, and the ones that run month ago.

Obviously, we could, always, clear the pg_stat_statements:

=$ SELECT pg_stat_statements_reset();

and then the view will contain only information since reset.

But, without keeping track of when exactly did the reset happen, we still couldn't get some things (like number of calls per second/minute). And, we still didn't really know if the last reset was 5 minutes ago, or 5 months ago.

The problem is now gone. With simple select:

=$ SELECT stats_reset FROM pg_stat_statements_info;
 2020-12-20 12:06:02.099943+01
(1 ROW)

I now know exactly when last reset happened, so I can do all the necessary calculations.

In the view (pg_stat_statements_info) there is also dealloc column:

$ SELECT * FROM pg_stat_statements_info;
 dealloc |          stats_reset          
       0 | 2020-12-20 12:06:02.099943+01
(1 ROW)

which shows how many times PSS had to remove a query from it's internal data due to the fact that too many distinct queries happened (more than set by pg_stat_statements.max).

This was long missed, and I love the fact that it will finally appear. Big thanks to all involved.

One thought on “Waiting for PostgreSQL 14 – pg_stat_statements: Track time at which all statistics were last reset.”

  1. it would be better to make it possible to clear statistics for a specific database

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.