Edit

Optimize query statistics collection on an Azure Database for PostgreSQL - Single Server

  • Article

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:

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: