Resetting pg_stat_statements statistics in Azure Database for PostgreSQL - Flexible Server

Question

How do I reset the pg_stat_statements statistics in Azure Database for PostgreSQL - Flexible Server?

Postgres has a pg_stat_statements_reset() function which can be used but I am not able to execute this function using the server admin account.

select pg_stat_statements_reset();
ERROR:  permission denied for function pg_stat_statements_reset

I am also unable to grant myself the access privilege to execute the reset function either:

GRANT EXECUTE ON FUNCTION pg_stat_statements_reset TO psqladmin;
ERROR:  permission denied for function pg_stat_statements_reset

Is there another way to reset the statistics within Azure or a way to allow a user to execute the pg_stat_statements_reset function? This makes debugging problem queries hard because I'm not able to see the effects of potential fixes.

Answer 1

@Jeff Chan
Thank you for the question and for using Microsoft Q&A platform.

In Azure Database for PostgreSQL - Flexible Server, the pg_stat_statements_reset() function requires superuser privileges, which are not available to server admin accounts for security reasons.

However, there are a few alternative approaches you can consider:

1) Use the Query Store: Azure Database for PostgreSQL - Flexible Server offers the Query Store feature, which can be used to monitor query performance and statistics. This feature does not require superuser privileges and can be a good alternative to pg_stat_statements. You can enable and configure the Query Store through the Azure portal.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-optimize-query-stats-collection#use-the-query-store
https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-query-store

2. You can also try granting the access by a super user.
   The documentation https://www.postgresql.org/docs/current/pgstatstatements.html says that

pg_stat_statements_reset discards statistics gathered so far by pg_stat_statements corresponding to the specified userid, dbid and queryid. If any of the parameters are not specified, the default value 0(invalid) is used for each of them and the statistics that match with other parameters will be reset. If no parameter is specified or all the specified parameters are 0(invalid), it will discard all statistics. By default, this function can only be executed by superusers. Access may be granted to others using GRANT.

The below code should work if executed by a DBA or a super user:

GRANT EXECUTE ON FUNCTION pg_stat_statements_reset TO user;

3. You can also use below code if it works:

SELECT pghero.pg_stat_statements_reset();

You can refer to the below links for more help:  

https://www.postgresql.org/docs/current/pgstatstatements.html  

https://stackoverflow.com/questions/65068446/pg-stat-statements-reset-schedule  

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-extensions#pg_stat_statements  

https://github.com/ankane/pghero/issues/102  

Hope this helps. Please let us know if you have any further questions.