Killing Idle connections in azure database for postgresql

Hi @sql user , welcome to Microsoft Q&A forum.

You can run below query to see the count of total connections state-wise:

SELECT count(*),state FROM pg_stat_activity GROUP BY 2;  

If the count is greater than 600 overall, you can run below query to clean up the idle connections:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'postgres' AND pid <> pg_backend_pid() AND state in ('idle');  

Please note the datname is the database where we want to check the query.

Please let us know if this helps or else we can discuss further on the same.

Referenced Article:
https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/connection-handling-best-practice-with-postgresql/ba-p/790883
https://stackoverflow.com/questions/30931683/how-to-kill-the-idle-connection-in-postgresql

----------

If answer helps, you can mark it as 'Accept Answer'

@sql user You can configure the alert to invoke an Azure function whenever it is triggered.

Whenever the active connections exceed 600, the alert is fired which in turn triggers the Azure function.
You can write your pgsql commands in the azure function to kill the connections.
A code sample to help you get started is here

Create a new Alert rule from the Monitoring blade

Provide a threshold value for the alert.

Create a new Action group and select the Action type as "Azure Function"

Do let us know if you face any issues.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.