Identify Idle PostgreSQL Connections: Keep Your Database Clean

This blog post equips you with the knowledge to identify and potentially terminate stale connections within your PostgreSQL database. We'll delve into the provided SQL code, explain its purpose, and offer valuable insights for maintaining optimal database performance.

Sample Code from Command Line

 1SELECT
 2  rank() over (partition by client_addr order by backend_start ASC) as rank,
 3  pid,
 4  backend_start,
 5  query_start,
 6  state_change,
 7  datname,
 8  usename,
 9  client_addr
10FROM
11  pg_stat_activity
12WHERE
13  -- don't kill yourself
14  pid <> pg_backend_pid()
15  --  AND
16  -- don't kill your admin tools
17  --application_name !~ '(?:psql)|(?:pgAdmin.+)'
18  --  AND
19  --usename not in ('postgres')
20    AND
21  query in ('')
22    AND
23  (
24    (current_timestamp - query_start) > interval '10 minutes'
25      OR
26    (query_start IS NULL AND (current_timestamp - backend_start) > interval '10 minutes')
27  )
28;

Notes List PostgreSQL stale connections that have been idle > 10 mins. Can be used to kill connections using the pids returned, eg. ( SELECT pg_terminate_backend(pid))

Purpose:

The presented code serves as a valuable tool for identifying PostgreSQL connections that have been inactive for an extended period (default: 10 minutes). Stale connections can consume resources and potentially impact database performance. This code allows you to:

• Pinpoint Idle Connections: By filtering based on specific criteria, you can effectively locate connections that haven't executed any queries for a set timeframe.
• Optimize Resource Allocation: Terminating stale connections (with caution) frees up resources like memory and CPU cycles, potentially improving overall database performance.
• Enhance Database Health: Regularly identifying and addressing idle connections contributes to a more efficient and healthy database environment.

Code Breakdown:

Let's dissect the code line by line:

• SELECT: This clause retrieves specific data from the pg_stat_activity view, which provides information about current PostgreSQL connections.
• Selected Columns:
  • rank(): Assigns a ranking based on client address and connection start time.
  • pid: Process ID (PID) of the backend connection.
  • backend_start: Timestamp of the connection's start time.
  • query_start: Timestamp of the last query start time for the connection (can be null).
  • state_change: Timestamp of the last state change for the connection.
  • datname: Name of the database the connection is using (can be null).
  • usename: Username associated with the connection.
  • client_addr: Client IP address that initiated the connection.
• FROM pg_stat_activity: Specifies the data source as the pg_stat_activity view.
• WHERE: This clause filters the results based on specific conditions:
  • pid <> pg_backend_pid(): Excludes the current connection (avoids killing itself).
  • -- Commented out sections: These commented sections provide options for excluding specific connection types (e.g., admin tools, specific usernames) based on your needs. Uncomment and modify as needed.
  • query in (''): Filters for connections with an empty query string, indicating inactivity.
  • (current_timestamp - query_start) > interval '10 minutes' OR ...: Identifies connections where either:
    • The time difference between the current timestamp and query_start is greater than 10 minutes (indicating no recent query execution).
    • query_start is null (no recent query) AND the time difference between the current timestamp and backend_start is greater than 10 minutes (connection has been idle for more than 10 minutes).

Key Points and Insights:

• This code offers a way to identify potentially problematic stale connections in your PostgreSQL database.
• Caution: Terminating connections can disrupt ongoing user sessions. Analyze the results thoroughly before terminating connections.
• Consider uncommenting the sections to exclude specific connections (e.g., admin tools) based on your environment.
• Adjust the 10 minutes threshold as needed to suit your specific requirements for identifying idle connections.

Conclusion By understanding and implementing this code with caution, you can effectively manage idle connections and contribute to a well-performing PostgreSQL database.

References:

PostgreSQL documentation on pg_stat_activity view: https://www.postgresqltutorial.com/postgresql-date-functions/postgresql-current_timestamp/

Source:

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_idle_sessions.sql