Re: pg_stat_statements -- Historical Query

On Thu, Aug 10, 2017 at 3:30 AM, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:

> On Thu, Aug 10, 2017 at 6:41 AM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
> > On Thu, Aug 10, 2017 at 6:23 AM, anand086 <anand086(at)gmail(dot)com> wrote:
> >> I was looking for a way to maintain historical query details in
> Postgres to
> >> answer questions like
> >>
> >> What was the sql call rate between time X and Y?
> >> Did the execution count increase for the query increase between time X
> and
> >> Y?
> >> In past 10mins what all queries were run in the db?
> >>
> >> and few others like this.
> >>
> >> What would be best way to do it? Any thoughts?
> >
> > pg_stat_statements has a function allowing to reset what the view
> > pg_stat_statements holds as information. You could copy periodically
> > the data of pg_stat_statements and then invoke
> > pg_stat_statements_reset to put everything back to zero. Then you
> > would just need to do your analysis work based on the amount of data
> > copied into your custom table.
>
> You can also use powa-archivist extension which does the aggregation,
> data retention and so on with a bgworker:
> https://github.com/dalibo/powa-archivist.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

*If you are interested in historical stats, you would probably fair a lot
better with PgBadger. It is free*
*and highly customizable. In addition to SQL call rates at different
times, it provides analysis of*

*most used queries, slowest queries, etc.*

*https://sourceforge.net/projects/pgbadger/
<https://sourceforge.net/projects/pgbadger/>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.