Enabling pg_stat_statements on Azure PostgreSQL Flexible Server Across Databases

If you’re working with Azure Database for PostgreSQL – Flexible Server, you might already be familiar with the powerful PostgreSQL extension pg_stat_statements. It allows you to track execution statistics of all SQL statements executed by a server, making it a crucial tool for performance monitoring and query optimization.
However, unlike self-managed PostgreSQL, Azure’s managed service adds a layer of security and control. This means certain extensions, including pg_stat_statements, must be explicitly allowed before they can be used — especially if you want to enable them in multiple databases.
Let’s walk through the process of enabling pg_stat_statements correctly on Azure’s Flexible Server offering.

🔍 What Is pg_stat_statements?
pg_stat_statements is a PostgreSQL extension that provides a means to track execution statistics of SQL statements. With it, you can:
• See the most expensive queries
• Identify frequently executed statements
• Analyze query planning and execution time
• Optimize your workload based on real usage patterns
But before you can use it on Azure Flexible Server, you must allow it through the server configuration.

⚙️ Azure's Controlled Extension Model
On Azure PostgreSQL Flexible Server, extensions can only be enabled if they're included in the azure.extensions server parameter. This list acts as a safeguard to prevent unauthorized or unsupported extensions from being used.
So, trying to directly run:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
…without allowing it first will result in an error like:
ERROR: permission denied to create extension "pg_stat_statements"
HINT: Must be superuser to create this extension.

✅ Step-by-Step: How to Allow and Use pg_stat_statements

1. Add pg_stat_statements to the Allowed Extensions Follow the steps in Microsoft’s official documentation to allow an extension: 👉 How to allow extensions in Azure PostgreSQL Using Azure Portal: • Navigate to your Azure Database for PostgreSQL Flexible Server • Go to Server Parameters • Search for azure.extensions • Add pg_stat_statements to the comma-separated list (e.g., pg_stat_statements,hstore) • Click Save

This makes the extension available to all databases in the server.

1. Load the Extension in Your Target Database(s) Now that the extension is allowed, you can create it in any database on that server: CREATE EXTENSION IF NOT EXISTS pg_stat_statements; Repeat this for each database where you want to collect query statistics.

📈 Bonus: Viewing the Statistics
Once the extension is active and your server has collected some traffic, you can query the stats like this:
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
This gives you the top 10 slowest queries by total execution time. Extremely useful for identifying bottlenecks.

🧼 Clean Up
If you no longer need pg_stat_statements, you can drop it from a database:
DROP EXTENSION IF EXISTS pg_stat_statements;
You can also remove it from azure.extensions if you want to revoke the ability to enable it server-wide.

🔐 Final Thoughts
Azure Database for PostgreSQL – Flexible Server gives you the power of PostgreSQL while maintaining security and performance controls. Just remember:
• Allow extensions explicitly via azure.extensions
• Create the extension in each database where needed
• Monitor your queries effectively
Using pg_stat_statements properly can significantly improve your visibility into database performance, helping you deliver faster and more efficient applications.

Have you enabled pg_stat_statements on Azure yet? Share your experience or tips in the comments!