Optimize query statistics collection on Azure Database for PostgreSQL - Flexible Server

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

This article describes how to optimize query statistics collection on an Azure Database for PostgreSQL flexible server instance.

Use pg_stat_statements

Pg_stat_statements is a PostgreSQL extension that can be enabled in Azure Database for PostgreSQL flexible server. 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.

To set pg_stat_statements.track = NONE:

Use the Query Store

Using the Query Store feature in Azure Database for PostgreSQL flexible server offers a different way to monitor query execution statistics. To prevent performance overhead, it is recommended to utilize only one mechanism, either the pg_stat_statements extension or the Query Store.

Next steps