Optimize query statistics collection on an Azure Database for PostgreSQL - Single Server
APPLIES TO: 
Azure Database for PostgreSQL - Single Server
Important
Azure Database for PostgreSQL - Single Server is on the retirement path. We strongly recommend that you upgrade to Azure Database for PostgreSQL - Flexible Server. For more information about migrating to Azure Database for PostgreSQL - Flexible Server, see What's happening to Azure Database for PostgreSQL Single Server?.
This article describes how to optimize query statistics collection on an Azure Database for PostgreSQL server.
Use pg_stat_statements
Pg_stat_statements is a PostgreSQL extension that can be enabled in Azure Database for PostgreSQL. The extension provides a means to track execution statistics for all SQL statements executed by a server. This module hooks into every query execution and comes with a non-trivial performance cost. Enabling pg_stat_statements forces query text writes to files on disk.
If you have unique queries with long query text or you don't actively monitor pg_stat_statements, disable pg_stat_statements for best performance. To do so, change the setting to pg_stat_statements.track = NONE.
Some customer workloads have seen up to a 50 percent performance improvement when pg_stat_statements is disabled. The tradeoff you make when you disable pg_stat_statements is the inability to troubleshoot performance issues.
To set pg_stat_statements.track = NONE:
In the Azure portal, go to the PostgreSQL resource management page and select the Server parameters blade.
Use the Azure CLI az postgres server configuration set to
--name pg_stat_statements.track --resource-group myresourcegroup --server mydemoserver --value NONE.
Use the Query Store
The Query Store feature in Azure Database for PostgreSQL provides a more effective method to track query statistics. We recommend this feature as an alternative to using pg_stat_statements.
Next steps
Consider setting pg_stat_statements.track = NONE in the Azure portal or by using the Azure CLI.
For more information, see: