On Thu, Aug 10, 2017 at 3:30 AM, Julien Rouhaud <rjuju...@gmail.com> wrote:
> On Thu, Aug 10, 2017 at 6:41 AM, Michael Paquier > <michael.paqu...@gmail.com> wrote: > > On Thu, Aug 10, 2017 at 6:23 AM, anand086 <anand...@gmail.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@postgresql.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.