Slow Queries on Azure PostgreSQL Flexible Server

Question

We want to log all slow queries and CPU utilized queries on Azure PostgreSQL Flexible Server. Is there a way to log slow queries and CPU utilized queries?

Also, when we enable the diagnostic settings for PostgreSQL logs and then after 2 hours if we remove diagnostic settings configuration, can we see the logs even after removing diagnostics settings? like below query results the data even after deleting diagnostics settings?

AzureDiagnostics

| where Resource == "myservername"

| where Category == "PostgreSQLLogs"

| where TimeGenerated > ago(2h)

Answer 1

Hi @Naveen Kumar Kakarla (Quadrant Resource LLC)

If I understand correctly you are asking about logging slow queries and CPU-utilized queries on Azure PostgreSQL Flexible Server. and also want to know if you can see the logs even after removing the diagnostic settings

Yes, you can log slow queries and CPU-utilized queries on Azure PostgreSQL Flexible Server

To log slow queries, you can enable the log_min_duration_statement parameter in your PostgreSQL configuration. This parameter logs all SQL statements that take longer than the specified number of milliseconds to execute__.__ You can set this parameter to a value that suits your needs.

For example, to log all statements that take longer than 100 milliseconds to execute, you can set log_min_duration_statement = 100.

you can also use the pg_stat_statements extension. This extension provides a view that shows statistics about SQL statements executed by the server, including the amount of CPU time used by each statement. To use this extension, you need to enable it in your PostgreSQL configuration pg_stat_statementsconfiguration.https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/howto-configure-and-access-logs

Regarding your question about viewing logs after removing the diagnostic settings configuration, you can view the logs even after removing the diagnostic settings configuration. The logs are stored in the Azure Storage account that you specified in the diagnostic settings configuration

if you have already collected the logs and stored them in a destination such as Azure Monitor logs or a storage account, you can still access the logs even after removing the diagnostic settings configuration. In this case, you can query the logs to retrieve the data that was collected during the time when the diagnostic settings were enabled.

I hope this information helps

Regards

Geetha