Connections

Active and idle database connections

The Insights Connections page displays all currently active and idle connections in the database cluster. Active connections are in a session that is currently connected to the database and is executing a query or waiting to execute one.

Idle connections are common and they aren't inherently a problem, but they can become an issue depending on your workload and configuration. Idle connections consume memory, so a large number of them can lead to excessive memory usage. High idle connections is typically an indication that the database would benefit from pgBouncer connection pooling.

To see the active and idle connections in Postgres, query the pg_stat_activity table:

SELECT * FROM pg_stat_activity GROUP BY state;

Each running session has a pid which is the process id - a unique identifier assigned to each active backend connection.

To cancel a connection, query, or process but leave the session open use this statement:

SELECT pg_cancel_backend(<pid>);

A more forceful action, which will close the connection and roll back any transactions, is:

SELECT pg_terminate_backend(<pid>);

Connection metrics

Shows the number of connected Postgres clients over time. The Y axis uses your existing setting for max_connections. This can be altered by updating the max_connections parameters.

Crunchy Bridge offers built in connection pooling. See Your Guide to Connection Management in Postgres on our blog for more details.

pgBouncer metrics

If you have enabled pgBouncer, several additional metrics graphs are available to show insight on system load and performance.

  • Client connections shows the number of application connections that PgBouncer manages and pools before they are forwarded to the Postgres database. This includes both active connections and ones waiting.

  • Server connections shows the number of active and idle connections from PgBouncer to the Postgres server.

  • Performance displays the average time in seconds that it takes for queries to be executed and the wait time before queries can be served by the database.

  • Network usage shows the amount of data being transferred over the network per query.

Performance indicators from pgBouncer metrics

pgbouncer_avg_query_time shows the average time in seconds that it takes for queries to be executed by the Postgres database.

pgbouncer_maxwait shows the maximum time in seconds a client connection waited in the PgBouncer queue before being served by the database.

If both metrics are high, it indicates that your database and connection pooler are experiencing significant load. Resource allocation of either the max_connections or server may need to be reviewed.

If avg_query_time is high but maxwait is low, focus on optimizing query performance. You might have some queries that need refinement.

If maxwait is high but avg_query_time is low, review the max_connections setting.