Managing connections in Microsoft Azure Database for PostgreSQL is a topic that seems to come up several times in conversations with our customers. The connections in Postgres aren’t free each connection, whether idle or active, consumes a certain overhead of memory (10MB per connection). Idle is something that grabs connection from your application and holds it. Application connection poolers often also consume one or more idle connections.
In Azure Database for PostgreSQL, you can use various ways, for example using Postgres metadata and Azure Monitor, to better track what is going through your database and take proactive steps accordingly.
Using Postgres metadata
Under the covers, Postgres has a lot of metadata about both historical and current activity against a system. Within Postgres, you can run the following query to get a few results:
SELECT count(*),state FROM pg_stat_activity GROUP BY 2;
count | state
-------+------------------------------
7 | active
32 | idle
22 | idle in transaction
4 | idle in transaction(aborted)
(4 rows)
Each of the above output is useful in determining what you should do to better manage your connection count. All these numbers can be useful to record every say 30 seconds and chart on your own internal monitoring.
Let's break down each:
- active – Identifies currently running queries; in a sense, this is truly how many connections you may require at a time
- idle – Identifies connections opened to the DB (most frameworks do this and maintain a pool of connections), that are not in use. This is the one area in which a connection pooler such as PgBouncer can help most.
- idle in transaction – Identifies connections against which your app has run a BEGIN but it is now waiting somewhere in a transaction and not doing work.
- idle in transaction (aborted) – Identifies connections that were idle in the transaction that have since been aborted.
You should monitor idle connections, and if you see a high count here it’s worth investing in setting up a PgBouncer. You can read more about setting up PgBouncer in azure database for PostgreSQL here. Connections that are idle in transaction are a bit more interesting. For these, what you likely want to do when first investigating is to determine the age of the connections. You can do this by querying pg_stat_activity and filtering for connections that are idle in transaction, and then checking the age of those queries. For ones that have been running too long, you may want to end them manually.
If you find that you have some stale transactions hanging around for days, hours, or even just a few minutes, you may want to set a default to end those transactions. To help with this, Postgres has a nice feature of a statement_timeout. A statement timeout will automatically end queries that run longer than the allotted time. You can set this at both a global level and for a specific session.
To do this at the database level, run the following command:
alter database dbnamehere set statement_timeout = 60000;
The command above sets the statement_timeout to 60 s.
To do so during a given session, simply run the following command:
set statement_timeout = 6000000;
For idle in transaction connections that have been running too long, using Postgres 9.6 or higher, you can take advantage of a dedicated setting (idle_in_transaction_session_timeout), which you configure in a similar fashion.
Configuring both the statement_timeout and idle_in_transaction_session_timeout settings will help with cancelling long running queries and transactions.
Tracking and managing through Azure Monitor
You can receive an alert based on monitoring metrics for your Azure Database for PostgreSQL server. The alert triggers when the value of a specified metric exceeds a threshold you assign. The alert triggers both when the condition first exists and then afterwards, when the condition has abated.
You can configure an alert to trigger and perform the following actions:
- Send email notifications to the service administrator and co-administrators.
- Send email to additional email addresses you specify.
- Call a webhook.
You can configure and get information about alert rules using:
For more detail on creating alerts on metrics in Azure Database for PostgreSQL, see the article here.
Combining Postgres metadata checks with Azure Monitor alerts should lead to a much healthier performing database and would protect you from unwanted surprises.
If you have trouble setting up any extension supported by Azure Database for PostgreSQL, please let us know by contacting us at AskAzureDBforPostgreSQL@service.microsoft.com!
Thank you!
Craig Kerstiens
Principal PM Manager – Microsoft
Amol Bhatnagar
Program Manager - Microsoft
