In YSQL (Yugabyte Structured Query Language), idle sessions refer to database connections that remain open but are not actively executing queries. These sessions are typically in an “idle” state because they have completed executing a query but have not been explicitly closed by the client.
You can use the following query to check for idle sessions in YSQL:
SELECT pid, datname, usename, state, query, query_start
FROM pg_stat_activity
WHERE state = 'idle';
And to check for idle in transaction sessions:
SELECT pid, datname, usename, state, query, query_start
FROM pg_stat_activity
WHERE state = 'idle in transaction';
YugabyteDB 2025.1 will be the first version of YugabyteDB that is compatable with Postgres 15. Along with that comes the idle_session_timeout GUC which allows you to terminate any session that has been idle—waiting for a client query without an open transaction—for longer than the specified duration.
Example:
yugabyte=# SELECT split_part(version(), '-', 1) "YB Postgres Version";
YB Postgres Version
---------------------
PostgreSQL 15.2
(1 row)
yugabyte=# SHOW idle_session_timeout;
idle_session_timeout
----------------------
0
(1 row)
yugabyte=# SET idle_session_timeout = '5s';
SET
yugabyte=# SELECT pg_sleep_for('5s');
pg_sleep_for
--------------
(1 row)
yugabyte=# SELECT 1;
FATAL: terminating connection due to idle-session timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
While awaiting the general availability of YugabyteDB 2025.1, you can manage idle sessions in your current YugabyteDB environment by monitoring active sessions. Regularly inspect the pg_stat_activity system view to identify idle sessions and take appropriate action.
We can achieve this with a stored procedure that applies simple logic to identify idle sessions and then terminates them.
CREATE OR REPLACE PROCEDURE close_stale_sessions(mins INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
session_rec RECORD;
BEGIN
FOR session_rec IN
SELECT pid
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state = 'idle'
AND usename <> ''
AND usename <> 'postgres'
AND now() - state_change > (mins || ' minutes')::INTERVAL
LOOP
EXECUTE FORMAT('SELECT pg_terminate_backend(%s)', session_rec.pid);
END LOOP;
END;
$$;
Let’s give it a try!
yugabyte=# SELECT split_part(version(), '-', 1) "YB Postgres Version";
YB Postgres Version
---------------------
PostgreSQL 11.2
(1 row)
yugabyte=# CREATE USER duke WITH LOGIN;
CREATE ROLE
yugabyte=# \! ysqlsh -h $(hostname -I | awk {'print $1'}) -U duke
ysqlsh (11.2-YB-2024.2.2.0-b0)
Type "help" for help.
yugabyte=> SELECT current_user;
current_user
--------------
duke
(1 row)
yugabyte=> SELECT pg_sleep_for('1 minute');
pg_sleep_for
--------------
(1 row)
Now, in a separate session, let’s identify sessions that have been idle for more than a minute and terminate them.
yugabyte=# SELECT RANK() OVER (PARTITION BY client_addr ORDER BY backend_start ASC) AS rank,
yugabyte-# pid,
yugabyte-# backend_start,
yugabyte-# query_start,
yugabyte-# state_change,
yugabyte-# datname,
yugabyte-# usename,
yugabyte-# client_addr
yugabyte-# FROM pg_stat_activity
yugabyte-# WHERE pid <> pg_backend_pid()
yugabyte-# AND usename <> ''
yugabyte-# AND usename <> 'postgres'
yugabyte-# AND state = 'idle'
yugabyte-# AND (current_timestamp - state_change) > interval '1 minute';
rank | pid | backend_start | query_start | state_change | datname | usename | client_addr
------+---------+-------------------------------+-------------------------------+-------------------------------+----------+----------+---------------
1 | 3470834 | 2025-03-19 02:12:24.535399+00 | 2025-03-19 02:12:48.339798+00 | 2025-03-19 02:12:48.355089+00 | yugabyte | yugabyte | 74.208.207.36
2 | 3470974 | 2025-03-19 02:12:52.266777+00 | 2025-03-19 02:13:00.661533+00 | 2025-03-19 02:13:00.678202+00 | yugabyte | duke | 74.208.207.36
(2 rows)
Invoke the stored procedure to terminate the stale sessions:
yugabyte=# CALL close_stale_sessions(1); --Pass in minutes threshold
CALL
Checking for stale sessions again, we can see that none remain.
yugabyte=# SELECT RANK() OVER (PARTITION BY client_addr ORDER BY backend_start ASC) AS rank,
yugabyte-# pid,
yugabyte-# backend_start,
yugabyte-# query_start,
yugabyte-# state_change,
yugabyte-# datname,
yugabyte-# usename,
yugabyte-# client_addr
yugabyte-# FROM pg_stat_activity
yugabyte-# WHERE pid <> pg_backend_pid()
yugabyte-# AND usename <> ''
yugabyte-# AND usename <> 'postgres'
yugabyte-# AND state = 'idle'
yugabyte-# AND (current_timestamp - state_change) > interval '1 minute';
rank | pid | backend_start | query_start | state_change | datname | usename | client_addr
------+-----+---------------+-------------+--------------+---------+---------+-------------
(0 rows)
The user duke’s session has been terminated!
yugabyte=> SELECT 1;
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
Note that in a distributed database like YugabyteDB, where connections could be directed to any node in the cluster, you’ll need to monitor all nodes and execute the procedure on each one to eliminate stale connections across the entire cluster.
Have Fun!
