How to effectively manage and terminate old PostgreSQL connections

If you work with high-traffic applications that use PostgreSQL, you have probably encountered the problem of “hanging” or idle connections. This is a common scenario that can lead to serious performance issues and even exhaustion of available database resources. In this article, we will explore how to identify and safely terminate these old connections, and even how to automate this process.

Why are old connections a problem?

Every active connection to a PostgreSQL database, even if it is idle, consumes memory and server resources. In environments where applications frequently open new connections (e.g., in serverless architecture, scripts, or with an improperly configured connection pool), the number of these idle sessions can quickly grow. This can lead to reaching the max_connections limit, which will prevent new, legitimate processes from connecting to the database. In short, your application will stop working. Therefore, proactive connection management is critical to system stability.

Identifying old connections

Before we start closing anything, we first need to identify which connections are candidates for removal. PostgreSQL provides a fantastic tool for this purpose – the pg_stat_activity system view. It allows you to view all active server processes.
To find connections that have been idle for a long time, we can use the following SQL query. It searches for sessions in the idle, idle in transaction, or idle in transaction (aborted) states and sorts them by duration.

-- Find idle connections and their age
SELECT
  pid,
  usename,
  client_addr,
  state,
  query,
  age(clock_timestamp(), backend_start) AS connection_age
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction', 'idle in transaction (aborted)')
  -- You can optionally filter by a specific database name
  -- AND datname = 'your_database_name'
  AND age(clock_timestamp(), backend_start) > interval '15 minutes'
ORDER BY connection_age DESC;

In the above query:

  • pid is the process ID we will need to close the connection.
  • usename is the name of the user who established the connection.
  • state shows the current status of the connection.
  • connection_age calculates how long the connection has been active.
  • I added the condition age(...) > interval ‘15 minutes’ to focus only on connections older than 15 minutes. You can adjust this interval to suit your needs.

Safely closing connections

Once you have identified the process (PID) you want to terminate, you can use the pg_terminate_backend() function. This is a safer method than pg_cancel_backend() because pg_terminate_backend() immediately terminates the entire session, while pg_cancel_backend() only cancels the current query, leaving the session active. To terminate a connection with a specific PID, execute:

SELECT pg_terminate_backend(12345); -- Replace 12345 with the actual PID

Remember to execute with caution. Ensure that you do not shut down critical processes, such as replication processes or background tasks, which may appear to be idle but are actually waiting to perform important work.

Summary

Managing idle connections in PostgreSQL is a critical task for maintaining a healthy and performant database. By using the pg_stat_activity view, you can easily identify long-running idle sessions. The pg_terminate_backend() function provides a safe way to close them.

Happy terminating!